Protocol 2 and query parameters support

On 04/23/2017 12:08 PM, Konstantin Izmailov wrote:

Does Postgres protocol 2 support query parameters? e.g.:
SELECT colA FROM tbl WHERE colB=$1

Some systems (Presto) are still using protocol 2, and I need to
understand the scope of changes in the middleware to support protocol 2.

?
https://www.postgresql.org/docs/9.6/static/protocol-changes.html

--
Adrian Klaver
adrian.klaver@aklaver.com

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

On Sunday, April 23, 2017, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/23/2017 12:08 PM, Konstantin Izmailov wrote:

Does Postgres protocol 2 support query parameters? e.g.:
SELECT colA FROM tbl WHERE colB=$1

Some systems (Presto) are still using protocol 2, and I need to
understand the scope of changes in the middleware to support protocol 2.

?
https://www.postgresql.org/docs/9.6/static/protocol-changes.html

Docs for the last version 2.0 release, 7.3:

https://www.postgresql.org/docs/7.3/static/protocol.html

David J.

On 2017-04-23 12:08:51 -0700, Konstantin Izmailov wrote:

Does Postgres protocol 2 support query parameters? e.g.:
SELECT colA FROM tbl WHERE colB=$1

Some systems (Presto) are still using protocol 2, and I need to understand
the scope of changes in the middleware to support protocol 2.

Could you provide a reference about presto using v2 protocol? A quick
search didn't turn anything up. Presto seems a bit too new to rely on
v2, given how long ago v3 has been introduced.

Greetings,

Andres Freund

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

Andres Freund <andres@anarazel.de> writes:

On 2017-04-23 12:08:51 -0700, Konstantin Izmailov wrote:

Some systems (Presto) are still using protocol 2, and I need to understand
the scope of changes in the middleware to support protocol 2.

Could you provide a reference about presto using v2 protocol? A quick
search didn't turn anything up. Presto seems a bit too new to rely on
v2, given how long ago v3 has been introduced.

There's been moderately serious discussion about dropping v2 support
altogether, so I sure hope there isn't anything in the wild that still
depends on it.

Also, moving from v2 to v3 per se just isn't very hard, if you aren't
worried about making use of new-in-v3 protocol features. I wonder whether
Konstantin is confusing v2 vs v3 with use of simple vs. extended query
protocol within v3.

regards, tom lane

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

Andres Freund <andres@anarazel.de> writes:

On 2017-04-23 12:08:51 -0700, Konstantin Izmailov wrote:

Some systems (Presto) are still using protocol 2, and I need to understand
the scope of changes in the middleware to support protocol 2.

Could you provide a reference about presto using v2 protocol? A quick
search didn't turn anything up. Presto seems a bit too new to rely on
v2, given how long ago v3 has been introduced.

There's been moderately serious discussion about dropping v2 support
altogether, so I sure hope there isn't anything in the wild that still
depends on it.

Also, moving from v2 to v3 per se just isn't very hard, if you aren't
worried about making use of new-in-v3 protocol features. I wonder whether
Konstantin is confusing v2 vs v3 with use of simple vs. extended query
protocol within v3.

It seems Presto uses v3 protocol only when ODBC driver is used. If
JDBC is used, it is required to set "protocolVersion=2" property of
JDBC driver, which means v2 protocol is used I think.

https://github.com/treasure-data/prestogres#limitation

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

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

On Sun, Apr 23, 2017 at 10:33 PM, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

Andres Freund <andres@anarazel.de> writes:

On 2017-04-23 12:08:51 -0700, Konstantin Izmailov wrote:

Some systems (Presto) are still using protocol 2, and I need to

understand

the scope of changes in the middleware to support protocol 2.

Could you provide a reference about presto using v2 protocol? A quick
search didn't turn anything up. Presto seems a bit too new to rely on
v2, given how long ago v3 has been introduced.

There's been moderately serious discussion about dropping v2 support
altogether, so I sure hope there isn't anything in the wild that still
depends on it.

Also, moving from v2 to v3 per se just isn't very hard, if you aren't
worried about making use of new-in-v3 protocol features. I wonder

whether

Konstantin is confusing v2 vs v3 with use of simple vs. extended query
protocol within v3.

It seems Presto uses v3 protocol only when ODBC driver is used. If
JDBC is used, it is required to set "protocolVersion=2" property of
JDBC driver, which means v2 protocol is used I think.

https://github.com/treasure-data/prestogres#limitation

As Tom mentioned, it sounds like the issue is that Presto expects to only
use simple query, not extended query (no server-side prepared statements).
The JDBC driver supports setting the prepare threshold to 0 to disable
using server-side prepares.

https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGStatement.html#setPrepareThreshold-int-

"If threshold is zero, server-side prepare will not be used."

On 2017-04-24 16:07:01 -0400, Rader, David wrote:

As Tom mentioned, it sounds like the issue is that Presto expects to only
use simple query, not extended query (no server-side prepared statements).
The JDBC driver supports setting the prepare threshold to 0 to disable
using server-side prepares.

https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGStatement.html#setPrepareThreshold-int-

"If threshold is zero, server-side prepare will not be used."

Note that you can use the extended protocol without server side
prepares...

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

On 2017-04-24 16:07:01 -0400, Rader, David wrote:

As Tom mentioned, it sounds like the issue is that Presto expects to only
use simple query, not extended query (no server-side prepared statements).
The JDBC driver supports setting the prepare threshold to 0 to disable
using server-side prepares.

https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGStatement.html#setPrepareThreshold-int-

"If threshold is zero, server-side prepare will not be used."

Note that you can use the extended protocol without server side
prepares...

Quite correct. Just setting the prepare threshold to 0 does not
prevent PostgreSQL from using extended queries. So the only way not
using extended queires with JDBC is setting the protocol version to 2.
I wonder why PostgreSQL JDBC does not provide similar API to ODBC...

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

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