Re: Slaves show different results for query

First, are your postgresql.conf options the same on master and slave2 with
regard to memory allocation and all Planner Method Configuration options?

Next, is it possible you have a corrupted index on slave2.?

I would suggest verifying there is no difference in the query plan between
master and slave2.
IE: EXPLAIN SELECT * FROM MyTable WHERE email = 'foo(at)example(dot)com';

Check your indexes with:

SELECT n.nspname as schema,
i.relname as table,
i.indexrelname as index,
i.idx_scan,
i.idx_tup_read,
i.idx_tup_fetch,
CASE WHEN idx.indisprimary
THEN 'pkey'
WHEN idx.indisunique
THEN 'uidx'
ELSE 'idx'
END AS type,
pg_get_indexdef(idx.indexrelid),
CASE WHEN idx.indisvalid
THEN 'valid'
ELSE 'INVALID'
END as istatus,
pg_relation_size(quote_ident(n.nspname)|| '.' ||
quote_ident(i.relname)) as size_in_bytes,
pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' ||
quote_ident(i.relname))) as size
FROM pg_stat_all_indexes i
JOIN pg_class c ON (c.oid = i.relid)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_index idx ON (idx.indexrelid = i.indexrelid )
WHERE i.relname = 'MyTable'
ORDER BY 1, 2, 3;

On Wed, May 20, 2015 at 2:52 AM, Musall Maik <lists(at)musall(dot)de> 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 = 'foo(at)example(dot)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(at)example(dot)com%';
>
> or this one
>
> SELECT * FROM MyTable WHERE email LIKE '%foo(at)example(dot)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
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.