Delay or Wait-For Statement

Question

I have a 500,000 line SQL script:

UPDATE users SET region_id = 9814746 WHERE id = 101 AND region_id IS null;
UPDATE users SET region_id = 9814731 WHERE id = 102 AND region_id IS null;
UPDATE users SET region_id = 3470676 WHERE id = 103 AND region_id IS null;

I want to INSERT a delay of 10 seconds every 50 lines. Does pgsql have a waitfor statement like t-sql.

Thanks.

score 105 · Accepted Answer · answered Aug 26 '09 at 03:42

105

Does pgsql have a waitfor statement like t-sql.

Yes, pg_sleep:

pg=> SELECT pg_sleep(10);
 pg_sleep 
----------

(1 row)

answered Aug 26 '09 at 03:42

pilcrow

56,591
13
94
135

If for some reason you want to run this statement as part of single select statement, you can use `select data.id from (select id, pg_sleep(10) from sometable) AS data;` – mouse_freak Mar 27 '23 at 11:09

score 29 · Answer 2 · answered Jun 27 '16 at 14:09

29

You could call the pg_sleep function with the PERFORM statement since we don't care about returning values:

PERFORM pg_sleep(10);

answered Jun 27 '16 at 14:09

Thomas C. G. de Vilhena

13,819
3
50
44

5

@dannymo This should work: `DO $$ BEGIN PERFORM pg_sleep(1); END $$;` – Martin May 14 '18 at 12:47
what specifically `perform` does? – T.S. Jun 15 '23 at 22:32

score 3 · Answer 3 · edited Feb 09 '23 at 05:24

3

You can use pg_sleep() and pg_sleep_for() to delay 10 seconds as shown below according to Delaying Execution:

SELECT pg_sleep(10);

SELECT pg_sleep_for('10 seconds');

SELECT pg_sleep_for('10 second');

In addition, you can use pg_sleep_until() to delay until some time as shown below:

SELECT pg_sleep_until('today 21:45');

SELECT pg_sleep_until('tomorrow 03:00');

edited Feb 09 '23 at 05:24

Henry Ecker

34,399
18
41
57

answered Jan 05 '23 at 12:48

Super Kai - Kazuya Ito

22,221
10
124
129

score 0 · Answer 4 · edited May 23 '17 at 11:45

0

Not to my knowledge.

You could do something in the shell, piping your SQL through a simple script and then into PostgreSQL. E.g. with Perl:

cat regionupdates.sql | perl -e '$i = 1; while(<STDIN>) { $i++; print $_; if ($i % 50 == 0) { sleep 10; } }' | psql -d MYDB -L output.txt

BTW: I see you asked a very similar question before. It would be nice if you could accept the answers you found solved your problem:

Begin...commit every 50 rows

edited May 23 '17 at 11:45

Community

1
1

answered Aug 25 '09 at 23:26

Jamie Love

5,418
6
30
33

I included the cat as the source of the SQL may quite easily not be a file, but be generated by another program or shell script, so the structure of the command could easily be altered. But I take your point in general. – Jamie Love Aug 26 '09 at 23:14
Hmm, not sure why you got the downvote -- stylistic uses of cat aside. ;-) If I had an upvote for every time I filtered SQL statements through perl/awk/you-name-it to get the behavior I wanted, I'd be Jon Skeet. +1 – pilcrow Aug 27 '09 at 14:40

score 0 · Answer 5 · answered Mar 27 '20 at 13:57

0

As pilcrow says that is the easiest way. If you do not want to see the results back, just turn off display before running pg_sleep like this -->

\pset tuples_only on
select pg_sleep(10) ;
\pset tuples_only off

answered Mar 27 '20 at 13:57

Sumit S

516
5
17

Delay or Wait-For Statement

5 Answers5

Linked