Re: Somewhat odd messages being logged on replicated server

On Wed, 2010-09-29 at 20:04 -0500, Karl Denninger wrote:
> Sep 29 19:58:54 dbms2 postgres[8564]: [2-2] STATEMENT: update post set
> views = (select views from post where number='116763' and toppost='1') +
> 1 where number='116763' and toppost='1'
> Sep 29 20:01:11 dbms2 postgres[8581]: [2-1] ERROR: cannot execute
> UPDATE in a read-only transaction
> Sep 29 20:01:11 dbms2 postgres[8581]: [2-2] STATEMENT: update post set
> views = (select views from post where number='2040327' and toppost='1')
> + 1 where number='2040327' and toppost='1'
> Sep 29 20:02:02 dbms2 postgres[8582]: [2-1] ERROR: cannot execute
> UPDATE in a read-only transaction
> Sep 29 20:02:02 dbms2 postgres[8582]: [2-2] STATEMENT: update post set
> views = (select views from post where number='140406' and toppost='1') +
> 1 where number='140406' and toppost='1'
> Sep 29 20:04:58 dbms2 postgres[8586]: [2-1] ERROR: cannot execute
> UPDATE in a read-only transaction
>
> When I go look at the value of "views" on both the master and replicated
> slave, they have the same value..... so I'm not sure why the error is
> showing up.
>
> There are no clients attempting to connect to the replicated server at
> all at this point (I will enable that later once I'm satisfied that it
> is working in the general sense), so this has to be coming from the
> replication system itself.

It looks very much like clients are connecting and issuing UPDATEs (or
trying to). Can you re-examine the situation? Perhaps try changing
pg_hba.conf to be sure nobody is connecting. 9.0-style replication
doesn't issue queries by itself.

Also, the UPDATEs look unsafe by themselves (replication or not). If two
such updates are executed simultaneously, "views" might not be updated
twice. Instead, try:

update post set views = views + 1
where number='140406' and toppost='1'

Regards,
Jeff Davis