[YSQL] ALTER TABLE ALTER COLUMN TYPE fails when on range key table with split options #17756
Closed
1 task done
Assignees
Labels
Comments
fizaaluthra
added
area/ysql
yugabyte-ci
added
kind/bug
This was referenced Jul 12, 2023
fizaaluthra
added a commit
that referenced
this issue
Jul 24, 2023
Summary:
This diff addresses the following issues:
1) Split properties on indexes aren't copied over during table rewrite operations (ADD/DROP PRIMARY KEY, ALTER COLUMN TYPE)
2) ALTER COLUMN ... TYPE errors out when it is executed on a table with range split properties (because range split propertes are not copied).
The different scenarios where split properties are preserved vs aren't are as follows:
1) ALTER TABLE ADD PRIMARY KEY:
- Hash: Split properties are preserved.
- Range: Split properties are not preserved.
- Indexes: split properties for indexes are always preserved.
2) ALTER TABLE DROP CONSTRAINT <primary key>:
- Hash: Split properties are preserved.
- Range key: Only the number of tablets are preserved. Split points are omitted.
- Indexes: split properties for indexes are always preserved.
3) ALTER TABLE ALTER COLUMN TYPE:
- Hash: Split properties are preserved.
- Range: Split properties are preserved only if the altered column is not a part of the range key.
- Similarly, for indexes: split properties are always preserved for hash indexes. For range based indexes, they are only preserved if the altered column is not a part of the index's range key.
4) Partitioned Tables:
- Split properties on the parent table are ignored.
- When an index is created on a partitioned table, the indexes created on the *existing* partitions will inherit the split properties. Any new partitions that are attached after the index is created, will not inherit the split properties for their indexes.
5) CREATE TABLE ... LIKE:
- Split properties are not copied for the table/indexes. This operation doesn't copy data, so we will be prematurely splitting the new table/indexes.
6) REFRESH MATERIALIZED VIEW (non-concurrent):
- Split properties are not copied for the matview/indexes. The data may change after this operation, so we will rely on tablet splitting instead of copying over the existing split properties.
Jira: DB-5466, DB-6853
Test Plan: `TestPgAlterTableChangePrimaryKey`, `TestPgAlterTableColumnType`, `yb_create_index`, `yb_matview`, `yb_create_table_like`
Reviewers: yguan, jason
Reviewed By: yguan
Subscribers: jason, yql
Differential Revision: https://phorge.dev.yugabyte.com/D23931
|
Reoping issue due to #19382 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Jira Link: DB-6853
Description
yugabyte=# CREATE TABLE x (key char, notkey varchar(10), PRIMARY KEY (key ASC)) SPLIT AT VALUES (('m'));
yugabyte=# ALTER TABLE x ALTER COLUMN notkey TYPE varchar(1);
ERROR: HASH columns must be present to split by number of tablets
Warning: Please confirm that this issue does not contain any sensitive information
The text was updated successfully, but these errors were encountered: