Does error within transaction imply restarting it?

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