Hi,

recently someone show us this code in the spanish list...
BEGIN WORK;
INSERT INTO mitabla VALUES (1);
BEGIN TRANSACTION;
INSERT INTO mitabla VALUES (2);
INSERT INTO mitabla VALUES (3);
COMMIT TRANSACTION;
INSERT INTO mitabla VALUES (4);
ROLLBACK WORK;
this is clearly bad you can't use a begin transaction inside a
transaction... but the user was expecting other results and because he
receives no error (actually was a warning but he is sending the
commands via an external application)...

he was expecting an empty table but instead he gets this:

mitabla
========
1
2
3
(3 rows)

so, why BeginTransactionBlock emits just a warning and not an error?
this is not the same as in the case of the one who was closing and
already closed cursor?

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Search Discussions

  • Tom Lane at Dec 16, 2005 at 11:11 pm

    Jaime Casanova writes:
    recently someone show us this code in the spanish list...
    BEGIN WORK;
    INSERT INTO mitabla VALUES (1);
    BEGIN TRANSACTION;
    INSERT INTO mitabla VALUES (2);
    INSERT INTO mitabla VALUES (3);
    COMMIT TRANSACTION;
    INSERT INTO mitabla VALUES (4);
    ROLLBACK WORK;
    he was expecting an empty table but instead he gets this:
    so, why BeginTransactionBlock emits just a warning and not an error?
    I can't get real excited about this case. If the second BEGIN had
    errored out, he'd *still* not get an empty table: the COMMIT would
    end the aborted transaction, then the last INSERT would succeed,
    then the ROLLBACK would complain about "no transaction in progress".
    Maybe there's an argument for turning the warning into an error,
    but this example doesn't provide it.

    regards, tom lane
  • Robert Treat at Dec 16, 2005 at 11:17 pm

    On Fri, 2005-12-16 at 17:36, Jaime Casanova wrote:
    Hi,

    recently someone show us this code in the spanish list...
    BEGIN WORK;
    INSERT INTO mitabla VALUES (1);
    BEGIN TRANSACTION;
    INSERT INTO mitabla VALUES (2);
    INSERT INTO mitabla VALUES (3);
    COMMIT TRANSACTION;
    INSERT INTO mitabla VALUES (4);
    ROLLBACK WORK;
    this is clearly bad you can't use a begin transaction inside a
    transaction... but the user was expecting other results and because he
    receives no error (actually was a warning but he is sending the
    commands via an external application)...

    he was expecting an empty table but instead he gets this:

    mitabla
    ========
    1
    2
    3
    (3 rows)
    I'm not entirely sure that it's relevant, but he should have actually
    recieved all 4 rows in his query, since the "commit transaction" would
    have committed the first three inserts, and the 4th insert should have
    gone in via auto-commit. So if he really got this result, his external
    application is doing something extra here for him. Which might be the
    point, emulating non-autocommit through an interface would be harder if
    multiple begin's tossed an error. I'm sure other reasons have been
    brought up as well.
    so, why BeginTransactionBlock emits just a warning and not an error?
    this is not the same as in the case of the one who was closing and
    already closed cursor?
    I might argue that closing a closed cursor should only emit a warning
    and not an error... but perhaps someone else will jump in here.


    Robert Treat
    --
    Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedDec 16, '05 at 10:36p
activeDec 16, '05 at 11:17p
posts3
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase