pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kris Kiger <kris@musicrebellion.com>
Cc: Pgsql-Admin (E-mail) <pgsql-admin@postgresql.org>
Subject: Functions and transactions
Date: Wed, 09 Mar 2005 15:52:19 -0500
Message-ID: <422F6203.1090800@musicrebellion.com> (raw)
In-Reply-To: <421CF1CE.3000800@turtle-entertainment.de>
References: <421CF15C.6010306@turtle-entertainment.de>
	<421CF1CE.3000800@turtle-entertainment.de>

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



view thread (11+ messages)  latest in thread

Message-ID: <422F6203.1090800@musicrebellion.com>
Permalink:  ../422F6203.1090800@musicrebellion.com/
Also on:    postgresql.org/message-id/422F6203.1090800@musicrebellion.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: kris@musicrebellion.com
  Subject: Re: Functions and transactions
  In-Reply-To: <422F6203.1090800@musicrebellion.com>
  References: <421CF15C.6010306@turtle-entertainment.de> <421CF1CE.3000800@turtle-entertainment.de> <422F6203.1090800@musicrebellion.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox