Re: Slaves show different results for query

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
rowon another slave and on the master. It does however return that row on all slaves when queried with LIKE and
trailingor 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
startedreplication 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 = 'foo@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 'foo@example.com%';
>
> or this one
>
>     SELECT * FROM MyTable WHERE email LIKE '%foo@example.com';
>
> returns the correct 1 row on all three systems. Note that this works with the wildcard on either end, or also
somewherein 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
NOToccur on any other table, which all have integer primary keys. There is also no problem when I select for other
attributeson these tables. 
>
> Does anyone have a hint?

What are the encodings on the various machines and in the databases?

>
> Thanks
> Maik
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com