second "begin transaction" emits a warning

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 ;)

Jaime Casanova <systemguards(at)gmail(dot)com> 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

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