Re: contrib/pg_stat_statements

ITAGAKI Takahiro wrote:
> It might be possbile to add a queryText field into QueryDesc
> and all of the codes using QueryDesc initialize the field with
> their own query texts, but it requires modifications in many
> different modules and copying query text might be needed.
> I don't want to do it only for this contrib module,
> so I'll support only top statements at the moment. Sorry.
>

Indeed, this turned out to be more difficult than I initally thought.
Nevertheless I still think that tracking nested statements is worth the
effort. Attached is a patch that adds sourceText to QueryDesc (should
apply cleanly after auto_explain.patch). This is very WIP, for one thing
it assumes that the source text pointers can be passed around freely.
But is the idea of extending QueryDesc generally acceptable? Is it OK
to make a copy of the query string?

I tested with modified pg_stat_statements (removed toplevel checks),
and much to my delight it didn't crash immediately :) I've only done
some limited testing but a lot of interesting stuff seems to turns out --
for instance we get to see the lookups generated by referential integrity
checks (could help to identify missing indexes on FK fields).

Regards,
Martin

*** a/src/backend/commands/copy.c
--- b/src/backend/commands/copy.c
***************
*** 1056,1062 **** DoCopy(const CopyStmt *stmt, const char *queryString)
          /* Create a QueryDesc requesting no output */
          cstate->queryDesc = CreateQueryDesc(plan, GetActiveSnapshot(),
                                              InvalidSnapshot,
!                                             dest, NULL, false);

          /*
           * Call ExecutorStart to prepare the plan for execution.
--- 1056,1062 ----
          /* Create a QueryDesc requesting no output */
          cstate->queryDesc = CreateQueryDesc(plan, GetActiveSnapshot(),
                                              InvalidSnapshot,
!                                             dest, NULL, false, queryString);

          /*
           * Call ExecutorStart to prepare the plan for execution.
*** a/src/backend/commands/explain.c
--- b/src/backend/commands/explain.c
***************
*** 172,178 **** ExplainOneQuery(Query *query, ExplainStmt *stmt, const char *queryString,
          plan = pg_plan_query(query, 0, params);

          /* run it (if needed) and produce output */
!         ExplainOnePlan(plan, params, stmt, tstate);
      }
  }

--- 172,178 ----
          plan = pg_plan_query(query, 0, params);

          /* run it (if needed) and produce output */
!         ExplainOnePlan(plan, params, stmt, tstate, queryString);
      }
  }

***************
*** 219,225 **** ExplainOneUtility(Node *utilityStmt, ExplainStmt *stmt,
   */
  void
  ExplainOnePlan(PlannedStmt *plannedstmt, ParamListInfo params,
!                ExplainStmt *stmt, TupOutputState *tstate)
  {
      QueryDesc  *queryDesc;
      instr_time    starttime;
--- 219,226 ----
   */
  void
  ExplainOnePlan(PlannedStmt *plannedstmt, ParamListInfo params,
!                ExplainStmt *stmt, TupOutputState *tstate,
!                const char *queryString)
  {
      QueryDesc  *queryDesc;
      instr_time    starttime;
***************
*** 237,243 **** ExplainOnePlan(PlannedStmt *plannedstmt, ParamListInfo params,
      queryDesc = CreateQueryDesc(plannedstmt,
                                  GetActiveSnapshot(), InvalidSnapshot,
                                  None_Receiver, params,
!                                 stmt->analyze);

      INSTR_TIME_SET_CURRENT(starttime);

--- 238,244 ----
      queryDesc = CreateQueryDesc(plannedstmt,
                                  GetActiveSnapshot(), InvalidSnapshot,
                                  None_Receiver, params,
!                                 stmt->analyze, queryString);

      INSTR_TIME_SET_CURRENT(starttime);

*** a/src/backend/commands/prepare.c
--- b/src/backend/commands/prepare.c
***************
*** 697,703 **** ExplainExecuteQuery(ExecuteStmt *execstmt, ExplainStmt *stmt,
                  pstmt->intoClause = execstmt->into;
              }

!             ExplainOnePlan(pstmt, paramLI, stmt, tstate);
          }
          else
          {
--- 697,703 ----
                  pstmt->intoClause = execstmt->into;
              }

!             ExplainOnePlan(pstmt, paramLI, stmt, tstate, queryString);
          }
          else
          {
*** a/src/backend/executor/functions.c
--- b/src/backend/executor/functions.c
***************
*** 309,320 **** postquel_start(execution_state *es, SQLFunctionCachePtr fcache)
          es->qd = CreateQueryDesc((PlannedStmt *) es->stmt,
                                   snapshot, InvalidSnapshot,
                                   None_Receiver,
!                                  fcache->paramLI, false);
      else
          es->qd = CreateUtilityQueryDesc(es->stmt,
                                          snapshot,
                                          None_Receiver,
!                                         fcache->paramLI);

      /* We assume we don't need to set up ActiveSnapshot for ExecutorStart */

--- 309,320 ----
          es->qd = CreateQueryDesc((PlannedStmt *) es->stmt,
                                   snapshot, InvalidSnapshot,
                                   None_Receiver,
!                                  fcache->paramLI, false, fcache->src);
      else
          es->qd = CreateUtilityQueryDesc(es->stmt,
                                          snapshot,
                                          None_Receiver,
!                                         fcache->paramLI, fcache->src);

      /* We assume we don't need to set up ActiveSnapshot for ExecutorStart */

*** a/src/backend/executor/spi.c
--- b/src/backend/executor/spi.c
***************
*** 1795,1801 **** _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI,
                  qdesc = CreateQueryDesc((PlannedStmt *) stmt,
                                          snap, crosscheck_snapshot,
                                          dest,
!                                         paramLI, false);
                  res = _SPI_pquery(qdesc, fire_triggers,
                                    canSetTag ? tcount : 0);
                  FreeQueryDesc(qdesc);
--- 1795,1802 ----
                  qdesc = CreateQueryDesc((PlannedStmt *) stmt,
                                          snap, crosscheck_snapshot,
                                          dest,
!                                         paramLI, false,
!                                         plansource->query_string);
                  res = _SPI_pquery(qdesc, fire_triggers,
                                    canSetTag ? tcount : 0);
                  FreeQueryDesc(qdesc);
*** a/src/backend/tcop/pquery.c
--- b/src/backend/tcop/pquery.c
***************
*** 37,43 **** bool        force_instrument = false;
  static void ProcessQuery(PlannedStmt *plan,
               ParamListInfo params,
               DestReceiver *dest,
!              char *completionTag);
  static void FillPortalStore(Portal portal, bool isTopLevel);
  static uint32 RunFromStore(Portal portal, ScanDirection direction, long count,
               DestReceiver *dest);
--- 37,44 ----
  static void ProcessQuery(PlannedStmt *plan,
               ParamListInfo params,
               DestReceiver *dest,
!              char *completionTag,
!              const char *sourceText);
  static void FillPortalStore(Portal portal, bool isTopLevel);
  static uint32 RunFromStore(Portal portal, ScanDirection direction, long count,
               DestReceiver *dest);
***************
*** 64,70 **** CreateQueryDesc(PlannedStmt *plannedstmt,
                  Snapshot crosscheck_snapshot,
                  DestReceiver *dest,
                  ParamListInfo params,
!                 bool doInstrument)
  {
      QueryDesc  *qd = (QueryDesc *) palloc(sizeof(QueryDesc));

--- 65,72 ----
                  Snapshot crosscheck_snapshot,
                  DestReceiver *dest,
                  ParamListInfo params,
!                 bool doInstrument,
!                 const char *sourceText)
  {
      QueryDesc  *qd = (QueryDesc *) palloc(sizeof(QueryDesc));

***************
*** 77,82 **** CreateQueryDesc(PlannedStmt *plannedstmt,
--- 79,85 ----
      qd->dest = dest;            /* output dest */
      qd->params = params;        /* parameter values passed into query */
      qd->doInstrument = force_instrument || doInstrument;    /* instrumentation wanted? */
+     qd->sourceText = sourceText;

      /* null these fields until set by ExecutorStart */
      qd->tupDesc = NULL;
***************
*** 93,99 **** QueryDesc *
  CreateUtilityQueryDesc(Node *utilitystmt,
                         Snapshot snapshot,
                         DestReceiver *dest,
!                        ParamListInfo params)
  {
      QueryDesc  *qd = (QueryDesc *) palloc(sizeof(QueryDesc));

--- 96,103 ----
  CreateUtilityQueryDesc(Node *utilitystmt,
                         Snapshot snapshot,
                         DestReceiver *dest,
!                        ParamListInfo params,
!                        const char *sourceText)
  {
      QueryDesc  *qd = (QueryDesc *) palloc(sizeof(QueryDesc));

***************
*** 105,110 **** CreateUtilityQueryDesc(Node *utilitystmt,
--- 109,115 ----
      qd->dest = dest;            /* output dest */
      qd->params = params;        /* parameter values passed into query */
      qd->doInstrument = false;    /* uninteresting for utilities */
+     qd->sourceText = sourceText;

      /* null these fields until set by ExecutorStart */
      qd->tupDesc = NULL;
***************
*** 152,158 **** static void
  ProcessQuery(PlannedStmt *plan,
               ParamListInfo params,
               DestReceiver *dest,
!              char *completionTag)
  {
      QueryDesc  *queryDesc;

--- 157,164 ----
  ProcessQuery(PlannedStmt *plan,
               ParamListInfo params,
               DestReceiver *dest,
!              char *completionTag,
!              const char *sourceText)
  {
      QueryDesc  *queryDesc;

***************
*** 168,174 **** ProcessQuery(PlannedStmt *plan,
       */
      queryDesc = CreateQueryDesc(plan,
                                  GetActiveSnapshot(), InvalidSnapshot,
!                                 dest, params, false);

      /*
       * Set up to collect AFTER triggers
--- 174,180 ----
       */
      queryDesc = CreateQueryDesc(plan,
                                  GetActiveSnapshot(), InvalidSnapshot,
!                                 dest, params, false, sourceText);

      /*
       * Set up to collect AFTER triggers
***************
*** 504,510 **** PortalStart(Portal portal, ParamListInfo params, Snapshot snapshot)
                                              InvalidSnapshot,
                                              None_Receiver,
                                              params,
!                                             false);

                  /*
                   * We do *not* call AfterTriggerBeginQuery() here.    We assume
--- 510,517 ----
                                              InvalidSnapshot,
                                              None_Receiver,
                                              params,
!                                             false,
!                                             portal->sourceText);

                  /*
                   * We do *not* call AfterTriggerBeginQuery() here.    We assume
***************
*** 1252,1265 **** PortalRunMulti(Portal portal, bool isTopLevel,
                  /* statement can set tag string */
                  ProcessQuery(pstmt,
                               portal->portalParams,
!                              dest, completionTag);
              }
              else
              {
                  /* stmt added by rewrite cannot set tag */
                  ProcessQuery(pstmt,
                               portal->portalParams,
!                              altdest, NULL);
              }

              if (log_executor_stats)
--- 1259,1272 ----
                  /* statement can set tag string */
                  ProcessQuery(pstmt,
                               portal->portalParams,
!                              dest, completionTag, portal->sourceText);
              }
              else
              {
                  /* stmt added by rewrite cannot set tag */
                  ProcessQuery(pstmt,
                               portal->portalParams,
!                              altdest, NULL, portal->sourceText);
              }

              if (log_executor_stats)
*** a/src/include/commands/explain.h
--- b/src/include/commands/explain.h
***************
*** 39,45 **** extern void ExplainOneUtility(Node *utilityStmt, ExplainStmt *stmt,
                    TupOutputState *tstate);

  extern void ExplainOnePlan(PlannedStmt *plannedstmt, ParamListInfo params,
!                ExplainStmt *stmt, TupOutputState *tstate);

  extern void ExplainOneResult(StringInfo str, QueryDesc *queryDesc,
                               bool analyze, bool verbose);
--- 39,46 ----
                    TupOutputState *tstate);

  extern void ExplainOnePlan(PlannedStmt *plannedstmt, ParamListInfo params,
!                ExplainStmt *stmt, TupOutputState *tstate,
!                const char *queryString);

  extern void ExplainOneResult(StringInfo str, QueryDesc *queryDesc,
                               bool analyze, bool verbose);
*** a/src/include/executor/execdesc.h
--- b/src/include/executor/execdesc.h
***************
*** 44,54 **** typedef struct QueryDesc
--- 44,56 ----
      DestReceiver *dest;            /* the destination for tuple output */
      ParamListInfo params;        /* param values being passed in */
      bool        doInstrument;    /* TRUE requests runtime instrumentation */
+     const char *sourceText;        /* Source text for the query */

      /* These fields are set by ExecutorStart */
      TupleDesc    tupDesc;        /* descriptor for result tuples */
      EState       *estate;            /* executor's query-wide state */
      PlanState  *planstate;        /* tree of per-plan-node state */
+
  } QueryDesc;

  /* in pquery.c */
***************
*** 57,68 **** extern QueryDesc *CreateQueryDesc(PlannedStmt *plannedstmt,
                  Snapshot crosscheck_snapshot,
                  DestReceiver *dest,
                  ParamListInfo params,
!                 bool doInstrument);

  extern QueryDesc *CreateUtilityQueryDesc(Node *utilitystmt,
                         Snapshot snapshot,
                         DestReceiver *dest,
!                        ParamListInfo params);

  extern void FreeQueryDesc(QueryDesc *qdesc);

--- 59,72 ----
                  Snapshot crosscheck_snapshot,
                  DestReceiver *dest,
                  ParamListInfo params,
!                 bool doInstrument,
!                 const char *sourceText);

  extern QueryDesc *CreateUtilityQueryDesc(Node *utilitystmt,
                         Snapshot snapshot,
                         DestReceiver *dest,
!                        ParamListInfo params,
!                        const char *sourceText);

  extern void FreeQueryDesc(QueryDesc *qdesc);