Re: Database Insertion commitment

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.