Problems with DBI, DBD::Pg and Postgres 7.2. - Mailing list pgsql-general

Hello,

we're writing a small piece of software in Perl, utilizing a Postgres 7.2
database via DBI and DBD::Pg.

Our problem is that we get this message while running our software:

DBD::Pg::db do failed: server closed the connection unexpectedly at
/usr/local/lib/perl5/5.6.1/Atomic/afv3/urls.pm line 542.

The line 542 is just a Do, and I won't type it out since this can choke of any
of the SELECTs or UPDATEs we're doing.

The query it chokes on is among others this one:

UPDATE urldata SET eflags=0,modified='2002-02-23 17:40:52
+0:00',localservertime=1014479227,bytesize=35518,pagecrc=0,counter=counter+1,req
uesttime='0.447651982307434',cache='web/www.hn.se/arkiv.php?avdelning_1=106&mont
h=2&submit=Visa&year=2002' WHERE id = 217

We've tried reducing it, but at that point in the script, it doesn't matter if
we run this query or anything as simple as a very small INSERT or a DELETE, it
all chokes.

We can reproduce this as many times as we want - it always crashes on the same
position, but no matter what the query!

I we run the query from pgAdmin or another perl script (using the same DBI and
DBD::Pg on the same computer), it works flawlessly.

This is the last lines of the log file (level 2 debugging).

--------------------------------------------------------------------------------
----------------------------------------------------------------------

DEBUG:  query: INSERT INTO urldata
(spider_id,session_id,language,source,url,urlcrc,parent,depth,flags,eflags,frame
,mainframe,delete
d,bytesize,pagecrc,contcrc,linkname,linkpos,links,created,modified,counter,lasts
een,requesttime,localservertime,title,summary,cache)

VALUES(22,23,'SV',NULL,'http://www.hn.se/arkiv.php?avdelning_1=127&month=2&submi
t=Visa&year=2002',2471205215,0,0,2,0,0,0,0,0,0,0,''
,0,NULL,'2002-02-23 17:44:21 +0:00','1970-01-01 01:00:00 +0:00',0,'2002-02-23
17:44:21 +0:00',0,0,'','','')
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT id FROM urldata WHERE spider_id = 22 AND urlcrc =
2471205215
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT
id,url,session_id,flags,eflags,frame,bytesize,created,modified,localservertime,p
agecrc,contcrc,linkpos,counter
,title,summary FROM urldata WHERE spider_id = 22 AND urlcrc = 3297898899
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: INSERT INTO urldata
(spider_id,session_id,language,source,url,urlcrc,parent,depth,flags,eflags,frame
,mainframe,delete
d,bytesize,pagecrc,contcrc,linkname,linkpos,links,created,modified,counter,lasts
een,requesttime,localservertime,title,summary,cache)

VALUES(22,23,'SV',NULL,'http://www.hn.se/arkiv.php?avdelning_1=161&month=2&submi
t=Visa&year=2002',3297898899,0,0,2,0,0,0,0,0,0,0,''
,0,NULL,'2002-02-23 17:44:21 +0:00','1970-01-01 01:00:00 +0:00',0,'2002-02-23
17:44:21 +0:00',0,0,'','','')
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT id FROM urldata WHERE spider_id = 22 AND urlcrc =
3297898899
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT
id,url,session_id,flags,eflags,frame,bytesize,created,modified,localservertime,p
agecrc,contcrc,linkpos,counter
,title,summary FROM urldata WHERE spider_id = 22 AND urlcrc = 1001135336
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: INSERT INTO urldata
(spider_id,session_id,language,source,url,urlcrc,parent,depth,flags,eflags,frame
,mainframe,delete
d,bytesize,pagecrc,contcrc,linkname,linkpos,links,created,modified,counter,lasts
een,requesttime,localservertime,title,summary,cache)

VALUES(22,23,'SV',NULL,'http://www.hn.se/arkiv.php?avdelning_1=170&month=2&submi
t=Visa&year=2002',1001135336,0,0,2,0,0,0,0,0,0,0,''
,0,NULL,'2002-02-23 17:44:21 +0:00','1970-01-01 01:00:00 +0:00',0,'2002-02-23
17:44:21 +0:00',0,0,'','','')
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT id FROM urldata WHERE spider_id = 22 AND urlcrc =
1001135336
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  proc_exit(0)
DEBUG:  shmem_exit(0)
DEBUG:  exit(0)
DEBUG:  reaping dead processes
DEBUG:  child process (pid 24958) exited with exit code 0

--------------------------------------------------------------------------------
----------------------------------------------------------------------

As you can see, this log doesn't note the last UPDATE query that fails, it just
claims that we lost connection and the child process exited with exit code 0.

Before this query
8 INSERTS
1 SELECT (to get the ID on which to run the UPDATE)
is run.

Does anybody have a clue? At least we don't :(

Best Regards,

Johan Mjönes

_______________________________________
Johan Mjönes        johan.mjones@agent25.se
Programmerare        08 - 527 90 464
Agent25            070 - 528 38 55



_______________________________________
Johan Mjönes        johan.mjones@agent25.se
Programmerare        08 - 527 90 464
Agent25            070 - 528 38 55