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
