Re: not aborting transactions on failed select

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...

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. "

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(at)yahoo(dot)com> wrote:

> 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(at)postgresql(dot)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.