Thread: Does error within transaction imply restarting it?
Hello dear fellows, I need to get insight about this: 1. A transaction begins 2. LOCK TABLE XYZ; is issued. 3. Since table XYZ doesn't exist, the backend gives error. 4. A CREATE TABLE XYZ(field char); is issued. 5. The backend says that it is in abort state, and that all queries will be ignored until commit, or end of transaction. Is this behaviour unavoidable? I mean, can the backend ignore the fact that I issued a command that yielded an error, and continue accepting SQL statements within the transaction? Is there a hack or something? Thank you very much in advance. Cheers, Haroldo.
Haroldo Stenger wrote: > > Hello dear fellows, > > I need to get insight about this: > > 1. A transaction begins > 2. LOCK TABLE XYZ; is issued. > 3. Since table XYZ doesn't exist, the backend gives error. > 4. A CREATE TABLE XYZ(field char); is issued. > 5. The backend says that it is in abort state, and that all queries will > be ignored until commit, or end of transaction. > > Is this behaviour unavoidable? This is problematic (and arguably non-standard) handling of an error within a transaction, and is a problem for precisely the reason you experienced. At last check, it appeared some of the key developers might have been coming around to that understanding, though I'm not sure it has made its way into any kind of action plan. Regards, Ed Loehr
Ed Loehr wrote: > > Haroldo Stenger wrote: > > 1. A transaction begins > > 2. LOCK TABLE XYZ; is issued. > > 3. Since table XYZ doesn't exist, the backend gives error. > > 4. A CREATE TABLE XYZ(field char); is issued. > > 5. The backend says that it is in abort state, and that all queries will > > be ignored until commit, or end of transaction. > > > > Is this behaviour unavoidable? > > This is problematic (and arguably non-standard) handling of an error within > a transaction, and is a problem for precisely the reason you experienced. > At last check, it appeared some of the key developers might have been > coming around to that understanding, though I'm not sure it has made its > way into any kind of action plan. Thanks, I feel so well of pointing to something worth worrying about. I've been asking elsewhere, whether other DBMSs, behave like or unlike PostgreSQL. I seems that other DBMSs, don't care about erroneous statements within a transaction. Now, I have several paths to follow: 1) Hacking the backend ;-) 2) Hacking the JDBC driver; 3) Hacking GeneXus' generated code. In such hipotetic hacks, I could force not issuing the LOCKs, ignoring'em by the backend, ignoring 'em by the JDBC driver, making'em conditional within the driver, etc. Other ideas, are welcome too. How can I motivate key developers to make their way into an action plan? Cheers, Haroldo.
Haroldo Stenger writes:
> I seems that other DBMSs, don't care about erroneous statements within
> a transaction. Now, I have several paths to follow: 1) Hacking the
> backend ;-)
If you're really brave you can try this change in
backend/tcop/postgres.c:
if (sigsetjmp(Warn_restart, 1) != 0)
{
time(&tim);
if (Verbose)
TPRINTF(TRACE_VERBOSE, "AbortCurrentTransaction");
- AbortCurrentTransaction();
InError = false;
if (ExitAfterAbort)
{
ProcReleaseLocks(); /* Just to be sure... */
proc_exit(0);
}
}
Absolutely no guarantee, there's probably more to it. Hmm, I wonder, maybe
not.
> How can I motivate key developers to make their way into an action
> plan?
Becoming one yourself or throwing large amounts of cash at the existing
ones. :) Trying the above and tracing down any arising problems might be a
start though.
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
[Charset ISO-8859-1 unsupported, filtering to ASCII...]
> Haroldo Stenger writes:
>
> > I seems that other DBMSs, don't care about erroneous statements within
> > a transaction. Now, I have several paths to follow: 1) Hacking the
> > backend ;-)
>
> If you're really brave you can try this change in
> backend/tcop/postgres.c:
>
> if (sigsetjmp(Warn_restart, 1) != 0)
> {
> time(&tim);
>
> if (Verbose)
> TPRINTF(TRACE_VERBOSE, "AbortCurrentTransaction");
>
> - AbortCurrentTransaction();
> InError = false;
> if (ExitAfterAbort)
> {
> ProcReleaseLocks(); /* Just to be sure... */
> proc_exit(0);
> }
> }
>
> Absolutely no guarantee, there's probably more to it. Hmm, I wonder, maybe
> not.
>
> > How can I motivate key developers to make their way into an action
> > plan?
>
> Becoming one yourself or throwing large amounts of cash at the existing
> ones. :) Trying the above and tracing down any arising problems might be a
> start though.
Seems this would be an interesting SET option.
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Peter Eisentraut wrote:
>
> Haroldo Stenger writes:
>
> > I seems that other DBMSs, don't care about erroneous statements within
> > a transaction. Now, I have several paths to follow: 1) Hacking the
> > backend ;-)
>
> If you're really brave you can try this change in
> backend/tcop/postgres.c:
>
> if (sigsetjmp(Warn_restart, 1) != 0)
> {
> time(&tim);
>
> if (Verbose)
> TPRINTF(TRACE_VERBOSE, "AbortCurrentTransaction");
>
> - AbortCurrentTransaction();
> InError = false;
> if (ExitAfterAbort)
> {
> ProcReleaseLocks(); /* Just to be sure... */
> proc_exit(0);
> }
> }
>
> Absolutely no guarantee, there's probably more to it. Hmm, I wonder, maybe
> not.
>
It worked fine. I could do what I wanted, i.e. using GeneXus with
PostgreSQL. I'm happy. Thanks :)
> > How can I motivate key developers to make their way into an action
> > plan?
>
> Becoming one yourself or throwing large amounts of cash at the existing
> ones. :)
Maybe the former?
> Trying the above and tracing down any arising problems might be a
> start though.
Good enough.
A SET option, as Bruce sugested, would be great.
Peter and Harold (and all):
I just tried this with current CVS sources. While it _does_ allow one to
continue after SQL syntax errors, and 'relation not found' type errors,
it makes a mess if the error is caused by a constraint firing, such
as a unique column constraint. I ended up with the dead tuple going live,
with a duplicate field, after some NOTICES about buffer leaks.
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
On Sat, Apr 15, 2000 at 08:52:02PM +0200, Peter Eisentraut wrote:
> Haroldo Stenger writes:
>
> > I seems that other DBMSs, don't care about erroneous statements within
> > a transaction. Now, I have several paths to follow: 1) Hacking the
> > backend ;-)
>
> If you're really brave you can try this change in
> backend/tcop/postgres.c:
>
> if (sigsetjmp(Warn_restart, 1) != 0)
> {
> time(&tim);
>
> if (Verbose)
> TPRINTF(TRACE_VERBOSE, "AbortCurrentTransaction");
>
> - AbortCurrentTransaction();
> InError = false;
> if (ExitAfterAbort)
> {
> ProcReleaseLocks(); /* Just to be sure... */
> proc_exit(0);
> }
> }
>
> Absolutely no guarantee, there's probably more to it. Hmm, I wonder, maybe
> not.
>
> > How can I motivate key developers to make their way into an action
> > plan?
>
> Becoming one yourself or throwing large amounts of cash at the existing
> ones. :) Trying the above and tracing down any arising problems might be a
> start though.
>