Re: Functions and transactions

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