Thread: Transaction aborts on syntax error.

Hello,

Is is possible to change the transaction behaviour not to abort when a
syntax error occurs.

I've done some searches on the list, and have not found anything.

-ESR-

On Fri, Jan 30, 2004 at 07:43:06AM -0800, Edwin S. Ramirez wrote:

> Is is possible to change the transaction behaviour not to abort when a
> syntax error occurs.

Not currently.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"And as an added bonus, now my computer goes to the toilet for me, leaving me
free to spend time on more useful activities! yay slug codefests!" (C. Parker)

Edwin S. Ramirez wrote:
> Hello,
> 
> Is is possible to change the transaction behaviour not to abort when a
> syntax error occurs.
> 
> I've done some searches on the list, and have not found anything.

No, we need nested transactions for that.  We are working on it or at
least have a plan.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:

> Edwin S. Ramirez wrote:
> > Hello,
> > 
> > Is is possible to change the transaction behaviour not to abort when a
> > syntax error occurs.
> > 
> > I've done some searches on the list, and have not found anything.
> 
> No, we need nested transactions for that.  We are working on it or at
> least have a plan.

I'm not clear why nested transactions are necessary. Oracle certainly doesn't
require dealing with nested transactions to get this, and its been a long time
but I don't recall MSSQL doing anything like that either. If I recall
correctly they both do this by automatically by default.

I can see the rationale for aborting a transaction after a syntax error in an
application where syntax errors are a sign of a problem. And I could see how
nested transactions would be a good tool to deal with that.

But why does the database enforce that every syntax error *requires* a
transaction roll back? Shouldn't that be up to the application to decide?
Perhaps the syntax error is for a known reason and the application would be
fine with committing the previous changes or performing an alternate query.

In interactive use in particular the "application", actually the user, likely
knows that the syntax error doesn't indicate any problem with the transaction
at all. The user could see an error message and fix the query and repeat it
himself without having to invoke any special commands to begin and roll back a
nested transaction. Humans are good at things like that.

I think this is as simple as an "interactive" or "manual error rollback"
option that would make syntax errors not cause a transaction to fail at all.
They could simply be ignored. Pretty much any database query that didn't cause
any incomplete writes could be treated this way.

When I used Oracle the fact that every sqlplus session was always in
autocommit-off mode was oftentimes a lifesaver. I would do major database
updates, then do several selects to verify that everything went as planned
before committing.

In postgres that's not feasible. I would have to remember before beginning to
type "BEGIN". Then as soon as I make a typo on one of those selects the whole
update has to be rolled back and done again. Nested transactions would make it
possible, but still not automatic. It would only work if I think in advance to
start nested transactions, and then I would have to tediously roll back the
nested transaction and start a new one for every typo.

I think the typo -> transaction rollback implication fails the least surprise
principle. And nested transactions are a red herring. While they would be a
useful tool for dealing with this situation programmatically, they shouldn't
be necessary for dealing with it when a human is at the console.

-- 
greg

On Mon, 8 Feb 2004, Greg Stark wrote:

>
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>
> > Edwin S. Ramirez wrote:
> > > Hello,
> > >
> > > Is is possible to change the transaction behaviour not to abort when a
> > > syntax error occurs.
> > >
> > > I've done some searches on the list, and have not found anything.
> >
> > No, we need nested transactions for that.  We are working on it or at
> > least have a plan.
>
> I'm not clear why nested transactions are necessary. Oracle certainly doesn't
> require dealing with nested transactions to get this, and its been a long time
> but I don't recall MSSQL doing anything like that either. If I recall
> correctly they both do this by automatically by default.
>
> I can see the rationale for aborting a transaction after a syntax error in an
> application where syntax errors are a sign of a problem. And I could see how
> nested transactions would be a good tool to deal with that.

Its not that there's a rationale behind it. Rather, the existing error
handling code *has* to abort the current transaction because an error has
taken place. In a multi statement transaction block (ie, BEGIN; ...; ...;
... COMMIT;) each statement piggy backs on onto the whole transaction.
Because we're aborted one query, we've aborted them all.

With nested transactions, every query within a transaction block could be
run within its own (sub)transaction. The backend could be jigged so
that if parse errors occur, we abort the second level transaction and roll
back to the start point at the moment before the error generating
statement took place. This keeps the rest of the queries executed in the
transaction block in place

> When I used Oracle the fact that every sqlplus session was always in
> autocommit-off mode was oftentimes a lifesaver. I would do major database
> updates, then do several selects to verify that everything went as planned
> before committing.
>
> In postgres that's not feasible. I would have to remember before beginning to
> type "BEGIN". Then as soon as I make a typo on one of those selects the whole
> update has to be rolled back and done again. Nested transactions would make it
> possible, but still not automatic. It would only work if I think in advance to
> start nested transactions, and then I would have to tediously roll back the
> nested transaction and start a new one for every typo.
>


In psql: \set AUTOCOMMIT off

Gavin

Greg Stark <gsstark@mit.edu> writes:
> But why does the database enforce that every syntax error *requires* a
> transaction roll back?

PG enforces that every error requires a transaction abort.  Period, full
stop.  Picking and choosing which errors might not really require a
rollback involves a level of detailed code-behavior analysis (and
consequent fragility in the face of changes) that no one has wanted to
undertake.

As an example: "SELECT * FROM foo" where foo doesn't exist will result
in a 'not found' error reported from somewhere in the catalog lookup
code.  Fine, we probably wouldn't need a transaction abort to clean up
after that.  But there are a ton of error cases right next door in that
same code that we'd better do an abort to clean up after --- deadlocks,
corrupted data structures, who knows what.  Even 'not found' is
problematic if the elog-induced longjmp occurs at a point where we're
holding some locks or other resources that need to be released.

What it comes down to is that a lot of code in the backend assumes that
transaction abort can be relied on to do any post-elog cleanup needed,
such as releasing locks or reclaiming leaked memory.  I don't think we
can afford to give up that assumption; the costs in code complexity and
instability would be horrific.  What we have to do is generalize the
abort cleanup code so it can handle partial rollbacks as well as
complete ones.  Thus "nested transactions" is really a shorthand for
this problem of post-error cleanup.

> And nested transactions are a red herring.

You seem to think this is being driven by user-interface issues.  It's
an implementation necessity.
        regards, tom lane

Hi all,

I am not sure if this should be sent to this mailing list. If i am wrong
could someone please direct me to the correct one so I can subscribe
there.

I wanted to ask a simple question. Say I have a table with the timestamp
field. What is the best way to say get all the records that were created
say 2 hours before the query. One of the options would be to generate the
timestamp in the correct format and then send a query in the format 
SELECT * from <table> where create_time < <generated_timestamp>

Is there a better way than this?

Any help would be greatly appreciated,
Regards,
Slavisa

> I wanted to ask a simple question. Say I have a table with the timestamp
> field. What is the best way to say get all the records that were created
> say 2 hours before the query. One of the options would be to generate the
> timestamp in the correct format and then send a query in the format 
> SELECT * from <table> where create_time < <generated_timestamp>
> 
> Is there a better way than this?

Sure is:

SELECT * from <table> where create_time < (CURRENT_TIMESTAMP - INTERVAL 
'2 hours');

Chris

Tom Lane <tgl@sss.pgh.pa.us> writes:

> What it comes down to is that a lot of code in the backend assumes that
> transaction abort can be relied on to do any post-elog cleanup needed,
> such as releasing locks or reclaiming leaked memory.  I don't think we
> can afford to give up that assumption; the costs in code complexity and
> instability would be horrific.  What we have to do is generalize the
> abort cleanup code so it can handle partial rollbacks as well as
> complete ones.  Thus "nested transactions" is really a shorthand for
> this problem of post-error cleanup.

So you picture the backend automatically introducing a mini-nested-transaction
for every request and automatically rolling that back on any error. So the
application or user wouldn't have to do anything to continue processing
ignoring the error?

-- 
greg

Greg Stark <gsstark@mit.edu> writes:
> So you picture the backend automatically introducing a mini-nested-transaction
> for every request and automatically rolling that back on any error. So the
> application or user wouldn't have to do anything to continue processing
> ignoring the error?

You're assuming a bunch of facts not in evidence about how we choose to
present this functionality to clients, including a rather dubious
assumption that we'd choose to break backward compatibility.

My guess is that there will be some way to get the above behavior
(possibly implemented by client-library code rather than the backend),
but that it won't be the default.
        regards, tom lane

Gavin Sherry wrote:

> Its not that there's a rationale behind it. Rather, the existing error
>
>handling code *has* to abort the current transaction because an error has
>taken place. In a multi statement transaction block (ie, BEGIN; ...; ...;
>... COMMIT;) each statement piggy backs on onto the whole transaction.
>Because we're aborted one query, we've aborted them all.
>
>With nested transactions, every query within a transaction block could be
>run within its own (sub)transaction. The backend could be jigged so
>that if parse errors occur, we abort the second level transaction and roll
>back to the start point at the moment before the error generating
>statement took place. This keeps the rest of the queries executed in the
>transaction block in place
>  
>
Who is currently working on this [nested transactions] and what 
specifically needs to be done at this point?
This is a major bug which greatly diminishes the confidence of my 
co-workers in postgresql.  I don't
don't have a wealth of knowledge about RDBMS implementations.  How can I 
best contribute to
solve this problem?

Andrej

>Andrej Czapszys
> This is a major bug which greatly diminishes the confidence of my
> co-workers in postgresql. 

This is NOT a bug. Transactional robustness is important and PostgreSQL
has a very strict implementation in this area.


>Greg Stark
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Edwin S. Ramirez wrote:
> > > Is is possible to change the transaction behaviour not to abort
when a
> > > syntax error occurs.
> > >
> > > I've done some searches on the list, and have not found anything.
> >
> > No, we need nested transactions for that.  We are working on it or
at
> > least have a plan.
> 
> I'm not clear why nested transactions are necessary. Oracle certainly
> doesn't
> require dealing with nested transactions to get this, and its been a
long
> time
> but I don't recall MSSQL doing anything like that either. If I recall
> correctly they both do this by automatically by default.

Greg is correct I believe. DB2 also implements transactions in this way.

There are other databases which have historically implemented
Transaction control in this way, notably earlier versions of Teradata
RDBMS.

Most importantly, other references I have state that: the ANSI SQL-99
specification does require that if a statement errors then only that
statement's changes are rolled back. Control is returned to the calling
application to decide what to do. Some statement types are theoretically
retryable, such as those which have been evicted because of deadlock
errors, so this is a normal situation (on some rdbms!).

Having said that it's not a bug, I'm not sure exactly where it says it
behaves like this in the PostgreSQL manual. I've checked the ANSI SQL-99
unsupported features section and nothing springs out at me from there;
if anybody has a copy of the actual spec could they check on this, so we
can at least document carefully the current behaviour.

Archaeology aside :), I couldn't comment on whether implementing this in
PostgreSQL would require the equivalent of nested transaction behaviour.
If Bruce says so...

Also this makes me think there may be some investigation required into
XA two-phase commit behaviour regarding this point. Anybody?

Best regards, Simon Riggs

"Simon Riggs" <simon@2ndquadrant.com> writes:
> Most importantly, other references I have state that: the ANSI SQL-99
> specification does require that if a statement errors then only that
> statement's changes are rolled back.

No.  The spec says
        The execution of a <rollback statement> may be initiated implicitly        by an SQL-implementation when it
detectsunrecoverable errors.
 

and leaves it up to the implementation to define what is "unrecoverable".
Currently Postgres treats all errors as "unrecoverable".  This is
certainly not ideal, but it is within the letter of the spec.
        regards, tom lane

>Tom Lane
> "Simon Riggs" <simon@2ndquadrant.com> writes:
> > Most importantly, other references I have state that: the ANSI
SQL-99
> > specification does require that if a statement errors then only that
> > statement's changes are rolled back.

> > ...if anybody has a copy of the actual spec could they check on
this, so > > we can at least document carefully the current behaviour.

> 
> No.  The spec says
> 
>          The execution of a <rollback statement> may be initiated
> implicitly by an SQL-implementation when it detects unrecoverable
errors.
> 
> and leaves it up to the implementation to define what is
"unrecoverable".
> Currently Postgres treats all errors as "unrecoverable".  This is
> certainly not ideal, but it is within the letter of the spec.

Thanks for checking back to the spec, it's the only way.

Improving on "not ideal" would be good, and would get even closer to
full Oracle/SQLServer migration/compatibility. However, since I've never
looked at that section of code, I couldn't comment on any particular
approach nor implement such a change, so I'll shut up and be patient.

I've have tried to work out which section of the manual to document this
in. The most likely section would seem to be: doc/src/sgml/mvcc.sgml,
which is the Concurrency Control chapter of the User's Guide (on PDF).
I'd suggest including an extra sect1 section like this...either ahead of
or immediately behind the current Intro section
<sect1 id="txn-integrity">  <title>Transactional Integrity</title>

which would discuss:
- ACID compliance -  how PostgreSQL protects your data
- transactional semantics, as just discussed on this conversation

Overall, not much bigger than the current Intro

If nobody objects I'll bash out a change tomorrow night.

Best Regards, Simon Riggs

Simon Riggs wrote:
> >Tom Lane
> > "Simon Riggs" <simon@2ndquadrant.com> writes:
> > > Most importantly, other references I have state that: the ANSI
> SQL-99
> > > specification does require that if a statement errors then only that
> > > statement's changes are rolled back.
> 
> > > ...if anybody has a copy of the actual spec could they check on
> this, so > > we can at least document carefully the current behaviour.
> 
> > 
> > No.  The spec says
> > 
> >          The execution of a <rollback statement> may be initiated
> > implicitly by an SQL-implementation when it detects unrecoverable
> errors.
> > 
> > and leaves it up to the implementation to define what is
> "unrecoverable".
> > Currently Postgres treats all errors as "unrecoverable".  This is
> > certainly not ideal, but it is within the letter of the spec.
> 
> Thanks for checking back to the spec, it's the only way.
> 
> Improving on "not ideal" would be good, and would get even closer to
> full Oracle/SQLServer migration/compatibility. However, since I've never
> looked at that section of code, I couldn't comment on any particular
> approach nor implement such a change, so I'll shut up and be patient.

Imagine this:
BEGIN WORK;LOCK oldtab;CREATE_X TABLE newtab AS SELECT * FROM oldtab;DELETE oldtab;COMMIT

In this case, you would want the database to abort on a syntax error, right?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Simon Riggs wrote:
> I've have tried to work out which section of the manual to document this
> in. The most likely section would seem to be: doc/src/sgml/mvcc.sgml,
> which is the Concurrency Control chapter of the User's Guide (on PDF).
> I'd suggest including an extra sect1 section like this...either ahead of
> or immediately behind the current Intro section
> <sect1 id="txn-integrity">
>    <title>Transactional Integrity</title>
> 
> which would discuss:
> - ACID compliance -  how PostgreSQL protects your data
> - transactional semantics, as just discussed on this conversation
> 
> Overall, not much bigger than the current Intro
> 
> If nobody objects I'll bash out a change tomorrow night.

I would think a mention should go in the BEGIN WORK manual page.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

>> Improving on "not ideal" would be good, and would get even closer to
>> full Oracle/SQLServer migration/compatibility. However, since I've never
>> looked at that section of code, I couldn't comment on any particular
>> approach nor implement such a change, so I'll shut up and be patient.
>
> Imagine this:
>
>     BEGIN WORK;
>     LOCK oldtab;
>     CREATE_X TABLE newtab AS SELECT * FROM oldtab;
>     DELETE oldtab;
>     COMMIT
>
> In this case, you would want the database to abort on a syntax error, right?

Yeah, but in other db's this is solved by the frontend. e.g. in Informix
dbaccess has a mode that simply stops execution upon first error. So I don't
think this is a nogo argument, if we added such a feature to psql.

Imagine your script continuing with "insert into newtab ..." after the commit,
wouldn't you actually want that to not run eighter ?

Andreas

On Thu, Feb 12, 2004 at 09:55:36AM +0100, Zeugswetter Andreas SB SD wrote:
> 
> Yeah, but in other db's this is solved by the frontend. e.g. in Informix
> dbaccess has a mode that simply stops execution upon first error. So I don't 
> think this is a nogo argument, if we added such a feature to psql.
It does require that the application be meticulous in its checking though.
Existing client programs, for instance, may ignore any errors coming back
from PQexec() during the transaction and just see if the COMMIT succeeds.
Such could would break in very nasty ways with this change.


Jeroen

Zeugswetter Andreas SB SD wrote:
> 
> >> Improving on "not ideal" would be good, and would get even closer to
> >> full Oracle/SQLServer migration/compatibility. However, since I've never
> >> looked at that section of code, I couldn't comment on any particular
> >> approach nor implement such a change, so I'll shut up and be patient.
> >
> > Imagine this:
> > 
> >     BEGIN WORK;
> >     LOCK oldtab;
> >     CREATE_X TABLE newtab AS SELECT * FROM oldtab;
> >     DELETE oldtab;
> >     COMMIT
> > 
> > In this case, you would want the database to abort on a syntax error, right?
> 
> Yeah, but in other db's this is solved by the frontend. e.g. in Informix
> dbaccess has a mode that simply stops execution upon first error. So I don't 
> think this is a nogo argument, if we added such a feature to psql.

Stops execution on the first error?  What does that mean?  It means it
stops reading the rest of the command file?  We might be able to do
that (invalidate the entire session), but is that desired?

> Imagine your script continuing with "insert into newtab ..." after the commit, 
> wouldn't you actually want that to not run eighter ?

Oh, yea, that would be bad.  So you want to invalidate the entire
session on any error?  That could be done.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

"Jeroen T. Vermeulen" <jtv@xs4all.nl> writes:
> It does require that the application be meticulous in its checking though.
> Existing client programs, for instance, may ignore any errors coming back
> from PQexec() during the transaction and just see if the COMMIT succeeds.
> Such could would break in very nasty ways with this change.

I think it's a given that the *default* behavior will not change.
You'll have to do something --- at least set a SET variable --- to get
intratransaction error recovery to behave differently.  The risk of
breaking existing clients in subtle ways is too great if we do
otherwise.
        regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

> Imagine this:
> 
>     BEGIN WORK;
>     LOCK oldtab;
>     CREATE_X TABLE newtab AS SELECT * FROM oldtab;
>     DELETE oldtab;
>     COMMIT
> 
> In this case, you would want the database to abort on a syntax error, right?

Certainly not if I was typing this from the command line. Imagine the
frustration if the typo was in "DELETE oldtab" and the create statement took
hours.

I would want the application to receive the error in a clean API that provides
an option to automatically initiate a rollback whenever the client receives an
error.

In an application I would expect the database layer to provide a clean API to
catch the error. Preferably one making it hard to avoid aborting the
transaction and rolling back except intentionally. The best interface in most
languages is to throw an exception. In any case it's up to the application to
decide how to handle the error.

Tom's explanation of the implementation issues makes perfect sense. Though I
do wonder whether it would be possible to detect certain degenerate cases of
queries that haven't caused any database changes at all before they errored
out.

This wouldn't help if you do a "delete" that causes an error after deleting a
few thousand records, but it would catch the low hanging fruits of syntax
errors.

-- 
greg

Greg Stark wrote:
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
> > Imagine this:
> > 
> >     BEGIN WORK;
> >     LOCK oldtab;
> >     CREATE_X TABLE newtab AS SELECT * FROM oldtab;
> >     DELETE oldtab;
> >     COMMIT
> > 
> > In this case, you would want the database to abort on a syntax error, right?
> 
> Certainly not if I was typing this from the command line. Imagine the
> frustration if the typo was in "DELETE oldtab" and the create statement took
> hours.
> 
> I would want the application to receive the error in a clean API that provides
> an option to automatically initiate a rollback whenever the client receives an
> error.
> 
> In an application I would expect the database layer to provide a clean API to
> catch the error. Preferably one making it hard to avoid aborting the
> transaction and rolling back except intentionally. The best interface in most
> languages is to throw an exception. In any case it's up to the application to
> decide how to handle the error.
> 
> Tom's explanation of the implementation issues makes perfect sense. Though I
> do wonder whether it would be possible to detect certain degenerate cases of
> queries that haven't caused any database changes at all before they errored
> out.
> 
> This wouldn't help if you do a "delete" that causes an error after deleting a
> few thousand records, but it would catch the low hanging fruits of syntax
> errors.

I suppose we could have a SET that psql could set when it was
interactive and skip rollback on syntax errors, but that is pretty
exotic.  Also consider that other errors could abort a query aside from
syntax errors, like deadlocks.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Rod Taylor wrote:
> > > >     BEGIN WORK;
> > > >     LOCK oldtab;
> > > >     CREATE_X TABLE newtab AS SELECT * FROM oldtab;
> > > >     DELETE oldtab;
> > > >     COMMIT
> > > > 
> > > > In this case, you would want the database to abort on a syntax error, right?
> > > 
> > > Certainly not if I was typing this from the command line. Imagine the
> > > frustration if the typo was in "DELETE oldtab" and the create statement took
> > > hours.
> > 
> > I suppose we could have a SET that psql could set when it was
> > interactive and skip rollback on syntax errors, but that is pretty
> > exotic.  Also consider that other errors could abort a query aside from
> > syntax errors, like deadlocks.
> 
> Can this be done entirely on the client side?
> 
> Have psql silently wrap every statement going out with a BEGIN and a
> COMMIT or ROLLBACK depending on whether there was an error or not?
> 
> It depends on subtransactions but those are bound to appear eventually,
> and be infinitely more useful.

Yep, we could do it in the client like we do for autocommit.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

> > >     BEGIN WORK;
> > >     LOCK oldtab;
> > >     CREATE_X TABLE newtab AS SELECT * FROM oldtab;
> > >     DELETE oldtab;
> > >     COMMIT
> > > 
> > > In this case, you would want the database to abort on a syntax error, right?
> > 
> > Certainly not if I was typing this from the command line. Imagine the
> > frustration if the typo was in "DELETE oldtab" and the create statement took
> > hours.
> 
> I suppose we could have a SET that psql could set when it was
> interactive and skip rollback on syntax errors, but that is pretty
> exotic.  Also consider that other errors could abort a query aside from
> syntax errors, like deadlocks.

Can this be done entirely on the client side?

Have psql silently wrap every statement going out with a BEGIN and a
COMMIT or ROLLBACK depending on whether there was an error or not?

It depends on subtransactions but those are bound to appear eventually,
and be infinitely more useful.

>Bruce Momjian wrote
> Zeugswetter Andreas SB SD wrote:
> > 
> > >> Improving on "not ideal" would be good, and would get even closer
to
> > >> full Oracle/SQLServer migration/compatibility. However, since
I've
> never
> > >> looked at that section of code, I couldn't comment on any
particular
> > >> approach nor implement such a change, so I'll shut up and be
patient.
> > >
> > > Imagine this:
> > >
> > >     BEGIN WORK;
> > >     LOCK oldtab;
> > >     CREATE_X TABLE newtab AS SELECT * FROM oldtab;
> > >     DELETE oldtab;
> > >     COMMIT
> > >
> > > In this case, you would want the database to abort on a syntax
error,
> right?
> >
> > Yeah, but in other db's this is solved by the frontend. e.g. in
Informix
> > dbaccess has a mode that simply stops execution upon first error. So
I
> don't
> > think this is a nogo argument, if we added such a feature to psql.
> 
> Stops execution on the first error?  What does that mean?  It means it
> stops reading the rest of the command file?  We might be able to do
> that (invalidate the entire session), but is that desired?

I wouldn't want to stop execution on the first error. All of that
transaction stuff doesn't apply to batch execution of script files - the
script is being executed blind, so having a decision point mid-way thru
a transaction isn't that important. This thread wasn't originally about
psql behaviour, though if we divert in that direction....I could see a
use for:

\if error then quit "newtab create failed"
or something like that

or even:
\retry on    (with default=off)
to automatically submit an SQL statement if it fails with a retryable
error

Regards, Simon Riggs

 >>
In this case, you would want the database to abort on a syntax error, right?>>

Am I completely off thread to ask why HOW we allow an abort on syntax 
errors? (at least in regard to stored functions)
Shouldn't PostgreSQL do somethng intellignet like *notice* the syntax 
error in the stored function when it is saved and once again so somethng 
intellignet?

I don't know what PostgreSQL should do.

I know Oracle would mark the stored function as invalid and mark any 
stored function invalid if it called an invalid function. Its a 
fascinating cascade to watch in your IDE.

Rick

>Bruce Momjian
> Simon Riggs wrote:
> > >Tom Lane
> > > "Simon Riggs" <simon@2ndquadrant.com> writes:
> > > > Most importantly, other references I have state that: the ANSI
> > SQL-99
> > > > specification does require that if a statement errors then only
that
> > > > statement's changes are rolled back.
> >
> > > > ...if anybody has a copy of the actual spec could they check on
> > this, so > > we can at least document carefully the current
behaviour.
> >
> > >
> > > No.  The spec says
> > >
> > >          The execution of a <rollback statement> may be initiated
> > > implicitly by an SQL-implementation when it detects unrecoverable
> > errors.
> > >
> > > and leaves it up to the implementation to define what is
> > "unrecoverable".
> > > Currently Postgres treats all errors as "unrecoverable".  This is
> > > certainly not ideal, but it is within the letter of the spec.
> >
> > Thanks for checking back to the spec, it's the only way.
> >
> > Improving on "not ideal" would be good, and would get even closer to
> > full Oracle/SQLServer migration/compatibility. However, since I've
never
> > looked at that section of code, I couldn't comment on any particular
> > approach nor implement such a change, so I'll shut up and be
patient.
> 
> Imagine this:
> 
>     BEGIN WORK;
>     LOCK oldtab;
>     CREATE_X TABLE newtab AS SELECT * FROM oldtab;
>     DELETE oldtab;
>     COMMIT
> 
> In this case, you would want the database to abort on a syntax error,
> right?

I'm happy to discuss this further, though I do want to reiterate my very
first position, which is "its not a bug" and that I agree with Tom that
the transaction semantics are defensible as they stand. With that said,
please forgive the rather long winded explanation which I think is
necessary to go through this topic in required detail.

Overall, the database must end every transaction by either committing
all changes made during it, or rolling back all changes. That is part of
the ACID properties of a transaction. That part is not under discussion.

The transaction semantics *during* a transaction can be implemented in a
number of ways, yet in the end arrive at one of those two states.

In the example above, consider what will occur if the first and second
statements succeed and then the third statement fails:

In one style of transaction semantics, the third statement can fail but
the transaction does not abort (yet), control is returned to the
application to decide what to do. If the error is a "retryable" error,
such as those produced by a deadlock, then the application could decide
to retry the statement and if it works commit the transaction - no
re-execution of the first and second statement is required.

In the second style of transaction semantics, the failure of the third
statement causes the whole transaction, including all statements
previously executed to rollback, with no option to retry and continue.

In both cases, the transaction either commits or rollback occurs. No
other option is possible at the end of the transaction, but in the first
style of transaction semantics you get a "mid-way" decision point. This
only refers to retryable errors, since errors like access rights
violations and many other system errors aren't retryable. In the example
you give regarding a syntax error, that's non-retryable, so yes I
definitely do want the whole transaction rolled back.

For reference only, Oracle, SQLServer and DB2 implement the first style
- they give the option to retry. This is because historically, all of
these RDBMSs were prone to deadlock because they originally implemented
block or table level locking, before moving to their current level of
function. Since deadlocks were frequent when using block level locking
with OLTP style applications, it was important to conserve resources by
not requiring the whole transaction to be retried.

PostgreSQL uses the second style of transaction semantics. Teradata also
originally implemented only the second style, though now implements both
- which is how come I know this fairly obscure technical stuff.
(Teradata refers to the first style as "ANSI" transaction semantics,
though I am happy with Tom's reading of the standard.)

Anyone reading this who is worried now about PostgreSQL should not be -
transactions are very definitely watertight, no question. PostgreSQL's
transaction semantics are fine since with MVCC, very few deadlock
situations exist that aren't directly avoidable by good application
coding. The need for "retryable" statements is much reduced. The
functionality is not a bug, just the way it has been decided to
implement transaction semantics.

The only reason I have said PostgreSQL's behaviour is not ideal is that
it is different from the main commercial RDBMS and could cause some
porting annoyances in the error handling code of SQL applications - not
too much of a problem, as long as you know about this and are willing to
make some changes, hence the need for docs. My wish is to get close to
100% "application portability" in as many areas as possible (which
includes functionality such as PITR, which I know you are aware of my
interest in) - I do particularly appreciate the "it just works" approach
of PostgreSQL with significantly easier automated facilities and
advanced functionality.

I'll write up some man page notes as you suggest, though without the
long winded comparison of implementation techniques...

Best Regards, Simon Riggs

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Rod Taylor wrote:
>> Can this be done entirely on the client side?
>> 
>> Have psql silently wrap every statement going out with a BEGIN and a
>> COMMIT or ROLLBACK depending on whether there was an error or not?

> Yep, we could do it in the client like we do for autocommit.

Yeah, after more thought I think we probably want to insist that this be
driven off subtransaction BEGIN/COMMIT commands issued by the client.
Yesterday I suggested allowing a SET variable to change the behavior,
but I now realize that would be as bad a mistake as server-side
autocommit was: changing the installation default for such a variable
would break clients left and right.

So, whatever "error handling mode" conveniences we wish to put in should
be put in on the client side.
        regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Rod Taylor wrote:
> >> Can this be done entirely on the client side?
> >> 
> >> Have psql silently wrap every statement going out with a BEGIN and a
> >> COMMIT or ROLLBACK depending on whether there was an error or not?
> 
> > Yep, we could do it in the client like we do for autocommit.
> 
> Yeah, after more thought I think we probably want to insist that this be
> driven off subtransaction BEGIN/COMMIT commands issued by the client.
> Yesterday I suggested allowing a SET variable to change the behavior,
> but I now realize that would be as bad a mistake as server-side
> autocommit was: changing the installation default for such a variable
> would break clients left and right.
> 
> So, whatever "error handling mode" conveniences we wish to put in should
> be put in on the client side.

Added to TODO:
* Use nested transactions to prevent syntax errors from aborting  a transaction

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

> In both cases, the transaction either commits or rollback occurs. No
> other option is possible at the end of the transaction, but in the first
> style of transaction semantics you get a "mid-way" decision point. This
> only refers to retryable errors, since errors like access rights
> violations and many other system errors aren't retryable. In the example

You seem to ignore the fact, that a lot of errors (and I assume most of
the real world cases, where the appl actually reacts inside a transaction) are
"repared" by these applications by doing something else instead.

Like the application does an insert gets a duplicate key error and does an update
instead. Or it does an insert gets a foreign key constraint violation,
populates the foreign table and does the insert again. Note that this programming
practice is more efficient, than letting the appl check beforehand if the error cases
are seldom.

It seems to me, that leaving all this to the client (which implicitly inserts
savepoints) can never be as efficient as a serverside feature.

Andreas

"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> It seems to me, that leaving all this to the client (which implicitly
> inserts savepoints) can never be as efficient as a serverside feature.

I think this is an overly narrow view of "efficiency".  With client
control, the client can insert savepoints whereever it needs them,
which might not be for every statement.  Savepoints that you don't
actually need are going to be a fairly expensive overhead, AFAICS.

Also, in the V3 protocol, sending along extra BEGIN and COMMIT commands
doesn't have to cost you any extra network round trips.
        regards, tom lane

> > It seems to me, that leaving all this to the client (which implicitly
> > inserts savepoints) can never be as efficient as a serverside feature.
>
> I think this is an overly narrow view of "efficiency".  With client
> control, the client can insert savepoints whereever it needs them,

Yes, but not if the client API does implicit savepoints. So imho if it is not
cheap we should not start to supply API's that do them implicitly.

> which might not be for every statement.  Savepoints that you don't
> actually need are going to be a fairly expensive overhead, AFAICS.

Well with other db's per statement rollback is a no overhead feature,
so this is pg specific. (In the sense of: nothing is done that would not need
to be done anyway, since they all undo the changes)

Imho the 80% main use case for applications would be for "duplicate key"
to not abort. For interactive psql it would probably be the syntax error.
Maybe something can be done about those special cases to make partial
rollback cheaper for those.

Andreas

Bruce,

> > So, whatever "error handling mode" conveniences we wish to put in should
> > be put in on the client side.
>
> Added to TODO:
>
>         * Use nested transactions to prevent syntax errors from aborting
>           a transaction

Hmmm .... I'm not sure how you arrived at this wording for the TODO.  How are
we defining a "syntax error"?

I write a lot of procedures for T-SQL with error-controlled rollback, and a
few for Oracle.   I can tell you that all of the errors which I anticipate
for, and thus do not abort the operation when I encounter, fall into one of
these types:
1) Constraint conflict: duplicate key.
2) Constraint/Data Type conflict:  bad value format
3) Duplicate object name
4) Object not found
5) Lock conflict

Other types of errors, such as the syntax error raised by forgetting the
"GROUP BY" are things that I *want* to be fatal and cause immediate rollback.
In fact, one of issues I have on-and-off with SQL Server is that *nothing* is
fatal by default except not being able to access the databse; as a result,
one needs to manually check for an error after every statement.   You can
imagine what happens if you forget one of those checks.

I don't want to go to this by default with postgresql; I still prefer the
default abort transaction.   What would be a much easier integration, IMHO,
is offering something like Perl's eval{ } that would allow for special
rollback conditions in an application-defined block.

--
-Josh BerkusAglio Database SolutionsSan Francisco

"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
>> which might not be for every statement.  Savepoints that you don't
>> actually need are going to be a fairly expensive overhead, AFAICS.

> Well with other db's per statement rollback is a no overhead feature, 
> so this is pg specific.

I very much doubt that.  We are not expending any disk I/O to do
rollback, which is not true in (say) Oracle.  I'm concerned about the
internal bookkeeping overhead.
        regards, tom lane

Josh Berkus wrote:
> Bruce,
> 
> > > So, whatever "error handling mode" conveniences we wish to put in should
> > > be put in on the client side.
> > 
> > Added to TODO:
> > 
> > ????????* Use nested transactions to prevent syntax errors from aborting
> > ???????? ?a transaction
> 
> Hmmm .... I'm not sure how you arrived at this wording for the TODO.  How are 
> we defining a "syntax error"?

Parser error, I would say.  Misspelling a table name, perhaps.  Not sure
on that one, but clearly this opens a can of worms we will have to deal
with someday.

> I write a lot of procedures for T-SQL with error-controlled rollback, and a 
> few for Oracle.   I can tell you that all of the errors which I anticipate 
> for, and thus do not abort the operation when I encounter, fall into one of 
> these types:
> 1) Constraint conflict: duplicate key.
> 2) Constraint/Data Type conflict:  bad value format
> 3) Duplicate object name
> 4) Object not found
> 5) Lock conflict

Certainly this will never be the default.   My guess is that for these
cases, you will have to code the transaction/subtransaction yourself in
your script.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

> Oh, yea, that would be bad.  So you want to invalidate the entire
> session on any error?  That could be done.
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001

Well, that's exactly the current behaviour, which creates certain
problems during interactive or programatic operation.  Tom Lane,
described an excellent compromise to the problem, using "nested
transactions".  libpg should be configurable to automatically start a
nested transaction for each statement within a transaction allowing
the outer transaction to continue in case of error.  The error would
be communicated to the client in the normal manner.

This would not be the default since existing applications rely on the
entire transaction aborting.

-ESR-

Can we clarify what is meant by the client?  It is my
expectation/desire that the client library would handle this as a
setting similar to "AutoCommit", which would implicitly protect each
statement within a nested block (savepoint), causing only itself to
abort.  Such as, "OnError=>[abort|continue]", abort being the default.

Performance considerations are currently secondary to the fact that
the transaction abort problem can only be solved by nested
transactions.  In their current state transactions are not
convinient/practical (for me).

-ESR-

tgl@sss.pgh.pa.us (Tom Lane) wrote in message news:<2098.1076683808@sss.pgh.pa.us>...
> "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> > It seems to me, that leaving all this to the client (which implicitly
> > inserts savepoints) can never be as efficient as a serverside feature.
> 
> I think this is an overly narrow view of "efficiency".  With client
> control, the client can insert savepoints whereever it needs them,
> which might not be for every statement.  Savepoints that you don't
> actually need are going to be a fairly expensive overhead, AFAICS.
> 
> Also, in the V3 protocol, sending along extra BEGIN and COMMIT commands
> doesn't have to cost you any extra network round trips.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

Hello,

I have a much clearer picture of the issue.  So, does this mean that
with nested transactions, all statements will execute within a
mini-transaction, which may be executed within a branch of user
defined sub-transactions.  Such that:

begin
...
...    begin     ...    ...    mini-transaction {syntax error}    ...    commit
...
...
commit

-ESR-

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Josh Berkus wrote:
>> Hmmm .... I'm not sure how you arrived at this wording for the TODO.  How are 
>> we defining a "syntax error"?
>
> Parser error, I would say.  Misspelling a table name, perhaps.

FWIW, a misspelled table name is plainly a semantic error, not a
syntactic one.

-Neil