Re: Transaction and SQL errors

Le 04/04/2022 à 18:20, Sebastien Flaesch a écrit :
> 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

FYI there was a discussion [1] and a patch to allow this feature through
an extension because having this implemented in core will probably never
happen. Everything was ready but the final status is Rejected so I don't
think you might expect any planned work on this feature in any term. But
who knows, things can evolve.

[1]
https://www.postgresql.org/message-id/983d80b3-d187-127a-2de5-38c92ccb38ab%40darold.net

--
Gilles Darold
http://www.darold.net/