Re: BUG #8404: JDBC block hot standby replication?

Hello

This is a well documented feature of Hot-Standby Replication.

see:
http://www.postgresql.org/docs/9.2/static/runtime-config-replication.html#R=
UNTIME-CONFIG-REPLICATION-STANDBY

Regards,

-- Valentin

=E1=83=95=E1=83=90=E1=83=9A=E1=83=94=E1=83=9C=E1=83=A2=E1=83=98=E1=83=9C =
=E1=83=92=E1=83=9D=E1=83=92=E1=83=98=E1=83=A9=E1=83=90=E1=83=A8=E1=83=95=E1=
=83=98=E1=83=9A=E1=83=98
Valentine Gogichashvili


On Wed, Aug 28, 2013 at 2:30 AM, <pchan@contigo.com> wrote:

> 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:
> =3D=3D=3D=3D=3D=3D=3D
> Set up a master db cluster and hot standby db cluster using async streami=
ng
> 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
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> - 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 followi=
ng
> 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
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> - 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?
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>