Re: The enormous s->childXids problem

Theo Schlossnagle <jesus(at)omniti(dot)com> writes:

> I've tracked the problem I mentioned earlier with my 4.5 million node linked
> list of s->childXids down. We use plperl to connect to Oracle over DBI. The
> select function is dbi-link's remote_select. remote_select will perform the
> query and then for each row return_next which calls the SPI.xs stuff to do
> plperl_return_next which is wrapped in a PG_TRY block. I see the value of the
> try block to kick back sensible errors to perl, but creating childXids for
> every row of a setof seems wildly excessive. What's the harm in simply not
> TRY'ing around there?

PG_TRY alone just sets up a longmp handler. Often it is used in conjunction
with subtransactions though and I wouldn't be surprised if that was happening
here but it's not quite right there.

If you look in plperl.c you'll see a number of places that do call
BeginInternalSubTransaction (and each one has a comment above that mentions
"sub-transaction"). They use PG_TRY blocks to recover control so they can
abort the subtransaction and throw a perl error.

However plperl_return_next is one of the few cases that *doesn't*. I'm not
sure exactly by what logic it gets an exception. I suppose the idea is that
there aren't very many SQL errors return next can actually trigger.

Anyways, perhaps you're also calling one of the other functions like
plperl_spi_{exec,query,fetchrow,prepare,exec_prepared,query_prepared}? I
wouldn't expect so given that you're actually doing Oracle queries though.

> I ask with respect to the suitability as general solution and as the
> suitability for my acute issue (of a 5 million row setof returned from that).
> Will it break anything?

If you don't have a subtransaction then you can't really recover from any
error. There will be locks left over, memory allocated that isn't freed etc.
PG_TRY will recover control but you're pretty much stuck rethrowing it
eventually. In other words, any errors will require that you roll back the
whole transaction.

I'm not sure what happens to perl when you longjmp out of the perl interpreter
instead of finishing execution normally. Subtransactions and PG_TRY are both
relatively new and Postgres has had plperl for a lot longer so I imagine
there's a way to get it to work. I don't remember how it used to work though.
Perhaps it set a flag and returned from the perl interpreter normally and then
rethrew the error from outside the perl interpreter? Or perhaps perl is ok
with you longjmping out from inside it.

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