postgresql 延迟或等待声明

Delay or Wait-For Statement

提问by sharadov

I have a 500,000 line SQL script:

我有一个 500,000 行的 SQL 脚本：

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 10seconds every 50lines. Does pgsqlhave a waitforstatement like t-sql.

我想10每50行插入几秒钟的延迟。确实pgsql有waitfor像t-sql.

Thanks.

谢谢。

回答by pilcrow

Does pgsql have a waitfor statement like t-sql.

pgsql 有没有像 t-sql 那样的 waitfor 语句。

Yes, pg_sleep:

是的，pg_sleep：

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

(1 row)

回答by Thomas C. G. de Vilhena

You could call the pg_sleepfunction with the PERFORMstatement since we don't care about returning values:

您可以pg_sleep使用PERFORM语句调用函数，因为我们不关心返回值：

PERFORM pg_sleep(10);

回答by Sumit S

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 -->

正如 pilcrow 所说，这是最简单的方法。如果您不想看到返回结果，只需在像这样运行 pg_sleep 之前关闭显示 -->

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

回答by Jamie Love

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:

您可以在 shell 中做一些事情，通过一个简单的脚本将您的 SQL 用管道传输到 PostgreSQL。例如使用 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:

BTW：我看到你之前问过一个非常相似的问题。如果您能接受您发现解决问题的答案，那就太好了：

Begin...commit every 50 rows

开始...每 50 行提交一次