Re: Several issues with postgres_fdw stats import

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.
>
> > (1) User-defined functions may be executed with unexpected privileges
> >
> > ANALYZE on a foreign table with import_stats disabled invokes
> > user-defined functions (e.g., domain constraints) as the foreign table's
> > owner. But, with import_stats enabled, they are invoked as the user
> > running ANALYZE. So, if that user is a superuser, those functions would
> > be run with superuser privileges. Could this be a security issue?
>
> Sorry, I don't follow this. Could you elaborate on it using an example?
>

Me either. I think he's referring to generated columns, in which case the
generated column wouldn't have a corresponding source column, hence would
fail match_attrmap() and already falls back to sampling.

>
> > (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. The query
will error, it will fall back to sampling, and that's the right thing to do
in these cases, in my opinion.

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.

All of these things should already fall back to sampling.

>
> > (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.

>
> From a cost calculation perspective, I think it's appropriate to use
> the imported relpages as-is, as it's used to estimate the cost for
> remote operations, not local ones, and thus we should actually instead
> fix the normal-ANALYZE handling to calculate the size based on the
> remote definition of block size.
>

+1