Re: Understanding EXPLAIN ANALYZE output

On Thu, Feb 10, 2005 at 08:11:42PM -0500, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
> > Do PL/Perl and the other PLs require any changes to make this work?
> > I tried $rv = spi_exec_query("EXPLAIN $query") but $rv contained
> > only the following:
>
> > $rv->{processed} = 0
> > $rv->{status} = SPI_OK_UTILITY
>
> Looking around, it seems that the PLs (and a lot of contrib modules) are
> using SPI_execute rather than the SPI cursor features --- which is what
> I fixed. It looks from the code like SPI_execute does the right things
> in terms of returning a tuple table, but it returns completion code
> SPI_OK_UTILITY; and these callers only expect there to be result rows
> when SPI_execute returns SPI_OK_SELECT.
>
> Seems we have three possibilities to fix this:
>
> 1. Alter SPI_execute to say SPI_OK_SELECT after executing a utility
> statement that returns tuples.

This doesn't sound good.

> 2. Leave SPI_execute alone and fix the callers.

By "fix the callers," do you mean "outfit them with SPI cursor
features," or something else? Outfitting with SPI cursor features
would solve some problems I've been having in re: materializing entire
relations into memory, and I suspect that others will run into them,
too. How big a job would this be?

> 3. Invent a new result code (SPI_OK_UTILITY_TUPLES maybe?) to return
> in this case ... which means changing both SPI_execute *and* the
> callers. It would probably even propagate up to user code, since
> plperl for one exposes the set of SPI result codes...

This sounds *really* bad.

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!