From: | Michał Kłeczek <michal(at)kleczek(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: postgres_fdw aggregate pushdown for group by with expressions |
Date: | 2024-03-03 18:02:50 |
Message-ID: | FF59181A-A183-4FCF-A599-151E01A35897@kleczek.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Ok, so that means the workaround is to create an extension with year(date) and month(date) functions that internally would call extract.
Not ideal but workable I guess.
—
Michał
> On 3 Mar 2024, at 18:42, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal(at)kleczek(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | yudhi s | 2024-03-03 19:06:09 | How to add columns faster |
Previous Message | Tom Lane | 2024-03-03 17:42:53 | Re: postgres_fdw aggregate pushdown for group by with expressions |