[YSQL] Feature support - ALTER TABLE

[nocaway]

Jira Link: DB-1178
This is a master issue that keeps track of ALTER TABLE variants.
The checkmark means that the statement is already supported.

Add / remove / rename columns, constraints

Status	Feature	Comments
✅	ALTER TABLE name ADD [COLUMN] [IF NOT EXISTS] colname
⬜️	ALTER TABLE name ADD [COLUMN] [IF NOT EXISTS] colname UNIQUE #6985
⬜️	ALTER TABLE name ADD [COLUMN] [IF NOT EXISTS] colname integer GENERATED ALWAYS AS IDENTITY [PRIMARY KEY]
✅	ALTER TABLE name DROP [COLUMN] [IF NOT EXISTS] non_primary_column [RESTRICT | CASCADE]
⬜️	ALTER TABLE name DROP primary_column
✅	ALTER TABLE name RENAME TO new_name
✅	ALTER TABLE name RENAME COLUMN
✅	ALTER TABLE name RENAME CONSTRAINT #3943
✅	ALTER TABLE name ADD PRIMARY KEY #1104	Not an online operation!
✅	ALTER TABLE name ADD FOREIGN KEY
✅	ALTER TABLE name ADD UNIQUE constraint
✅	ALTER TABLE name ADD CHECK constraint
⬜️	ALTER TABLE name ADD EXCLUDE constraints #3944
⬜️	ALTER TABLE name ADD DEFERRABLE / INITIALLY DEFERRED / INITIALLY IMMEDIATE constraints #1709
✅	ALTER TABLE name DROP CONSTRAINT
✅	ALTER TABLE name DROP CONSTRAINT primary_constraint #8735	Not an online operation!
⬜️	ALTER TABLE name ALTER CONSTRAINT #3945
⬜️	ALTER TABLE name VALIDATE CONSTRAINT #3946
⬜️	Make ALTER table PRIMARY KEY online operations #13274

Alter columns

Status	Feature	Comments
✅	ALTER TABLE name ALTER [COLUMN] colname SET DEFAULT
✅	ALTER TABLE name ALTER [COLUMN] colname DROP DEFAULT
✅	ALTER TABLE name ALTER [COLUMN] colname SET NOT NULL
✅	ALTER TABLE name ALTER [COLUMN] colname DROP NOT NULL
✅	ALTER TABLE name ALTER [COLUMN] colname ADD GENERATED AS IDENTITY
✅	ALTER TABLE name ALTER [COLUMN] colname SET GENERATED
✅	ALTER TABLE name ALTER [COLUMN] colname SET sequence_option
✅	ALTER TABLE name ALTER [COLUMN] colname RESTART sequence
✅	ALTER TABLE name ALTER [COLUMN] colname DROP IDENTITY
✅	ALTER TABLE name ALTER [COLUMN] colname [ SET DATA ] TYPE that does not require on-disk changes #4424
✅	ALTER TABLE name ALTER [COLUMN] colname [ SET DATA ] TYPE that requires on-disk changes #1013
✅	ALTER TABLE name ALTER [COLUMN] colname SET STATISTICS
⬜️	ALTER TABLE name ALTER [COLUMN] colname SET STORAGE
⬜️	ALTER TABLE name ALTER [COLUMN] colname SET ( attribute = value )
⬜️	ALTER TABLE name ALTER [COLUMN] colname RESET ( attribute )

Triggers, partitions and tablespaces

Status	Feature	Comments
✅	ALTER TABLE name ENABLE TRIGGER (name	ALL
✅	ALTER TABLE name ENABLE ALWAYS TRIGGER
✅	ALTER TABLE name ENABLE REPLICA TRIGGER
✅	ALTER TABLE name DISABLE TRIGGER (name | ALL | USER)
✅	ALTER TABLE name ATTACH PARTITION
✅	ALTER TABLE name DETACH PARTITION
⬜️	ALTER TABLE ALL IN TABLESPACE
✅	ALTER TABLE name SET TABLESPACE

Ownership and row level security

Status	Feature	Comments
✅	ALTER TABLE name OWNER TO
✅	ALTER TABLE name ENABLE ROW LEVEL SECURITY
✅	ALTER TABLE name DISABLE ROW LEVEL SECURITY
✅	ALTER TABLE name FORCE ROW LEVEL SECURITY
✅	ALTER TABLE name NO FORCE ROW LEVEL SECURITY

Misc: schema, rules, IODs, table inheritance, clustering etc

Status	Feature	Comments
⬜️	ALTER TABLE name SET SCHEMA #3947
⬜️	ALTER TABLE name ENABLE RULE
⬜️	ALTER TABLE name DISABLE RULE
✅	ALTER TABLE name SET WITHOUT OIDS
⬜️	ALTER TABLE name SET WITH OIDS
⬜️	ALTER TABLE name INHERITS
⬜️	ALTER TABLE name NO INHERIT
⬜️	ALTER TABLE name CLUSTER ON
⬜️	ALTER TABLE name SET WITHOUT CLUSTER
⬜️	ALTER TABLE name OF type_name
⬜️	ALTER TABLE name NOT OF
⬜️	ALTER TABLE name SET (storage_option = value)
⬜️	ALTER TABLE name RESET (storage_option)
⬜️	ALTER TABLE name SET LOGGED | UNLOGGED
⬜️	ALTER TABLE name REPLICA IDENTITY

[dodilp]

Also fails to work with queries like below -
ALTER TABLE ONLY bookings
ADD CONSTRAINT fk_bookings_facid FOREIGN KEY (facid) REFERENCES facilities(facid);

[kmuthukk]

@dodilp - FOREIGN KEY is being implemented as we speak. Hope to have it in a few weeks.

[ramanans]

Fails for
ALTER TABLE public.<table_name> OWNER TO postgres;

ALTER TABLE OWNER not supported yet

[igremmerlb]

For us we are getting the following running migrations from Entity Framework:

Failed executing DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "KeySetRoleAssignment" ALTER COLUMN "PermissionId" TYPE varchar(128);
ALTER TABLE "KeySetRoleAssignment" ALTER COLUMN "PermissionId" SET NOT NULL;
ALTER TABLE "KeySetRoleAssignment" ALTER COLUMN "PermissionId" DROP DEFAULT;
Npgsql.PostgresException (0x80004005): 0A000: ALTER TABLE ALTER column not supported yet

[fire]

Fails to be used as https://www.metabase.com/'s relational database backend.

2-10 09:15:02 ERROR changelog.ChangeSet :: Change Set migrations/000_migrations.yaml::23::agilliland failed. Error: ERROR: ALTER TABLE ALTER column not supported yet
Hint: See #1124. Click '+' on the description to raise its priority
Position: 35 [Failed SQL: ALTER TABLE public.metabase_table ALTER COLUMN rows TYPE BIGINT USING (rows::BIGINT)]
12-10 09:15:02 WARN metabase.util :: auto-retry metabase.db$migrate_up_if_needed_BANG_$fn__18515@2eba1b7: Migration failed for change set migrations/000_migrations.yaml::23::agilliland:
Reason: liquibase.exception.DatabaseException: ERROR: ALTER TABLE ALTER column not supported yet
Hint: See #1124. Click '+' on the description to raise its priority
Position: 35 [Failed SQL: ALTER TABLE public.metabase_table ALTER COLUMN rows TYPE BIGINT USING (rows::BIGINT)]

[rkarthik007]

cc @nocaway @m-iancu : any thoughts? Maybe we don't yet support these alter table variants?