From: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
To: Kris Kiger <kris@musicrebellion.com>
Cc: Pgsql-Admin (E-mail) <pgsql-admin@postgresql.org>
Subject: Re: Functions and transactions
Date: Wed, 09 Mar 2005 23:07:51 +0200
Message-ID: <422F65A7.1070303@mail.jct.ac.il> (raw)
In-Reply-To: <422F6203.1090800@musicrebellion.com>
References: <421CF15C.6010306@turtle-entertainment.de>
<421CF1CE.3000800@turtle-entertainment.de>
<422F6203.1090800@musicrebellion.com>
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
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: tsurkin@mail.jct.ac.il, kris@musicrebellion.com
Subject: Re: Functions and transactions
In-Reply-To: <422F65A7.1070303@mail.jct.ac.il>
References: <421CF15C.6010306@turtle-entertainment.de> <421CF1CE.3000800@turtle-entertainment.de> <422F6203.1090800@musicrebellion.com> <422F65A7.1070303@mail.jct.ac.il>
* 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