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

Search Discussions

  • Hannu Krosing at Sep 24, 2009 at 9:16 am

    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
  • Daveg at Sep 24, 2009 at 12:09 pm

    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 daveg@sonic.net 510 536 1443 510 282 0869
    If simplicity worked, the world would be overrun with insects.
  • Ning at Sep 24, 2009 at 1:17 pm

    On Thu, Sep 24, 2009 at 6:16 PM, Hannu Krosing 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedSep 24, '09 at 8:51a
activeSep 24, '09 at 1:17p
posts4
users3
websitepostgresql.org...
irc#postgresql

3 users in discussion

Ning: 2 posts Daveg: 1 post Hannu Krosing: 1 post

People

Translate

site design / logo © 2021 Grokbase