[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?

[irizzant]

+1 For this issue cause I can't restore my dump for this

[ddorian]

@irizzant which exact statement is blocking you ?

[irizzant]

@ddorian

pg_restore: [archiver (db)] could not execute query: ERROR:  ALTER TABLE ALTER column not supported yet
LINE 14: ALTER TABLE ONLY crm.customer_relationships ALTER COLUMN ind...
                                                     ^
HINT:  See https://github.com/YugaByte/yugabyte-db/issues/1124. Click '+' on the description to raise its priority
    Command was: CREATE TABLE crm.customer_relationships (
    customer_id character(16) NOT NULL,
    index integer NOT NULL,
    relationship_type character varying(20) NOT NULL,
    assigned_by integer NOT NULL,
    assigned_on timestamp without time zone NOT NULL,
    since date NOT NULL,
    upto date,
    deleted_by integer,
    deleted_on timestamp without time zone,
    record_status character(1) NOT NULL
)
WITH (autovacuum_enabled='true');
ALTER TABLE ONLY crm.customer_relationships ALTER COLUMN index SET STATISTICS 0;

[ddorian]

@irizzant I believe you need this issue then : #1013 ?

[irizzant]

@ddorian ok thank you, I've also subscribed to #1013

[ddorian]

@irizzant I don't think we use index-statistics like in postgresql. Can you comment that statement and continue migration and see if there's anything else blocking you ?

[irizzant]

@ddorian not an easy task, I have a whole company db dump I have to try and search/replace for occurrences is not feasible.
The blocking issues I'm having, apart from this, are:

• [YSQL] Add support for CREATE TEXT SEARCH TEMPLATE #3512
• [YSQL] Feature Support - Column Collation #1127
• [YSQL] ALTER OPERATOR SET <define> #2711

[z0mb1ek]

+1

[sergeyshaykhullin]

+1

[uwejan]

Good work guys, i can not migrate my schema for the following reason, is there a way around this?
*** There was an issue. Reason: This ALTER TABLE command is not yet supported.. Table: null Column: null

[frozenspider]

Hey @uwejan, would you mind posting the exact command you're executing and YB version you're using on our community slack or forum so we could give you more detailed answer?

[uwejan]

@frozenspider Hi, i have posted on slack. Thank you.

[dbotwinick]

+1 for support for: "ALTER TABLE name CLUSTER ON"

I'm looking to use yugabytedb as a stand-in for pgsql for an existing application. I'm looking to see if I can modify the application & schema as needed, but obviously it'd be easier for me to use yugabytedb as a 'drop-in' replacement if it did not throw an unsupported error.

[ddorian]

@dbotwinick the tables & indexes are clustered on disk by default https://docs.yugabyte.com/latest/architecture/docdb/ by the PRIMARY KEY.

And I don't think there's a chance in the near/medium term to support clustering on something else besides the primary key. So for now you can just ignore this error.

We'll look internally into enabling it and making it a no-op for now.

[dbotwinick]

@dbotwinick the tables & indexes are clustered on disk by default https://docs.yugabyte.com/latest/architecture/docdb/ by the PRIMARY KEY.

And I don't think there's a chance in the near/medium term to support clustering on something else besides the primary key. So for now you can just ignore this error.

We'll look internally into enabling it and making it a no-op for now.

@ddorian that would work for me! I haven't done particular testing on the performance implications that could possibly come up here... but my gut feeling is that it wouldn't particularly be a problem in this case. Ideally I could just use the vanilla application without modification, so if it were a no-op and it logs a warning or something--that would be sufficient for my purposes. It would also work for me if there were some sort of configuration flags to switch between no-op while logging issue and throwing an error. I don't know how treating it as a no-op might cause trouble for others, so it might be safer to make it a configurable option so that the defaults are safer? (although that comes with its own issues)

[pcmid]

+1 I can't migrate from my dump

ERROR: ALTER TABLE ALTER column not supported yet
LINE 1: ALTER TABLE ONLY public.event_search ALTER COLUMN room_id SE...

CREATE TABLE public.event_search (
    event_id text,
    room_id text,
    sender text,
    key text,
    vector tsvector,
    origin_server_ts bigint,
    stream_ordering bigint
);
ALTER TABLE ONLY public.event_search ALTER COLUMN room_id SET (n_distinct=-0.01);

[ddorian]

Hi @pcmid

ALTER TABLE ONLY public.event_search ALTER COLUMN room_id SET (n_distinct=-0.01);

Can you explain the use case behind manually changing the statistics? Do you know in advance the number of unique values in the column?

[pcmid]

Hi @pcmid

ALTER TABLE ONLY public.event_search ALTER COLUMN room_id SET (n_distinct=-0.01);

Can you explain the use case behind manually changing the statistics? Do you know in advance the number of unique values in the column?

Thanks for reply. The reason is here.
Maybe this is only needed in postgresql?

[ddorian]

@pcmid probably yes, you can ignore it in this case.

[purvish-bs]

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

Is this implemented?

[ddorian]

@purvish-bs yes https://docs.yugabyte.com/preview/explore/indexes-constraints/foreign-key-ysql/

[purvish-bs]

I am not a pro in SQL, Could anyone please explain what is not supported here? Thanks in Advance

[ddorian]

@purvish-bs

can you provide the full sql migration?
I have to look at your schema. I think you may have linked the table to a composite type, not really sure though without having all the migration.

[fizaaluthra]

Known issues for ALTER TYPE:
#17756 (ALTER TABLE ALTER COLUMN TYPE fails when on range key table with split options)
#18066 (ALTER TYPE fails after DROP COLUMN)

[ctr49]

I'm seeing this in a real world scenario upon schema installation, see https://github.com/element-hq/synapse/blob/master/synapse/storage/schema/state/full_schemas/72/full.sql.postgres#L23

Are there any workarounds available on Yugabyte side? I could obviously change the schema to make it compatible, but then I couldn't rely on Synapse schema upgrades going forward...

[ddorian]

@ctr49

Are there any workarounds available on Yugabyte side? I could obviously change the schema to make it compatible, but then I couldn't rely on Synapse schema upgrades going forward...

There are no current workarounds. You can make a fork of synapse and just comment that part and you still can rely on them for the other upgrades. You can also subscribe to issue #16675 for updates.