Re: master and sync-replica diverging

Erik,

Are you taking the counts *while* the table is loading?  In sync replication, it's possible for the counts to differ
fora short time due to one of three things:
 

* transaction has been saved to the replica and confirm message hasn't reached the master yet
* replica has synched the transaction to the WAL log, but due to wal_delay settings hasn't yet applied it to the tables
inmemory.
 
* updating the master with synchronous_commit = local.

----- Original Message -----
> 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 | sync
>  19567 | 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;
>     create table $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
> 
> 
> 
> 
> 
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>