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

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

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)
  while(row = handle.fetchrow_hashref) {
    return_next $row;

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

Reply via email to