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 <mcass...@escholar.com > <mailto:mcass...@escholar.com>>: > > 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 <m...@selbstdenker.ag <mailto:m...@selbstdenker.ag>> > Date: 05/20/2015 8:25 AM (GMT-05:00) > To: WebObjects-Dev Apple <webobjects-dev@lists.apple.com > <mailto:webobjects-dev@lists.apple.com>> > 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 = 'f...@example.com > <mailto:f...@example.com>'; > > This returns 1 row on master and slave 1, but 0 on slave 2, while this query: > > SELECT * FROM MyTable WHERE email LIKE 'f...@example.com > <mailto:f...@example.com>%'; > > or this one > > SELECT * FROM MyTable WHERE email LIKE '%f...@example.com > <mailto:f...@example.com>'; > > 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 (Webobjects-dev@lists.apple.com > <mailto:Webobjects-dev@lists.apple.com>) > 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 mcass...@escholar.com <mailto:mcass...@escholar.com>
_______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
