[Pgpool-general] Possible bug with pgpool-3.0.0 + patches and PQprepare()

[David Boreham]

Hi, this problem showed up in a real appliction , but I've reproduced it
with a simple test program, attached. Upon executing PQprepare(),
the test app hangs when using pgpool. If I point the test app at the
database server directly, it does not hang.

In the course of investigating this, I captured packet traces from
the pgpool and non-pgpool cases, but they didn't make much sense
to me in ethereal (they're different, but I couldn't see an obvious smoking
gun as to what went wrong). If it would help, I can go back and take
similar packet captures from this test program and send them along.

This is the debug output from pgpool  when I run the command:

[david at buffalo t]$ ./test_pgpool1 9999
<hangs>


2006-02-09 12:57:10 DEBUG: pid 8243: Protocol Major: 1234 Minor: 5679 
database:  user:
2006-02-09 12:57:10 DEBUG: pid 8243: SSLRequest: sent N; retry startup
2006-02-09 12:57:10 DEBUG: pid 8243: Protocol Major: 3 Minor: 0 
database: postgres user: david
2006-02-09 12:57:10 DEBUG: pid 8243: pool_read_message_length: lenghth: 8
2006-02-09 12:57:10 DEBUG: pid 8243: pool_read_message_length2: master 
lenghth: 25
2006-02-09 12:57:10 DEBUG: pid 8243: name: client_encoding value: UTF8
2006-02-09 12:57:10 DEBUG: pid 8243: secondary name: client_encoding 
value: UTF8
2006-02-09 12:57:10 DEBUG: pid 8243: pool_read_message_length2: master 
lenghth: 23
2006-02-09 12:57:10 DEBUG: pid 8243: name: DateStyle value: ISO, MDY
2006-02-09 12:57:10 DEBUG: pid 8243: secondary name: DateStyle value: 
ISO, MDY
2006-02-09 12:57:10 DEBUG: pid 8243: pool_read_message_length2: master 
lenghth: 26
2006-02-09 12:57:10 DEBUG: pid 8243: name: integer_datetimes value: off
2006-02-09 12:57:10 DEBUG: pid 8243: secondary name: integer_datetimes 
value: off
2006-02-09 12:57:10 DEBUG: pid 8243: pool_read_message_length2: master 
lenghth: 20
2006-02-09 12:57:10 DEBUG: pid 8243: name: is_superuser value: on
2006-02-09 12:57:10 DEBUG: pid 8243: secondary name: is_superuser value: on
2006-02-09 12:57:10 DEBUG: pid 8243: pool_read_message_length2: master 
lenghth: 25
2006-02-09 12:57:10 DEBUG: pid 8243: name: server_encoding value: UTF8
2006-02-09 12:57:10 DEBUG: pid 8243: secondary name: server_encoding 
value: UTF8
2006-02-09 12:57:10 DEBUG: pid 8243: pool_read_message_length2: master 
lenghth: 25
2006-02-09 12:57:10 DEBUG: pid 8243: name: server_version value: 8.1.2
2006-02-09 12:57:10 DEBUG: pid 8243: secondary name: server_version 
value: 8.1.2
2006-02-09 12:57:10 DEBUG: pid 8243: pool_read_message_length2: master 
lenghth: 32
2006-02-09 12:57:10 DEBUG: pid 8243: name: session_authorization value: 
david
2006-02-09 12:57:10 DEBUG: pid 8243: secondary name: 
session_authorization value: david
2006-02-09 12:57:10 DEBUG: pid 8243: pool_read_message_length2: master 
lenghth: 36
2006-02-09 12:57:10 DEBUG: pid 8243: name: standard_conforming_strings 
value: off
2006-02-09 12:57:10 DEBUG: pid 8243: secondary name: 
standard_conforming_strings value: off
2006-02-09 12:57:10 DEBUG: pid 8243: pool_read_message_length2: master 
lenghth: 20
2006-02-09 12:57:10 DEBUG: pid 8243: name: TimeZone value: Navajo
2006-02-09 12:57:10 DEBUG: pid 8243: secondary name: TimeZone value: Navajo
2006-02-09 12:57:10 DEBUG: pid 8243: pool_read_message_length: lenghth: 12
2006-02-09 12:57:10 DEBUG: pid 8243: read kind from backend pending data 
Z len: 5 po: 263
2006-02-09 12:57:10 DEBUG: pid 8243: pool_process_query: kind from 
backend: Z
2006-02-09 12:57:10 DEBUG: pid 8243: pool_read_message_length: lenghth: 5
2006-02-09 12:57:10 DEBUG: pid 8243: ReadyForQuery: message length: 5
2006-02-09 12:57:10 DEBUG: pid 8243: ReadyForQuery: transaction state: I
2006-02-09 12:57:10 DEBUG: pid 8243: read kind from frontend P(50)
2006-02-09 12:57:10 DEBUG: pid 8243: read kind from frontend S(53)
2006-02-09 12:57:10 DEBUG: pid 8243: read kind from backend 1
2006-02-09 12:57:10 DEBUG: pid 8243: pool_process_query: kind from 
backend: 1
2006-02-09 12:57:10 DEBUG: pid 8243: read kind from backend pending data 
Z len: 5 po: 5
2006-02-09 12:57:10 DEBUG: pid 8243: pool_process_query: kind from 
backend: Z
2006-02-09 12:57:10 DEBUG: pid 8243: pool_read_message_length: lenghth: 5

#include <iostream>
#include "libpq-fe.h"

int
main(int argc, char* argv[])
{

  char *lookup_SQL = "SELECT * from pg_catalog.pg_database";
  char *port = argv[1];
  PGconn *pg_connection = PQsetdbLogin( "buffalo", port, NULL, NULL, 
"postgres", "", "");

  if (PQstatus(pg_connection) == CONNECTION_BAD) {
    std::cerr << "Unable to connect to postgres database:" << 
PQerrorMessage(pg_connection);
    exit(1);
  }

  PGresult *result = PQprepare(pg_connection, "my_lookup", lookup_SQL, 
1, NULL);

  if (PQresultStatus(result) != PGRES_COMMAND_OK) {
    std::cerr << "prepare statement failed:\n" << 
PQerrorMessage(pg_connection);
    PQclear(result);
    PQfinish(pg_connection);
    exit(2);
  }

  PQclear(result);
  PQfinish(pg_connection);
  exit(0);
}

Thanks !