On 22 March 2016 at 21:01, Andres Freund <and...@anarazel.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
