Thread: plgpsql and transactions
In a BEFORE INSERT trigger, depending on input values, I need to lock a table and do a few selects. Of course, the "lock table" isn't much use if not currently in a transaction. So my question is this: can I tell if I'm inside a transaction or not and issue a BEGIN if not. And then also set a flag so that after the INSERT I can detect that I issued a BEGIN and do a COMMIT? Or, maybe better is to just throw an exception if not already inside a transaction. BTW -- it seems odd to me that you can issue a lock table outside of an explicit begin/commit and not get a warning. When would issuing a lock table outside an explicit transaction be of any use? -- Bill Moseley moseley@hank.org
On Thursday 06 April 2006 02:36 pm, Bill Moseley saith: > In a BEFORE INSERT trigger, depending on input values, I need to lock > a table and do a few selects. Of course, the "lock table" isn't much > use if not currently in a transaction. > > So my question is this: can I tell if I'm inside a transaction or > not and issue a BEGIN if not. And then also set a flag so that after > the INSERT I can detect that I issued a BEGIN and do a COMMIT? > > Or, maybe better is to just throw an exception if not already inside a > transaction. > > BTW -- it seems odd to me that you can issue a lock table outside of > an explicit begin/commit and not get a warning. When would issuing a > lock table outside an explicit transaction be of any use? > > > -- > Bill Moseley > moseley@hank.org > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly Bill, Triggers fire inside a transaction.
On Thu, Apr 06, 2006 at 01:44:57PM -0500, Terry Lee Tucker wrote:
> Triggers fire inside a transaction.
Ah, thanks. Makes sense since each statement is in an implicit
transaction.
Granted, would help to see the trigger, but
these are basically the same?
-- 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;
--
Bill Moseley
moseley@hank.org
On Thursday 06 April 2006 03:27 pm, Bill Moseley saith: > On Thu, Apr 06, 2006 at 01:44:57PM -0500, Terry Lee Tucker wrote: > > Triggers fire inside a transaction. > > Ah, thanks. Makes sense since each statement is in an implicit > transaction. > > Granted, would help to see the trigger, but > these are basically the same? > > -- 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.
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@hank.org
> > > > 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. Understanding is good. You need to read the documentation on transactions: http://www.postgresql.org/docs/7.4/interactive/tutorial-transactions.html and Concurrency control: http://www.postgresql.org/docs/7.4/interactive/mvcc.html > > 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? At the end of the transaction. > > 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. Again, reading the docs on concurrency control and transactions will answer these questions. > > > > -- > Bill Moseley > moseley@hank.org > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly