[Pgpool-general] data "invisibility"

Wed Mar 11 12:25:57 UTC 2009

Hello,

this morning a strange, mysterious behaviour occured.
We get different results, dependant of how we connect to pgpool.
a) connect from command line
#>psql -h iweblin01 -p 9999 -U postgres
\c dbtest
dbtest=# select * from customer where "Id" =9979;
=> returns one result set, nice

b) connect via PgAdmin, same host, same port, same user
select * from customer where "Id" =9979;
=> returns empty result...?!?!

Afterwards I detached node 2 from pgpool, and everything worked as expected
(PgAdmin returned a result set also).

These are the log entries connected via psql-command-line, and result as expected:

2009-03-11 11:28:20 LOG:   pid 7077: statement: select * from customer where
"Id" =9979;
2009-03-11 11:28:20 DEBUG: pid 7077: waiting for backend 0 completing the query
2009-03-11 11:28:20 DEBUG: pid 7077: read_kind_from_backend: read kind from 0 th
backend T NUM_BACKENDS: 1
2009-03-11 11:28:20 DEBUG: pid 7077: pool_process_query: kind from backend: T
2009-03-11 11:28:20 DEBUG: pid 7077: read_kind_from_backend: read kind from 0 th
backend D NUM_BACKENDS: 1
2009-03-11 11:28:20 DEBUG: pid 7077: pool_process_query: kind from backend: D
2009-03-11 11:28:20 DEBUG: pid 7077: read_kind_from_backend: read kind from 0 th
backend C NUM_BACKENDS: 1
2009-03-11 11:28:20 DEBUG: pid 7077: pool_process_query: kind from backend: C
2009-03-11 11:28:20 DEBUG: pid 7077: read_kind_from_backend: read kind from 0 th
backend Z NUM_BACKENDS: 1
2009-03-11 11:28:20 DEBUG: pid 7077: pool_process_query: kind from backend: Z
2009-03-11 11:28:20 DEBUG: pid 7077: pool_read_message_length: slot: 0 length: 5
2009-03-11 11:28:20 DEBUG: pid 7077: ReadyForQuery: message length: 5
2009-03-11 11:28:20 DEBUG: pid 7077: ReadyForQuery: transaction state: I
2009-03-11 11:28:20 DEBUG: pid 7077: end_load_balance: end load balance mode

and here are the log entries if connected via PgAdmin, fired the same query:

2009-03-11 11:29:01 LOG:   pid 7079: statement: select * from customer where
"Id" =9979;
2009-03-11 11:29:01 DEBUG: pid 7079: waiting for backend 1 completing the query
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend T NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: T
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend C NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: C
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend Z NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: Z
2009-03-11 11:29:01 DEBUG: pid 7079: pool_read_message_length: slot: 1 length: 5
2009-03-11 11:29:01 DEBUG: pid 7079: ReadyForQuery: message length: 5
2009-03-11 11:29:01 DEBUG: pid 7079: ReadyForQuery: transaction state: I
2009-03-11 11:29:01 DEBUG: pid 7079: end_load_balance: end load balance mode
2009-03-11 11:29:01 DEBUG: pid 7079: read kind from frontend Q(51)
2009-03-11 11:29:01 LOG:   pid 7079: statement: SELECT format_type(oid,-1) as
typname FROM pg_type WHERE oid = 23
2009-03-11 11:29:01 DEBUG: pid 7079: waiting for backend 1 completing the query
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend T NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: T
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend D NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: D
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend C NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: C
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend Z NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: Z
2009-03-11 11:29:01 DEBUG: pid 7079: pool_read_message_length: slot: 1 length: 5
2009-03-11 11:29:01 DEBUG: pid 7079: ReadyForQuery: message length: 5
2009-03-11 11:29:01 DEBUG: pid 7079: ReadyForQuery: transaction state: I
2009-03-11 11:29:01 DEBUG: pid 7079: end_load_balance: end load balance mode
2009-03-11 11:29:01 DEBUG: pid 7079: read kind from frontend Q(51)
2009-03-11 11:29:01 LOG:   pid 7079: statement: SELECT CASE WHEN typbasetype=0
THEN oid else typbasetype END AS basetype
  FROM pg_type WHERE oid=23
2009-03-11 11:29:01 DEBUG: pid 7079: waiting for backend 1 completing the query
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend T NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: T
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend D NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: D
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend C NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: C
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend Z NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: Z
2009-03-11 11:29:01 DEBUG: pid 7079: pool_read_message_length: slot: 1 length: 5
2009-03-11 11:29:01 DEBUG: pid 7079: ReadyForQuery: message length: 5
2009-03-11 11:29:01 DEBUG: pid 7079: ReadyForQuery: transaction state: I
2009-03-11 11:29:01 DEBUG: pid 7079: end_load_balance: end load balance mode
2009-03-11 11:29:01 DEBUG: pid 7079: read kind from frontend Q(51)
2009-03-11 11:29:01 LOG:   pid 7079: statement: SELECT format_type(oid,-1) as
typname FROM pg_type WHERE oid = 23
2009-03-11 11:29:01 DEBUG: pid 7079: waiting for backend 1 completing the query
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend T NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: T
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend D NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: D
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend C NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: C
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend Z NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: Z
......
hundreds of lines
......
2009-03-11 11:29:01 DEBUG: pid 7079: ReadyForQuery: message length: 5
2009-03-11 11:29:01 DEBUG: pid 7079: ReadyForQuery: transaction state: I
2009-03-11 11:29:01 DEBUG: pid 7079: end_load_balance: end load balance mode
2009-03-11 11:29:01 DEBUG: pid 7079: read kind from frontend Q(51)
2009-03-11 11:29:01 LOG:   pid 7079: statement: SELECT CASE WHEN typbasetype=0
THEN oid else typbasetype END AS basetype
  FROM pg_type WHERE oid=1114
2009-03-11 11:29:01 DEBUG: pid 7079: waiting for backend 1 completing the query
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend T NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: T
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend D NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: D
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend C NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: C
2009-03-11 11:29:01 DEBUG: pid 7079: read_kind_from_backend: read kind from 1 th
backend Z NUM_BACKENDS: 2
2009-03-11 11:29:01 DEBUG: pid 7079: pool_process_query: kind from backend: Z
2009-03-11 11:29:01 DEBUG: pid 7079: pool_read_message_length: slot: 1 length: 5
2009-03-11 11:29:01 DEBUG: pid 7079: ReadyForQuery: message length: 5
2009-03-11 11:29:01 DEBUG: pid 7079: ReadyForQuery: transaction state: I
2009-03-11 11:29:01 DEBUG: pid 7079: end_load_balance: end load balance mode

any help appreciated....GERD....