Re: Transaction aborts on syntax error.

On Mon, 8 Feb 2004, Greg Stark wrote:

>
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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