Is it Possible to Add or Modify Columns in Remote Tables After Tiering Data?

[CHEN-Jing194]

After Tiering Data, I found that I can still insert data, but I cannot add or modify columns in remote tables. Is there any way to achieve this?

[shhnwz]

@CHEN-Jing194 ,
Tiered table now a Foreign Table, it should support all the ALTER FOREIGN TABLE commands. Be cautious, in ADD and DROP COLUMN if there's data in S3.

[shhnwz]

@CHEN-Jing194 ,
It would be helpful if you add a comment with the test case.

[CHEN-Jing194]

Add a new column 'email' to the foreign table 'people' with default value

ALTER FOREIGN TABLE people ADD COLUMN email text DEFAULT '[email protected]';

insert data

insert into people values ('Leo', 34, '[email protected]');
insert into people values ('B', 34);

Query the remote table

select * from people;

  name   | age |        email
---------+-----+---------------------
 Alice   |  34 | NULL
 Bob     |  45 | NULL
 Charlie |  56 | NULL
 Leo     |  34 | [email protected]
 B       |  34 | [email protected]
(5 rows)

drop column

ALTER FOREIGN TABLE people DROP COLUMN email;

Query the remote table

select * from people;

  name   | age
---------+-----
 Alice   |  34
 Bob     |  45
 Charlie |  56
 Leo     |  34
 B       |  34
(5 rows)

[CHEN-Jing194]

@CHEN-Jing194 , Tiered table now a Foreign Table, it should support all the ALTER FOREIGN TABLE commands. Be cautious, in ADD and DROP COLUMN if there's data in S3.

However, the update and delete statements do not seem to be supported, and the following error is reported.

[CHEN-Jing194]

@shhnwz However, I see that parquet_s3_fdw should support update and delete operations when key columns are set. If this is feasible, I believe this should be able to meet the needs of most people.

[CHEN-Jing194]

@shhnwz

I think we may need to upgrade parquet_s3_fdw.

[shhnwz]

Hi @CHEN-Jing194 ,
Excellent analysis. All the columns of regular table are transitioned to Key columns in order to support UPDATE and DELETE while generating DDL. I think you are right regarding the fix of cache lookup stuff.

[CHEN-Jing194]

Hi @CHEN-Jing194 , Excellent analysis. All the columns of regular table are transitioned to Key columns in order to support UPDATE and DELETE while generating DDL. I think you are right regarding the fix of cache lookup stuff.

Looking forward to the new version of the pg_tier plugin!