Re: Database Insertion commitment

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(at)ipcb(dot)net <mailto:vka(at)ipcb(dot)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?