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-

30 Jan 2004 07:43:06 -0800
ramirez@idconcepts.org (Edwin S. Ramirez)
Transaction aborts on syntax error.
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)

Sat, 31 Jan 2004 17:30:20 -0300
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Transaction aborts on syntax error.
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, Pennsylvania 19073

Sat, 31 Jan 2004 16:17:23 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Transaction aborts on syntax error.

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


08 Feb 2004 23:37:19 -0500
Greg Stark <gsstark@mit.edu>
Re: Transaction aborts on syntax error.
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

Mon, 9 Feb 2004 16:22:14 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: Transaction aborts on syntax error.
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

Mon, 09 Feb 2004 00:37:47 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Transaction aborts on syntax error. 
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



Mon, 09 Feb 2004 17:34:37 +1100 (EST)
Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au>
Timestamps
> 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


Mon, 09 Feb 2004 15:28:19 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Timestamps

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


09 Feb 2004 09:33:11 -0500
Greg Stark <gsstark@mit.edu>
Re: Transaction aborts on syntax error.
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

Mon, 09 Feb 2004 10:30:13 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Transaction aborts on syntax error. 
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-

9 Feb 2004 08:59:35 -0800
ramirez@idconcepts.org (Edwin S. Ramirez)
Re: Transaction aborts on syntax error.
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

Mon, 09 Feb 2004 21:11:25 -0800
Andrej Czapszys <czapszys@comcast.net>
Re: Transaction aborts on syntax error.
>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



Tue, 10 Feb 2004 23:29:36 -0000
"Simon Riggs" <simon@2ndquadrant.com>
Re: Transaction aborts on syntax error.
"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 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.

			regards, tom lane

Wed, 11 Feb 2004 00:17:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Transaction aborts on syntax error. 
>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



Wed, 11 Feb 2004 21:10:02 -0000
"Simon Riggs" <simon@2ndquadrant.com>
Re: Transaction aborts on syntax error. 
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, Pennsylvania 19073

Wed, 11 Feb 2004 16:37:32 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Transaction aborts on syntax error.
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, Pennsylvania 19073

Wed, 11 Feb 2004 16:39:13 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Transaction aborts on syntax error.

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


12 Feb 2004 10:45:03 -0500
Greg Stark <gsstark@mit.edu>
Re: Transaction aborts on syntax error.
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, Pennsylvania 19073

Thu, 12 Feb 2004 12:44:22 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Transaction aborts on syntax error.
> > > 	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.


Thu, 12 Feb 2004 14:13:46 -0500
Rod Taylor <pg@rbt.ca>
Re: Transaction aborts on syntax error.
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, Pennsylvania 19073

Thu, 12 Feb 2004 14:35:42 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Transaction aborts on syntax error.
>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


Thu, 12 Feb 2004 20:14:27 -0000
"Simon Riggs" <simon@2ndquadrant.com>
Re: Transaction aborts on syntax error.
 >>
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


Thu, 12 Feb 2004 15:18:51 -0500
Rich Hall <rhall@micropat.com>
Re: Transaction aborts on syntax error.
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

Thu, 12 Feb 2004 19:15:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Transaction aborts on syntax error. 
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, Pennsylvania 19073

Thu, 12 Feb 2004 23:35:48 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Transaction aborts on syntax error.