The following bug has been logged on the website:
Bug reference: 8404
Logged by: Pius Chan
Email address: pchan@contigo.com
PostgreSQL version: 9.1.9
Operating system: Linux version 2.6.32-279.14.1.el6.x86_64
Description:
When investigating an "out of memory" issue in hot standby, it is found that
JDBC can block the application of replicated transaction log to hot standby.
The set up of the test is as follows:
Set up:
=======
Set up a master db cluster and hot standby db cluster using async streaming
replication. On master database server, create a database TEST. Implement a
cron job which on every minute:
- drop table if exists heartbeat;
- create table heartbeat (timenow timestamp);
- insert into heartbeat values (now());
- this is to ensure transaction entry is generated at least every minute.
Test 1 - psql does not block replication
========================================
- on hot standy db server, start a psql session (session 1), then submit the
following:
psql> begin;
psql> select now();
..... idle
- start another psql session on hot standby db server and run the following
SQLs to monitor the replication delay:
select current_timestamp, pg_last_xact_replay_timestamp(),
current_timestamp-pg_last_xact_replay_timestamp() as replication_delay,
pg_last_xlog_receive_location();
Result: it is found that session 1 does
not block replication.
Test 2 - JDBC can block replication
====================================
- write a Java test program to connect to hot standby TEST database
- in the test program, submit the following SQLs:
begin;
select now();
then sleep for 2 minutes
- on hot standby db server, start another psql session and monitor the
replication delay with the same SQL.
Results: it is found that during the 2 minutes sleeping time,
pg_last_xlog_receive_location is advancing but pg_last_xact_replay_timestamp
is stuck. The result of "select * from heartbeat" does not change.
Is that a bug in JDBC driver?