Hi all,
I apologize in advance for cross posting and for the rather long post.
I am loading some big tables (three tables with ~580000, ~468000, and
~248000 rows) on Postgres 8.3 db with data retrieved from two different
types of network devices and from a Oracle database.
To do so in a speedy way I am using a Perl thread with its own db
connection for each device, therefore I have about 175 thread running
concurrently and 175 db connections. I just detected a weird behavior
with one of three type of threads, (I have 131 threads of this type).
In this case I load the table with data retrieved from network devices
(routers) and, as soon as done with it, in the same thread, I start to
update *same* rows just inserted with other data collected from the same
router.
Briefly the script does something like this:

....
my $insert=$pch->prepare("insert into mytable (...fields...) values
('$device', ...?...)");

while(...loop on device...){
... parsing data...
$insert->execute($1, $2, $3);
}

.... FIRST PART OF THREAD STOPS HERE ...

my $traffsh=$pch->prepare("update mytable set traffic=? where id=?");
my $pvcsh=$pch->prepare("select * from mytable where device='$device'");
$pvcsh->execute;
while($r=$pvcsh->fetchrow_hashref('NAME_lc')){
... code to retrieve statistics from device ...
$traffsh->execute($1+$2, $r->{id});
}

With the first version of my script I kept autocommit enabled. With that
version only a small part (~50k over ~460k) of the table got the update,
like Postgres did not completed the insertions in spite it was returning
from ->execute (one transaction for each insertion).
Then, to speed up things and in suspicion for an issue, I disabled
autocommit and put a ->commit after the first part of thread. With only
this modification I got almost all updates leaving only 50 (unit) rows
without update. Then I introduced another small modification by putting
a ->commit; ->disconnect; and reconnect to the db after the first part
of thread. This way only 26 rows were leaved out. Note that in all of
three versions, if I run again only the update part filtering by
"traffic is null" I get all rows updated. This strange behavior get me
to think that PG and/or DBI is returning control from commits even if
the transaction is not *really* committed, or that there is a issue with
the sequence that feeds the "id" serial field (not keeping up?). Note
that the single threads are accessing a table part well identified by
"device" field, and that I am using Perl ithreads, thus there should be
no real-threads issues, nor data interaction between threads.
Anyone have any idea what's going on?