Обсуждение: BUG #9198: psql -c 'SET; ...' not working
The following bug has been logged on the website: Bug reference: 9198 Logged by: Christoph Berg Email address: christoph.berg@credativ.de PostgreSQL version: 9.3.2 Operating system: Linux Description: This seems to be a bug: # alter role cbe set statement_timeout = '1s'; psql -c "SHOW statement_timeout" statement_timeout ------------------- 1s psql -c "SET statement_timeout = '3s'; SHOW statement_timeout" statement_timeout ------------------- 3s 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.
Re: To pgsql-bugs@postgresql.org 2014-02-12 <20140212125626.2710.94912@wrig= leys.postgresql.org> > time psql -c "SET statement_timeout =3D '3s'; SELECT pg_sleep(2)" > ERROR: canceling statement due to statement timeout > real 0m1.065s >=20 > 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. >=20 > 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 --=20 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
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.
>=20
> 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
>=20
> echo "SET statement_timeout =3D '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 =3D 0; SELECT ...'</litera=
l>
! will not work as expected. (Use <literal>echo '...' | psql</liter=
al>
! 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=FC=DFen,
Christoph Berg
--=20
Senior Berater, Tel.: +49 (0)21 61 / 46 43-187
credativ GmbH, HRB M=F6nchengladbach 12080, USt-ID-Nummer: DE204566209
Hohenzollernstr. 133, 41061 M=F6nchengladbach
Gesch=E4ftsf=FChrung: Dr. Michael Meskes, J=F6rg 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 =3D '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 =3D '3s'; SELECT pg_sleep(2)" | psql
>
>
How if psql could handle multiple "-c" commands, something like this:
psql -c "SET statement_timetout =3D '3s'" -c "SELECT pg_sleep(2)"
It wouldn't break existent applications and would make simpler to work on
scripts.
Best regards,
--=20
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br n=EDvel 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.
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.
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
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