When I set the synchronous_standby_names and synchronous_commit to off, the replication query state also show in sync mode.

When I use the sync replation in PG9.6, I find a question about the query
state display.The following is the testing steps:

1) set the postgresql.conf parameter

synchronous_standby_names = '5(*)'

synchronous_commit = on

2) start the standby node ,and query the replication state

It shows that the replication in the sync mode.

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 2650
usesysid | 10
usename | houjibofa
application_name | walreceiver
client_addr | 127.0.0.1
client_hostname |
client_port | 50816
backend_start | 2016-10-07 15:32:22.179723+08
backend_xmin |
state | streaming
sent_location | 0/70000F4
write_location | 0/70000F4
flush_location | 0/70000F4
replay_location | 0/70000F4
sync_priority | 1
sync_state | sync

postgres=# show synchronous_commit;
-[ RECORD 1 ]------+---
synchronous_commit | on

4) set the synchronous_commit to off, using the pg_ctl reload method

postgres=# show synchronous_commit;
-[ RECORD 1 ]------+----
synchronous_commit | off

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 2650
usesysid | 10
usename | houjibofa
application_name | walreceiver
client_addr | 127.0.0.1
client_hostname |
client_port | 50816
backend_start | 2016-10-07 15:32:22.179723+08
backend_xmin |
state | streaming
sent_location | 0/70000F4
write_location | 0/70000F4
flush_location | 0/70000F4
replay_location | 0/70000F4
sync_priority | 1
sync_state | sync

It also shows in the sync replication mode, however, it is in the async
mode. And I can create a table in the primary mode.
postgres=# show synchronous_standby_names;
-[ RECORD 1 ]-------------+-----
synchronous_standby_names | 5(*)

postgres=# create table test(a int);
CREATE TABLE

The PG version is follows.

postgres=# select version();
version

-------------------------------------------------------------------------------------
-------------------
PostgreSQL 9.6.0 on i686-pc-linux-gnu, compiled by gcc (Ubuntu
5.3.1-14ubuntu2) 5.3.
1 20160413, 32-bit
(1 row)

I think the reason is that when get the replication priority in the function
SyncRepGetStandbyPriority, it does not consider the
parameter synchronous_commit. So if we set the synchronous_standby_names
parameter and the standby fit the setting, no matter whether sync or not,
it's priority will not equal to 0.

--
View this message in context: http://postgresql.nabble.com/When-I-set-the-synchronous-standby-names-and-synchronous-commit-to-off-the-replication-query-state-a-tp5924787.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.