Re: Transaction and SQL errors

David,

Personally, I can see where it has significant value for psql in interactive mode because people make typos. Application code doesn't. That removes a whole class of problems where the feature provides benefit.

Sure, application code must not have typos, but I prefer to let the DB engine check for SQL constraints.

Imagine the following case:

BEGIN WORK
... (some other SQL) ...
DELETE FROM items WHERE item_id = 12823 -- Can raise foreign key error
if sql-error then
...

To me it's better than:

BEGIN WORK
...
SELECT ... FROM orders WHERE item_id = 12823
if not-found then -- make sure we get no SQL error than cancels TX!
DELETE FROM items WHERE item_id = 12823
endif
...

... and not even sure it's valid atomic code depending on isolation level...

A good argument for PostgreSQL's behavior would be that it's better to cancel the whole transaction and restart all SQL commands.

However, legacy code is often spaghetti code where one function starts the TX, then calls other functions doing SQL ... ( yes, good candidate for savepoints usage! )

Anyway, thanks for the info, nothing planed short term, and that's what I was asking for.

Cheers!
Seb