Обсуждение: 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. 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>
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
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
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
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
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>