Postgresql – Rate-limiting query execution in a batch process

postgresqlpsqlsleep

I have a set of queries in my queries.sql file which I am feeding to psql for execution. But I don't want them to run instantly one after the other: I would like to set some sort of delay – I was looking at \watch and query buffer but I am not so sure how to proceed. I would prefer not to write PG_SLEEP after each of my queries.

Best Answer

\watch executes the query buffer (one statement). It doesn't loop through different statements.

\set title "EVAN SAYS HAI"
SELECT 'HELLO WORLD' AS "Evan Greets the Plebs";
\watch

Now move that window over to your fifth monitor and when the boss comes by pretend like you're waiting for it to change.

If you want to put a delay in between query executions, make a spool as @Craig Ringer said in the comments

Write a Python or Perl script with your list of queries and have it loop over them, sleeping after each execution. – Craig Ringer Mar 5 at 11:18

You can do it one line or whatever too..

echo -e "SELECT 1;\nSELECT 2;\nSELECT 3;" |
  perl -pe'$|=1; sleep 1;' |
  psql -d foo;

Related Solutions

Postgresql – Span single transaction over several sequential psql sessions

Maybe it would be easiest to run a psql in the background, with it set to execute stdin, and connect its stdin to a named pipe. Then you can continually push data into that pipe, and finally push "end; \quit". Something like:

#!/bin/sh

psql_pipe=/tmp/psql$$
mkfifo -m 600 $psql_pipe
psql < $psql_pipe &
exec 3>$psql_pipe
psql_pid=$!

echo "> Started psql (pid=$psql_pid) reading from $psql_pipe"
trap '
  kill $psql_pid
  rm -f $psql_pipe
' EXIT

echo "begin;" >&3

echo "select now();" >&3

sleep 2

echo "select now();" >&3

sleep 2

echo "end; \quit" >&3

wait $psql_pid

Note that you can't simply do echo "sql" >$psql_pipe since the EOF would be transmitted to psql, which would then exit early-- the shell script has to keep its fd open.

PostgreSQL Meta Commands – How to Automatically Set Meta Commands When Starting psql

You are looking for the .psqlrc file. Per documentation:

psqlrc and ~/.psqlrc

Unless it is passed an -X or -c option, psql attempts to read and execute commands from the system-wide startup file (psqlrc) and then the user's personal startup file (~/.psqlrc), after connecting to the database but before accepting normal commands. These files can be used to set up the client and/or the server to taste, typically with \set and SET commands.

Best Answer

Related Solutions

Postgresql – Span single transaction over several sequential psql sessions

PostgreSQL Meta Commands – How to Automatically Set Meta Commands When Starting psql

Related Question