Re: not aborting transactions on failed select - Mailing list pgsql-general

Sergey Shelukhin wrote
> Hi.
> Is there any way to make postgres not abort the transaction on failed
> select?
>
> I have a system that uses ORM to retrieve data; ORM is very slow for some
> cases, so there's a perf optimization that issues ANSI SQL queries
> directly
> thru ORM's built-in passthru, and falls back to ORM if they fail.
> All of these queries are select-s, and the retrieval can be a part of an
> external transaction.
>
> It worked great in MySQL, but Postgres being differently
> ANSI-non-compliant, the queries do fail. Regardless of whether they can be
> fixed, in such cases the fall-back should work. What happens in Postgres
> however is that the transaction is aborted; all further SELECTs are
> ignored.
>
> Is there some way to get around this and not abort the transaction on
> failed selects?
> This behavior seems extremely counter-intuitive.

This behavior is extremely intuitive.  I have a transaction.  Either the
whole things succeeds or the whole thing fails.  Not, "its OK if select
statements fail; I'll just try something else instead."

If the ORM knows its going to issue something that could fail AND it needs
to do so within a transaction it needs to issue a SAVEPOINT, try the SELECT,
then either release the savepoint (on success) or ROLLBACK_TO_SAVEPONT to
revert to the savepoint (on failure) then continue on with its work.

Short answer is that the PostgreSQL team has a made a decision to have
transactions behave strictly according to their intended purpose and it is
not possible to make them behave less-correctly even if you know that your
application can compensate for degradation.

I cannot speak about the MySQL experience and my cursory search of their
documentation describing this behavior got me nothing.  I also cannot speak
intelligently about the SQL standard but from experience and instinct the
PostgreSQL behavior is what the standard intends and relying on the ability
for a fail statement of any kind to not cause an open transaction to fail
(in the absence of a savepoint) may have been a convenient choice but one
that is non-standard and thus potentially (and in reality) non-portable.

I could be mistaken on this - though I doubt - since I have not personally
tried to accomplish this in PostgreSQL (though the default behavior is
something I've experienced) and I cannot confirm or test any of this on a
MySQL installation.  Others will correct my if I am indeed mistaken.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/not-aborting-transactions-on-failed-select-tp5770387p5770393.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.