Re: BUG #3244: problem with PREPARE

"William Lawrance" <bill(dot)lawrance(at)bull(dot)com> writes:
> This program that does "PQprepare" and then
> "PQexecPrepared" has worked previously, but doesn't
> work now.
> ...
> strcpy(openStmt, "declare C1 cursor for select cola"
> " from tprep"
> " where cola = $1");
> res = PQprepare(conn, "stmtopen", openStmt, 0, 0);

I looked into this a bit and found that the issue comes from my recent
changes in support of plan caching. To simplify matters, I instituted
a rule that utility statements don't have any interesting
transformations done at parse analysis time; see this new comment in
analyze.c:

* For optimizable statements, we are careful to obtain a suitable lock on
* each referenced table, and other modules of the backend preserve or
* re-obtain these locks before depending on the results. It is therefore
* okay to do significant semantic analysis of these statements. For
* utility commands, no locks are obtained here (and if they were, we could
* not be sure we'd still have them at execution). Hence the general rule
* for utility commands is to just dump them into a Query node untransformed.
* parse_analyze does do some purely syntactic transformations on CREATE TABLE
* and ALTER TABLE, but that's about it. In cases where this module contains
* mechanisms that are useful for utility statements, we provide separate
* subroutines that should be called at the beginning of utility execution;
* an example is analyzeIndexStmt.

This means that "preparing" a DECLARE CURSOR is now effectively a no-op;
it doesn't do much more than detect basic syntax errors that the Bison
grammar can catch. If you run this program without having created
the tprep table, the PQprepare doesn't fail! But the bigger problem,
at least for Bill's complaint, is that we also don't notice, let alone
assign datatypes to, any parameter symbols appearing in the query.

I don't see any particular problem in this for the other command types
that had their analyze-time processing removed; there's no value in a
parameter in CREATE VIEW, for example. But evidently there's some
interest in having parameters in prepared DECLARE CURSOR commands.

The easiest answer I can think of at the moment is to run parse analysis
for a DECLARE CURSOR and then throw away the result. To avoid this
overhead in cases where it's useless, we could probably teach analyze.c
to do it only if p_variableparams is true (which essentially would mean
that the DECLARE CURSOR came in via PQprepare or equivalent, and not as
a simply executable statement).

Plan B would be to promote DECLARE CURSOR to an "optimizable statement"
that is treated under the same rules as SELECT/UPDATE/etc, in particular
that we assume locks obtained at analysis are held through to execution.
This might be a cleaner answer overall, but I have no idea right now
about the effort required or any possible downsides.

Comments, better ideas?

regards, tom lane