Thread: Inconsistency between Compression and Storage for Foreign Tables

Inconsistency between Compression and Storage for Foreign Tables

From

Zhang Mingli

Date:

05 February, 20:42:34

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 falsebecause 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:

Modifying the documentation to reflect that we allow setting COMPRESSION and STORAGE on columns when creating foreign tables.
Allowing ALTER COLUMN SET COMPRESSION in ALTER FOREIGN TABLE, similar to how we handle SET STORAGE.
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

Re: Inconsistency between Compression and Storage for Foreign Tables

From

Sami Imseih

Date:

11 February, 02:52:24

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

Hi,

It looks like cb1ca4d [1], from nearly 10 years ago, allowed storage
on an FDW to
avoid introducing any special handling for foreign tables in the case of
inheritance/partitioning, especially when partitions are a mixture of
both normal
and foreign tables.

COMPRESSION, on the other hand, did not require special handling because,
when you run ALTER TABLE ... COMPRESSION on a parent table, the setting
is only applied to the parent table and to new partitions created afterward.
Existing partitions do not inherit the setting, as somewhat described here [2]:
"but the configured value will be inherited by newly-created partitions."

IMO, the correct approach is to disallow the STORAGE parameter on
foreign tables,
which is what [1] tried to avoid for some reason. However, since this
behavior has
existed for over 10 years, I don’t think we should change it.
Maybe someone has a different opinion about this, and we should disallow
setting STORAGE on an fdw table in future versions.

I am not in favor of allowing compression to be set on a foreign table
just for the
sake of consistency, when there is no reason for this on a foreign table.

[1] https://github.com/postgres/postgres/commit/cb1ca4d800621dc
[2] https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-SET-COMPRESSION

Regards,

Sami

Re: Inconsistency between Compression and Storage for Foreign Tables

From

Zhang Mingli

Date:

21 February, 12:11:40

On Feb 11, 2025 at 07:52 +0800, Sami Imseih <samimseih@gmail.com>, wrote:

IMO, the correct approach is to disallow the STORAGE parameter on
foreign tables,
which is what [1] tried to avoid for some reason. However, since this
behavior has
existed for over 10 years, I don’t think we should change it.

Hi,

I understand that the options might seem unnecessary for foreign tables in general, but they could still be useful for FDWs that choose to utilize them, as mentioned in the documentation.
And if STORAGE can exist on a foreign table, why not COMPRESSION?
That’s what confuses me, and I think it could confuse others as well if they encounter something like that.

Maybe someone has a different opinion about this, and we should disallow
setting STORAGE on an fdw table in future versions.

If that’s the case, why not today? I’d be happy to take care of it.

--
Zhang Mingli
HashData