Re: NOT EXIST for PREPARE

* Merlin Moncure (mmoncure(at)gmail(dot)com) wrote:
> On Tue, Mar 22, 2016 at 8:01 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > 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.
>
> Strongly disagree! A typical use case of this feature would be in
> connection pooler scenarios where you *are* in control of the
> statement but it's a race to see who creates it first. This feature
> should be immediately be incorporated by the JDBC driver so that we'd
> no longer have to disable server side prepared statements when using
> pgbounder (for example).

Indeed, and we already said we're not going to verify that the object
that already exists in a 'IF NOT EXISTS' case matches exactly whatever
you're trying to create, see CREATE TABLE.

I agree that PREPARE IF NOT EXISTS would be nice to have, but only if we
can keep it fast somehow, which is the part that makes me wonder a bit.

Having PREPARE IF NOT EXISTS would imply that application authors would
be expected to run a set of PREPAREs at the start of each transaction
(if you want to support transaction pooling mode in, say, pgbouncer),
for each prepared statement they want to use in that transaction. That
doesn't seem completely unreasonable, but it'd need to be fast.

Thanks!

Stephen