Re: Database Insertion commitment

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> 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.
>
>