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)

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

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

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-

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 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

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, Pennsylvania 19073

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

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, Pennsylvania 19073