pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michał Kłeczek <michal@kleczek.org>
Cc: PG-General Mailing List <pgsql-general@postgresql.org>
Subject: Re: postgres_fdw aggregate pushdown for group by with expressions
Date: Sun, 03 Mar 2024 12:42:53 -0500
Message-ID: <3299634.1709487773@sss.pgh.pa.us> (raw)
In-Reply-To: <5FC17CB3-3048-455D-B638-B48949F78D06@kleczek.org>
References: <AF30BDFB-7215-4E8C-BF11-C97573199D6C@kleczek.org>
	<5FC17CB3-3048-455D-B638-B48949F78D06@kleczek.org>

=?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org> writes:
> I’ve performed some more tests and it seems expressions with “extract” function are not pushed down at all -

Yeah :-(.  I traced through this, and it seems it's a collation
problem.  Internally, that call looks like
	extract('year'::text, date_column)
The text constant is marked as having collation "default", which means
that extract() is marked as having input collation "default", and then
it falls foul of this rule:

 * ... An expression is considered safe to send
 * only if all operator/function input collations used in it are traceable to
 * Var(s) of the foreign table.  That implies that if the remote server gets
 * a different answer than we do, the foreign table's columns are not marked
 * with collations that match the remote table's columns, which we can
 * consider to be user error.

Of course, extract() doesn't actually care about collation, but
postgres_fdw has no good way to know that.  Nor does it trust the
remote server to have the same set of collations the local one does,
so it doesn't want to try to fix this by sending explicit COLLATE
clauses.

Somebody ought to work on improving that mess sometime.  One thought
that comes to mind is to have a server option authorizing postgres_fdw
to believe that all local collations exist on the remote side.

			regards, tom lane





view thread (10+ messages)  latest in thread

Message-ID: <3299634.1709487773@sss.pgh.pa.us>
Permalink:  ../3299634.1709487773@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3299634.1709487773@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: tgl@sss.pgh.pa.us, michal@kleczek.org
  Subject: Re: postgres_fdw aggregate pushdown for group by with expressions
  In-Reply-To: <3299634.1709487773@sss.pgh.pa.us>
  References: <AF30BDFB-7215-4E8C-BF11-C97573199D6C@kleczek.org> <5FC17CB3-3048-455D-B638-B48949F78D06@kleczek.org> <3299634.1709487773@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox