[pgpool-general: 2292] Many short CGI connections - needed to downgrade to version 2.3

[deven at trikon.co.in]

My use case:

1. Streaming replication slave physically far from Master, about 250ms round trip time.
2. Application web server close to slave.
3. Application web server makes many short connections to database, with just one or two SQL commands - mostly SELECT - before ending session.
4. Need to send reads to slave, and writes to master.

We had bad performance with pgpool 3.1 and 3.3 due to:

1. when session is established to a previously unused pgpool process to issue a single SELECT, as many as 7 sql commands are first sent to the master to set session variables, establish user rights, and check whether table is temporary. This process took 1750ms, after which SELECT query was sent to slave, which responded in 1ms.

2. Subsequent sessions to a pgpool process SELECTing from same table only caused a check to see if it was temporary, by way of 2 commands that took 500ms before SELECT was sent to slave.  

This was solved by downgrading to 2.3 which has following results:

1. user rights are rapidly established by checking catalog on slave.
2. SELECTs are sent immediately to slave with no interaction with master
3. No check for temporary tables.  

SELECT are now returning in 1.5ms, down from 500ms to 1750ms in case of version 3+.

I think our use case (geographically distributed slaves and many short CGI connections) is common, and would request it to be handled by an option in ini file to check temporary table status, user rights etc. from slave and to stop setting session variables on the master. It is not clear why pgpool has switched from checking catalogs on slave to master.