Re: Transient plans versus the SPI API

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> How about a new function like SPI_parse that has the new semantics?

Yeah, I'd considered that idea (and even exactly that name for it).
Howver, the disadvantage of inventing a separate entry point is that
it isn't going to be nice for multi-level call chains, of which there
are several inside the core code and probably plenty elsewhere. The
bottom level would have to do something like

if (new-behavior-wanted)
SPI_parse(args...);
else
SPI_prepare(args...);

and then invent some way for its callers to signal new-behavior-wanted,
and it won't be pretty if they all pick different ways to do that.

Plus we've already got SPI_prepare_cursor and SPI_prepare_params, each
of which would need a matching SPI_parse_foo entry point.

So if we want a knob here, I think that the sanest way to install it is
to add a couple more flag bits to the existing "int cursorOptions"
bitmask arguments of the latter two functions, perhaps

CURSOR_OPT_USE_GENERIC_PLAN
CURSOR_OPT_USE_CUSTOM_PLAN

to force generic-plan-always or custom-plan-always respectively.
(The "cursor" naming of those flag bits is starting to look a bit
unfortunate, but I'm not inclined to rename them now.)

If we set it up like that, then the default behavior with flags == 0
would be to use the heuristic plan-selection approach, and presumably
that is what you would also get from SPI_prepare (which is both coded
and documented as matching SPI_prepare_cursor with flags == 0).

So the question is whether it's okay to change the default behavior...

> Note that the SPI functions are more or less directly exposed in PL/Perl
> and PL/Python, and there are a number of existing idioms there that make
> use of prepared plans. Changing the semantics of those functions might
> upset a lot of code.

Right, but by the same token, if we don't change the default behavior,
there is going to be a heck of a lot of code requiring manual adjustment
before it can make use of the (hoped-to-be) improvements. To me it
makes more sense to change the default and then provide ways for people
to lock down the behavior if the heuristic doesn't work for them.

regards, tom lane