pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedFunctions and transactions
11+ messages / 3 participants
[nested] [flat]
* Functions and transactions
@ 2005-03-09 20:52 Kris Kiger <kris@musicrebellion.com>
0 siblings, 2 replies; 11+ messages in thread
From: Kris Kiger @ 2005-03-09 20:52 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
Here is my problem. I have a function that is triggered on insert. For
simplicity's sake, lets say the function looks like this:
CREATE OR REPLACE FUNCTION dostuff_on_insert() RETURNS TRIGGER AS '
DECLARE lockrows RECORD;
BEGIN
select into lockrows * from table1 where pkey_id = NEW.pkey_id for
update on table1;
update table1 set active = false where NEW.pkey_id = pkey_id and active;
NEW.active := true;
END;
'language 'plpgsql';
I have two inserts, lets say insert A and insert B. A new explicit
transaction block is started with the intent of executing insert A.
begin;
insert into table1 (stuff) VALUES (morestuff);
At this time another terminal is opened up and insert B is executed in
the same fasion:
begin;
insert into table1 (stuff) VALUES (different_more_stuff);
In my two open terminals insert A has completed and insert B is waiting
for insert A's transaction to be committed, before it can move on. I
commit insert A and check to see how many active row's I have for that
ID (there should be 1, the new row).
commit;
select * from table1;
I find that there is one active row. Everything is fine at this point.
Now, I commit insert B, that has just finished, because insert A has
been committed. I expect to see 1 active row, because the update
contained in the function has not been executed, and has therefore not
grabbed a snapshot of the table yet. I expect that the new row from
insert A will be updated as well.
commit;
select * from table1;
To my surprise, I see 2 active rows. What i'm assuming is happening
with the transaction must be flawed. Does the function handle a
transaction outside of the one the insert is using? Just trying to
figure out what exactly is going on and why.
Thanks in advance for the insight. If it would be easier to understand
by having me paste what is happening directly from the terminals, let me
know.
Kris
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Functions and transactions
@ 2005-03-09 21:07 Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
parent: Kris Kiger <kris@musicrebellion.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tsirkin Evgeny @ 2005-03-09 21:07 UTC (permalink / raw)
To: Kris Kiger <kris@musicrebellion.com>; +Cc: pgsql-admin
What transaction level are you using?
Evgeny.
Kris Kiger wrote:
> Here is my problem. I have a function that is triggered on insert. For
> simplicity's sake, lets say the function looks like this:
>
> CREATE OR REPLACE FUNCTION dostuff_on_insert() RETURNS TRIGGER AS '
> DECLARE lockrows RECORD;
> BEGIN
> select into lockrows * from table1 where pkey_id = NEW.pkey_id for
> update on table1;
> update table1 set active = false where NEW.pkey_id = pkey_id and active;
> NEW.active := true;
> END;
> 'language 'plpgsql';
>
> I have two inserts, lets say insert A and insert B. A new explicit
> transaction block is started with the intent of executing insert A.
>
> begin;
> insert into table1 (stuff) VALUES (morestuff);
>
>
> At this time another terminal is opened up and insert B is executed in
> the same fasion:
>
> begin;
> insert into table1 (stuff) VALUES (different_more_stuff);
>
> In my two open terminals insert A has completed and insert B is waiting
> for insert A's transaction to be committed, before it can move on. I
> commit insert A and check to see how many active row's I have for that
> ID (there should be 1, the new row).
>
> commit;
> select * from table1;
>
> I find that there is one active row. Everything is fine at this point.
> Now, I commit insert B, that has just finished, because insert A has
> been committed. I expect to see 1 active row, because the update
> contained in the function has not been executed, and has therefore not
> grabbed a snapshot of the table yet. I expect that the new row from
> insert A will be updated as well.
>
> commit;
> select * from table1;
>
> To my surprise, I see 2 active rows. What i'm assuming is happening
> with the transaction must be flawed. Does the function handle a
> transaction outside of the one the insert is using? Just trying to
> figure out what exactly is going on and why.
> Thanks in advance for the insight. If it would be easier to understand
> by having me paste what is happening directly from the terminals, let me
> know.
>
> Kris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Functions and transactions
@ 2005-03-09 21:10 Kris Kiger <kris@musicrebellion.com>
parent: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
0 siblings, 1 reply; 11+ messages in thread
From: Kris Kiger @ 2005-03-09 21:10 UTC (permalink / raw)
To: pgsql-admin
transaction_isolation
-----------------------
read committed
Running Postgres 7.4 btw
Kris
Tsirkin Evgeny wrote:
>
> What transaction level are you using?
> Evgeny.
> Kris Kiger wrote:
>
>> Here is my problem. I have a function that is triggered on insert.
>> For simplicity's sake, lets say the function looks like this:
>>
>> CREATE OR REPLACE FUNCTION dostuff_on_insert() RETURNS TRIGGER AS '
>> DECLARE lockrows RECORD;
>> BEGIN
>> select into lockrows * from table1 where pkey_id = NEW.pkey_id for
>> update on table1;
>> update table1 set active = false where NEW.pkey_id = pkey_id and
>> active;
>> NEW.active := true;
>> END;
>> 'language 'plpgsql';
>>
>> I have two inserts, lets say insert A and insert B. A new explicit
>> transaction block is started with the intent of executing insert A.
>>
>> begin;
>> insert into table1 (stuff) VALUES (morestuff);
>>
>>
>> At this time another terminal is opened up and insert B is executed
>> in the same fasion:
>>
>> begin;
>> insert into table1 (stuff) VALUES (different_more_stuff);
>>
>> In my two open terminals insert A has completed and insert B is
>> waiting for insert A's transaction to be committed, before it can
>> move on. I commit insert A and check to see how many active row's I
>> have for that ID (there should be 1, the new row).
>>
>> commit;
>> select * from table1;
>>
>> I find that there is one active row. Everything is fine at this
>> point. Now, I commit insert B, that has just finished, because
>> insert A has been committed. I expect to see 1 active row, because
>> the update contained in the function has not been executed, and has
>> therefore not grabbed a snapshot of the table yet. I expect that the
>> new row from insert A will be updated as well.
>>
>> commit;
>> select * from table1;
>>
>> To my surprise, I see 2 active rows. What i'm assuming is happening
>> with the transaction must be flawed. Does the function handle a
>> transaction outside of the one the insert is using? Just trying to
>> figure out what exactly is going on and why.
>> Thanks in advance for the insight. If it would be easier to
>> understand by having me paste what is happening directly from the
>> terminals, let me know.
>>
>> Kris
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Functions and transactions
@ 2005-03-09 21:51 Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
parent: Kris Kiger <kris@musicrebellion.com>
0 siblings, 0 replies; 11+ messages in thread
From: Tsirkin Evgeny @ 2005-03-09 21:51 UTC (permalink / raw)
To: Kris Kiger <kris@musicrebellion.com>; +Cc: pgsql-admin
I guess first we should understand why the insert B waits at all,the
insert A did not commit ,right ,then how did it found any pkey_id =
NEW.pkey_id? That means you have already had those while starting your
experiment.
So ,insert B wait for those "old" rows not for your insert (i mean an
INSERT) to commit.Once the A function commits the old rows are released
but the INSERT is not yet done!it will take place only now when the
trigger of A is done.
This means that you have transaction in a wrong place - place it around
the insert not inside the trigger and commit AFTER the insert .
All this is an assumption only ,not realy sure if i am right.
Evgeny
Kris Kiger wrote:
> transaction_isolation
> -----------------------
> read committed
>
> Running Postgres 7.4 btw
>
> Kris
>
> Tsirkin Evgeny wrote:
>
>>
>> What transaction level are you using?
>> Evgeny.
>> Kris Kiger wrote:
>>
>>> Here is my problem. I have a function that is triggered on insert.
>>> For simplicity's sake, lets say the function looks like this:
>>>
>>> CREATE OR REPLACE FUNCTION dostuff_on_insert() RETURNS TRIGGER AS '
>>> DECLARE lockrows RECORD;
>>> BEGIN
>>> select into lockrows * from table1 where pkey_id = NEW.pkey_id for
>>> update on table1;
>>> update table1 set active = false where NEW.pkey_id = pkey_id and
>>> active;
>>> NEW.active := true;
>>> END;
>>> 'language 'plpgsql';
>>>
>>> I have two inserts, lets say insert A and insert B. A new explicit
>>> transaction block is started with the intent of executing insert A.
>>>
>>> begin;
>>> insert into table1 (stuff) VALUES (morestuff);
>>>
>>>
>>> At this time another terminal is opened up and insert B is executed
>>> in the same fasion:
>>>
>>> begin;
>>> insert into table1 (stuff) VALUES (different_more_stuff);
>>>
>>> In my two open terminals insert A has completed and insert B is
>>> waiting for insert A's transaction to be committed, before it can
>>> move on. I commit insert A and check to see how many active row's I
>>> have for that ID (there should be 1, the new row).
>>>
>>> commit;
>>> select * from table1;
>>>
>>> I find that there is one active row. Everything is fine at this
>>> point. Now, I commit insert B, that has just finished, because
>>> insert A has been committed. I expect to see 1 active row, because
>>> the update contained in the function has not been executed, and has
>>> therefore not grabbed a snapshot of the table yet. I expect that the
>>> new row from insert A will be updated as well.
>>>
>>> commit;
>>> select * from table1;
>>>
>>> To my surprise, I see 2 active rows. What i'm assuming is happening
>>> with the transaction must be flawed. Does the function handle a
>>> transaction outside of the one the insert is using? Just trying to
>>> figure out what exactly is going on and why.
>>> Thanks in advance for the insight. If it would be easier to
>>> understand by having me paste what is happening directly from the
>>> terminals, let me know.
>>>
>>> Kris
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 4: Don't 'kill -9' the postmaster
>>
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Functions and transactions
@ 2005-03-09 22:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kris Kiger <kris@musicrebellion.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2005-03-09 22:34 UTC (permalink / raw)
To: Kris Kiger <kris@musicrebellion.com>; +Cc: pgsql-admin
Kris Kiger <kris@musicrebellion.com> writes:
> Here is my problem. I have a function that is triggered on insert. For
> simplicity's sake, lets say the function looks like this:
> CREATE OR REPLACE FUNCTION dostuff_on_insert() RETURNS TRIGGER AS '
> DECLARE lockrows RECORD;
> BEGIN
> select into lockrows * from table1 where pkey_id = NEW.pkey_id for
> update on table1;
> update table1 set active = false where NEW.pkey_id = pkey_id and active;
> NEW.active := true;
> END;
> 'language 'plpgsql';
This is awfully vague. What table is the trigger placed on? (If table1
itself, seems like there are more efficient ways to do this.) What
events is the trigger fired for, and is it BEFORE or AFTER?
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Functions and transactions
@ 2005-03-10 14:27 Kris Kiger <kris@musicrebellion.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Kris Kiger @ 2005-03-10 14:27 UTC (permalink / raw)
To: pgsql-admin
Hmm.. I was trying simplify my function to get the point across with
minimal confusion. If you don't think there is enough detail, let me
know what is lacking and I will add the appropriate detail. The
function is executed BEFORE insert on table1. Thanks again for the help all
Kris
Tom Lane wrote:
>Kris Kiger <kris@musicrebellion.com> writes:
>
>
>>Here is my problem. I have a function that is triggered on insert. For
>>simplicity's sake, lets say the function looks like this:
>>
>>
>
>
>
>>CREATE OR REPLACE FUNCTION dostuff_on_insert() RETURNS TRIGGER AS '
>>DECLARE lockrows RECORD;
>>BEGIN
>> select into lockrows * from table1 where pkey_id = NEW.pkey_id for
>>update on table1;
>> update table1 set active = false where NEW.pkey_id = pkey_id and active;
>> NEW.active := true;
>>END;
>>'language 'plpgsql';
>>
>>
>
>This is awfully vague. What table is the trigger placed on? (If table1
>itself, seems like there are more efficient ways to do this.) What
>events is the trigger fired for, and is it BEFORE or AFTER?
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Functions and transactions
@ 2005-03-10 15:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kris Kiger <kris@musicrebellion.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2005-03-10 15:42 UTC (permalink / raw)
To: Kris Kiger <kris@musicrebellion.com>; +Cc: pgsql-admin
Kris Kiger <kris@musicrebellion.com> writes:
> Hmm.. I was trying simplify my function to get the point across with
> minimal confusion. If you don't think there is enough detail, let me
> know what is lacking and I will add the appropriate detail. The
> function is executed BEFORE insert on table1.
Mmm. This might work as you expect in 8.0, but it surely won't in any
prior release, because before 8.0 we didn't advance the transaction
snapshot between statements of a function.
Another issue is that your SELECT FOR UPDATE locks only one of the
rows having the target pkey_id (I assume that column is misnamed and
isn't actually a primary key?). If it happened to seize on a non-active
row then it might not accomplish your goal of blocking until other
updaters of the same row set commit. That would allow the UPDATE to
start and set its snapshot, which would mean you lose because it
wouldn't see the newly inserted row from the other transaction.
Even more to the point, if there aren't yet any committed rows at all of
the target pkey_id, there is nothing for the SELECT FOR UPDATE to block
on at all. You could fix the first problem in various ways but I see no
way around this one. Unless you can guarantee that there will always be
a suitable row already in existence, I think you have to abandon the
idea of using a SELECT FOR UPDATE for locking.
One possibility is to create a unique partial index:
CREATE UNIQUE INDEX foo ON table1(pkey_id) WHERE active;
This will provide an enforcement that you don't have more than one
active row at a time. Now you just simplify the trigger to
update table1 set active = false where NEW.pkey_id = pkey_id and active;
NEW.active := true;
Race conditions will end up causing unique-key errors, which you can just
retry.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Functions and transactions
@ 2005-03-10 19:57 Kris Kiger <kris@musicrebellion.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Kris Kiger @ 2005-03-10 19:57 UTC (permalink / raw)
To: pgsql-admin
In your second paragraph, I think that you are saying that SELECT FOR
UPDATE only locks one row, even though the select itself may return
many. Am I mis-interpreting you? Also, what do you mean by seizing on
a non-active row?
Your assumption about pkey_id is right, I meant for that to mean partial
key, bad naming on my part ;-).
In my case, the third paragraph applies to this situation, because I can
assume that there will always be an entry in the table that will be
active with that pkey_id (lets call this partialKey_id from now on, to
avoid further confusion).
The alternative you offer is a good idea, I didn't realize that I had
the option to create a unique index on a subset of data within the
table. Unfortunately, it will not work in this situation. I don't have
the option to report failure to the front-end application. I suppose,
i'm looking for a method to only allow one invocation of this function,
per partialKey_id, at a time.
If you have any other alternatives or suggestions, I'm all ears, err
eyes... Anyway, thank you ;-)
Kris
Tom Lane wrote:
>Mmm. This might work as you expect in 8.0, but it surely won't in any
>prior release, because before 8.0 we didn't advance the transaction
>snapshot between statements of a function.
>
>Another issue is that your SELECT FOR UPDATE locks only one of the
>rows having the target pkey_id (I assume that column is misnamed and
>isn't actually a primary key?). If it happened to seize on a non-active
>row then it might not accomplish your goal of blocking until other
>updaters of the same row set commit. That would allow the UPDATE to
>start and set its snapshot, which would mean you lose because it
>wouldn't see the newly inserted row from the other transaction.
>
>Even more to the point, if there aren't yet any committed rows at all of
>the target pkey_id, there is nothing for the SELECT FOR UPDATE to block
>on at all. You could fix the first problem in various ways but I see no
>way around this one. Unless you can guarantee that there will always be
>a suitable row already in existence, I think you have to abandon the
>idea of using a SELECT FOR UPDATE for locking.
>
>One possibility is to create a unique partial index:
>
>CREATE UNIQUE INDEX foo ON table1(pkey_id) WHERE active ;
>
>This will provide an enforcement that you don't have more than one
>active row at a time. Now you just simplify the trigger to
> update table1 set active = false where NEW.pkey_id = pkey_id and active;
> NEW.active := true;
>Race conditions will end up causing unique-key errors, which you can just
>retry.
>
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Functions and transactions
@ 2005-03-11 00:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kris Kiger <kris@musicrebellion.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2005-03-11 00:10 UTC (permalink / raw)
To: Kris Kiger <kris@musicrebellion.com>; +Cc: pgsql-admin
Kris Kiger <kris@musicrebellion.com> writes:
> In your second paragraph, I think that you are saying that SELECT FOR
> UPDATE only locks one row, even though the select itself may return
> many. Am I mis-interpreting you?
No, I'm saying that plpgsql's SELECT INTO operation only reads one row.
The fact that the SELECT might have found more rows if allowed to run
to completion doesn't enter into it. If the first row read doesn't have
active = true then it won't conflict against concurrent UPDATEs, because
you are carefully not UPDATEing rows with active = false. It's the
combination of those two things that creates the hazard.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Functions and transactions
@ 2005-03-11 16:48 Kris Kiger <kris@musicrebellion.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Kris Kiger @ 2005-03-11 16:48 UTC (permalink / raw)
To: pgsql-admin
Interesting. That makes sense, though. So, is there a good way to lock
a set of rows using SELECT FOR UPDATE in plpgsql? I assume using
PERFORM would yield the same problem, because it immediately discards
the results.
Thanks!
Kris
Tom Lane wrote:
>Kris Kiger <kris@musicrebellion.com> writes:
>
>
>>In your second paragraph, I think that you are saying that SELECT FOR
>>UPDATE only locks one row, even though the select itself may return
>>many. Am I mis-interpreting you?
>>
>>
>
>No, I'm saying that plpgsql's SELECT INTO operation only reads one row.
>The fact that the SELECT might have found more rows if allowed to run
>to completion doesn't enter into it. If the first row read doesn't have
>active = true then it won't conflict against concurrent UPDATEs, because
>you are carefully not UPDATEing rows with active = false. It's the
>combination of those two things that creates the hazard.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Functions and transactions
@ 2005-03-11 18:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kris Kiger <kris@musicrebellion.com>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2005-03-11 18:54 UTC (permalink / raw)
To: Kris Kiger <kris@musicrebellion.com>; +Cc: pgsql-admin
Kris Kiger <kris@musicrebellion.com> writes:
> Interesting. That makes sense, though. So, is there a good way to lock
> a set of rows using SELECT FOR UPDATE in plpgsql? I assume using
> PERFORM would yield the same problem, because it immediately discards
> the results.
I think PERFORM would work. The fact that plpgsql ignores the results
doesn't mean you don't have lock on the rows.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2005-03-11 18:54 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-09 20:52 Functions and transactions Kris Kiger <kris@musicrebellion.com>
2005-03-09 21:07 ` Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2005-03-09 21:10 ` Kris Kiger <kris@musicrebellion.com>
2005-03-09 21:51 ` Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2005-03-09 22:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-10 14:27 ` Kris Kiger <kris@musicrebellion.com>
2005-03-10 15:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-10 19:57 ` Kris Kiger <kris@musicrebellion.com>
2005-03-11 00:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-11 16:48 ` Kris Kiger <kris@musicrebellion.com>
2005-03-11 18:54 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox