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

On Thu, Sep 24, 2009 at 6:16 PM, Hannu Krosing <hannu(at)krosing(dot)net> wrote:
> 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
>>
>
>

hank you Hannu.

"PostgreSQL needs an explicit end of transaction from client, either
COMMIT; or ROLLBACK;"

In psql, after error occurred in transaction an explicit COMMIT leads
to ROLLBACK, either for "begin transaction" or "start transaction".
Doesn't this imply the PostgreSQL server react a "ROLLBACK" when
receiving a "COMMIT" according to the server-side state? Or do you
mean that it's psql that sends a "ROLLBACK" to server when receiving a
"COMMIT" according to the client-side state(which is based on the
reply of the server)? I am a little confused.

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

Thank you.
Ning Xie