Re: Interesting tight loop

On Sep 14, 2006, at 8:19 AM, Gregory Stark wrote:

> Theo Schlossnagle <jesus(at)omniti(dot)com> 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/