[YSQL] ysql_dump generates invalid sql statements for colocated table unique constraint #24057
Closed
1 task done
Assignees
Labels
Comments
yifanguan
added
area/ysql
yugabyte-ci
added
kind/bug
yifanguan
added a commit
that referenced
this issue
Sep 24, 2024
…able unique constraint
Summary:
ysql_dump generates invalid statements for colocated table unique constraint.
Example:
```
CREATE TABLE tbl (k INT, V INT UNQUE);
```
has the corresponding unique constraint related statements in ysql_dump output:
```
CREATE UNIQUE INDEX NONCONCURRENTLY tbl_v_key ON public.tbl USING lsm (v ASC) WITH (colocation_id=1976786484);
ALTER TABLE ONLY public.tbl
ADD CONSTRAINT tbl_v_key UNIQUE USING INDEX tbl_v_key WITH (colocation_id='1976786484');
```
In the dump output, the `WITH (...` part after `USING INDEX` is invalid and redundant, and not supported by grammar.
For ysql_dump,
D15799 started outputing reloptions and colocation_id for a colocated table's unique constraint in the `ALTER TABLE ADD CONSTRAINT UNIQUE` statement.
D19352 changed the logic of dumping unique constraints: `CREATE UNIQUE INDEX` and `ALTER TABLE ADD CONSTRAINT UNIQUE USING INDEX` are used to replace the original single `ALTER TABLE ADD CONSTRAINT UNIQUE` statement.
D29672 started outputing the whole index definition including reloptions and YB table properties (e.g. colocaiton_id) in the `CREATE UNIQUE INDEX` statement.
This diff solves the issue by removing the ysql_dump colocated table unique constraint change added by D15799 since the `CREATE UNIQUE INDEX` index definition introduced by D29672 is complete.
In addition, a C++ backup & restore unit test is added, which would fail without the fix.
Jira: DB-12949
Test Plan:
./yb_build.sh --cxx-test yb-backup-test --gtest-filter YBBackupTest.TestColocatedTableUniqueConstraint
./yb_build.sh --java-test 'org.yb.pgsql.TestYsqlDump#ysqlDumpColocatedDB'
Reviewers: mihnea, loginov, myang
Reviewed By: myang
Subscribers: asrivastava, ybase, yql
Differential Revision: https://phorge.dev.yugabyte.com/D38261
|
Issue fixed by commit 326e883 |
timothy-e
pushed a commit
that referenced
this issue
Sep 25, 2024
Summary: 2307937 [PLAT-14939]: PG-15 Upgrade support in YBA 85d88fa [PLAT-15318] Handle MCS universe for Namespaced scope services caa5da6 [PLAT-15396] Fix up update db scoped replication local provider test 395a4ba [PLAT-15367] Delete PITR schedule correctly using correct universe + drop database from replication deletes PITr schedule 6897046 [PLAT-15397]Add a check for software version when configuring Namespaced services 310bc84 [PLAT-15341] updated time drift warn threshold to match alert e221e66 [PLAT-15395] YBM doesn't need to auto populate latest release 48be5e9 [PLAT-15196] kubernetes universe health check does not require clock sync 3ba7be6 [PLAT-15402][PLAT-15426] - : Connection Pool UI improvements d4f4846 [PLAT-14460] Implementing restore from s3 backup Excluded: f448170 [#24051] xCluster: Create Extension automatically 82b2dd6 [PLAT-15385] Remove ProgressMonitor from Commissioner as it is no longer used Excluded: 326e883 [#24057] YSQL: ysql_dump generates invalid statements for colocated table unique constraint d72faae [YSQL][conn-mgr] Change the log level in the message during sleep post DDL 5f22282 [#24105] xCluster: Do not set LogRetention during xCluster bootstrap fb49a39 [#24103] DocDB: Filter gFlag sent to callhome using an allowlist f788dd3 [doc][ybm] Tablet peer alert details (#24082) 5aca2b9 Jobqueue (#24099) Excluded: 475d76c [#23656] YSQL: fix PgAutoAnalyzeTest.TriggerAnalyzeTableRenameAndDelete unit test Excluded: 9e4774c [#24114] xCluster: Ignore hidden tables in DB Scoped xCluster Test Plan: Jenkins: rebase: pg15-cherrypicks Reviewers: jason, tfoucher Subscribers: telgersma Differential Revision: https://phorge.dev.yugabyte.com/D38410
yifanguan
added a commit
that referenced
this issue
Sep 30, 2024
…s for colocated table unique constraint
Summary:
ysql_dump generates invalid statements for colocated table unique constraint.
Example:
```
CREATE TABLE tbl (k INT, V INT UNQUE);
```
has the corresponding unique constraint related statements in ysql_dump output:
```
CREATE UNIQUE INDEX NONCONCURRENTLY tbl_v_key ON public.tbl USING lsm (v ASC) WITH (colocation_id=1976786484);
ALTER TABLE ONLY public.tbl
ADD CONSTRAINT tbl_v_key UNIQUE USING INDEX tbl_v_key WITH (colocation_id='1976786484');
```
In the dump output, the `WITH (...` part after `USING INDEX` is invalid and redundant, and not supported by grammar.
For ysql_dump,
D15799 started outputing reloptions and colocation_id for a colocated table's unique constraint in the `ALTER TABLE ADD CONSTRAINT UNIQUE` statement.
D19352 changed the logic of dumping unique constraints: `CREATE UNIQUE INDEX` and `ALTER TABLE ADD CONSTRAINT UNIQUE USING INDEX` are used to replace the original single `ALTER TABLE ADD CONSTRAINT UNIQUE` statement.
D29672 started outputing the whole index definition including reloptions and YB table properties (e.g. colocaiton_id) in the `CREATE UNIQUE INDEX` statement.
This diff solves the issue by removing the ysql_dump colocated table unique constraint change added by D15799 since the `CREATE UNIQUE INDEX` index definition introduced by D29672 is complete.
In addition, a C++ backup & restore unit test is added, which would fail without the fix.
Jira: DB-12949
Original commit: 326e883 / D38261
Test Plan:
./yb_build.sh --cxx-test yb-backup-test --gtest-filter YBBackupTest.TestColocatedTableUniqueConstraint
./yb_build.sh --java-test 'org.yb.pgsql.TestYsqlDump#ysqlDumpColocatedDB'
Reviewers: mihnea, loginov, myang
Reviewed By: myang
Subscribers: yql, ybase, asrivastava
Differential Revision: https://phorge.dev.yugabyte.com/D38402
yifanguan
added a commit
that referenced
this issue
Sep 30, 2024
…s for colocated table unique constraint
Summary:
This backport diff removes preserving pg_class OIDs part from the expected output files because D36675 hasn't been backported to branch 2024.1.
ysql_dump generates invalid statements for colocated table unique constraint.
Example:
```
CREATE TABLE tbl (k INT, V INT UNQUE);
```
has the corresponding unique constraint related statements in ysql_dump output:
```
CREATE UNIQUE INDEX NONCONCURRENTLY tbl_v_key ON public.tbl USING lsm (v ASC) WITH (colocation_id=1976786484);
ALTER TABLE ONLY public.tbl
ADD CONSTRAINT tbl_v_key UNIQUE USING INDEX tbl_v_key WITH (colocation_id='1976786484');
```
In the dump output, the `WITH (...` part after `USING INDEX` is invalid and redundant, and not supported by grammar.
For ysql_dump,
D15799 started outputing reloptions and colocation_id for a colocated table's unique constraint in the `ALTER TABLE ADD CONSTRAINT UNIQUE` statement.
D19352 changed the logic of dumping unique constraints: `CREATE UNIQUE INDEX` and `ALTER TABLE ADD CONSTRAINT UNIQUE USING INDEX` are used to replace the original single `ALTER TABLE ADD CONSTRAINT UNIQUE` statement.
D29672 started outputing the whole index definition including reloptions and YB table properties (e.g. colocaiton_id) in the `CREATE UNIQUE INDEX` statement.
This diff solves the issue by removing the ysql_dump colocated table unique constraint change added by D15799 since the `CREATE UNIQUE INDEX` index definition introduced by D29672 is complete.
In addition, a C++ backup & restore unit test is added, which would fail without the fix.
Jira: DB-12949
Original commit: 326e883 / D38261
Test Plan:
./yb_build.sh --cxx-test yb-backup-test --gtest-filter YBBackupTest.TestColocatedTableUniqueConstraint
./yb_build.sh --java-test 'org.yb.pgsql.TestYsqlDump#ysqlDumpColocatedDB'
Reviewers: mihnea, loginov, myang
Reviewed By: myang
Subscribers: yql, ybase, asrivastava
Differential Revision: https://phorge.dev.yugabyte.com/D38403
yifanguan
added a commit
that referenced
this issue
Sep 30, 2024
…for colocated table unique constraint
Summary:
This backport diff removes preserving pg_class OIDs part from the expected output files because D36675 hasn't been backported to branch 2.20.
Also, this backport diff removes ROLE-related ysql_dump output from the expected test output files because D32252 hasn't been backported to branch 2.20.
ysql_dump generates invalid statements for colocated table unique constraint.
Example:
```
CREATE TABLE tbl (k INT, V INT UNQUE);
```
has the corresponding unique constraint related statements in ysql_dump output:
```
CREATE UNIQUE INDEX NONCONCURRENTLY tbl_v_key ON public.tbl USING lsm (v ASC) WITH (colocation_id=1976786484);
ALTER TABLE ONLY public.tbl
ADD CONSTRAINT tbl_v_key UNIQUE USING INDEX tbl_v_key WITH (colocation_id='1976786484');
```
In the dump output, the `WITH (...` part after `USING INDEX` is invalid and redundant, and not supported by grammar.
For ysql_dump,
D15799 started outputing reloptions and colocation_id for a colocated table's unique constraint in the `ALTER TABLE ADD CONSTRAINT UNIQUE` statement.
D19352 changed the logic of dumping unique constraints: `CREATE UNIQUE INDEX` and `ALTER TABLE ADD CONSTRAINT UNIQUE USING INDEX` are used to replace the original single `ALTER TABLE ADD CONSTRAINT UNIQUE` statement.
D29672 started outputing the whole index definition including reloptions and YB table properties (e.g. colocaiton_id) in the `CREATE UNIQUE INDEX` statement.
This diff solves the issue by removing the ysql_dump colocated table unique constraint change added by D15799 since the `CREATE UNIQUE INDEX` index definition introduced by D29672 is complete.
In addition, a C++ backup & restore unit test is added, which would fail without the fix.
Jira: DB-12949
Original commit: 326e883 / D38261
Test Plan:
./yb_build.sh --cxx-test yb-backup-test --gtest-filter YBBackupTest.TestColocatedTableUniqueConstraint
./yb_build.sh --java-test 'org.yb.pgsql.TestYsqlDump#ysqlDumpColocatedDB'
Reviewers: mihnea, loginov, myang
Reviewed By: myang
Subscribers: yql, ybase, asrivastava
Differential Revision: https://phorge.dev.yugabyte.com/D38404
yifanguan
added a commit
that referenced
this issue
Oct 1, 2024
… statements for colocated table unique constraint Summary: Original commit: 326e883 / D38261 - pg_dump.c: - dumpConstraint: - master commit removes the redundant colocated table unique constraint definition. - YB pg15 keeps the redundant part as a YB_TODO. - Resolve this merge conflict by removing both the redundant part as the master commit does and the YB_TODO. - yb_ysql_dump_colocated_database.data.sql: - test fix: - ysql_dump in master commit only preserves pg_class oids - PG upstream 9a974cbcba005256a19991203583a94b4f9a21a9 adds code to preserve relfilenodes - fix the test by adding `binary_upgrade_set_next_index_relfilenode` function calls to the expected output for both cases where `binary_upgrade_set_next_index_pg_class_oid` was added. - conflict: - YB pg15 11024ea adds a table htest - master commit adds a table tbl5 - resolve by keeping both, with tbl5 first - yb_ysql_dump_legacy_colocated_database.data.sql: - test fix: - ysql_dump in master commit only preserves pg_class oids - PG upstream 9a974cbcba005256a19991203583a94b4f9a21a9 adds code to preserve relfilenodes - fix the test by adding `binary_upgrade_set_next_index_relfilenode` function calls to the expected output for both cases where `binary_upgrade_set_next_index_pg_class_oid` was added. - conflict: - YB pg15 11024ea adds a table htest - master commit adds a table tbl5 - resolve by keeping both, with tbl5 first - yb_ysql_dump_describe_colocated_database.out: - test fix: - master commit lists a partitioned table as type: table in command `\d` output - YB pg15 lists a partitioned table as type: partitioned table in command `\d` output - resolve the merge conflict by changing type from table to partitioned table for the partitioned table in command `\d` output - yb_ysql_dump_describe_legacy_colocated_database.out: - test fix: - master commit lists a partitioned table as type: table in command `\d` output - YB pg15 lists a partitioned table as type: partitioned table in command `\d` output - resolve the merge conflict by changing type from table to partitioned table for the partitioned table in command `\d` output ysql_dump generates invalid statements for colocated table unique constraint. Example: ``` CREATE TABLE tbl (k INT, V INT UNQUE); ``` has the corresponding unique constraint related statements in ysql_dump output: ``` CREATE UNIQUE INDEX NONCONCURRENTLY tbl_v_key ON public.tbl USING lsm (v ASC) WITH (colocation_id=1976786484); ALTER TABLE ONLY public.tbl ADD CONSTRAINT tbl_v_key UNIQUE USING INDEX tbl_v_key WITH (colocation_id='1976786484'); ``` In the dump output, the `WITH (...` part after `USING INDEX` is invalid and redundant, and not supported by grammar. For ysql_dump, D15799 started outputing reloptions and colocation_id for a colocated table's unique constraint in the `ALTER TABLE ADD CONSTRAINT UNIQUE` statement. D19352 changed the logic of dumping unique constraints: `CREATE UNIQUE INDEX` and `ALTER TABLE ADD CONSTRAINT UNIQUE USING INDEX` are used to replace the original single `ALTER TABLE ADD CONSTRAINT UNIQUE` statement. D29672 started outputing the whole index definition including reloptions and YB table properties (e.g. colocaiton_id) in the `CREATE UNIQUE INDEX` statement. This diff solves the issue by removing the ysql_dump colocated table unique constraint change added by D15799 since the `CREATE UNIQUE INDEX` index definition introduced by D29672 is complete. In addition, a C++ backup & restore unit test is added, which would fail without the fix. Jira: DB-12949 Test Plan: ./yb_build.sh --cxx-test yb-backup-test --gtest-filter YBBackupTest.TestColocatedTableUniqueConstraint ./yb_build.sh --java-test 'org.yb.pgsql.TestYsqlDump#ysqlDumpColocatedDB' Reviewers: fizaa, jason, tfoucher, telgersma Reviewed By: telgersma Subscribers: telgersma, yql Differential Revision: https://phorge.dev.yugabyte.com/D38569
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Jira Link: DB-12949
Description
Example:
Issue Type
kind/bug
Warning: Please confirm that this issue does not contain any sensitive information
The text was updated successfully, but these errors were encountered: