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

>
>
> I think a GUC is a decent, though not perfect, mechanism for this.
> This problem isn't restricted to PL/pgsql; indeed, the cases I've seen
> have come via prepared queries, not PL/pgsql functions. Even without
> that, one advantage of a GUC is that they are fairly broadly
> understood and experienced users understand what they can do with
> them. They can be set at various different scopes (system, user,
> database, SET clause for a particular function) and it's relatively
> convenient to do so. Some kind of PL/pgsql-specific PRAGMA syntax is
> more likely to be overlooked by users who would actually benefit from
> it, and also won't cover non-PL/pgsql cases. If we were going to go
> the PRAGMA route, it would make more sense to me to define that as a
> way of setting a GUC for the scope of one PL/pgsql block, like PRAGMA
> SETTING(custom_plan_tries, 0). I think it is in general unfortunate
> that we don't have a mechanism to change a GUC for the lifespan of one
> particular query, like this:
>
> LET custom_plan_tries = 0 IN SELECT ...
>
> I bet a lot of people would find that quite convenient. The problem
> of needing to set a planner GUC for one particular query is pretty
> common, and Pavel is absolutely right that having to do SET beforehand
> and RESET afterward is ugly.
>

Currently only prepared statement and PLpgSQL uses plan cache, what I know
- so some special GUC has sense only for this two environments.

I understand so GUC can be used and has sense when users cannot to modify
source code or when they want to apply this change globally.

For PLpgSQL there should be block, or statement level related syntax -
PRAGMA is well known alternative from ADA language. Maybe another syntax
like

BEGIN SET xxx = 1, yyy = 2 .. END ... theoretically we can introduce block
level GUC. I don't like it, because there are successful syntax from ADA,
PL/SQL.

Maybe can be useful enhance a PREPARE command to accept second optional
parameter for plan cache controlling - I have not idea about syntax.

Regards

Pavel

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>