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 7BAA9561ED for ; Wed, 9 Mar 2005 21:10:31 +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 49354-06 for ; Wed, 9 Mar 2005 21:10:28 +0000 (GMT) Received: from titan.internal (dsl093-203-229.ind1.dsl.speakeasy.net [66.93.203.229]) by svr1.postgresql.org (Postfix) with ESMTP id DD08D558B1 for ; Wed, 9 Mar 2005 21:10:27 +0000 (GMT) Received: from [10.0.1.102] (helo=[10.0.1.102] ident=kris) by titan.internal with asmtp (Exim 4.34 #1 (Debian)) id 1D98Rm-0002BQ-1J for ; Wed, 09 Mar 2005 16:10:26 -0500 Message-ID: <422F6644.3010504@musicrebellion.com> Date: Wed, 09 Mar 2005 16:10:28 -0500 From: Kris Kiger User-Agent: Mozilla Thunderbird 0.9 (X11/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "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> In-Reply-To: <422F65A7.1070303@mail.jct.ac.il> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/133 X-Sequence-Number: 16631 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 > >