Thread: Problems with DBI transaction handling

Problems with DBI transaction handling

From
Hans-Jürgen Schönig
Date:
Does anybody else have problems with DBI transaction handling? Since 
PostgreSQL 7.3 (most likely) the stuff seems to be broken. Errors occur 
even if autocommit is set properly and rollbacks are not performed the 
way it should be.

Does anybody have similar problems? This is VERY annoying and should not 
happen.
The problem is: There is no other interface around providing persistent 
connections with Perl/Apache.

Maybe it is worth thinking about a Perl implementation of LibEasy with 
Apache persistent connections.
   Hans


<http://kernel.cybertec.at>



Re: Problems with DBI transaction handling

From
Rudy Lippan
Date:
On Tue, 4 Feb 2003, Hans-Jürgen Schönig wrote:

> Date: Tue, 04 Feb 2003 09:59:36 +0100
> From: Hans-Jürgen Schönig <hs@cybertec.at>
> To: pgsql-interfaces@postgresql.org
> Subject: [INTERFACES] Problems with DBI transaction handling
>
> Does anybody else have problems with DBI transaction handling? Since
> PostgreSQL 7.3 (most likely) the stuff seems to be broken. Errors occur
> even if autocommit is set properly and rollbacks are not performed the
> way it should be.

What version of DBD::Pg are you using? There was a fix put in for a bug
with failed transactions in v 1.21. From the chagelog:
   Properly reset transaction status after failed transaction when   autocommit is off. Properly report transaction
failuremessage.   Kai <kai@xs4all.nl> 
>
> Does anybody have similar problems? This is VERY annoying and should not
> happen.

I have been using 7.3 since the end of november, and I have not had any
problems with transactions, BTA this is just a development envionment.

What are you seeing? Do you have any sample code?  Maybe a trace?

> The problem is: There is no other interface around providing persistent
> connections with Perl/Apache.

How are you using  persistant connections?


-r




Re: Problems with DBI transaction handling

From
"Nigel J. Andrews"
Date:
On Tue, 4 Feb 2003, Hans-Jürgen Schönig wrote:

> Does anybody else have problems with DBI transaction handling? Since
> PostgreSQL 7.3 (most likely) the stuff seems to be broken. Errors occur
> even if autocommit is set properly and rollbacks are not performed the
> way it should be.
>
> Does anybody have similar problems? This is VERY annoying and should not
> happen.
> The problem is: There is no other interface around providing persistent
> connections with Perl/Apache.
>
> Maybe it is worth thinking about a Perl implementation of LibEasy with
> Apache persistent connections.

Well I have wasted so much time recently, some of it spent trying to work out
what DBI was doing. However, that was my lack of knowledge, although I'm still
not convinced my tests don't now pass by luck. I'm also using a 7.2.3 server
not 7.3 yet and it's not using the mod_perl persistent connection stuff.

So in short, I'm not surprised you're having problems but unfortunately I can't
say I experienced the same problem. Perhaps you could give a small test case
that shows the problem as I'm sure DBI must have been tested against 7.3.


--
Nigel J. Andrews



Re: Problems with DBI transaction handling

From
"Nigel J. Andrews"
Date:
On Fri, 7 Feb 2003, Hans-Jürgen Schönig wrote:

> Hi ...
>
> I am using:
>
> DBI-1.21.tar.gz
> DBD-Pg-1.01.tar.gz

The versions I'v been using are DBI 1.30 an DBD::Pg 1.13

>
> my problem is that Perl complains about connections which are not closed
> properly and so forth but the main problem is that transactions don't
> seem to commit although the entire transaction seems to work fine. Maybe
> this has to do with SERIALIZABLE mode but it is hard to say. The only
> thing I am doing is a simple loop doing UPDATEs. I have been using DBI
> for a long time but this is the first time where transactions are used
> that heavily (normally we use Pg instead).

Gotcha. The issue I was having with transactions was messages (can't remember
if warnings or errors now) about active connections. What I was doing was some
queries were basic tests of existance and others I knew returned only one row
so I only read one row. Not fetching until the likes of fetchrow_arrayref
return undef leaves the connection in an active state. Perhaps you have a
similar situation. If so you need to fetch all the data queried _or_ use the
finish() method to signal you have finished with the query.

>
> Maybe I should upgrade to a newer version but it seems as if this does
> not help in my case. I have no idea.

Not sure, it seems pretty old going by version numbers but mine are from a perl
5.8 installation I have no idea what is with the perl 5.05 that was originally
installed.

>
> What we really need: A Perl module accessing LibEasy providing an
> interface to persistent database connections. I am using ApacheDBI at
> the moment (this seems to work well).

As I said before my experience wasn't with the mod_perl and persistent
connections and I don't know how those are controlled. I can well imagine I
will be going that route sometime though so all this is useful for me as
well.

> >     Hans
>
>
>
> Nigel J. Andrews wrote:
>
> >On Tue, 4 Feb 2003, Hans-Jürgen Schönig wrote:
> >
> >
> >
> >>Does anybody else have problems with DBI transaction handling? Since
> >>PostgreSQL 7.3 (most likely) the stuff seems to be broken. Errors occur
> >>even if autocommit is set properly and rollbacks are not performed the
> >>way it should be.
> >>
> >>Does anybody have similar problems? This is VERY annoying and should not
> >>happen.
> >>The problem is: There is no other interface around providing persistent
> >>connections with Perl/Apache.
> >>
> >>Maybe it is worth thinking about a Perl implementation of LibEasy with
> >>Apache persistent connections.
> >>
> >>
> >
> >Well I have wasted so much time recently, some of it spent trying to work out
> >what DBI was doing. However, that was my lack of knowledge, although I'm still
> >not convinced my tests don't now pass by luck. I'm also using a 7.2.3 server
> >not 7.3 yet and it's not using the mod_perl persistent connection stuff.
> >
> >So in short, I'm not surprised you're having problems but unfortunately I can't
> >say I experienced the same problem. Perhaps you could give a small test case
> >that shows the problem as I'm sure DBI must have been tested against 7.3.
> >
> >
> >
> >
>
>
>
>
>
>

--
Nigel J. Andrews



Re: Problems with DBI transaction handling

From
Rudy Lippan
Date:
On Fri, 7 Feb 2003, Hans-Jürgen Schönig wrote:

>
> I am using:
>
> DBI-1.21.tar.gz
> DBD-Pg-1.01.tar.gz

They are a bit old, but no major bug fixen since then (on the DBD::Pg
side, I do not remember about DBI offhand)

>
> my problem is that Perl complains about connections which are not closed
> properly and so forth but the main problem is that transactions don't
> seem to commit although the entire transaction seems to work fine. Maybe

That could be your problem.  When you close a connection, or perl closes
the connection for you,  the driver will automatically issue a rollback
because it will assume that since you have not called commit, you did not
want the data saved to the database (think along the lines of an untrapped
die that causes perl to terminate your program -- do you want the data
commited?)

> this has to do with SERIALIZABLE mode but it is hard to say. The only

Shouldn't.

> thing I am doing is a simple loop doing UPDATEs. I have been using DBI
> for a long time but this is the first time where transactions are used
> that heavily (normally we use Pg instead).
>

Do you have sample code that shows the problem?


> Maybe I should upgrade to a newer version but it seems as if this does
> not help in my case. I have no idea.
>
> What we really need: A Perl module accessing LibEasy providing an
> interface to persistent database connections. I am using ApacheDBI at
> the moment (this seems to work well).

LibEasy?  Have a URL?  Google is not giving me much.


Interesting.  Apache::DBI should overload the disconnect method so that
you never get disconnected from the databse; and hence, you should never
see bit about connections not being closed properly?  Do you, have
a die, or an exit() that is causing your process to terminate before you
call commit()?

You also might want to call 'DBI->trace(<trace level 1..9>);' so that you
can see exactly what is going on.


-r



Re: Problems with DBI transaction handling

From
Hans-Jürgen Schönig
Date:
Hi ...

I am using:

DBI-1.21.tar.gz
DBD-Pg-1.01.tar.gz

my problem is that Perl complains about connections which are not closed
properly and so forth but the main problem is that transactions don't
seem to commit although the entire transaction seems to work fine. Maybe
this has to do with SERIALIZABLE mode but it is hard to say. The only
thing I am doing is a simple loop doing UPDATEs. I have been using DBI
for a long time but this is the first time where transactions are used
that heavily (normally we use Pg instead).

Maybe I should upgrade to a newer version but it seems as if this does
not help in my case. I have no idea.

What we really need: A Perl module accessing LibEasy providing an
interface to persistent database connections. I am using ApacheDBI at
the moment (this seems to work well).
   Hans



Nigel J. Andrews wrote:

>On Tue, 4 Feb 2003, Hans-Jürgen Schönig wrote:
>
>
>
>>Does anybody else have problems with DBI transaction handling? Since
>>PostgreSQL 7.3 (most likely) the stuff seems to be broken. Errors occur
>>even if autocommit is set properly and rollbacks are not performed the
>>way it should be.
>>
>>Does anybody have similar problems? This is VERY annoying and should not
>>happen.
>>The problem is: There is no other interface around providing persistent
>>connections with Perl/Apache.
>>
>>Maybe it is worth thinking about a Perl implementation of LibEasy with
>>Apache persistent connections.
>>
>>
>
>Well I have wasted so much time recently, some of it spent trying to work out
>what DBI was doing. However, that was my lack of knowledge, although I'm still
>not convinced my tests don't now pass by luck. I'm also using a 7.2.3 server
>not 7.3 yet and it's not using the mod_perl persistent connection stuff.
>
>So in short, I'm not surprised you're having problems but unfortunately I can't
>say I experienced the same problem. Perhaps you could give a small test case
>that shows the problem as I'm sure DBI must have been tested against 7.3.
>
>
>
>





--
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at
<http://cluster.postgresql.at>, www.cybertec.at
<http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>