Horton

Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails

Etsuro Fujita <etsuro.fujita@gmail.com>

View thread View on postgr.es
From: Etsuro Fujita <etsuro.fujita@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Ibrar Ahmed <ibrar.ahmad@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Neil Chen <carpenter.nail.cz@gmail.com>
Date: 2021-09-10T11:42:27Z
Lists: pgsql-bugs, pgsql-hackers 
On Fri, Sep 10, 2021 at 1:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Etsuro Fujita <etsuro.fujita@gmail.com> writes:
> > Having said that, I think another option for this would be to left the
> > code as-is; assume that 1) the foreign var has "COLLATE default”, not
> > an unknown collation, when labeled with "COLLATE default”, and 2)
> > "COLLATE default” on the local database matches "COLLATE default” on
> > the remote database.
>
> The fundamental complaint that started this thread was exactly that
> assumption (2) isn't safe.  So it sounds to me like you're proposing
> that we do nothing, which isn't a great answer either.  I suppose
> we could try documenting our way out of this, but people will
> continue to get bit because they won't read or won't understand
> the limitation.

Yeah, but I think it’s the user’s responsibility to make sure that the
local and remote default collations match if labeling collatable
columns with “COLLATE default” when defining foreign tables manually
IMO.

> I'd be happier if we had a way to check whether the local and remote
> default collations are compatible.  But it seems like that's a big ask,
> especially in cross-operating-system situations.

Agreed.

Best regards,
Etsuro Fujita

Commits

  1. Doc: extend warnings about collation-mismatch hazards in postgres_fdw.

  2. Add support for asynchronous execution.

  3. Suppress unnecessary RelabelType nodes in yet more cases.