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

2017-04-05 23:22 GMT+02:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Andres Freund <andres(at)anarazel(dot)de> writes:
> > I'd like some input from other committers whether we want this. I'm
> > somewhat doubtful, but don't have particularly strong feelings.
>
> I don't really want to expose the workings of the plancache at user level.
> The heuristics it uses certainly need work, but it'll get hard to change
> those once there are SQL features depending on it.
>

I am very sceptical about enhancing heuristics - but I am open to any
proposals.

The advanced users disable a plan cache with dynamic SQL. But this
workaround has strong disadvantages:

1. it is vulnerable to SQL injection
2. it is less readable

>
> Also, as you note, there are debatable design decisions in this particular
> patch. There are already a couple of ways in which control knobs can be
> attached to plgsql functions (i.e. custom GUCs and the comp_option stuff),
> so why is this patch wanting to invent yet another fundamental mechanism?
> And I'm not very happy about it imposing a new reserved keyword, either.
>

1.

custom GUC has not local scope - so it doesn't allow precious settings.
With PRAGMA I have perfect control what will be impacted.

#option has function scope

2. I'll not introduce a PRAGMA keyword just for this feature. We would to
implement autonomous transactions. There was not any objection against this
feature. The PRAGMA allows to share PL/SQL syntax and functionality.

> A bigger-picture question is why we'd only provide such functionality
> in plpgsql, and not for other uses of prepared plans.
>

It is out of scope of this patch.

>
> Lastly, it doesn't look to me like the test cases prove anything at all
> about whether the feature does what it's claimed to.
>

I can enhance regress tests - currently there are not direct access to
these attributes - so the tests can be indirect only :(

Regards

Pavel

>
> regards, tom lane
>