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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
