Postgres Pro
Downloads
Home   >   mailing lists

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=eaGFmdNPCmQZx=55+a2uZQ4D6cdAO7d_=0uVoq0EDA@mail.gmail.com
Whole thread Raw
In response to Re: not aborting transactions on failed select  (Scott Marlowe <scott.marlowe@gmail.com>)
Responses Re: not aborting transactions on failed select
List pgsql-general
Tree view
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.marlowe@gmail.com> wrote:
On Tue, Sep 10, 2013 at 7:02 PM, Sergey Shelukhin
<sergey@hortonworks.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.

pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: pg_largeobjects
Next
From: Alban Hertroys
Date:
Subject: Re: not aborting transactions on failed select