Re: "BEGIN TRANSACTION" and "START TRANSACTION": different error handling

On Thu, 2009-09-24 at 17:51 +0900, ning wrote:
> Hi all,
>
> I am using psqlodbc to connect to PostgreSQL8.2.4 server on Linux.
> The manual says "BEGIN TRANSACATION" is equlvalent to "START
> TRANSACTION", but it turns out that they throw different error message
> and have different effect to subsequent queries.
>
> I have a table "CREATE TEMPORARY TABLE ooid (oid_ INTEGER NOT NULL);"
> The autocommit is set to on. When inserting into ooid with a NULL
> value within a transaction, I expect the transaction is aborted and
> rollback is executed automatically. With the transaction started by
> "BEGIN TRANSACTION", PostgreSQL runs as expected. The server log shows
> error message is "ERROR: null value in column "oid_" violates
> not-null constraint", and any following query runs well.

"I expect the transaction is aborted and rollback is executed
automatically." - this is not how postgreSQL behaves. PostgreSQL needs
an explicit end of transaction from client, either COMMIT; or ROLLBACK;

when run from psql, they both act the same, except the string returned

hannu=# begin transaction;
BEGIN
hannu=# select 1/0;
ERROR: division by zero
hannu=# select 1/0;
ERROR: current transaction is aborted, commands ignored until end of
transaction block
hannu=# abort;
ROLLBACK
hannu=# start transaction;
START TRANSACTION
hannu=# select 1/0;
ERROR: division by zero
hannu=# select 1/0;
ERROR: current transaction is aborted, commands ignored until end of
transaction block
hannu=# abort;
ROLLBACK

I suspect, that psqlodbc is the one doing the automatic rollback and it
seems to rely on reply "BEGIN" to establish an in-transaction state.

so when "start transaction;" returns "START TRANSACTION" instead of
"BEGIN", psqlodbc does not realise that it is in transaction and does
not initiate the automatic rollback.

> -----
> 2009-09-24 13:53:13 JST jmdb postgres STATEMENT: BEGIN TRANSACTION;
> DELETE FROM pid; INSERT INTO pid VALUES (9765); DELETE FROM jid;
> INSERT INTO jid VALUES (644); DELETE FROM did; INSERT INTO did VALUES
> (2029); DELETE FROM ooid; INSERT INTO ooid (SELECT COALESCE( (SELECT
> Document.oid_ FROM Document, Job WHERE Document.DocNum = (SELECT
> DocNum FROM did) AND Job.jobId = (SELECT jobId FROM jid) AND
> Document.memberOf_ = Job.oid_ AND Job.assignedTo_ = (SELECT
> PrinterObjId FROM pid)), NULL)); update Document set DocName=NULL
> where Document.oid_=(SELECT oid_ FROM ooid);update Document set
> DocLastModified = CURRENT_TIMESTAMP where Document.oid_=(SELECT oid_
> FROM ooid); COMMIT;
>
> 2009-09-24 13:53:17 JST jmdb postgres ERROR: null value in column
> "oid_" violates not-null constraint
> -----
>
> When the transaction is started by "START TRANSACTION", the error
> message is different, and all following query failed with the same
> error message "ERROR: current transaction is aborted, commands
> ignored until end of transaction block".
> -----
> 2009-09-24 13:53:59 JST jmdb postgres STATEMENT: START TRANSACTION;
> DELETE FROM pid; INSERT INTO pid VALUES (9765); DELETE FROM jid;
> INSERT INTO jid VALUES (779); DELETE FROM did; INSERT INTO did VALUES
> (2530); DELETE FROM ooid; INSERT INTO ooid (SELECT COALESCE( (SELECT
> Document.oid_ FROM Document, Job WHERE Document.DocNum = (SELECT
> DocNum FROM did) AND Job.jobId = (SELECT jobId FROM jid) AND
> Document.memberOf_ = Job.oid_ AND Job.assignedTo_ = (SELECT
> PrinterObjId FROM pid)), NULL)); update Document set DocName=NULL
> where Document.oid_=(SELECT oid_ FROM ooid);update Document set
> DocLastModified = CURRENT_TIMESTAMP where Document.oid_=(SELECT oid_
> FROM ooid); COMMIT;
>
> 2009-09-24 13:53:59 JST jmdb postgres ERROR: current transaction is
> aborted, commands ignored until end of transaction block
> 2009-09-24 13:53:59 JST jmdb postgres STATEMENT: SELECT oid_,DocName
> FROM DocView WHERE DocNum=2530 and DocJobId=779 and pid_=0 FOR READ
> ONLY
> 2009-09-24 13:53:59 JST jmdb postgres ERROR: current transaction is
> aborted, commands ignored until end of transaction block
> 2009-09-24 13:53:59 JST jmdb postgres STATEMENT: DELETE FROM Printer
> WHERE PrinterObjId=0;
> 2009-09-24 13:53:59 JST jmdb postgres ERROR: current transaction is
> aborted, commands ignored until end of transaction block
> -----
>
> I searched archives, but no related comment is found.
> Should I do some setting on server to make the "START TRANSACTION" act
> as "BEGIN TRANSACTION"?
>
> Thank you.
>
> Greetings,
> Ning Xie
>