0

I would expect that the following code completes:

SET statement_timeout = 30000;
DO language plpgsql $$
DECLARE
  min_id integer := 1;
  max_id integer := 300;
BEGIN  
  FOR index IN min_id..max_id LOOP
    PERFORM pg_sleep(1);
    COMMIT;
    RAISE NOTICE '%', index;
  END LOOP;
END;
$$;

However, it times out.

NOTICE:  1
NOTICE:  2
NOTICE:  3
NOTICE:  4
NOTICE:  5
NOTICE:  6
NOTICE:  7
NOTICE:  8
NOTICE:  9
NOTICE:  10
NOTICE:  11
NOTICE:  12
NOTICE:  13
NOTICE:  14
NOTICE:  15
NOTICE:  16
NOTICE:  17
NOTICE:  18
NOTICE:  19
NOTICE:  20
NOTICE:  21
NOTICE:  22
NOTICE:  23
NOTICE:  24
NOTICE:  25
NOTICE:  26
NOTICE:  27
NOTICE:  28
NOTICE:  29
ERROR:  canceling statement due to statement timeout
CONTEXT:  SQL statement "SELECT pg_sleep(1)"
PL/pgSQL function inline_code_block line 7 at PERFORM

It appears that a DO block is being treated as a statement, despite it committing state inside of the procedure.

What's the appropriate setting that would prevent long running statements but allow a DO block to continue beyond that timeout?

The reason I need this is because we use DO blocks to make large updates in such a way that does not allow xmin to stay behind, and as far as I understand, committing state inside of a DO procedure prevents it.

I realize that I can increase the timeout inside of the DO block. However, it is not clear to me why that is even necessary, given that the actual statement (as I understand it) is being committed every second.

Gajus
  • 69,002
  • 70
  • 275
  • 438
  • It does not. It would extend the timeout of the DO produce. However, I am confused why the timeout applies to DO procedure to begin with since (as far as I understand) it is not a statement. – Gajus May 08 '23 at 18:00
  • The linked [answer over at dba.SE](https://dba.stackexchange.com/a/83035/188406) explains "*The way `statement_timeout` works, the time starts counting when the server receives a new command from the client. Queries launches inside server-side functions are not commands from a client, they don't reset that timer or push a new one onto a stack of timers.*" The same would apply to a `DO` statement I guess – Bergi May 08 '23 at 18:26
  • Thanks @Bergi. This is counter intuitive given how procedures can execute transaction control statements. However, as it is, it appears that the only viable workaround is to leverage dblink such as in this [example](https://stackoverflow.com/a/22163648/368691). – Gajus May 08 '23 at 19:03

0 Answers0