Re: Feature discussion: Should syntax errors abort a transaction?

On 20/06/2012 08:24, Chris Travers wrote:
> It seems to me there is one very simple reason not to change current
> behavior which those in favor are glossing over.
>
> Most interactions with a database are not occurring over an interface
> like psql with one person typing on one side and the db executing on
> the other. If that were the case I would understand the concern
> that a typo should give the user an opportunity to pick up the
> statement where he/she left off.
>
> However most interactions with the database are purely through
> intermediary software. Adding a lot of "do what I mean" or "give me a
> chance to retry that" adds a great deal of complexity to the job of
> the software in trapping and handling errors. It is far, far more
> simple to say "syntax errors abort transactions" and leave it at that.
> I know as a developer I don't want that behavior to change.
>
> I guess it seems to me that I would not object to a new option for
> transaction behavior where one could do something like SET TRANSACTION
> INTERACTIVE; and have no errors abort the transaction at all (explicit
> commit or rollback required) but I would complain loudly if this were
> to be the default, and I don't see a real need for it.
>
> Best Wishes,
> Chris Travers
>

It would be very nice to turn this feature off completely as a property
of your session.

I generally see it as necessary to do everything inside a transaction
when working in the DB manually. It adds greater protection against
forgotten WHERE clauses etc. I've seen too many DBs mashed because of a
careless typo. The current behavior encourages admins not to use
transactions because any error (typo or not) forces them to re-do all
their work so far or put in a lot of extra typing to wrap everything.

On the idea of different error behavior between bad syntax and pragmatics...

Splitting hairs between a syntax error and other errors is dangerous.
There are too many cases where the division can not be clear. And any
implementation would find it difficult not to fall foul of the principle
of least astonishment.
http://en.wikipedia.org/wiki/Principle_of_least_astonishment

For example pg/plsql executing dynamic SQL. An error may have been
caused by faulty arguments. However one of the arguments may have been a
SQL statement in part or full. How should PostgreSQL behave? See the
argument as bad (data error) or the SQL it contains as a syntax error.
You can always find an answer to this that works, but will that answer
be obvious to every developer?

Regards

Phil