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] Colocation: Create table partition fails with colocation option #20302

Closed
1 task done
yifanguan opened this issue Dec 13, 2023 · 2 comments
Closed
1 task done

[YSQL] Colocation: Create table partition fails with colocation option #20302

yifanguan opened this issue Dec 13, 2023 · 2 comments
Assignees
@yifanguan
Labels
area/ysql Yugabyte SQL (YSQL) kind/bug This issue is a bug priority/medium Medium priority issue

Comments

@yifanguan
Copy link
Contributor

yifanguan commented Dec 13, 2023 •
edited by jira bot
Loading

Jira Link: DB-9268

Description

For colocation, CREATE <table> PARTITION OF ... WITH (COLOCATION=FALSE/TRUE) fails.

Example:

db1=# CREATE TABLE p4 PARTITION OF t FOR VALUES IN (4) WITH (COLOCATION=FALSE);
ERROR:  cannot use 'colocation=true/false' with tablegroup

Issue Type

kind/bug

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

  • I confirm this issue does not contain any sensitive information.
@yifanguan yifanguan added area/ysql Yugabyte SQL (YSQL) status/awaiting-triage Issue awaiting triage labels Dec 13, 2023
@yifanguan yifanguan self-assigned this Dec 13, 2023
@yugabyte-ci yugabyte-ci added kind/bug This issue is a bug priority/medium Medium priority issue labels Dec 13, 2023
@yugabyte-ci yugabyte-ci removed the status/awaiting-triage Issue awaiting triage label Feb 26, 2024
@tverona1
Copy link
Contributor

Note: We support creating individual partitions as colocated or non-colocated via CREATE + ATTACH:

-- Create partitioned table
CREATE TABLE t (col int) PARTITION BY LIST (col);

-- Create a colocated partition
CREATE TABLE p1 (LIKE t INCLUDING ALL);
ALTER TABLE t ATTACH PARTITION p1 FOR VALUES IN (1);

-- Create a non-colocated partition
CREATE TABLE p2 (LIKE t INCLUDING ALL) WITH (COLOCATION=FALSE);
ALTER TABLE t ATTACH PARTITION p2 FOR VALUES IN (2);

However, if we try to use CREATE <table> PARTITION OF ... WITH (COLOCATION=FALSE) (instead of ATTACH PARTITION), we fail with the above error. This defect tracks fixing this issue.

yifanguan added a commit that referenced this issue Mar 19, 2024
@yifanguan
[#20302,#20914] YSQL: fix colocation option issues related to table r…
2cbb49d 
…ewrite and table partition

Summary:
This diff fixes two issues of the colocation option in table creation.
(1) Table rewrite
YB table rewrite operations like ALTER TABLE ADD PRIMARY KEY and REFRESH MATERIALIZED VIEW create a new YB table under the hood.
`reloptions` field in `pg_class` catalog table is read to maintain the property of tables.
Option value in `reloptions` syntax like `colocation=0` is parsed as integer type by the PG parser,
but when untransformed from `reloptions` field, its type is string.
To solve this issue, extend function `defGetBoolean` to process string `0` and `1` as false and true, respectively.

(2) Table partition
We have a check to prevent tablegroup from using with colocation option mainly serving as a grammar guard,
and this check is placed in an inappropriate place causing table partition creation to fail when the colocation option is specified.
Solve this issue by moving the check to an earlier place in the CREATE TABLE execution path.
Jira: DB-9268, DB-9896

Test Plan: ./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressColocation'

Reviewers: tverona, fizaa

Reviewed By: fizaa

Subscribers: yql

Tags: #jenkins-ready

Differential Revision: https://phorge.dev.yugabyte.com/D33001
yifanguan added a commit that referenced this issue Apr 2, 2024
@yifanguan
[BACKPORT 2024.1][#20302,#20914] YSQL: fix colocation option issues r…
a6cdee6 
…elated to table rewrite and table partition

Summary:
This diff fixes two issues of the colocation option in table creation.
(1) Table rewrite
YB table rewrite operations like ALTER TABLE ADD PRIMARY KEY and REFRESH MATERIALIZED VIEW create a new YB table under the hood.
`reloptions` field in `pg_class` catalog table is read to maintain the property of tables.
Option value in `reloptions` syntax like `colocation=0` is parsed as integer type by the PG parser,
but when untransformed from `reloptions` field, its type is string.
To solve this issue, extend function `defGetBoolean` to process string `0` and `1` as false and true, respectively.

(2) Table partition
We have a check to prevent tablegroup from using with colocation option mainly serving as a grammar guard,
and this check is placed in an inappropriate place causing table partition creation to fail when the colocation option is specified.
Solve this issue by moving the check to an earlier place in the CREATE TABLE execution path.
Jira: DB-9268, DB-9896

Original commit: 2cbb49d / D33001

Test Plan: ./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressColocation'

Reviewers: tverona, fizaa

Reviewed By: fizaa

Subscribers: yql

Tags: #jenkins-ready

Differential Revision: https://phorge.dev.yugabyte.com/D33344
yifanguan added a commit that referenced this issue Apr 2, 2024
@yifanguan
[BACKPORT 2.18][#20302,#20914] YSQL: fix colocation option issues rel…
78a258e 
…ated to table rewrite and table partition

Summary:
This diff fixes two issues of the colocation option in table creation.
(1) Table rewrite
YB table rewrite operations like ALTER TABLE ADD PRIMARY KEY and REFRESH MATERIALIZED VIEW create a new YB table under the hood.
`reloptions` field in `pg_class` catalog table is read to maintain the property of tables.
Option value in `reloptions` syntax like `colocation=0` is parsed as integer type by the PG parser,
but when untransformed from `reloptions` field, its type is string.
To solve this issue, extend function `defGetBoolean` to process string `0` and `1` as false and true, respectively.

(2) Table partition
We have a check to prevent tablegroup from using with colocation option mainly serving as a grammar guard,
and this check is placed in an inappropriate place causing table partition creation to fail when the colocation option is specified.
Solve this issue by moving the check to an earlier place in the CREATE TABLE execution path.
Jira: DB-9268, DB-9896

Original commit: 2cbb49d / D33001

Test Plan: ./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressColocation'

Reviewers: tverona, fizaa

Reviewed By: fizaa

Subscribers: yql

Tags: #jenkins-ready

Differential Revision: https://phorge.dev.yugabyte.com/D33348
yifanguan added a commit that referenced this issue Apr 2, 2024
@yifanguan
[BACKPORT 2.20][#20302,#20914] YSQL: fix colocation option issues rel…
b0126ad 
…ated to table rewrite and table partition

Summary:
This diff fixes two issues of the colocation option in table creation.
(1) Table rewrite
YB table rewrite operations like ALTER TABLE ADD PRIMARY KEY and REFRESH MATERIALIZED VIEW create a new YB table under the hood.
`reloptions` field in `pg_class` catalog table is read to maintain the property of tables.
Option value in `reloptions` syntax like `colocation=0` is parsed as integer type by the PG parser,
but when untransformed from `reloptions` field, its type is string.
To solve this issue, extend function `defGetBoolean` to process string `0` and `1` as false and true, respectively.

(2) Table partition
We have a check to prevent tablegroup from using with colocation option mainly serving as a grammar guard,
and this check is placed in an inappropriate place causing table partition creation to fail when the colocation option is specified.
Solve this issue by moving the check to an earlier place in the CREATE TABLE execution path.
Jira: DB-9268, DB-9896

Original commit: 2cbb49d / D33001

Test Plan: ./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressColocation'

Reviewers: tverona, fizaa

Reviewed By: fizaa

Subscribers: yql

Tags: #jenkins-ready

Differential Revision: https://phorge.dev.yugabyte.com/D33346
@yifanguan
Copy link
Contributor Author

Issue resolved by commit 2cbb49d

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@yifanguan yifanguan

Labels
area/ysql Yugabyte SQL (YSQL) kind/bug This issue is a bug priority/medium Medium priority issue
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@tverona1 @yugabyte-ci @yifanguan