Hi Mark, I think you're spot on. Reason seams to be in different locale/encoding handling on different operating systems. Strange though that I never ran in to this problem all the years before. Well, I guess I'll move this replication slave to a Linux VM.
I can't re-index anything because a replication slave in this setup is readonly. Maik > Am 21.05.2015 um 04:21 schrieb Mark Cassidy <[email protected] > <mailto:[email protected]>>: > > Well the obvious fix is to run PostgreSQL on the same OS and version of OS + > PostgreSQL. :) > > 1. Have you verified that the PK index was built successfully during the > restore? Or, if you used pgbasebackup, then since it is physical not > logical replication something may be mismatched. Tried re-indexing the PK on > server2? > > If the pk index is broken for some reason on server 2, then this could > explain it because Pg may not use indexes at all when doing LIKEs ... even > with the % at the end ...if your locale isn't appropriate. So the explain > analyze may show that the LIKEs are not using the index even if LIKE 'xyz%'. > > 2. Aside from a bug with Postgres on the Mac, it may possibly be due to a > locale/encoding problem so that's the second thing I'd check. Maybe the @ > sign is throwing it off for the locale? (since the problems seem to be around > the email addresses on slave 2). Not sure why LIKE seems to work though in > the case. > > 3. Make sure Pg is the same version on all three. > select version(); > > Good luck. (But I would not do streaming replication across different OSs > like this.) > > Sent from my Verizon Wireless 4G LTE smartphone > > > -------- Original message -------- > From: Musall Maik <[email protected] <mailto:[email protected]>> > Date: 05/20/2015 8:25 AM (GMT-05:00) > To: WebObjects-Dev Apple <[email protected] > <mailto:[email protected]>> > Subject: strange SELECT results in postgresql slave > > Hi, > > I know many of you work with PostgreSQL so I'll try this here. > > I have a strange case where a SELECT for a primary key returns 0 rows on one > slave, while it returns the correct 1 row on another slave and on the master. > It does however return that row on all slaves when queried with LIKE and > trailing or leading wildcard. > > psql version is 9.3.5, because that's what comes with Ubuntu 14.04 LTS > Master runs Ubuntu 14.04 LTS > Slave 1 runs also Ubuntu 14.04 LTS > Slave 2 runs Mac OS X 10.7, pgsql installed via homebrew > > Both slaves are configured with streaming replication, and I've been using > that setup for years, starting with psql 9.1, with no problems so far. > Suspecting some weird problem, I already re-initialized slave 2 with a fresh > backup and started replication from the beginning, so the database is fresh > from a master copy, and is verified to be current. > > 2015-05-19 20:53:43.937 CEST LOG: entering standby mode > 2015-05-19 20:53:43.974 CEST LOG: redo starts at 31/3F000028 > 2015-05-19 20:53:45.522 CEST LOG: consistent recovery state reached at > 31/40CCE6E8 > 2015-05-19 20:53:45.523 CEST LOG: database system is ready to accept read > only connections > 2015-05-19 20:53:45.604 CEST LOG: started streaming WAL from primary at > 31/41000000 on timeline 1 > > > So here's the query. > > SELECT * FROM MyTable WHERE email = '[email protected] > <mailto:[email protected]>'; > > This returns 1 row on master and slave 1, but 0 on slave 2, while this query: > > SELECT * FROM MyTable WHERE email LIKE '[email protected] > <mailto:[email protected]>%'; > > or this one > > SELECT * FROM MyTable WHERE email LIKE '%[email protected] > <mailto:[email protected]>'; > > returns the correct 1 row on all three systems. Note that this works with the > wildcard on either end, or also somewhere in the middle, doesn't matter. > Note: "email" is the primary key on this table. > > This behaviour is the same with any address to be queried, and is also the > same on a similar second table. This does NOT occur on any other table, which > all have integer primary keys. There is also no problem when I select for > other attributes on these tables. > > Does anyone have a hint? > > Thanks > Maik > > _______________________________________________ > Do not post admin requests to the list. They will be ignored. > Webobjects-dev mailing list ([email protected] > <mailto:[email protected]>) > Help/Unsubscribe/Update your Subscription: > https://lists.apple.com/mailman/options/webobjects-dev/mcassidy%40escholar.com > > <https://lists.apple.com/mailman/options/webobjects-dev/mcassidy%40escholar.com> > > This email sent to [email protected] <mailto:[email protected]>
_______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list ([email protected]) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to [email protected]
