NOT EXIST for PREPARE

Em terça-feira, 22 de março de 2016, Yury Zhuravlev <
u.zhuravlev@postgrespro.ru> escreveu:

Hello hackers.

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

+1

What do you think about adding NOT EXIST functionality to PREPARE?

I think you meant IF NOT EXISTS, right?

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Fabrízio de Royes Mello wrote:

I think you meant IF NOT EXISTS, right?

Thanks, you right.

--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Em terça-feira, 22 de março de 2016, Yury Zhuravlev <
u.zhuravlev@postgrespro.ru> escreveu:

Fabrízio de Royes Mello wrote:

I think you meant IF NOT EXISTS, right?

Thanks, you right.

You already have a patch? If yes I'm glad to review it.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

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.

Andres

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andres Freund wrote:

you'd still need to compare the
statement somehow

You right, I think about that as syntax sugar. Maybe with some performance
increase but hardly. We can save on a round trip.

It may be necessary to add an index on the field "statement"?

Thanks.
--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Fabrízio de Royes Mello wrote:

You already have a patch? If yes I'm glad to review it.

If the community is not against it, I'll do it quickly. Changing the syntax
is the risk. In addition, we have already missed 9.6.

Thanks.

--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2016-03-22 16:51:26 +0300, Yury Zhuravlev wrote:

Andres Freund wrote:

you'd still need to compare the
statement somehow

You right, I think about that as syntax sugar. Maybe with some performance
increase but hardly. We can save on a round trip.

If anything what'd be useful would be DEALLOCATE IF EXISTS; that'd allow
you to use prepare safely this way.

It may be necessary to add an index on the field "statement"?

It's not an actual table, it's a view over a function.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Yury Zhuravlev wrote:

It may be necessary to add an index on the field "statement"?

Sorry. Said nonsense.

We need to understand what kind of behavior should be if the name is the
same but query_string not? Replace?

Thanks.

--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Mar 22, 2016 at 10:01 AM, Andres Freund <andres@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.

You're correct, but IMHO it should be used when you have control of
prepared statement... isn't it analogous to CREATE TABLE IF NOT EXISTS??

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

On Tue, Mar 22, 2016 at 8:01 AM, Andres Freund <andres@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.

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).

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Merlin Moncure (mmoncure@gmail.com) wrote:

On Tue, Mar 22, 2016 at 8:01 AM, Andres Freund <andres@anarazel.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

On 2016-03-22 09:37:15 -0500, Merlin Moncure wrote:

On Tue, Mar 22, 2016 at 8:01 AM, Andres Freund <andres@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.

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).

Uh. JDBC precisely is a scenario where that's *NOT* applicable? You're
not in control of the precise prepared statement names it generates, so
you have no guarantee that one prepared statement identified by its name
means the same in another connection.

You can use something like PREPARE IF NOT EXISTS across a statement
level pooler if, and only if, the prepared statements have a name that's
fixed and unique for each statement.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Andres Freund (andres@anarazel.de) wrote:

On 2016-03-22 09:37:15 -0500, Merlin Moncure wrote:

On Tue, Mar 22, 2016 at 8:01 AM, Andres Freund <andres@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.

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).

Uh. JDBC precisely is a scenario where that's *NOT* applicable? You're
not in control of the precise prepared statement names it generates, so
you have no guarantee that one prepared statement identified by its name
means the same in another connection.

Clearly, you'd need to be able to control the prepared statement name to
use such a feature.

Given that we're talking about what sounds like a new feature in the
JDBC driver, I don't see why you wouldn't also make that a requirement
of the feature..? Or have the JDBC driver calculate a unique ID for
each statement using a good hash, perhaps?

Note: I don't pretend to have any clue as to the internals of the JDBC
driver, but it hardly seems far-fetched to have this be supported in a
way that works.

Thanks!

Stephen

On Tue, Mar 22, 2016 at 11:50 AM, Stephen Frost <sfrost@snowman.net> wrote:

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.

Skip error if already exists when catched in src/backend/commands/prepare.c
isn't enough?

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Fabrízio de Royes Mello wrote:

Skip error if already exists when catched in
src/backend/commands/prepare.c isn't enough?

I think that's enough. And expand PrepareStmt of course.
--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Mar 22, 2016 at 9:53 AM, Andres Freund <andres@anarazel.de
<javascript:;>> wrote:

On 2016-03-22 09:37:15 -0500, Merlin Moncure wrote:

On Tue, Mar 22, 2016 at 8:01 AM, Andres Freund <andres@anarazel.de

<javascript:;>> 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.

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).

Uh. JDBC precisely is a scenario where that's *NOT* applicable? You're
not in control of the precise prepared statement names it generates, so
you have no guarantee that one prepared statement identified by its name
means the same in another connection.

The name is under control of the JDBC driver, and there are simple
strategies to make the name global assuming the app did not pass the name.
So it should work.

merlin

You already have a patch? If yes I'm glad to review it.

Please. Patch in attachment.

--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Yury Zhuravlev wrote:

You already have a patch? If yes I'm glad to review it.

Please. Patch in attachment.

Fix bug, forgot change attr number in parser.
And, I forgot example:
PREPARE usrrptplan (int) IF NOT EXISTS AS
SELECT * FROM pg_operator;
PREPARE

New patch in attachment.

--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Yury Zhuravlev wrote:

Fabr�zio de Royes Mello wrote:

You already have a patch? If yes I'm glad to review it.

If the community is not against it, I'll do it quickly. Changing the syntax
is the risk. In addition, we have already missed 9.6.

Also we're in the middle of a commitfest, and it would be polite to
review the patches that have been listed in it for almost two months
now. We shouldn't distract reviewing power towards new patches at this
point.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers