[HACKERS] explain doesn't work with execute using

[Pavel Stehule]

Hello

I found following bug - using explain in stored procedures like:

CREATE OR REPLACE FUNCTION test(int)
RETURNS void AS $$
DECLARE s varchar;
BEGIN
  FOR s IN EXECUTE 'EXPLAIN SELECT * FROM o WHERE a = $1+1' USING $1 LOOP
    RAISE NOTICE '%', s;
  END LOOP;
END; $$
LANGUAGE plpgsql;

produce wrong result. Real plan is correct, etc variables are
substituted. Bud this explain show variables. Reason is in difference
in pflags. Planner works with PARAM_FLAG_CONST's variables, but
explain (proc ExplainQuery) get variables from Portal, where flag
PARAM_FLAG_CONST is lost.

Portal
SPI_cursor_open_with_args(const char *name,
                                                  const char *src,
                                                  int nargs, Oid *argtypes,
                                                  Datum *Values, const
char *Nulls,
                                                  bool read_only, int
cursorOptions)
{
   ...
        paramLI = _SPI_convert_params(nargs, argtypes,
                                                                  Values, Nulls,

PARAM_FLAG_CONST);

       // variables are correct

but
       result = SPI_cursor_open(name, &plan, Values, Nulls, read_only);
       // result->portalParams lost flags

Portal
SPI_cursor_open(const char *name, SPIPlanPtr plan,
                                Datum *Values, const char *Nulls,
                                bool read_only)
{
        CachedPlanSource *plansource;
        CachedPlan *cplan;
        List       *stmt_list;
        char       *query_string;
        ParamListInfo paramLI;
 ....
        if (plan->nargs > 0)
        {
                /* sizeof(ParamListInfoData) includes the first array
element */
                paramLI = (ParamListInfo) palloc(sizeof(ParamListInfoData) +

(plan->nargs - 1) *sizeof(ParamExternData));
                paramLI->numParams = plan->nargs;

                for (k = 0; k < plan->nargs; k++)
                {
                        ParamExternData *prm = &paramLI->params[k];

                        prm->ptype = plan->argtypes[k];

/***************************************************/
                        prm->pflags = 0; // correct flags is overwritten
/***************************************************/
                        prm->isnull = (Nulls && Nulls[k] == 'n');
                        if (prm->isnull)
                        {
                                /* nulls just copy */
                                prm->value = Values[k];
                        }

so this is strange bug - EXECUTE USING use well plan, but isn't
possible verify it.

Regards
Pavel Stehule

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers