Re: Functions and transactions - Mailing list pgsql-admin
From | Tsirkin Evgeny |
---|---|
Subject | Re: Functions and transactions |
Date | |
Msg-id | 422F6FD3.8070008@mail.jct.ac.il Whole thread Raw |
In response to | Re: Functions and transactions (Kris Kiger <kris@musicrebellion.com>) |
List | 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
pgsql-admin by date: