Re: Interesting tight loop

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.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com