Re-indexing didn't help. Will check via amcheck.
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@xxxxxxxxxxxxx> wrote:
Andrey Lizenko <lizenko79@xxxxxxxxx> 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.
regards, tom lane
Regards, Andrei Lizenko
