PostgreSQL Discussion - plgpsql and transactions

Author

plgpsql and transactions

Bill Moseley

2006-04-06, 8:25 pm

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

Terry Lee Tucker

2006-04-06, 8:25 pm

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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Bill Moseley

2006-04-06, 8:25 pm

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Terry Lee Tucker

2006-04-06, 8:25 pm

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:
>
> 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.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Bill Moseley

2006-04-07, 8:25 pm

On Thu, Apr 06, 2006 at 03:48:15PM -0500, Terry Lee Tucker wrote:
>
> 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

---------------------------(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

Terry Lee Tucker

2006-04-07, 8:25 pm

> >
>
> 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/...ansactions.html

and Concurrency control:
http://www.postgresql.org/docs/7.4/...ctive/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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links