[HACKERS] BEGIN TRANSACTION and START TRANSACTION: different error handling
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, JobWHERE 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, JobWHERE 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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, JobWHERE 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, JobWHERE 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BEGIN TRANSACTION and START TRANSACTION: different error handling
On Thu, Sep 24, 2009 at 12:16:43PM +0300, Hannu Krosing wrote: 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. Well. I'd always thought BEGIN and START were syntactic Aspartame and had the same underlying implementation. So this is a surprise. Why do they return a different status? -dg ` -- David Gould da...@sonic.net 510 536 1443510 282 0869 If simplicity worked, the world would be overrun with insects. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BEGIN TRANSACTION and START TRANSACTION: different error handling
On Thu, Sep 24, 2009 at 6:16 PM, Hannu Krosing ha...@krosing.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
