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.
Does pgsql have a waitfor statement like t-sql.
Yes, pg_sleep:
pg=> SELECT pg_sleep(10);
pg_sleep
----------
(1 row)
select data.id from (select id, pg_sleep(10) from sometable) AS data;You could call the pg_sleep function with the PERFORM statement since we don't care about returning values:
PERFORM pg_sleep(10);
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');
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
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:
