X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 467F5550E3 for ; Wed, 9 Mar 2005 21:51:24 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59845-09 for ; Wed, 9 Mar 2005 21:51:15 +0000 (GMT) Received: from mail1.cc.huji.ac.il (mail1.cc.huji.ac.il [132.64.1.17]) by svr1.postgresql.org (Postfix) with ESMTP id DC3F352D42 for ; Wed, 9 Mar 2005 21:51:14 +0000 (GMT) Received: from mail1.cc.huji.ac.il (localhost [127.0.0.1]) by mail1.cc.huji.ac.il (Postfix) with SMTP id 8E3F815B026; Wed, 9 Mar 2005 23:51:12 +0200 (IST) Received: by mail1.cc.huji.ac.il (Postfix, from userid 31998) id 7DE9115AFBA; Wed, 9 Mar 2005 23:51:12 +0200 (IST) Received: from [132.64.32.205] (di8-32205.dialin.huji.ac.il [132.64.32.205]) by mail1.cc.huji.ac.il (Postfix) with ESMTP id 623C715B06F; Wed, 9 Mar 2005 23:51:10 +0200 (IST) Message-ID: <422F6FD3.8070008@mail.jct.ac.il> Date: Wed, 09 Mar 2005 23:51:15 +0200 From: Tsirkin Evgeny User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Kris Kiger Cc: "Pgsql-Admin (E-mail)" Subject: Re: Functions and transactions References: <421CF15C.6010306@turtle-entertainment.de> <421CF1CE.3000800@turtle-entertainment.de> <422F6203.1090800@musicrebellion.com> <422F65A7.1070303@mail.jct.ac.il> <422F6644.3010504@musicrebellion.com> In-Reply-To: <422F6644.3010504@musicrebellion.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus: scanned by VAMS 2005 version 8.635 from Tue Mar 8 15:52:53 2005 CET X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/137 X-Sequence-Number: 16635 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