On 05/19/2015 11:52 PM, Musall Maik wrote:
Hi,
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';
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%';
or this one
SELECT * FROM MyTable WHERE email LIKE '%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?
What are the encodings on the various machines and in the databases?
Thanks
Maik
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general