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 = 'f...@example.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 <li...@musall.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 = '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?
>
> Thanks
> Maik
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.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.
