psql -c 'select * from pg_class; select * from no_such_table;'

Shouldn't this at least give me the result of the first select before
aborting the second?  Moreover, shouldn't

psql -c 'select * from no_such_table; select * from pg_class;'

give me the result of the second select?

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Sat, 26 May 2001 16:23:13 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Parser abort ignoring following commands
Peter Eisentraut <peter_e@gmx.net> writes:
> psql -c 'select * from pg_class; select * from no_such_table;'
> Shouldn't this at least give me the result of the first select before
> aborting the second?

The behavior you are complaining of is not the backend's fault.
The reason it acts that way is that psql is feeding the entire -c 
string to the backend as one query, and not paying any attention
to the possibility that multiple query results might be available
from the string.

It's a little bit inconsistent that psql feeds a -c string to the
backend as one query, whereas the same input line fed to it from a file
or terminal would be split into per-statement queries.  However,
I'd vote against changing it, since (a) you might break existing
applications that depend on this behavior, and (b) if you do that then
there will be no way to exercise multi-statement query strings from
psql.

It might possibly make sense for psql to stop using PQexec() but
instead use PQsendQuery() and PQgetResult(), so that it could
handle multiple results coming back from a single query string.
If you did that then the first example would work the way you want.

			regards, tom lane

Sat, 26 May 2001 11:12:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Parser abort ignoring following commands 
Tom Lane writes:

> Peter Eisentraut <peter_e@gmx.net> writes:
> > psql -c 'select * from pg_class; select * from no_such_table;'
> > Shouldn't this at least give me the result of the first select before
> > aborting the second?
>
> The behavior you are complaining of is not the backend's fault.
> The reason it acts that way is that psql is feeding the entire -c
> string to the backend as one query, and not paying any attention
> to the possibility that multiple query results might be available
> from the string.

No, I think there is another problem.  How about something without
selects:

$ psql -c 'delete from pk; delete from xx;'
ERROR:  Relation 'xx' does not exist

"pk" exists, but nothing is deleted.

$ psql -c 'drop user joe; drop user foo;'
ERROR:  DROP USER: user "foo" does not exist

User "joe" exists, but it is not dropped.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Sat, 26 May 2001 17:31:28 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Parser abort ignoring following commands 
Peter Eisentraut <peter_e@gmx.net> writes:
> No, I think there is another problem.  How about something without
> selects:

> $ psql -c 'delete from pk; delete from xx;'
> ERROR:  Relation 'xx' does not exist

> "pk" exists, but nothing is deleted.

Sure, because the transaction is rolled back.  The whole string
is executed in one transaction.  You will definitely break existing
applications if you change that.

			regards, tom lane

Sat, 26 May 2001 11:36:54 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Parser abort ignoring following commands 
Tom Lane writes:

> Peter Eisentraut <peter_e@gmx.net> writes:
> > No, I think there is another problem.  How about something without
> > selects:
>
> > $ psql -c 'delete from pk; delete from xx;'
> > ERROR:  Relation 'xx' does not exist
>
> > "pk" exists, but nothing is deleted.
>
> Sure, because the transaction is rolled back.  The whole string
> is executed in one transaction.  You will definitely break existing
> applications if you change that.

Applications that rely on this behaviour are broken.  It was always said
that statements are in their own transaction block unless in an explicit
BEGIN/COMMIT block.  A statement is defined to end at the semicolon, not
at the end of the string you submit to PQexec().

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Sat, 26 May 2001 17:57:16 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Parser abort ignoring following commands 
Peter Eisentraut <peter_e@gmx.net> writes:
>> Sure, because the transaction is rolled back.  The whole string
>> is executed in one transaction.  You will definitely break existing
>> applications if you change that.

> Applications that rely on this behaviour are broken.  It was always said
> that statements are in their own transaction block unless in an explicit
> BEGIN/COMMIT block.  A statement is defined to end at the semicolon, not
> at the end of the string you submit to PQexec().

Au contraire: single query strings have always been executed as single
transactions.  Whether that would be the most consistent behavior in a
green field is quite irrelevant.  We *cannot* change it now, or we will
break existing applications --- silently.

If you can find something in the documentation that states what you
claim is the definition, I'll gladly change it ;-).

			regards, tom lane

Sat, 26 May 2001 12:14:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Parser abort ignoring following commands 
Peter Eisentraut wrote:
> 
> Tom Lane writes:
> 
> > Peter Eisentraut <peter_e@gmx.net> writes:
> > > No, I think there is another problem.  How about something without
> > > selects:
> >
> > > $ psql -c 'delete from pk; delete from xx;'
> > > ERROR:  Relation 'xx' does not exist
> >
> > > "pk" exists, but nothing is deleted.
> >
> > Sure, because the transaction is rolled back.  The whole string
> > is executed in one transaction.  You will definitely break existing
> > applications if you change that.
> 
> Applications that rely on this behaviour are broken.  It was always said
> that statements are in their own transaction block unless in an explicit
> BEGIN/COMMIT block.  A statement is defined to end at the semicolon, not
> at the end of the string you submit to PQexec().

I guess that this is a multi-command statement ?

It has always been so, except that psql seems to do some parsing and
issue 
each command to backend separately.

----------------
Hannu

Sat, 26 May 2001 23:50:58 +0500
Hannu Krosing <hannu@tm.ee>
Re: Parser abort ignoring following commands
On Sat, May 26, 2001 at 05:57:16PM +0200, Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > Peter Eisentraut <peter_e@gmx.net> writes:
> > > No, I think there is another problem.  How about something without
> > > selects:
> >
> > > $ psql -c 'delete from pk; delete from xx;'
> > > ERROR:  Relation 'xx' does not exist
> >
> > > "pk" exists, but nothing is deleted.
> >
> > Sure, because the transaction is rolled back.  The whole string
> > is executed in one transaction.  You will definitely break existing
> > applications if you change that.
> 
> Applications that rely on this behaviour are broken.  It was always said
> that statements are in their own transaction block unless in an explicit
> BEGIN/COMMIT block.  A statement is defined to end at the semicolon, not
> at the end of the string you submit to PQexec().

	You put semicolons at the end of your strings to PQexec()?

-- 
Adam Haberlach            | At either end of the social spectrum there lies
adam@newsnipple.com       | a leisure class.  -- Eric Beck 1965 
http://www.newsnipple.com |
'88 EX500    '00 >^<      | http://youlook.org

Sat, 26 May 2001 19:00:44 -0700
Adam Haberlach <adam@newsnipple.com>
Re: Parser abort ignoring following commands