Обсуждение: v2 protocol: is it still used?

Hi,

A few years back, I analysed the reason why some JDBC requests
were slower than psql requests. It turned out that, by using
prepared statement in protocol v3, the query plan is determined
once and for all (IIRC, for a given PreparedStatement object),
and so when reused later with other query parameters (typically,
not as selective, I guess), it turned out to be much slower
(suboptimal query plan). The only workaround I found back then,
was to use protocol v2 (IIRC prepared statements are fully client
side in protocol v2?). I noticed recently though that it's not
going to be able anymore soon, because the 9.5 backend breaks on
the driver passing autocommit=true anyways.

Thanks

> Hi,
>
> Does anybody still use v2 protocol via pgjdbc?
>
> I'm reworking QueryExecutor/ProcotolConnection implementation as a part of
> "smarter returning" (https://github.com/pgjdbc/pgjdbc/issues/488), and I would
> like to just drop v2 support.
>
> As far as I know, v3 was introduced in PostgreSQL 7.4, thus it looks rather
> safe to just drop v2 support in pjgdbc.
>
> By the way, the oldest version we test in Travis is 8.4
>
> Vladimir

--
Guillaume Cottenceau

Hi,

A few years back, I analysed the reason why some JDBC requests
were slower than psql requests. It turned out that, by using
prepared statement in protocol v3, the query plan is determined
once and for all (IIRC, for a given PreparedStatement object),
and so when reused later with other query parameters (typically,
not as selective, I guess), it turned out to be much slower
(suboptimal query plan). The only workaround I found back then,
was to use protocol v2 (IIRC prepared statements are fully client
side in protocol v2?). I noticed recently though that it's not
going to be able anymore soon, because the 9.5 backend breaks on
the driver passing autocommit=true anyways.

Thanks

> Hi,
>
> Does anybody still use v2 protocol via pgjdbc?
>
> I'm reworking QueryExecutor/ProcotolConnection implementation as a part of
> "smarter returning" (https://github.com/pgjdbc/pgjdbc/issues/488), and I would
> like to just drop v2 support.
>
> As far as I know, v3 was introduced in PostgreSQL 7.4, thus it looks rather
> safe to just drop v2 support in pjgdbc.
>
> By the way, the oldest version we test in Travis is 8.4
>
> Vladimir

--
Guillaume Cottenceau

Vladimir Sitnikov <sitnikov.vladimir 'at' gmail.com> writes:

> Guillaume Cottenceau <gc@mnc.ch>:
>
>     The only workaround I found back then,
>     was to use protocol v2 (IIRC prepared statements are fully client
>     side in protocol v2?). I noticed recently though that it's not
>     going to be able anymore soon, because the 9.5 backend breaks on
>     the driver passing autocommit=true anyways.
>
>
> Workarounds exist for v3 as well: for instance, completely disable usage of
> server-prepared statements.
> Alternatively, we could even implement "use server-prepared statement at most 5
> times" (so the backend never switches to a generic plan).

Thanks for the hints!

> I wonder what would be the best way to handle current protocolVersion=
> 2 installations:
> 1) ignore protocolVersion=2 and just use v3
> 2) throw exception "A connection could not be made using the requested protocol
> 2"
>
> Even though #1 might not be that expected, however I believe it will provide
> smooth migration to never pgjdbc versions without "manual configuration changes
> here and there"

I humbly think #2 is more reliable and clear.

--
Guillaume Cottenceau

Vladimir Sitnikov <sitnikov.vladimir 'at' gmail.com> writes:

> Guillaume Cottenceau <gc@mnc.ch>:
>
>     The only workaround I found back then,
>     was to use protocol v2 (IIRC prepared statements are fully client
>     side in protocol v2?). I noticed recently though that it's not
>     going to be able anymore soon, because the 9.5 backend breaks on
>     the driver passing autocommit=true anyways.
>
>
> Workarounds exist for v3 as well: for instance, completely disable usage of
> server-prepared statements.
> Alternatively, we could even implement "use server-prepared statement at most 5
> times" (so the backend never switches to a generic plan).

Thanks for the hints!

> I wonder what would be the best way to handle current protocolVersion=
> 2 installations:
> 1) ignore protocolVersion=2 and just use v3
> 2) throw exception "A connection could not be made using the requested protocol
> 2"
>
> Even though #1 might not be that expected, however I believe it will provide
> smooth migration to never pgjdbc versions without "manual configuration changes
> here and there"

I humbly think #2 is more reliable and clear.

--
Guillaume Cottenceau