Re: Transaction aborts on syntax error.

>Tom Lane
> "Simon Riggs" <simon(at)2ndquadrant(dot)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