RE: strange SELECT results in postgresql slave
RE: strange SELECT results in postgresql slave
- Subject: RE: strange SELECT results in postgresql slave
- From: Mark Cassidy <email@hidden>
- Date: Thu, 21 May 2015 02:21:48 +0000
- Thread-topic: strange SELECT results in postgresql slave
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@hidden>
Date: 05/20/2015 8:25 AM (GMT-05:00)
To: WebObjects-Dev Apple <email@hidden>
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@hidden';
This returns 1 row on master and slave 1, but 0 on slave 2, while this query:
SELECT * FROM MyTable WHERE email LIKE 'email@hidden%';
or this one
SELECT * FROM MyTable WHERE email LIKE '%email@hidden';
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@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden