Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails - Mailing list pgsql-bugs

From Jiří Fejfar
Subject Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
Date
Msg-id 0b066b70-9a6c-c23b-1184-83f71a0b57fa@gmail.com
Whole thread Raw
In response to Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
On 17.08.2020 17:26, Tom Lane wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
>> Joining two identical tables placed on separate DBs with different collation
>> accessed through postgres_fdw failed when joined with merge join. Some
>> records are missing (7 vs. 16 rows in example) in output. See this snippet
>> https://gitlab.com/-/snippets/2004522 (or code pasted below) for psql script
>> reproducing error also with expected output (working fine on alpine linux).
> So I think what is happening here is that postgres_fdw's version of
> IMPORT FOREIGN SCHEMA translates "COLLATE default" on the remote
> server to "COLLATE default" on the local one, which of course is
> a big fail if the defaults don't match.  That allows the local
> planner to believe that remote ORDER BYs on the two foreign tables
> will give compatible results, causing the merge join to not work
> very well at all.

I am just wondering: if it is bug in IMPORT FOREIGN SCHEMA, how it is 
possible the bug is not present [1] when provided psql script [2] is run 
on Alpine Linux? I suppose, both Debian and Alpine has the same IMPORT 
FOREIGN SCHEMA behavior (both has PG12.4). But differs in glibc vs. musl 
libc. Is it possible, there is also something differing in those 
libraries with respect to cs.CZ-UTF8?

Best regards, Jiří.

[1] https://gitlab.com/-/snippets/2004522#note_396751634

[2] https://gitlab.com/-/snippets/2004522




pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #16461: Segfault in autovacuum process
Next
From: Tom Lane
Date:
Subject: Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails