Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[YSQL] Add support for ALTER TABLE ... ADD COLUMN ... with table rewrite #19563

Closed
1 task done
fizaaluthra opened this issue Oct 17, 2023 · 0 comments
Closed
1 task done

[YSQL] Add support for ALTER TABLE ... ADD COLUMN ... with table rewrite #19563

fizaaluthra opened this issue Oct 17, 2023 · 0 comments
Assignees
@fizaaluthra
Labels
area/ysql Yugabyte SQL (YSQL) kind/new-feature This is a request for a completely new feature priority/medium Medium priority issue

Comments

@fizaaluthra
Copy link
Contributor

fizaaluthra commented Oct 17, 2023 •
edited by jira bot

Jira Link: DB-8346

Description

Currently this operation isn't supported as it requires a table rewrite.

Issue Type

kind/new-feature

Warning: Please confirm that this issue does not contain any sensitive information

  • I confirm this issue does not contain any sensitive information.
@fizaaluthra fizaaluthra added area/ysql Yugabyte SQL (YSQL) status/awaiting-triage Issue awaiting triage labels Oct 17, 2023
@yugabyte-ci yugabyte-ci added kind/new-feature This is a request for a completely new feature priority/medium Medium priority issue labels Oct 17, 2023
@yugabyte-ci yugabyte-ci removed the status/awaiting-triage Issue awaiting triage label Oct 25, 2023
@fizaaluthra fizaaluthra changed the title [YSQL] Add support for ALTER TABLE ... ADD COLUMN ... SERIAL PRIMARY KEY [YSQL] Add support for ALTER TABLE ... ADD COLUMN ... with table rewrite Dec 5, 2023
@fizaaluthra fizaaluthra mentioned this issue Dec 5, 2023
Open
fizaaluthra added a commit that referenced this issue Jan 5, 2024
@fizaaluthra
[#19563] YSQL: Enable ALTER TABLE rewrite commands
2fb7ea6 
Summary:
Changes introduced by this diff:

  - Enable ALTER TABLE rewrite commands: Specifically, `ALTER TABLE ADD COLUMN` rewrite commands. The other two cases (`ALTER TYPE`, `ADD/DROP PRIMARY KEY`) are already supported using the old rewrite approach.
            Notes:
             - `ADD COLUMN ... PRIMARY KEY` is only permitted when DDL atomicity is enabled.
             - Split properties are always preserved for the table after an ADD COLUMN rewrite operation.

  - Revise the REINDEX implementation to adopt the updated rewrite approach for user indexes (note: the current `TRUNCATE` approach is retained for system indexes).
            - With the new rewrite approach, a `REINDEX` on a user index involves the following:
                 - The old DocDB table associated with the index is dropped (note: this dropped is finalized only after the transaction commits/aborts).
                 - A new "relfilenode" is created for the index. In YB, a relfilenode maps to a DocDB table, so we create a new DocDB table for the index.
                 - The index's pg_class.relfilenode is updated.
                 - The index is rebuilt (`index_build` is called, and is executed on the new relfilenode/DocDB table).
             - Split properties are always preserved for the table's indexes after an ADD COLUMN rewrite operation.

Upgrade / Rollback:
This feature is guarded by a `LocalPersisted` autoflag, as it introduces a new proto field in the tablet metadata. The feature will only be turned on after upgrade is finalized.
Jira: DB-8346

Test Plan:
Regress tests: `yb_alter_table`, `yb_pg_fast_default`
Cross feature tests:
- Atomicity:
    - `PgLibPqTableRewrite.TestTableRewriteRollback` - Verify that orphaned tables left after failed rewrites are cleaned up.
    - PgLibPqTableRewrite.TestTableRewriteSuccess` - Verify that old tables left after successful rewrites are cleaned up.
- PITR:
    - `YbAdminSnapshotScheduleTestWithYsqlParam.PgsqlAlterTableWithRewrite` - Verify that we can successfully restore to a point in time before the table rewrite operation.
- Backup/Restore:
    - `YBBackupTestWithTableRewrite.TestYSQLBackupAndRestoreAfterRewrite` - Verify backup and restore on rewritten tables, indexes and materialized views.
- xCluster/CDC
    - `XClusterYsqlTest.TestAlterOperationTableRewrite`, `CDCSDKYsqlTest. TestAlterOperationTableRewrite` : Verify that rewrite operations fail on tables that are a part of xCluster/CDC.

Reviewers: dsrinivasan, jhe, myang

Reviewed By: dsrinivasan

Subscribers: bogdan, esheng, yql, ybase

Differential Revision: https://phorge.dev.yugabyte.com/D30850
@fizaaluthra fizaaluthra mentioned this issue Feb 7, 2024
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@fizaaluthra fizaaluthra

Labels
area/ysql Yugabyte SQL (YSQL) kind/new-feature This is a request for a completely new feature priority/medium Medium priority issue
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@yugabyte-ci @fizaaluthra