Several issues with postgres_fdw stats import

Hi,

While reviewing the postgres_fdw statistics import implementation, I
noticed several potential issues:

1. *User-defined functions and privileges:* With stats import enabled,
some user-defined functions may be executed as the user running ANALYZE
rather than as the foreign table owner. This could potentially result in
functions being executed with unintended privileges, particularly when
ANALYZE is run by a superuser.
2. *COLLATE and old remote servers:* The generated query may send COLLATE
"C" to remote servers without checking their version. Since COLLATE is
only supported from PostgreSQL 9.1, this could break statistics import
against older servers. IMPORT FOREIGN SCHEMA already handles this
compatibility issue.
3. *n_distinct** is ignored:* The imported statistics path appears not
to apply the foreign table column's n_distinct option, whereas normal
ANALYZE applies it after collecting statistics.
4. *relpages** and different block sizes:* The remote relpages value
appears to be stored unchanged. If the local and remote servers use
different BLCKSZ values, this would result in an incorrect local page
count. The value should presumably be converted to local block units,
similar to how normal ANALYZE handles it.

Could these be addressed in the statistics import implementation?

On Thu, Sep 10, 2026 at 12:29 PM Fujii Masao <masao.fujii@gmail.com> wrote:

On Thu, Sep 10, 2026 at 3:59 PM Fujii Masao <masao.fujii@gmail.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?

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

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

(4) Imported relpages may use different block sizes

Stats import stores the remote relpages value unchanged, whereas the
normal ANALYZE uses pg_relation_size() divided by the local BLCKSZ to
handle the case where the block sizes differ between the local and remote
servers.

We should convert the imported page count to local block units, for example?

Good point! Actually, this is a known issue in the normal ANALYZE:

/*
* Construct SELECT statement to acquire size in blocks of given relation.
*
* Note: we use local definition of block size, not remote definition.
* This is perhaps debatable.
*
* Note: pg_relation_size() exists in 8.1 and later.
*/
void
deparseAnalyzeSizeSql(StringInfo buf, Relation rel)

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.

Thanks for the report!

Best regards,
Etsuro Fujita

On Thu, Sep 10, 2026 at 7:55 PM Osama Abdul Qader
<osamaabdulqader.cs@gmail.com> wrote:

While reviewing the postgres_fdw statistics import implementation, I noticed several potential issues:

[snip]

Could these be addressed in the statistics import implementation?

I noticed that your comments are exactly the same as Fujii-san. You
both used the same LLM for this?

Anyway, please check the email I sent just before. Thanks for reviewing!

Best regards,
Etsuro Fujita

On Thu, Sep 10, 2026 at 11:02 AM Etsuro Fujita <etsuro.fujita@gmail.com>
wrote:

On Thu, Sep 10, 2026 at 3:59 PM Fujii Masao <masao.fujii@gmail.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

On Fri, Sep 11, 2026 at 12:46 AM Corey Huinker <corey.huinker@gmail.com> wrote:

On Thu, Sep 10, 2026 at 11:02 AM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:

On Thu, Sep 10, 2026 at 3:59 PM Fujii Masao <masao.fujii@gmail.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

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.

My mistake. The patch looks good. Clearly there's no sufficiently venerable
buildfarm animal that could have detected this, or else we would have found
it sooner, and it's hard to justify supporting it if we cant test it.

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.

It's a bummer that this change will make it slightly harder to do so, but
the chance of redshift or vertica doing such a thing is very low, and if
they did they'd probably bump their server_version_number along with the
change.

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

+1. Applies clean. Passes tests tho obviously the test we needed all along
would have been on a very old buildfarm animal. The wording of the
documentation change is clear.

On Sat, Sep 12, 2026 at 5:53 AM Corey Huinker <corey.huinker@gmail.com> wrote:

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.

My mistake. The patch looks good. Clearly there's no sufficiently venerable buildfarm animal that could have detected this, or else we would have found it sooner, and it's hard to justify supporting it if we cant test it.

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

+1. Applies clean. Passes tests tho obviously the test we needed all along would have been on a very old buildfarm animal. The wording of the documentation change is clear.

Pushed/backpatched.

The regression test just uses a loopback server, so it only tests this
feature against the same version... I think it would be nice if we
could do so against old versions, but that would need a new testing
framework that can also support other features like IMPORT FOREIGN
SCHEMA, so I'd like to leave that for future work.

Thanks for looking!

Best regards,
Etsuro Fujita

On Mon, Sep 14, 2026 at 8:12 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:

Pushed/backpatched.

Thanks for working on this issue!

When I used v11 as the remote server, stats import via postgres_fdw
resulted in the following error. I think this should be addressed as well.

ERROR: collations are not supported by type name
CONTEXT: remote SQL command: SELECT DISTINCT ON (attname COLLATE "C")
attname, null_frac, avg_width, n_distinct, most_common_vals,
most_common_freqs, histogram_bounds, correlation, most_common_elems,
most_common_elem_freqs, elem_count_histogram, NULL, NULL, NULL FROM
pg_catalog.pg_stats WHERE schemaname = 'public' AND tablename = 't'
AND attname = ANY(ARRAY['i', 'j']) ORDER BY attname COLLATE "C",
inherited DESC

Regards,

--
Fujii Masao

On Tue, Sep 15, 2026 at 5:52 PM Fujii Masao <masao.fujii@gmail.com> wrote:

When I used v11 as the remote server, stats import via postgres_fdw
resulted in the following error. I think this should be addressed as well.

ERROR: collations are not supported by type name
CONTEXT: remote SQL command: SELECT DISTINCT ON (attname COLLATE "C")
attname, null_frac, avg_width, n_distinct, most_common_vals,
most_common_freqs, histogram_bounds, correlation, most_common_elems,
most_common_elem_freqs, elem_count_histogram, NULL, NULL, NULL FROM
pg_catalog.pg_stats WHERE schemaname = 'public' AND tablename = 't'
AND attname = ANY(ARRAY['i', 'j']) ORDER BY attname COLLATE "C",
inherited DESC

Reproduced here. Will fix.

I was thinking this lacks testing against old servers, so thanks for
the testing and report!

Best regards,
Etsuro Fujita

On Tue, Sep 15, 2026 at 8:31 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:

On Tue, Sep 15, 2026 at 5:52 PM Fujii Masao <masao.fujii@gmail.com> wrote:

When I used v11 as the remote server, stats import via postgres_fdw
resulted in the following error. I think this should be addressed as well.

ERROR: collations are not supported by type name
CONTEXT: remote SQL command: SELECT DISTINCT ON (attname COLLATE "C")
attname, null_frac, avg_width, n_distinct, most_common_vals,
most_common_freqs, histogram_bounds, correlation, most_common_elems,
most_common_elem_freqs, elem_count_histogram, NULL, NULL, NULL FROM
pg_catalog.pg_stats WHERE schemaname = 'public' AND tablename = 't'
AND attname = ANY(ARRAY['i', 'j']) ORDER BY attname COLLATE "C",
inherited DESC

Reproduced here. Will fix.

As the error message says, the cause of this is that the name type
isn't collatable in v11. It was made so in v12, so I fixed this by
just s/attname COLLATE "C"/attname::text COLLATE "C"/ to the query
generated for v11 or older.

Also, I fixed another bug in the same function: a typo in the if-test
to check whether the remote server is v9.2 or later. The if-test in
Corey's original version was correct, so that's my fault when updating
it to the current version. :palmface:

Attached is a patch for that.

Best regards,
Etsuro Fujita