master and sync-replica diverging

AMD FX 8120 / centos 6.2 / latest source (git head)


It seems to be quite easy to force a 'sync' replica to not be equal to master by
recreating+loading a table in a while loop.


For this test I compiled+checked+installed three separate instances on the same machine.  The
replica application_name are names 'wal_receiver_$copy' where $copy is 01, resp. 02.

$ ./sync_state.sh pid  | application_name |   state   | sync_state
-------+------------------+-----------+------------19520 | wal_receiver_01  | streaming | sync19567 | wal_receiver_02
|streaming | async
 
(2 rows)
port | synchronous_commit | synchronous_standby_names
------+--------------------+---------------------------6564 | on                 | wal_receiver_01
(1 row)
port | synchronous_commit | synchronous_standby_names
------+--------------------+---------------------------6565 | off                |
(1 row)
port | synchronous_commit | synchronous_standby_names
------+--------------------+---------------------------6566 | off                |
(1 row)



The test consists of creating a table and loading tab-separated data from file with COPY and then
taking the rowcount of that table (13 MB, almost 200k rows) in all three instances:


# wget http://flybase.org/static_pages/downloads/FB2012_03/genes/fbgn_annotation_ID_fb_2012_03.tsv.gz

slurp_file=fbgn_annotation_ID_fb_2012_03.tsv.gz

zcat $slurp_file \| grep -v '^#' \| grep -Ev '^[[:space:]]*$' \| psql -c "   drop table if exists $table cascade;
createtable $table (            gene_symbol      text       ,    primary_fbgn     text       ,    secondary_fbgns  text
     ,    annotation_id    text       ,    secondary_annotation_ids text   );   copy $table from stdin csv delimiter
E'\t';";

# count on master:
echo "select current_setting('port') port,count(*) from $table"|psql -qtXp 6564

# count on wal_receiver_01 (sync replica):
echo "select current_setting('port') port,count(*) from $table"|psql -qtXp 6565

# count on wal_receiver_02 (async replica):
echo "select current_setting('port') port,count(*) from $table"|psql -qtXp 6566



I expected the rowcounts from master and sync replica to always be the same.

Initially this seemed to be the case, but when I run the above sequence in a while loop for a few
minutes about 10% of rowcounts from the sync-replica are not equal to the master.

Perhaps not a likely scenario, but surely such a deviating rowcount on a sync replica should not
be possible?


thank you,


Erik Rijkers