Re: not aborting transactions on failed select - Mailing list pgsql-general
| From | Sergey Shelukhin |
|---|---|
| Subject | Re: not aborting transactions on failed select |
| Date | |
| Msg-id | CAHXxaiA+dvVV66UgXQncdGHL5wJxhNxVxveRYHO1HPdSnw8UYA@mail.gmail.com Whole thread Raw |
| In response to | Re: not aborting transactions on failed select (David Johnston <polobo@yahoo.com>) |
| Responses |
Re: not aborting transactions on failed select
(David Johnston <polobo@yahoo.com>)
|
| List | pgsql-general |
ORM in this case doesn't execute the failing statements, we do. And obviously we want to avoid implementing another "better ORM" w/database-specific code for this "side path" if possible, so we just stick to ANSI SQL (for now).
As for the question itself, I believe the relevant standard (that is SQL92) is:
"The execution of a <rollback statement> may be initiated implicitly
by an implementation when it detects unrecoverable errors. When
such an error occurs, an exception condition is raised: transaction
rollback with an implementation-defined subclass code."
In no way is a select syntax failure unrecoverable error, although of course this section leaves a lot to interpretation...
"The execution of a <rollback statement> may be initiated implicitly
by an implementation when it detects unrecoverable errors. When
such an error occurs, an exception condition is raised: transaction
rollback with an implementation-defined subclass code."
In no way is a select syntax failure unrecoverable error, although of course this section leaves a lot to interpretation...
There may be more but I didn't find it. One example of the spirit of the standard may be the constraint check description:
"When a constraint is checked other than at the end of an SQL- transaction, if it is not satisfied, then an exception condition is raised and the SQL-statement that caused the constraint to be checked has no effect other than entering the exception information into the diagnostics area. "
"When a constraint is checked other than at the end of an SQL- transaction, if it is not satisfied, then an exception condition is raised and the SQL-statement that caused the constraint to be checked has no effect other than entering the exception information into the diagnostics area. "
This is the behavior I would intuitively expect from select in this case (which is not a constraint check failure, of course; but it's even less "dangerous" to ignore, in spirit).
On Tue, Sep 10, 2013 at 7:03 PM, David Johnston <polobo@yahoo.com> wrote:
Sergey Shelukhin wroteThis behavior is extremely intuitive. I have a transaction. Either the> 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.
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
CONFIDENTIALITY NOTICE
NOTICE: This message is intended for the use of the individual or entity to which it is addressed and may contain information that is confidential, privileged and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, you are hereby notified that any printing, copying, dissemination, distribution, disclosure or forwarding of this communication is strictly prohibited. If you have received this communication in error, please contact the sender immediately and delete it from your system. Thank You.
pgsql-general by date: