Re: sql insert function

My function does not call commit, and I have autocommit turned off.

In the postgresql server logs it looks like this without using the function:

LOG:  statement: begin
LOG:  statement: insert into...
LOG:  statement: insert into...
LOG:  statement: insert into...
LOG:: statement: commit
LOG: statement: begin

With the function it does this:

LOG:  statement: begin
LOG:  statement: insert into...
LOG:  statement:
        insert into taxship(s_oid,order_id,mer_id,tax,shipping) values
('0000-10000000',10000000,'0000',1,1);
        END
CONTEXT:  SQL function "taxship" during startup
LOG:  statement: insert into...
LOG:: statement: commit
WARNING:  there is no transaction in progress
LOG: statement: begin


In both cases all the data gets inserted correctly,  but I would like to
know how I could be getting the warning that there is no open transaction.
I am running with autocommit turned off, so it seems there would have to be
a transaction or the data wouldn't get inserted.  Either that or there is
something else that is causing the data to commit without an explicit commit
being called?  I'm at a loss.




> Chris Ochs wrote:
> > My program starts a transaction, does about 20 inserts, then commits.
When
> > I replace once of the inserts with a function that does the insert, when
I
> > do the commit I get this message:
> >
> > WARNING:  there is no transaction in progress
> >
> > The inserts all commit fine.  Do functions used through DBD::Pg do
something
> > like turn on autocommit after a function is called?
>
> Is your function calling 'commit' itself?  If so, it could be committing
> before your SQL statement issues the 'commit', thus attempting to commit
> a transaction which doesn't exist any more.
>
> DBD::Pg defaults to "AutoCommit" behaviour, unless you explicitly turn
> it off:
>
>     my $dbh = DBI->connect (
>        "DBI:Pg:dbname=database", "user" , "password",
>        {AutoCommit => 0}
>     );
>
> HTH
> Alex Satrapa
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>