Re: Understanding EXPLAIN ANALYZE output

On Thu, Feb 10, 2005 at 09:51:18PM -0500, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > On Thu, Feb 10, 2005 at 08:11:42PM -0500, Tom Lane wrote:
> >> 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.
>
> It does seem like it's obscuring a distinction that some callers might
> care about.  On the other hand, it would be a localized solution.
>
> >> 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?
>
> I meant teach them to accept rows when the result is either OK_SELECT
> or OK_UTILITY.

At least in pl/perl, this appears to be a one-line change.

> I'm not volunteering to cursor-ify all the PLs ;-) ... not that I
> have anything against it, it's just not high on my own to-do list.

Any idea what size such a project might be?

> > How big a job would this be?
>
> Big, I would think; in most cases it would mean defining a new API
> to expose to users of the PL, no?

I suppose it would.  spi_exec_query() appears to be a thin wrapper
around an SPI utility of a similar name.  The needed functions appear
to be the rest of the interface functions, possibly with the exception
of SPI_saveplan().

> >> 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.
>
> It's the only one that really preserves a clean distinction between
> the various cases.
>
> After thinking it over, I realize that #2 would affect user code
> too, since the knowledge that SPI_OK_UTILITY might imply rows are
> available would propagate right up to anything that could see the
> result code.  (Exposing that code to plperl users might not have
> been such a hot idea, but I suppose it's done now.)  So I now do not
> like #2: it saves nothing in terms of the amount of code we have to
> touch, and it loses the distinction between utility statements that
> can return tuples and those that can't.

Where is this distinction in SPI?

> The realistic alternatives are #1 (small code change, but a bit
> ugly) and #3 (lots of code change, but preserves a distinction that
> some people may care about).

I think #2 in its weaker form[1] still has some potential.

Cheers,
D

[1] Not cursor-ifying.  Although cursor-ifying the PL's would be a
Good Thing(TM))
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!