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

[Lonni J Friedman]

ok, i'll set client_idle_limit=0 as a workaround.  thanks.

On Thu, Jun 20, 2013 at 8:34 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:
> Ok.
>
> "postgres_fdw establishes a connection to a foreign server during the
> first query that uses a foreign table associated with the foreign
> server. This connection is kept and re-used for subsequent queries in
> the same session."
>
> This seems to support my theory. Unless there's some way for pgpool to
> know the connection is from postgres_fdw, there's no way to avoid the
> client_idle_limit problem. The only workaround is set
> client_idle_limit to 0. It would be nice if postgres_fdw periodically
> sends something to pgpool to prevent pgpool from thinking the client
> is idle.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>
>> I don't know any more than is documented here:
>> http://www.postgresql.org/docs/devel/static/postgres-fdw.html
>>
>> If there's something specific that you'd like me to change or test, let me know.
>>
>> thanks
>>
>> On Thu, Jun 20, 2013 at 8:02 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>> No. Because I'm not familiar with postgres_fdw, I just wanted to make
>>> sure what the cause of the problem is. I cannot promise I can solve
>>> your problem, but I'm sure without knowing the cause, it's absolutely
>>> impossible to solve the problem.
>>> --
>>> Tatsuo Ishii
>>> SRA OSS, Inc. Japan
>>> English: http://www.sraoss.co.jp/index_en.php
>>> Japanese: http://www.sraoss.co.jp
>>>
>>>> I'm not sure that I understand the question. Are you asking me to set
>>>> client_idle_limit to 0?
>>>>
>>>> On Wed, Jun 19, 2013 at 6:15 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>>>> 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?
>>
>>
>>
>> --
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> L. Friedman                                    netllama at gmail.com
>> LlamaLand                       https://netllama.linux-sxs.org



-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman                                    netllama at gmail.com
LlamaLand                       https://netllama.linux-sxs.org