Inconsistency between Compression and Storage for Foreign Tables

Hi,

While developing the CREATE FOREIGN TABLE LIKE functionality in [0], I had to consider the like_options, such as STORAGE, COMPRESSION, and others.

Although foreign tables do not have actual storage in PostgreSQL, we allow the STORAGE option as it may be useful for foreign data wrappers (FDWs) that support this concept.

As stated in the ALTER FOREIGN TBALE documentation[1]:

> This form sets the storage mode for a column. See the similar form of ALTER TABLE for more details. Note that the storage mode has no effect unless the table's foreign-data wrapper chooses to pay attention to it.

However, when aligning COMPRESSION with STORAGE, I find it confusing. IMO, COMPRESSION should behave similarly to STORAGE for foreign tables,
even though they lack real storage. This could be particularly useful for FDWs like postgres_fdw.

I noticed several inconsistencies between COMPRESSION and STORAGE for foreign tables:

1. We actually allow both SET COMPRESSION and STORAGE for foreign table columns, but the CREATE FOREIGN TABLE documentation[2] does not mention this.
gpadmin=# CREATE FOREIGN DATA WRAPPER extstats_dummy_fdw;
CREATE FOREIGN DATA WRAPPER
gpadmin=# CREATE SERVER extstats_dummy_srv FOREIGN DATA WRAPPER extstats_dummy_fdw;
CREATE SERVER
gpadmin=# create foreign table ft1(a int, b text compression lz4) server extstats_dummy_srv;
CREATE FOREIGN TABLE
gpadmin=# \set HIDE_TOAST_COMPRESSION false
gpadmin=# \d+ ft1
Foreign table "public.ft1"
Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
a | integer | | | | | plain | |
b | text | | | | | extended | |
Server: extstats_dummy_srv

gpadmin=# select attname, attcompression from pg_attribute where attname = 'b' and attrelid = 'ft1'::regclass::oid;
attname | attcompression
---------+----------------
b | l
(1 row)

The COMPRESSION info is not listed even HIDE_TOAST_COMPRESSION is set to false because describe.c will ignore that column if table is a foreign table.
But select from pg_attribute will show that compression info.

And the COMPRESSION info is copied when creating a table like that foreign table including options.

gpadmin=# create table t1(like ft1 including all);
CREATE TABLE
gpadmin=# \d+ t1
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
a | integer | | | | plain | | |
b | text | | | | extended | lz4 | |
Access method: heap

The same goes for STORAGE:
gpadmin=# create foreign table ft2(a int, b text storage external) server extstats_dummy_srv;
CREATE FOREIGN TABLE
gpadmin=# \d+ ft2
Foreign table "public.ft2"
Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
a | integer | | | | | plain | |
b | text | | | | | external | |
Server: extstats_dummy_srv
gpadmin=# create table t2(like ft2 including all);
CREATE TABLE
gpadmin=# \d+ t2
Table "public.t2"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
a | integer | | | | plain | | |
b | text | | | | external | | |
Access method: heap

2. We allow ALTER COLUMN SET STORAGE for foreign table columns, but we disallow SET COMPRESSION.

gpadmin=# alter foreign table ft1 alter column b set compression pglz;
ERROR: ALTER action ALTER COLUMN ... SET COMPRESSION cannot be performed on relation "ft1"
DETAIL: This operation is not supported for foreign tables.

gpadmin=# alter foreign table ft1 alter column b set storage external;
ALTER FOREIGN TABLE
gpadmin=# \d+ ft1
Foreign table "public.ft1"
Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
a | integer | | | | | plain | |
b | text | | | | | external | |
Server: extstats_dummy_srv

If foreign tables can utilize STORAGE operations, why shouldn't they also support COMPRESSION? There should be consistency between the two.

I would like to propose a patch to address these inconsistencies, assuming we can reach an agreement on this matter. The changes would include:

1. Modifying the documentation to reflect that we allow setting COMPRESSION and STORAGE on columns when creating foreign tables.
2. Allowing ALTER COLUMN SET COMPRESSION in ALTER FOREIGN TABLE, similar to how we handle SET STORAGE.
3. Including COMPRESSION information for foreign tables when \d?

Thoughts?

[0] https://www.postgresql.org/message-id/42d3f855-2275-4361-a42a-826172ca2dc4%40Spark
[1]  https://www.postgresql.org/docs/current/sql-alterforeigntable.html
[2] https://www.postgresql.org/docs/current/sql-createforeigntable.html

--
Zhang Mingli
HashData