Re: PoC plpgsql - possibility to force custom or generic plan

2017-04-05 22:33 GMT+02:00 Andres Freund <andres(at)anarazel(dot)de>:

> Hi,
>
>
> I'd like some input from other committers whether we want this. I'm
> somewhat doubtful, but don't have particularly strong feelings.
>
>
> > +
> > + <sect2 id="plpgsql-declaration-pragma">
> > + <title>Block level PRAGMA</title>
> > +
> > + <indexterm>
> > + <primary>PRAGMA</>
> > + <secondary>in PL/pgSQL</>
> > + </indexterm>
> > +
> > + <para>
> > + The block level <literal>PRAGMA</literal> allows to change the
> > + <application>PL/pgSQL</application> compiler behavior. Currently
> > + only <literal>PRAGMA PLAN_CACHE</literal> is supported.
>
> Why are we doing this on a block level?
>

There are few reasons:

1. it is practical for some cases to mix more plan strategies in one
function

a)

FOR IN simple_select
LOOP
ENFORCE ONE SHOT PLANS
BEGIN
.. queries ..
END;
END LOOP;

b)

ENFORCE ONE SHOT PLANS
BEGIN
FOR IN complex query requires one shot plan
LOOP
RETURNS TO DEFAULT PLAN CACHE
BEGIN
.. queries ..
END;
END LOOP;

2. This behave is defined in Ada language, and in PL/SQL too. If we will
have autonomous transactions, then we can have a equal functionality

a) run complete function under autonomous transaction
b) run some parts of function (some blocks) under autonomous transaction

It is not necessary, but it can avoid to generate auxiliary functions.

>
>
> > +<programlisting>
> > +CREATE FUNCTION enforce_fresh_plan(_id text) RETURNS boolean AS $$
> > +DECLARE
> > + PRAGMA PLAN_CACHE(force_custom_plan);
> > +BEGIN
> > + -- in this block every embedded query uses one shot plan
>
> *plans
>
>
> > + <sect3 id="PRAGMA-PLAN_CACHE">
> > + <title>PRAGMA PLAN_CACHE</title>
> > +
> > + <para>
> > + The plan cache behavior can be controlled using
> > + <literal>PRAGMA PLAN_CACHE</>. This <literal>PRAGMA</> can be
> used both
> > + for whole function or in individual blocks. The following options
> are
>
> *functions
>
>
> > + possible: <literal>DEFAULT</literal> - default
> > + <application>PL/pgSQL</application> implementation - the system
> tries
> > + to decide between custom plan and generic plan after five query
> > + executions, <literal>FORCE_CUSTOM_PLAN</literal> - the chosen
> execution
> > + plan will be the one shot plan - it is specific for every set of
> > + used paramaters, <literal>FORCE_GENERIC_PLAN</literal> - the
> generic
> > + plan will be used from the start.
>
> I don't think it's a good idea to explain this here, this'll just get
> outdated. I think we should rather have a link here.
>
>
> > + </para>
> > +
> > + <para>
> > + <indexterm>
> > + <primary>PRAGMA PLAN_CACHE</>
> > + <secondary>in PL/pgSQL</>
> > + </indexterm>
> > + The plan for <command>INSERT</command> is always a generic
> > plan.
>
> That's this specific insert, right? Should be mentioned, sounds more
> generic to me.
>
> > +/* ----------
> > + * Returns pointer to current compiler settings
> > + * ----------
> > + */
> > +PLpgSQL_settings *
> > +plpgsql_current_settings(void)
> > +{
> > + return current_settings;
> > +}
> > +
> > +
> > +/* ----------
> > + * Setup default compiler settings
> > + * ----------
> > + */
> > +void
> > +plpgsql_settings_init(PLpgSQL_settings *settings)
> > +{
> > + current_settings = settings;
> > +}
>
> Hm. This is only ever set to &default_settings.
>
>
> > +/* ----------
> > + * Set compiler settings
> > + * ----------
> > + */
> > +void
> > +plpgsql_settings_set(PLpgSQL_settings *settings)
> > +{
> > + PLpgSQL_nsitem *ns_cur = ns_top;
> > +
> > + /*
> > + * Modify settings directly, when ns has local settings data.
> > + * When ns uses shared settings, create settings first.
> > + */
> > + while (ns_cur->itemtype != PLPGSQL_NSTYPE_LABEL)
> > + ns_cur = ns_cur->prev;
> > +
> > + if (ns_cur->local_settings == NULL)
> > + {
> > + ns_cur->local_settings = palloc(sizeof(PLpgSQL_settings));
> > + ns_cur->local_settings->prev = current_settings;
> > + current_settings = ns_cur->local_settings;
> > + }
> > +
> > + current_settings->cursor_options = settings->cursor_options;
> > +}
>
> This seems like a somewhat weird method. Why do we have a global
> settings, when we essentially just want to use something in the current
> ns?
>
>
I am not sure if I understand to question.

This settings is implemented as lazy. If ns has not any own settings, then
nothing is done. It requires some global variable, because some ns can be
skipped.

My first implementation was 1:1 .. ns:settings - but it add some overhead
for any ns although ns has not own settings.

Regards

Pavel

>
>
> - Andres
>