Re: not aborting transactions on failed select

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.

Thanks.

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

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.

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.

Sergey Shelukhin wrote
> 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...

PostgreSQL waits for an explicit Rollback in your situation - it just keeps
giving you exceptions if you choose not to do so. But if the system were to
crash it is perfectly in its rights to forgo waiting for you to Rollback and
instead do so itself - thus implicitly. Note that this is required so that
savepoint behavior can work properly.

I have and can argue for the behavior you describe but the hurdle to
implement it is very high. I would want some form of "approve error"
command that could be sent (manually in application code or automatically if
interactive) instead of blindly moving forward - MySQL behavior as you
describe is not desireable. In any case syntax errors in application code
are bad but I can see the use for it in interactive mode - but a manual
"approve error" command makes dealing with this distinction somewhat
flexible if you read error causes and are conservative in code when you
approve the error.

David J.

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

On Tue, Sep 10, 2013 at 7:02 PM, Sergey Shelukhin
<sergey(at)hortonworks(dot)com> 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.

I would like to see the query and in what way PostgreSQL is failing.
Generally a query that works on mysql and fails on postgresql is
itself usually non-ansi compliant.

As others pointed out, you can use savepoints if you need to rollback
part of a transaction to a previously good point and start back from
there.

The query suffers from the auto-lower-casing of unquoted table names, which
is not ANSI compliant. Technically we could add quotes (and stay ANSI), but
then MySQL would break without explicitly setting it to use ANSI mode, so
it's a lose-lose situation if we do not want to have DB-specific code.
So, the fix to the query exists in this case, and the failure will be
addressed, however in general it's important that this path stays as perf
optimization only, with things working without it (we do have tests that
verify that it returns the same results as ORM when it works). I guess I'll
have to work around it... Thanks.

On Tue, Sep 10, 2013 at 9:39 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Tue, Sep 10, 2013 at 7:02 PM, Sergey Shelukhin
> <sergey(at)hortonworks(dot)com> 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.
>
> I would like to see the query and in what way PostgreSQL is failing.
> Generally a query that works on mysql and fails on postgresql is
> itself usually non-ansi compliant.
>
> As others pointed out, you can use savepoints if you need to rollback
> part of a transaction to a previously good point and start back from
> there.
>

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

On 11 September 2013 07:53, Sergey Shelukhin <sergey(at)hortonworks(dot)com> wrote:

> The query suffers from the auto-lower-casing of unquoted table names,
> which is not ANSI compliant. Technically we could add quotes (and stay
> ANSI), but then MySQL would break without explicitly setting it to use ANSI
> mode, so it's a lose-lose situation if we do not want to have DB-specific
> code.
>

Whether identifiers get folded to lower case or to upper case (which IS
according to the standard) should not matter to you.
If you're using case sensitive table names, the standard says to quote your
identifiers.

It would appear that MySQL is doing something non-standard compliant that's
biting you here.

I haven't used MySQL in 10 years, but if memory serves me correctly, it
tends to use the literal file name of the table, which would be
case-sensitive on a case-sensitive file system. I remember the hilarity of
the same issue when we moved a MySQL database from a file system that was
not case sensitive (NTFS) to one that was (EXT2FS) - we had to rewrite all
our queries because MySQL could no longer find the tables in our queries.
That was when we wrote off MySQL for the project we were working on back
then.

From your remark it isn't entirely clear what your problem is; it would
appear that your ORM is generating case-sensitive table names and that your
query outside the ORM assumes that it doesn't need to quote those
identifiers?
Perhaps your ORM can be told not to do that?

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Folding to uppercase is according to standard, lowercase no, so in our case
we could expect it to work if we have uppercase tables... in fact, it
should work whether FS is case sensitive or not in MySQL, tables are
created and used uppercase.
Due to presence of a large number of historical installations renaming all
the tables is not viable.
Again, as I said, this is not the problem here, we'll figure out how to
deal with the queries in this case. Probably put in quotes and switch MySQL
to ANSI mode within session, some testing would be needed to see how it
affects ORM.

But the problem is that even when queries work, for the future potential
breakage the fall-back to ORM should always work... and it doesn't.

On Wed, Sep 11, 2013 at 12:57 AM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:

> On 11 September 2013 07:53, Sergey Shelukhin <sergey(at)hortonworks(dot)com>wrote:
>
>> The query suffers from the auto-lower-casing of unquoted table names,
>> which is not ANSI compliant. Technically we could add quotes (and stay
>> ANSI), but then MySQL would break without explicitly setting it to use ANSI
>> mode, so it's a lose-lose situation if we do not want to have DB-specific
>> code.
>>
>
> Whether identifiers get folded to lower case or to upper case (which IS
> according to the standard) should not matter to you.
> If you're using case sensitive table names, the standard says to quote
> your identifiers.
>
> It would appear that MySQL is doing something non-standard compliant
> that's biting you here.
>
> I haven't used MySQL in 10 years, but if memory serves me correctly, it
> tends to use the literal file name of the table, which would be
> case-sensitive on a case-sensitive file system. I remember the hilarity of
> the same issue when we moved a MySQL database from a file system that was
> not case sensitive (NTFS) to one that was (EXT2FS) - we had to rewrite all
> our queries because MySQL could no longer find the tables in our queries.
> That was when we wrote off MySQL for the project we were working on back
> then.
>
> From your remark it isn't entirely clear what your problem is; it would
> appear that your ORM is generating case-sensitive table names and that your
> query outside the ORM assumes that it doesn't need to quote those
> identifiers?
> Perhaps your ORM can be told not to do that?
>
> --
> If you can't see the forest for the trees,
> Cut the trees and you'll see there is no forest.
>

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

Sergey Shelukhin wrote
> Due to presence of a large number of historical installations {doing such
> and such} is not viable.

Yeah, PostgreSQL faces this same issue....

If you intend to stay here long, and we hope you do (welcome by the way), it
is customary to bottom-post on these lists.

One other thought is that exception generation and handling is expensive.
It probably is a fair trade-off - since the ORM is already slow the added
overhead of the failing optimization query shouldn't be that noticeable.
Failure means either bad code or incorrect pre-conditions; both of which
should be explicitly checked and reported on. If those checks fail the
system can auto-configure to use the backup (ORM) channel instead of the
primary (optimized) channel.

David J.

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