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
