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 736D256447 for ; Wed, 9 Mar 2005 21:07:59 +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 48684-08 for ; Wed, 9 Mar 2005 21:07:52 +0000 (GMT) Received: from mail2.cc.huji.ac.il (real-outmail.cc.huji.ac.il [132.64.1.18]) by svr1.postgresql.org (Postfix) with ESMTP id 06E0256374 for ; Wed, 9 Mar 2005 21:07:51 +0000 (GMT) Received: from mail2.cc.huji.ac.il (localhost [127.0.0.1]) by mail2.cc.huji.ac.il (Postfix) with SMTP id 84D5216363E; Wed, 9 Mar 2005 23:07:47 +0200 (IST) Received: by mail2.cc.huji.ac.il (Postfix, from userid 31998) id 7C98B16366C; Wed, 9 Mar 2005 23:07:47 +0200 (IST) Received: from [132.64.32.205] (di8-32205.dialin.huji.ac.il [132.64.32.205]) by mail2.cc.huji.ac.il (Postfix) with ESMTP id A8B5E163673; Wed, 9 Mar 2005 23:07:46 +0200 (IST) Message-ID: <422F65A7.1070303@mail.jct.ac.il> Date: Wed, 09 Mar 2005 23:07:51 +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> In-Reply-To: <422F6203.1090800@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/132 X-Sequence-Number: 16630 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