Re: NOT EXIST for PREPARE

On 22 March 2016 at 21:01, Andres Freund <andres(at)anarazel(dot)de> wrote:

> Hi,
>
>
> On 2016-03-22 12:41:43 +0300, Yury Zhuravlev wrote:
> > Do I understand correctly the only way know availability PREPARE it will
> > appeal to pg_prepared_statements?
> > I think this is not a good practice. In some cases, we may not be aware
> of
> > the PREPARE made (pgpool). Moreover, it seems popular question in the
> > Internet:
> http://stackoverflow.com/questions/1193020/php-postgresql-check-if-a-prepared-statement-already-exists
> >
> > What do you think about adding NOT EXIST functionality to PREPARE?
>
> Not very much. If you're not in in control of the prepared statements, you
> can't be sure it's not an entirely different statement. So NOT EXISTS
> doesn't really buy you anything, you'd still need to compare the
> statement somehow.
>

Yeah, agreed. I don't buy the reasoning given for using this in PgJDBC and
think it'd just be the source of new and exciting subtle bugs.

I can only see it vaguely working if the client were required to checksum
the statement text (or the server was) and compare it with a checksum
stored against the prepared statement. On mismatch, ERROR.

If the problem Yuri is trying to solve is with pgbouncer in
transaction-pooling mode, wouldn't a possible solution be PREPARE LOCAL ?
i.e. transaction-scoped prepared statements?

With PREPARE IF NOT EXISTS the client is also paying parse and network
overhead for every time you send that statement. Much better not to send it
repeatedly in the first place.

I think we need to take a step back here and better define the problem
before stepping in with a proposed solution. Something that avoids the need
to spam the server with endless copies of the same PREPARE statements would
be good.

BTW, PgJDBC doesn't use SQL-level PREPARE anyway, it does it with named
portals at the protocol level. You can't add IF NOT EXISTS there, at least
not the same way.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services