Thread: [HACKERS] Transaction abortions & recovery handling

Using perl DBI, I would like to catch a backend error and, if the
pgsql error string matches a particular regex (/ExecInitIndexScan:
both left and right op's are rel-vars/), vacuum on the fly and
immediately resubmit the query within the same transaction. 
[Vacuuming has sometimes worked as a temporary fix to the problem.]

The immediate problem seems to be that once the backend fails on a
query within a transaction, the client is not permitted to do any more
queries within that transaction, giving this msg:
NOTICE:  (transaction aborted): queries ignored until END

Any suggestions on how I might handle this? 

Regards,
Ed Loehr

Ed Loehr <eloehr@austin.rr.com> writes:
> Any suggestions on how I might handle this? 

Er ... run 7.0beta ?  Probably a cleaner answer than hacking up your
app to implement an incomplete workaround for that 6.5 bug.

Year before last, I moved my company's production applications onto
a pre-alpha 6.4 snapshot, because we were getting killed by a bad bug
in 6.3.*.  It made me pretty nervous, but guess what: we didn't have
any problems.
        regards, tom lane

Tom Lane wrote:
> 
> Ed Loehr <eloehr@austin.rr.com> writes:
> > Any suggestions on how I might handle this?
> 
> Er ... run 7.0beta ?  Probably a cleaner answer than hacking up your
> app to implement an incomplete workaround for that 6.5 bug.
> 
> Year before last, I moved my company's production applications onto
> a pre-alpha 6.4 snapshot, because we were getting killed by a bad bug
> in 6.3.*.  It made me pretty nervous, but guess what: we didn't have
> any problems.

I was sure that would be someone's first response.  I'd like to
express my perspective and
see if you still stick with your advice...

[flame suit on]

My system is live.  As such, I really don't want to trade this known
problem for (unknown) additional adjustments to 7.0beta right now
(pg_dump & views, not null unique, etc), also due to my own time
constraints.  Based on recent threads on this list, I have the
impression that 7.0beta is not quite ready for production.  The recent
flaming of one fellow for, among other things, using it in his
near-production system reinforced my impression.  Before I get derided
and flamed, let me admit I haven't tracked all these issues to their
conclusion, nor am I watching cvs for fixes.

[flame suit off]

Additionally, I already have the app code in place to catch the error 
& vacuum, and I think it has even worked in the past, though something
has apparently changed on my end to make that cease.

Having said all that, do you still advise 7.0beta for production?  Or
might there be a simple workaround to my original question?

OK, flame away...

Regards,
Ed Loehr

Ed Loehr <eloehr@austin.rr.com> writes:
>>>> Any suggestions on how I might handle this?
>> 
>> Er ... run 7.0beta ?

> Based on recent threads on this list, I have the
> impression that 7.0beta is not quite ready for production.

A fair objection, since in fact it isn't.  Moving to 7 now will probably
cost you at least one extra dump/initdb/reload cycle, since we are
likely to force another initdb before final release.  However, if the
alternative is continuing to get bit by a 6.5 bug, it seems to me that
being an early adopter of 7.0 is not such a bad choice.

I wouldn't actually suggest picking up 7.0beta1 at this point, since
we've fixed a number of flaws since then; the latest nightly snapshot
would be better.  Or you might want to wait for 7.0beta2, which should
be out in a day or two.
        regards, tom lane

Tom Lane wrote:
> 
> Ed Loehr <eloehr@austin.rr.com> writes:
> >>>> Any suggestions on how I might handle this?
> >>
> >> Er ... run 7.0beta ?
> 
> > Based on recent threads on this list, I have the
> > impression that 7.0beta is not quite ready for production.
> 
> A fair objection, since in fact it isn't. [snip] However, if the
> alternative is continuing to get bit by a 6.5 bug, it seems to me that
> being an early adopter of 7.0 is not such a bad choice.

Agreed, if that is in fact my only alternative.  Fortunately, this
showstopper bug shows up infrequently (it's been a month or two since
the last bite).  I'm still hoping to avoid the bleeding edge on this
production system.

Is there any reasonably straight-forward means to allowing additional
queries within the same transaction after I get an ERROR?

Regards,
Ed Loehr