On 9/29/2010 8:55 PM, Jeff Davis wrote: > 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. I'm VERY sure nobody is connecting - the machine in question is behind a firewall! In addition here's the netstat on it: $ netstat -a -n|more Active Internet connections (including servers) Proto Recv-Q Send-Q Local Address Foreign Address (state) tcp4 0 0 192.168.1.202.22 192.168.1.40.51232 ESTABLISHED tcp4 0 0 192.168.1.202.61119 192.168.1.201.5432 ESTABLISHED tcp4 0 0 *.5432 *.* LISTEN tcp6 0 0 *.5432 *.* LISTEN tcp4 0 0 *.514 *.* LISTEN tcp4 0 0 127.0.0.1.25 *.* LISTEN tcp4 0 0 *.22 *.* LISTEN tcp6 0 0 *.22 *.* LISTEN udp6 0 0 ::1.22165 ::1.22165 udp4 0 0 127.0.0.1.123 *.* udp6 0 0 ::1.123 *.* udp6 0 0 fe80:3::1.123 *.* udp4 0 0 192.168.1.202.123 *.* udp6 0 0 *.123 *.* udp4 0 0 *.123 *.* udp4 0 0 *.514 *.* udp6 0 0 *.514 *.* The only connection to a Postgresql server is to the master I am pulling the replication from (201) No other connections - the listener is there, but nobody's connected to it. > 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 That's actually ok - the update itself is a legitimate statement on the master, posted to that table on a reply, and is part of a transaction. I thought logshipping like this over TCP would present the actual WAL changes (e.g. page changes) and was quite surprised when I saw what look very much like actual statements in the logfile. A process status also shows no postgres processes. All of the real application connections are on a machine 1200 miles away and behind pgpool, so if there WAS a connection it would be persistent. It's not there (never mind that the clients don't have an entry in pg_hba that would work, nor could they cross the firewall since there is no port forwarding in the firewall to allow it.) I'll investigate this further. -- Karl -- Karl
begin:vcard fn:Karl Denninger n:Denninger;Karl email;internet:karl@xxxxxxxxxxxxx note:Read The Market Ticker at http://market-ticker.org x-mozilla-html:TRUE version:2.1 end:vcard
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
