[pgpool-general: 2134] Re: Can PgPool-2 be used with protocol version 2?

[Lachezar Dobrev]

  Well…
  That's a bummer.

  The problems I am facing with the Protocol Version 3 revolve around
sending NULL arguments to queries of the sort:
    SELECT ... WHERE field <= :arg0 OR :arg0 IS NULL;

  When using NULL as argument Hibernate declares the parameter as
Serializable, that ends up declared as bytea, that ends up in: 'no
operator <= for timestamp and bytea' (or something of the sorts). And
if I declare the argument explicitly as Time-stamp the query
compilation fails with a 'Can not find type of $2', because "? IS
NULL" tells the query compiler nothing about the type of the
parameter, and the JDBC driver does not send type, because JDBC has
one Time-stamp type, while PostgreSQL has two…

  It seems Protocol Version 2 processes arguments before sending the
query to the server, and that «fixes» these issues by replacing the
arguments with their text representation, so null arguments end up
being NULL, and time stamps are being rendered as text, and etc.

  These problems can be worked around, but it requires rewriting
significant part of the application, patching Hibernate and the
PostgreSQL JDBC Driver…

  Since I can not use Protocol Version 2 with PgPool-2, and Protocol
Version 3 will be very hard to use, it seems I'm out of options for
the time being: no load balancing for me.



2013/9/13 Tatsuo Ishii <ishii at postgresql.org>:
> Unfortunately you cannot use load balancing with pgpool-II in version
> 2 protocol.  (the problem you are facing is another story of course...)
>
> What are exactly the problems "untyped NULLs" or "IS NULL"?
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>
>>   Hello colleagues,
>>
>>   Recently I've been experimenting with PgPool-2 (3.1 and 3.3) and
>> PostgreSQL 9.1 in order to achieve some load-balancing for our
>> applications.
>>
>>   The application stack contains (relevant pieces):
>>   Tomcat, Springframework, Hibernate, JDBC PostgreSQL Driver.
>>
>>   My attempts currently fail due to some… inconsistencies… between the
>> various components of the application stack, namely the use of NULL as
>> query arguments (no support for untyped NULLs) and problems with using
>> Temporal types in type-agnostic operations (? IS NULL).
>>   That forces us to use Protocol Version 2 to work around these issues.
>>
>>   However I am unable to perform any transactional operations on the
>> PgPool-2 mediator. I have a (short) test-case in Java (using JDBC
>> Driver) that attempts to execute a small transaction, but I end up
>> with this:
>>
>> org.postgresql.util.PSQLException: kind mismatch among backends.
>> Possible last query was: "COMMIT" kind details are: 0[C] 1[N: WARNING:
>>  there is no transaction in progress
>> ]
>>     at org.postgresql.core.v2.QueryExecutorImpl.receiveErrorMessage(QueryExecutorImpl.java:561)
>>     at org.postgresql.core.v2.QueryExecutorImpl.processResults(QueryExecutorImpl.java:484)
>>     at org.postgresql.core.v2.QueryExecutorImpl.execute(QueryExecutorImpl.java:364)
>>     at org.postgresql.core.v2.QueryExecutorImpl.execute(QueryExecutorImpl.java:258)
>>     at org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(AbstractJdbc2Connection.java:793)
>>     at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:817)
>>
>>   I am not exactly sure what happens, but it seems, that the 'COMMIT'
>> goes to the slave server, that has no transaction in progress, while
>> the transaction on the main server is left hanging.
>>
>>   Q: Can I use PgPool-2 with Protocol Version 2?
>>   Can I do anything to make these work?
>>
>>   Lachezar
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general