Re: Interesting tight loop

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

> Theo Schlossnagle <jesus@omniti.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/