Thread: Database Insertion commitment

Database Insertion commitment

From
"Jasbinder Singh Bali"
Date:
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

Re: Database Insertion commitment

From
"A. Kretschmer"
Date:
am  Mon, dem 09.07.2007, um  2:53:48 -0400 mailte Jasbinder Singh Bali folgendes:
> 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 ?

Inside this function, yes. Outside the function no.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: Database Insertion commitment

From
Viatcheslav Kalinin
Date:
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.


Re: Database Insertion commitment

From
"Jasbinder Singh Bali"
Date:
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> 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.


Re: Database Insertion commitment

From
Ragnar
Date:
On mán, 2007-07-09 at 11:24 +0400, Viatcheslav Kalinin wrote:
> Jasbinder Singh Bali wrote:
> > 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 ?
> Functions are run in a single separate transaction

this is not correct.

functions run in the transaction where they were called,
not in a separate one.

gnari



Re: Database Insertion commitment

From
Viatcheslav Kalinin
Date:
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?


Re: Database Insertion commitment

From
Ragnar
Date:
On mán, 2007-07-09 at 03:34 -0400, 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.

maybe you should provide us with a simple test case, to illustrate your
problem.

gnari



Re: Database Insertion commitment

From
"Jasbinder Singh Bali"
Date:


On 7/9/07, Viatcheslav Kalinin <vka@ipcb.net> wrote:
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?


The only difference between you test case my a sample test case that I would provide is the Perform part in the trigger function.

In my program Perform part is about opening a socket connection with a Unix Tools server that runs traceroute and populates records in  table test.

Before this Perform part, I would check if table test has one row whose col1 value is 'xyz' say.

If No, then Perform
else
 don't perform.

One reason I see that new insert does't see the values of old insert is because as soon as socket connection is established, my trigger returns and 1st insert is complete even though I'm not sure whether the unix tools server has already inserted values in table test or not. There might be a time lag based on how fast traceroute returns, though right now its very fast.


Any comments?

Thanks,
Jas

Re: Database Insertion commitment

From
Viatcheslav Kalinin
Date:
Jasbinder Singh Bali wrote:
>
> One reason I see that new insert does't see the values of old insert
> is because as soon as socket connection is established, my trigger
> returns and 1st insert is complete even though I'm not sure whether
> the unix tools server has already inserted values in table test or
> not. There might be a time lag based on how fast traceroute returns,
> though right now its very fast.
This is most likely it if I understand you scheme right.

Re: Database Insertion commitment

From
"Jasbinder Singh Bali"
Date:


On 7/9/07, Viatcheslav Kalinin <vka@ipcb.net> wrote:
Jasbinder Singh Bali wrote:
>
> One reason I see that new insert does't see the values of old insert
> is because as soon as socket connection is established, my trigger
> returns and 1st insert is complete even though I'm not sure whether
> the unix tools server has already inserted values in table test or
> not. There might be a time lag based on how fast traceroute returns,
> though right now its very fast.
This is most likely it if I understand you scheme right.

Do you see any work around for this?
Nothing that I could think of.


Re: Database Insertion commitment

From
Ragnar
Date:
On mán, 2007-07-09 at 05:22 -0400, Jasbinder Singh Bali wrote:

>
> The only difference between you test case my a sample test case that I
> would provide is the Perform part in the trigger function.
>
> In my program Perform part is about opening a socket connection with a
> Unix Tools server that runs traceroute and populates records in  table
> test.

then the inserts into table test are made in a separate transaction.


>
> One reason I see that new insert does't see the values of old insert
> is because as soon as socket connection is established, my trigger
> returns and 1st insert is complete even though I'm not sure whether
> the unix tools server has already inserted values in table test or
> not. There might be a time lag based on how fast traceroute returns,
> though right now its very fast.

you should be able to see the row if the tools server has
committed before the next trigger fires

gnari

>


Re: Database Insertion commitment

From
Perry Smith
Date:
On Jul 9, 2007, at 4:38 AM, Jasbinder Singh Bali wrote:

On 7/9/07, Viatcheslav Kalinin <vka@ipcb.net> wrote:
Jasbinder Singh Bali wrote:
>
> One reason I see that new insert does't see the values of old insert
> is because as soon as socket connection is established, my trigger
> returns and 1st insert is complete even though I'm not sure whether
> the unix tools server has already inserted values in table test or
> not. There might be a time lag based on how fast traceroute returns,
> though right now its very fast.
This is most likely it if I understand you scheme right.

Do you see any work around for this?
Nothing that I could think of. 

I did not follow the reason this is happening completely.  But, section 34.2 is titled Visibility of Data Changes (and chapter 34 is titled Triggers).

If you are inside a transaction, then the trigger should see the data (according to 34.2).  "Network delays" does not seem like a valid reason this is not working.

My thought is to not use triggers but to use rules.  These operate inside the db engine.

I hope this helps,
Perry Smith ( pedz@easesoftware.com )
Ease Software, Inc. ( http://www.easesoftware.com )

Low cost SATA Disk Systems for IBMs p5, pSeries, and RS/6000 AIX systems