Re: Database Insertion commitment - Mailing list pgsql-general

Jasbinder Singh Bali wrote:
> My scenario is something like this. I'll try to make it modular and
> simple.
>
> Start Function A (Written in plperlu with no subtransactions)
>      Insert 1 on tbl_abc; (fires trigger A)
>      Insert 2 on tbl_abc; (fires trigger A)
> End Function A
>
> Start Trigger A
>        check the value of col_abc in tbl_abc
>        Start Activity A if col_abc in tbl_abc doesn't is not duplicated.
> End Trigger A
>
> Now, if Insert 1 inserts col_abc  = 'xyz' in tbl_abc
> and Insert 2 inserts the same value of col_abc ='xyz' the its not able
> to see the value of insert 1
> and erroneously starts Activity A that it should not actually.
>
> Do you think I am missing something vital here?
> I'm kind of stuck and confused because fundamentally Insert 2 should
> be able to see the value of Insert 1 as there is no subtransaction
> involved.
>
> Thanks,
> ~Jas
>
> On 7/9/07, *Viatcheslav Kalinin* <vka@ipcb.net <mailto:vka@ipcb.net>>
> wrote:
>
>     Jasbinder Singh Bali wrote:
>     > Hi,
>     >
>     > If I have a series of Insert statements within a loop in a
>     function on
>     > the same table.
>     > Would an Insert be able to see the values of previous insert in
>     that
>     > table ?
>     > I just wanted to know, when would the records be committed, as
>     in, is
>     > it after the whole function is done for with its execution or
>     > right after one single insert.
>     >
>     > Right now what I'm observing is that all the inserts are committed
>     > after the whole function is executed and one insert doesn't see the
>     > value of its previous insert.
>     > In this scenario, how can an insert see the value of its previous
>     > insert even though the whole transaction that lies within the
>     function
>     > is not complete.
>     >
>     > Thanks,
>     > ~Jas
>     Functions are run in a single separate transaction (unless then have
>     BEGIN ... EXCEPTION ... END block inside them which implies
>     subtransaction) thus inside a function all statements can see
>     results of
>     the previous ones just like if you ran them one by one. All
>     changes the
>     function does are committed at the end of the transaction, whether
>     they
>     are visible or not from the outside of that transaction depends on
>     the
>     transaction isolation level. There are only two distinct levels of
>     isolation in Postgresql: READ COMMITTED and SERIALIZABLE, hence
>     uncommitted data can never be seen before the transaction which
>     changed
>     them is over, the second one makes transaction fully independent
>     just as
>     the name states.
>
>

Hmm, afaik triggers are run within the same transaction so it shouldn't
really matter if the trigger is involved. I've made some tests too
(written in plpgsql, I hope you are fine with it):

CREATE TABLE test (x varchar);

CREATE OR REPLACE FUNCTION "public"."test_trg" () RETURNS trigger AS
$body$
BEGIN
    perform 1 from test1 where x = new.x;
    if not found then
         raise info 'not found';
    else
         raise info 'found';
    end if;

    return new;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

CREATE TRIGGER "trigger1" BEFORE INSERT
ON "public"."test" FOR EACH ROW
EXECUTE PROCEDURE "public"."test_trg"();

CREATE OR REPLACE FUNCTION "public"."test" () RETURNS "pg_catalog"."void" AS
$body$
begin
    insert into test values ('xxx');
    insert into test values ('xxx');
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
----------------

select test();

 >INFO:  not found
 >CONTEXT:  SQL statement "INSERT INTO test values ('xxx')"
 >PL/pgSQL function "test" line 5 at SQL statement
 >INFO:  found
 >CONTEXT:  SQL statement "INSERT INTO test values ('xxx')"
 >PL/pgSQL function "test" line 6 at SQL statement


As you can see it has found inserted value on the second insert. Could
it be that you misused after trigger instead of before?