Re: Avoiding bad prepared-statement plans. - Mailing list pgsql-hackers

Greg Stark wrote:
> On Mon, Feb 15, 2010 at 7:11 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > 1. Why do we only do bind-level planning for anonymous wire-level queries?
> >
> > 2. I realize we did anonymous-only because that was the only way we had
> > in the protocol to _signal_ bind-time planning, but didn't we think of
> > this when we were implementing the wire-level protocol?
>
> Is there any other difference between anonymous and non-anonymous
> queries? If this is the only major difference do we need to separate
> them? Is there any particular reason a driver would need two prepared
> queries if they're both just going to be planned at execution time?

Well, anonymous prepared queries are replanned for _every_ bind, so I
don't see a huge value in allowing multiple unnamed queries, except you
have to re-send the old query to prepare if you need to reuse it.

In fact, this behavior was not totally clear so I updated the
documentation a little with the attached patch.

> Incidentally, can you have two active anonymous portals at the same time?

No, the first one is deleted when the second is created, i.e., our docs
have:

    An unnamed prepared statement lasts only until the next Parse statement
    specifying the unnamed statement as destination is issued.  (Note that a
    simple Query message also destroys the unnamed statement.)

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/protocol.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/protocol.sgml,v
retrieving revision 1.78
diff -c -c -r1.78 protocol.sgml
*** doc/src/sgml/protocol.sgml    3 Feb 2010 09:47:19 -0000    1.78
--- doc/src/sgml/protocol.sgml    16 Feb 2010 20:11:41 -0000
***************
*** 737,745 ****
     <para>
      The unnamed prepared statement is likewise planned during Parse processing
      if the Parse message defines no parameters.  But if there are parameters,
!     query planning occurs during Bind processing instead.  This allows the
!     planner to make use of the actual values of the parameters provided in
!     the Bind message when planning the query.
     </para>

     <note>
--- 737,745 ----
     <para>
      The unnamed prepared statement is likewise planned during Parse processing
      if the Parse message defines no parameters.  But if there are parameters,
!     query planning occurs every time Bind parameters are supplied.  This allows the
!     planner to make use of the actual values of the parameters provided by
!     each Bind message, rather than use generic estimates.
     </para>

     <note>
Index: src/backend/tcop/postgres.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.588
diff -c -c -r1.588 postgres.c
*** src/backend/tcop/postgres.c    13 Feb 2010 01:32:19 -0000    1.588
--- src/backend/tcop/postgres.c    16 Feb 2010 20:11:45 -0000
***************
*** 1469,1475 ****
      }
      else
      {
!         /* special-case the unnamed statement */
          psrc = unnamed_stmt_psrc;
          if (!psrc)
              ereport(ERROR,
--- 1469,1475 ----
      }
      else
      {
!         /* Unnamed statements are re-prepared for every bind */
          psrc = unnamed_stmt_psrc;
          if (!psrc)
              ereport(ERROR,