Re: Transaction aborts on syntax error.

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.

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