> On Mar 5, 2019, at 11:55 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
>
> Matthew Pounsett <m...@conundrum.com> writes:
>> On Tue, 5 Mar 2019 at 12:54, Tom Lane <t...@sss.pgh.pa.us> wrote:
>>> Given that (a) this was triggered by a server migration and (b)
>>> the leading column of the index looks like it's probably varchar,
>>> I'm suspicious that the new server has different collation behavior.
>
>> The migration in question was an rsync from a Debian 9 box
>> running 9.4.19-0+deb8u1 to a FreeBSD 11 box running 9.4.20.
>
> Yeah, that would fit the theory :-(. Debian would be using glibc
> and FreeBSD would not be. If you were using C collation in the
> database, you'd be all right because that's standardized, but I'll
> bet you were using something else. What does psql \l show for the
> "collate" setting of this database? (Or, if by chance you had an
> explicit COLLATE setting on the column in question, what's that?)
>
> In any case, you should be reindexing any indexes on textual columns
> that were not using "C" collation; none of them can be trusted.
> The system catalogs should be OK.
Many thanks to you and everyone who helped with this issue, with informative
and actionable advice. Reindexing worked like a champ, and we seem to be back
in business.
Casey
