BUG #9198: psql -c 'SET; ...' not working

Re: To pgsql-bugs@postgresql.org 2014-02-12 <20140212125626.2710.94912@wrigleys.postgresql.org>

time psql -c "SET statement_timeout = '3s'; SELECT pg_sleep(2)"
ERROR: canceling statement due to statement timeout
real 0m1.065s

As witnessed by the time output, the timeout is the one from ALTER ROLE, not
the new one. Seen on 9.2 and 9.3.

The psql docs mention that -c is treated differently from stdin when
multiple commands are executed, but that shouldn't include SET not getting
into effect.

The same problem occurs with statement_timeout from postgresql.conf
and from PGOPTIONS.

Christoph
--
cb@df7cb.de | http://www.df7cb.de/

christoph.berg@credativ.de writes:

This seems to be a bug:

time psql -c "SET statement_timeout = '3s'; SELECT pg_sleep(2)"
ERROR: canceling statement due to statement timeout
real 0m1.065s

The reason this isn't a bug is that a -c command string is sent to the
server as a single statement (PQexec call), and what "statement timeout"
controls is the total time for the whole thing. The SET operation can't
change the already-running timer for the current statement. It would
affect the timeout for the next statement ... but there won't be one.

Many people have complained that it's unintuitive that -c works this way
rather than breaking up the string into multiple submissions the same way
psql would do with normal input. We're afraid to change it for fear of
breaking applications, though. If you want behavior more like psql's
normal operation, consider

echo "SET statement_timeout = '3s'; SELECT pg_sleep(2)" | psql

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: Tom Lane 2014-02-12 <25094.1392219652@sss.pgh.pa.us>

The reason this isn't a bug is that a -c command string is sent to the
server as a single statement (PQexec call), and what "statement timeout"
controls is the total time for the whole thing. The SET operation can't
change the already-running timer for the current statement. It would
affect the timeout for the next statement ... but there won't be one.

Many people have complained that it's unintuitive that -c works this way
rather than breaking up the string into multiple submissions the same way
psql would do with normal input. We're afraid to change it for fear of
breaking applications, though. If you want behavior more like psql's
normal operation, consider

echo "SET statement_timeout = '3s'; SELECT pg_sleep(2)" | psql

I think the docs don't really say that. The psql manpage says "single
transaction", but the problem here is rather "single command". I see
that "fixing" this would break the "one transaction" part, so it's
going to stay that way, but I'd propose something like this doc
update:

*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*************** PostgreSQL documentation
*** 100,106 ****
         string to divide it into multiple transactions.  This is
         different from the behavior when the same string is fed to
         <application>psql</application>'s standard input.  Also, only
!        the result of the last SQL command is returned.
        </para>
        </listitem>
      </varlistentry>
--- 100,111 ----
         string to divide it into multiple transactions.  This is
         different from the behavior when the same string is fed to
         <application>psql</application>'s standard input.  Also, only
!        the result of the last SQL command is returned.  <command>SET</>
!        commands that modify statement behavior will be ineffective because
!        they are part of the already running statement.  Most notably,
!        <literal>psql -c 'SET statement_timeout = 0; SELECT ...'</literal>
!        will not work as expected.  (Use <literal>echo '...' | psql</literal>
!        as above instead.)
        </para>
        </listitem>
      </varlistentry>

I'm actually unsure if there's more SETs that have this surprising
behavior, if statement_timeout is the only one, psql(1) should mention
that instead of what I wrote in the patch.

Mit freundlichen Grüßen,
Christoph Berg
--
Senior Berater, Tel.: +49 (0)21 61 / 46 43-187
credativ GmbH, HRB Mönchengladbach 12080, USt-ID-Nummer: DE204566209
Hohenzollernstr. 133, 41061 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer
pgp fingerprint: 5C48 FE61 57F4 9179 5970 87C6 4C5A 6BAB 12D2 A7AE

On Wed, Feb 12, 2014 at 1:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

christoph.berg@credativ.de writes:

This seems to be a bug:

time psql -c "SET statement_timeout = '3s'; SELECT pg_sleep(2)"
ERROR: canceling statement due to statement timeout
real 0m1.065s

Many people have complained that it's unintuitive that -c works this way
rather than breaking up the string into multiple submissions the same way
psql would do with normal input. We're afraid to change it for fear of
breaking applications, though. If you want behavior more like psql's
normal operation, consider

echo "SET statement_timeout = '3s'; SELECT pg_sleep(2)" | psql

How if psql could handle multiple "-c" commands, something like this:

psql -c "SET statement_timetout = '3s'" -c "SELECT pg_sleep(2)"

It wouldn't break existent applications and would make simpler to work on
scripts.

Best regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

Matheus de Oliveira wrote

How if psql could handle multiple "-c" commands, something like this:

psql -c "SET statement_timetout = '3s'" -c "SELECT pg_sleep(2)"

It wouldn't break existent applications and would make simpler to work on
scripts.

I'm dubious this would be much of a realistic improvement in ease-of-use -
at least in the Linux/bash world where you are much better off constructing
some form of here-doc - and passing that in via standard input - if you need
to make use of multiple statements and usually want white-space to make
reading/maintaining those statements easier.

Not saying such an implementation wouldn't be accepted but it isn't the most
novel of ideas and it hasn't piqued anyone's interest enough to implement
thus far....

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-9198-psql-c-SET-not-working-tp5791581p5791870.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Tom Lane-2 wrote

The reason this isn't a bug is that a -c command string is sent to the
server as a single statement (PQexec call), and what "statement timeout"
controls is the total time for the whole thing. The SET operation can't
change the already-running timer for the current statement. It would
affect the timeout for the next statement ... but there won't be one.

The wording of the documentation implies, though, that a single statement
has component statements that are independently subject to transaction
semantics.

"If the command string contains multiple SQL commands, they are processed in
a single transaction, unless there are explicit BEGIN/COMMIT commands
included in the string to divide it into multiple transactions."

Is there any support for making these component statements also operate on
their own timeout timers? This is not that infrequent a question and at
minimum the documentation could be more clear. That people are wanting to
do this means that enhancement is something to consider as well - though not
something that strikes me as being that useful generally.

I am thinking something like "substatement_timeout" that if unset would
resolve to the current value of "statement_timeout" but if set would cause
all subsequent sub-statements to operate on that timer while the entire
super-statement would continue to operate on the original timer.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-9198-psql-c-SET-not-working-tp5791581p5791873.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Christoph Berg <christoph.berg@credativ.de> writes:

... I'd propose something like this doc update:

<application>psql</application>'s standard input. Also, only
! the result of the last SQL command is returned. <command>SET</>
! commands that modify statement behavior will be ineffective because
! they are part of the already running statement. Most notably,
! <literal>psql -c 'SET statement_timeout = 0; SELECT ...'</literal>
! will not work as expected. (Use <literal>echo '...' | psql</literal>
! as above instead.)

This is incorrect though; most variables you can set via SET actually
will work unsurprisingly in this context. statement_timeout is different
because its value is only inspected at the start of a statement (where
"statement" is defined as "query string received from the client").

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

David Johnston <polobo@yahoo.com> writes:

I'm dubious this would be much of a realistic improvement in ease-of-use -
at least in the Linux/bash world where you are much better off constructing
some form of here-doc - and passing that in via standard input - if you need
to make use of multiple statements and usually want white-space to make
reading/maintaining those statements easier.

Yeah. The psql man page fails to suggest here-documents in this context,
which seems like rather an oversight. Perhaps what we should do is add
something like this to the description of -c:

Because of these legacy behaviors, passing more than one command to -c
often has unexpected results. It's better to feed multiple commands
to psql's standard input, either using "echo" as illustrated above,
or via a shell here-document, for example

psql <<EOF
\x
SELECT * FROM foo
EOF

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs