Thread: [GENERAL] Protocol 2 and query parameters support
Does Postgres protocol 2 support query parameters? e.g.:
SELECT colA FROM tbl WHERE colB=$1On 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
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:
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
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
> 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
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.
"If
threshold is zero, server-side prepare will not be used."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 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...
> 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