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

2017-04-05 23:22 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:

Andres Freund <andres@anarazel.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