Re: plgpsql and transactions

On Thu, Apr 06, 2006 at 03:48:15PM -0500, Terry Lee Tucker wrote:
> >
> > -- fires a trigger that updates more than one table
> > insert into semething (default);
> >
> > and:
> >
> > begin;
> > -- fires a trigger that updates more than one table
> > insert into somthing (default);
> > commit;
> >
>
> In the latter, you have expanded the scope of the transaction; which,
> sometimes you might want to do.

Yes, I might. But, I'd like to understand it so I do know when I might
or might not want to do it.

Say I have a BEFORE INSERT trigger that does a table lock. When
is that lock released? At the end of the trigger? Or after the
INSERT has completed?

For example, say I want to set a column on the row I'm inserting based
on what's already in the table. So I lock the table in the trigger
and check the current status of the table and set the column based on
that current status.

I want to make sure that between the time the trigger completes and
when the insert finally happens that another session can't also do an
insert and see the same table state.

--
Bill Moseley
moseley(at)hank(dot)org