On Sep 14, 2006, at 8:19 AM, Gregory Stark wrote:
Theo Schlossnagle <[EMAIL PROTECTED]> writes:
We don't use savepoint's too much. Maybe one or two across out 1k
or so
pl/pgsql procs.
Well if they're in a loop...
We use dbi-link which is plperl. Perhaps that is somehow creating
subtransactions?
Ok, I more or less see what's going on. plperl creates a
subtransaction
whenever you execute an SPI query from inside a perl function.
That's so that
errors in the query can throw perl exceptions and be caught in the
perl code.
So if your DBI source is an SPI connection (and not a connection to
some other
database source) you will get a subtransaction for every
remote_select() call.
In addition, dbi-link seems to do its work by creating a trigger
which fires
once for every record you modify in its "shadow table". I'm not
sure what
you're doing with those records but if your sending them on via an SPI
connection to another table you'll get a subtransaction every time
the trigger
fires.
It would be interesting to know which of these it is because in the
former
case it may be something that could be fixed. We only really need
to remember
subtransactions that have hit disk. But I rather suspect it's the
latter case
since it's easy to see you firing a trigger 4.3M times.
My remote_select() in DBI does a RETURN NEXT $row; You think that
might be the problem? If that's the case -- that needs to be fixed.
The metalevel of the remote_select is:
remote_select(query) {
handle = remote.prepare(query)
handle.execute;
while(row = handle.fetchrow_hashref) {
return_next $row;
}
handle.close;
return;
}
If that return_next is causing an subtransaction that would explain
my world of pain well.
// Theo Schlossnagle
// CTO -- http://www.omniti.com/~jesus/
// OmniTI Computer Consulting, Inc. -- http://www.omniti.com/
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
