Re: second "begin transaction" emits a warning

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