[YSQL] ALTER TYPE fails after DROP COLUMN #18066
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 13, 2023
Summary: Presently, `ALTER TYPE` on a column with a default value can fail if performed after a `DROP COLUMN` on any preceding columns. ``` yugabyte=# CREATE TABLE test(x int, y int DEFAULT 1); CREATE TABLE yugabyte=# ALTER TABLE test DROP COLUMN x; ALTER TABLE yugabyte=# ALTER TABLE test ALTER COLUMN y TYPE SMALLINT; ERROR: could not find attrdef tuple for relation 16420 attnum 2 ``` Moreover, we can also run into a situation where the following column's default value gets overwritten: ``` yugabyte=# CREATE TABLE test(x int, y int DEFAULT 1, z int DEFAULT 2); CREATE TABLE yugabyte=# ALTER TABLE test DROP COLUMN x; ALTER TABLE yugabyte=# ALTER TABLE test ALTER COLUMN y TYPE SMALLINT; -- z's default gets overwritten with y's default. ALTER TABLE yugabyte=# INSERT INTO test(y) VALUES (2); INSERT 1 yugabyte=# SELECT * FROM test; y | z ---+--- 2 | 1 (1 row) ``` Additionally, if the column has a UDT or a non-default collation specified, we create duplicate pg_depend entries (when no preceding column is dropped), and incorrect pg_depend entries referencing the following column (when any preceding column is dropped). These issues arise because after we rewrite the YB table, some code-paths operate on the column's attribute number in the old relation (which is incorrect if any preceding columns were dropped). Also, we execute the PG code-path of installing the collation and datatype dependencies, even though we have already recorded these as part of the YB table rewrite. This diff also adds support for preserving pg_statistic entries for unaffected columns after an ALTER TYPE operation (we presently drop all pg_statistic entries). Jira: DB-7110 Test Plan: `TestPgAlterTableColumnType#testDefaults`, `TestPgAlterTableColumnType#testMiscColumnDependencies` Reviewers: jason Reviewed By: jason Subscribers: myang, yql Differential Revision: https://phorge.dev.yugabyte.com/D26652
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Jira Link: DB-7110
Description
Warning: Please confirm that this issue does not contain any sensitive information
The text was updated successfully, but these errors were encountered: