Skip site navigation (1) Skip section navigation (2)

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

From: ning <mailxiening(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: "BEGIN TRANSACTION" and "START TRANSACTION": different error handling
Date: 2009-09-24 08:51:02
Message-ID: 27f31620909240151g6e3555c3ndf807ad943c36861@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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.
-----
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

Responses

pgsql-hackers by date

Next:From: Fujii MasaoDate: 2009-09-24 09:06:40
Subject: Re: walreceiver settings Re: Streaming Replication patch for CommitFest 2009-09
Previous:From: Fujii MasaoDate: 2009-09-24 08:20:56
Subject: Re: Streaming Replication patch for CommitFest 2009-09

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group