Another point regarding collations: query with ORDER BY returns different record set on master and on replica if more than one column used:
Master:
=# select date_started, symbol from raw.symbols_aggregates order by 1,2 limit 5; date_started | symbol --------------+--------- 1970-01-01 | AADR.EU 1970-01-01 | AADR.IV 1970-01-01 | AADR.NV 1970-01-01 | AADR.SO 1970-01-01 | AADR.TC
Replica:
=# select date_started, symbol from raw.symbols_aggregates order by 1,2 limit 5; date_started | symbol --------------+-------------- 1970-01-01 | /EPRH18:XMON 1970-01-01 | /EPRM18:XMON 1970-01-01 | /FCEF18:XMON 1970-01-01 | /FCEG18:XMON 1970-01-01 | /FCEH18:XMON
On Wed, Jun 13, 2018 at 10:38 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andrey Lizenko <lizenko79@gmail.com> writes: > I'm observing strange behaviour on comparing ::text field with string while > quering replica.
These symptoms seem consistent with the theory that the replica's index for that column (symbols_aggregates_uniq) is corrupt. I think your casts etc are just serving to defeat selection of an indexscan.
Why it's corrupt, I dunno, but you might try forcing a reindex on the master.