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@xxxxxxxx <mailto:vka@xxxxxxxx>>
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?
