[pgpool-general: 1853] weird error with postgres_fdw & pgpool

Lonni J Friedman netllama at gmail.com
Thu Jun 20 01:56:23 JST 2013


I'm in the process of testing out a beta of postgres-9.3 using
pgpool-3.2.4 and the (new) postgres_fdw.   I have two different
postgres-9.3 servers setup (db10 & db11), and pgpool is setup (db12)
pointing to one of them (db10).  I can connect with psql to both
servers directly, and also via pgpool, with no problems.  My intent
was to be able to run SQL queries while logged into db11 through
pgpool on db12 to the server on db10, using a postgres_fdw.

I setup the postgres_fdw server:
CREATE SERVER db10 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host
'db12', dbname 'nightly', port '9999');
CREATE USER MAPPING FOR lfriedman SERVER db10 OPTIONS (user 'lfriedman');
CREATE FOREIGN TABLE nppsmoke (id bigint NOT NULL, date_created
timestamp without time zone not null default now(), last_update
timestamp without time zone not null default now(), build_type text
NOT NULL,current_status text NOT NULL, info text NOT NULL, cudacode
text NOT NULL, gpu text NOT NULL, subtest text NOT NULL, os text NOT
NULL, osversion text NOT NULL, arch text NOT NULL, cl integer NOT
NULL, dispvers text NOT NULL, branch text NOT NULL, pass integer,fail
integer, oldfail integer, newfail integer, failureslog text, totdriver
boolean NOT NULL default 'true',ddcl integer NOT NULL, buildid
integer, testdcmd text NOT NULL, pclog text, filtercount integer,
filterlog text, error integer) SERVER db10 ;

After which, I verified that I could issue SQL queries from inside
psql while connected to db11, against the foreign table, nppsmoke that
was created above:
#########
nightly=# select last_update from nppsmoke where id='86901' ;
     last_update
---------------------
 2013-06-12 12:53:23
(1 row)
#########

However, I'm seeing some strange errors when my psql session exceeds
the pgpool client_idle_limit:
#######
nightly=# select last_update from nppsmoke where id='86901' ;
ERROR:  connection terminated due to client idle limit reached
CONTEXT:  Remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
nightly=# select last_update from nppsmoke where id='86901' ;
ERROR:  unknown error
CONTEXT:  Remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
########

And the only way that I've found to run the SQL queries against the
foreign table is to exit psql and connect again from scratch.  This is
definitely different behavior than I experience for local tables.  For
queries issued after the client_idle_limit, I always see:
#####
ERROR:  connection terminated due to client idle limit reached
ERROR:  connection terminated due to client idle limit reached
The connection to the server was lost. Attempting reset: Succeeded.
#####

after which I can re-run the query without exiting & reconnecting with psql.

This seems like a bug to me, as I'd expect the behavior to be
consistent regardless of what is on the remote end.  Thoughts?


More information about the pgpool-general mailing list