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