Re: Several issues with postgres_fdw stats import

On Fri, Sep 11, 2026 at 12:46 AM Corey Huinker <corey(dot)huinker(at)gmail(dot)com> wrote:
> On Thu, Sep 10, 2026 at 11:02 AM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
>> On Thu, Sep 10, 2026 at 3:59 PM Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> > postgres_fdw stats import seems to have several potential issues.

>> > (2) COLLATE is not supported by old remote servers
>> >
>> > Stats import sends COLLATE "C" to the remote server without checking
>> > its version, but COLLATE is supported only in v9.1 and later.
>> > The comment in deparse.c explicitly mentions this compatibility issue, and
>> > IMPORT FOREIGN SCHEMA disables collation import for remote servers older
>> > than v9.1.
>> >
>> > So, it seems stats import should handle this issue as well, e.g., either by
>> > avoiding COLLATE or by falling back to sampling.
>>
>> Good catch! I feel like just falling back to sampling.
>
> In other areas (pg_dump, for example) we're walking the minimum supported version to v10, so I don't feel like we want to spend much effort accommodating machines that went out of support > 12 years ago.

+1

> The query will error, it will fall back to sampling, and that's the right thing to do in these cases, in my opinion.

No, it won't fall back; the syntax error on the remote server will
lead to an error on the local server, actually. That isn't great, so
I modified postgres_fdw to do the fallback. Patch attached.

> The obvious counter-example are databases that are forks of postgres from the 8.x era (Vertica, Redshift), neither of which have a pg_stats view, so the queries are dead ends anyway. I could envision a future where those databases are encouraged to add a pg_stats view with the stats translated/synthesized to FDW-friendly values.

Me too.

>> > (3) n_distinct is ignored
>> >
>> > Stats import ignores the foreign table column's n_distinct option, whereas
>> > normal ANALYZE applies it after collecting statistics.
>> >
>> > IMO, n_distinct should also be applied to imported stats.
>>
>> Rather than making the code complicated for that, I feel like just
>> copying the remote table's stats as-proposed, as in most cases, those
>> stats are generated on the remote server under the correct settings of
>> parameters like n_distcint. How about adding a note about that?
>
> I agree. The user trusts the remote to have good stats, otherwise they wouldn't use this option.

I modified postgres-fdw.sgml as well to mention that that option is
ignored, tweaking a phrase a bit.

Best regards,
Etsuro Fujita