[pgpool-general: 1854] Re: weird error with postgres_fdw & pgpool

[Tatsuo Ishii]

Lonni,

Is it possible for postgres_fdw to create a persistent connection from
db11 to db12 for certain pupose(maybe connection pooling)? If so, the
connection will be treated as a normal conection from client by pgpool
and client_idle_limit will be applied.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> 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?
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general