Re: not aborting transactions on failed select

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.