Re: Prepared Statements vs. pgbouncer

On Sun, Sep 30, 2007 at 08:11:04PM +1300, Oliver Jowett wrote:
> Paul Lindner wrote:
>
> >2) If we try to prepare a statement with an hashed name and it already
> >   exists then we ignore the error and continue.
>
> Errors will cause the current transaction to fail..

Looking through the backend code and the protocol flow documenation it
appears we could just close the statement name and it won't cause an
error:

   switch (close_type) {
              case 'S':
                if (close_target[0] != '\0')
                   DropPreparedStatement(close_target, false);
                else

where

   void DropPreparedStatement(const char *stmt_name, bool showError)

So amend number 2 to say:

  2) Before preparing a statement with a hashed name, send a close
     statement to insure that we don't get an error inside of a
     transaction.

This is just as performant as the current driver which will happily
re-prepare the same SQL many times.

Sadly it appears that the protocol does not allow for arbitrary
optional data to be sent along with the query.

For example, it would be fairly easy to modify gram.y to support IF
EXISTS or OR REPLACE -- getting that into the protocol looks to be a
bit more difficult.

(FYI if anyone is interested in the following syntax let me know, If
there's interest I could spend some of my free time hacking the
backend)

  DEALLOCATE [IF EXISTS] <plan_name>;
  PREPARE [OR REPLACE] <plan_name> [(args, ...)] AS <query>


> >3) If we receive an error while executing a prepared statement with an
> >   hashed name the driver will try to re-prepare the statement and
> >   re-execute the query.  If an error occurs after this retry step then
> >   error are surfaced to the caller.
>
> ... so you can't just back off and try again.

Actually #3 isn't needed anymore if the deallocate/re-prepare steps
are carried out as mentioned above.

For me it still appears worth the effort to modify the driver to
support what I've outlined.  I do hope that you'll find any work done
useful for general consumption.

--
Paul Lindner        ||||| | | | |  |  |  |   |   |
lindner@inuus.com