Обсуждение: BUG #6117: psql -c does not work as expected. a documentation bug? a program bug?

The following bug has been logged online:

Bug reference:      6117
Logged by:          Aleksey Tsalolikhin
Email address:      atsaloli.tech@gmail.com
PostgreSQL version: 8.4.8
Operating system:   CentOS 5.5 (64-bit)
Description:        psql -c does not work as expected.  a documentation bug?
 a program bug?
Details:

The psql man page says, in the section for the -c option:

    If the command string contains multiple SQL
    commands, they are processed in a  single
    transaction

However when I run a command string with multiple
SQL commands, apparently only the last one is
processed.


For example:


# psql -U postgres -c  "select now();  select now(); select version();"
                                                     version

----------------------------------------------------------------------------
--------------------------------------
 PostgreSQL 8.4.8 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit
(1 row)

#

Yet this works fine:

# echo "select now();  select now(); select version();"|psql -f- -U postgres

              now
-------------------------------
 2011-07-13 13:51:01.594725-07
(1 row)

              now
-------------------------------
 2011-07-13 13:51:01.597903-07
(1 row)

                                                     version

----------------------------------------------------------------------------
--------------------------------------
 PostgreSQL 8.4.8 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit
(1 row)

#


This is what I expect to see.


Could you please fix psql or make the documentation for the -c option more
clear?

Thanks very much and thanks for a great product!

Yours truly,
Aleksey

On 14/07/2011 4:53 AM, Aleksey Tsalolikhin wrote:
> The following bug has been logged online:
>
> Bug reference:      6117
> Logged by:          Aleksey Tsalolikhin
> Email address:      atsaloli.tech@gmail.com
> PostgreSQL version: 8.4.8
> Operating system:   CentOS 5.5 (64-bit)
> Description:        psql -c does not work as expected.  a documentation bug?
>   a program bug?
> Details:
>
> The psql man page says, in the section for the -c option:
>
>      If the command string contains multiple SQL
>      commands, they are processed in a  single
>      transaction
>
> However when I run a command string with multiple
> SQL commands, apparently only the last one is
> processed.
>
What's happening is that all of them are processed, but only the output
for the last one is emitted. I agree that the documentation isn't clear
about that, and I'm not sure whether or not that was the intended result.

psql -c "CREATE TABLE test(x integer); INSERT INTO test(x) VALUES
(1),(2),(3); SELECT version(); SELECT * FROM test;"
  x
---
  1
  2
  3
(3 rows)


Comments anybody? Just fix the docs, or is this a behaviour issue?

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/

Craig Ringer <craig@postnewspapers.com.au> writes:
> What's happening is that all of them are processed, but only the output
> for the last one is emitted. I agree that the documentation isn't clear
> about that, and I'm not sure whether or not that was the intended result.
> ...
> Comments anybody? Just fix the docs, or is this a behaviour issue?

This is a byproduct of the fact that psql shoves the -c string out to
the backend as a single Query message, rather than splitting it at
semicolons and sending each command individually, which is what would
happen if you typed a multi-command line normally.

That has a bunch of indirect semantic implications, like the commands
all being done in one transaction and execution of the whole thing
always being abandoned after the first error.

We've talked many times about changing that, but always been too afraid
of breaking user scripts that rely on the current behavior.

We could possibly address Aleksey's complaint without changing the
behavior otherwise, but I'm not sure it's worth the trouble.  So I
vote for document-and-move-on.

            regards, tom lane

Document-and-move-on sounds fine to me.   Thank you!

Aleksey

On Thu, Jul 14, 2011 at 10:10:28AM +0800, Craig Ringer wrote:
> On 14/07/2011 4:53 AM, Aleksey Tsalolikhin wrote:
> >The following bug has been logged online:
> >
> >Bug reference:      6117
> >Logged by:          Aleksey Tsalolikhin
> >Email address:      atsaloli.tech@gmail.com
> >PostgreSQL version: 8.4.8
> >Operating system:   CentOS 5.5 (64-bit)
> >Description:        psql -c does not work as expected.  a documentation bug?
> >  a program bug?
> >Details:
> >
> >The psql man page says, in the section for the -c option:
> >
> >     If the command string contains multiple SQL
> >     commands, they are processed in a  single
> >     transaction
> >
> >However when I run a command string with multiple
> >SQL commands, apparently only the last one is
> >processed.
> >
> What's happening is that all of them are processed, but only the
> output for the last one is emitted. I agree that the documentation
> isn't clear about that, and I'm not sure whether or not that was the
> intended result.
>
> psql -c "CREATE TABLE test(x integer); INSERT INTO test(x) VALUES
> (1),(2),(3); SELECT version(); SELECT * FROM test;"
>  x
> ---
>  1
>  2
>  3
> (3 rows)
>
>
> Comments anybody? Just fix the docs, or is this a behaviour issue?

I have added documentation for this behavior.  Patch attached, and
backpatched to 9.2 and 9.1.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +