PostgreSQL 9.3 beta1 is out. One of pgpool users tried pgpool with postgres_fdw and found interesting thing. So I looked into this to know what actually happened here.
To test postgres_fdw, I created one pgpool installation with replication mode and two PostgreSQL clusters using pgpool_setup. One is running on port 11001. I executed following commands on 11001 PostgreSQL to create a foreign table called "pgpool".
CREATE EXTENSION postgres_fdw;
CREATE SERVER pgpool FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'test', port '11002');
CREATE USER MAPPING FOR PUBLIC SERVER pgpool;
CREATE FOREIGN TABLE fr1(i INTEGER) SERVER pgpool;
On port 11002 pgpool is running. On port 11000 PostgreSQL is running. To create a real table corresponding to the foreign table "fr1", I executed "CREATE fr1 TABLE(i INTEGER);". Below is the setup I explained above.
The point here is, the foreign table "fr1" accesses the real table "fr1" via pgpool-II. So from the pgpool's point of view, the foreign table is looks like an ordinary client. If I set "client_idle_limit" to, say 5, I saw an interesting thing. The client_idle_limit is a feature to kick out idle clients. If client sends no query within client_idle_limit seconds, the connection from the client to pgpool is forced to disconnected. Here is the session log when client_idle_limit is set to 5.
$ psql -p 11001 test
-- access foreign table fr1test=# select * from fr1;
i
---
1
(1 row)
-- after 6 seconds passedtest=# select * from fr1;
ERROR: unknown error
CONTEXT: Remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
test=# select * from fr1;
ERROR: unknown error
CONTEXT: Remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
test=# select * from fr1;
ERROR: unknown error
CONTEXT: Remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
test=# \q
This continues until I quit the session and restart a session. What happens here? you can know it from the pgpool log(my comments are written in red color):
Several commands are issued from postgres_fdw:
2013-06-22 21:01:55 LOG: pid 11209: DB node id: 0 backend pid: 13109 statement: SET search_path = pg_catalog
2013-06-22 21:01:55 LOG: pid 11209: DB node id: 0 backend pid: 13109 statement: SET timezone = 'UTC'
2013-06-22 21:01:55 LOG: pid 11209: DB node id: 0 backend pid: 13109 statement: SET datestyle = ISO
2013-06-22 21:01:55 LOG: pid 11209: DB node id: 0 backend pid: 13109 statement: SET intervalstyle = postgres
2013-06-22 21:01:55 LOG: pid 11209: DB node id: 0 backend pid: 13109 statement: SET extra_float_digits = 3
2013-06-22 21:01:55 LOG: pid 11209: DB node id: 0 backend pid: 13109 statement: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
2013-06-22 21:01:55 LOG: pid 11209: DB node id: 0 backend pid: 13109 statement: Parse: DECLARE c1 CURSOR FOR
SELECT i FROM public.fr1
Looks like postgres_fdw uses extended protocol2013-06-22 21:01:55 LOG: pid 11209: DB node id: 0 backend pid: 13109 statement: B message
2013-06-22 21:01:55 LOG: pid 11209: DB node id: 0 backend pid: 13109 statement: D message
2013-06-22 21:01:55 LOG: pid 11209: DB node id: 0 backend pid: 13109 statement: Execute: DECLARE c1 CURSOR FOR
SELECT i FROM public.fr1
2013-06-22 21:01:55 LOG: pid 11209: DB node id: 0 backend pid: 13109 statement: FETCH 100 FROM c1
2013-06-22 21:01:55 LOG: pid 11209: DB node id: 0 backend pid: 13109 statement: CLOSE c1
2013-06-22 21:01:55 LOG: pid 11209: DB node id: 0 backend pid: 13109 statement: COMMIT TRANSACTION
After 6 seconds passed, I executed "SELECT * from fr1" again2013-06-22 21:02:01 LOG: pid 11209: pool_process_query: child connection forced to terminate due to client_idle_limit(5) reached
Pgpool told that client_idle_limit expires2013-06-22 21:02:01 LOG: pid 11209: DB node id: 0 backend pid: 13109 statement: DISCARD ALL
How to avoid the error?
Because there's no way for pgpool-II to know the connection from client is from postgres_fdw, the only workaround is disabling client_idle_limit.
