Re: Transaction aborts on syntax error. - Mailing list pgsql-hackers

>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