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.

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

[Phase 1][YSQL][colocation] Wrong table description for tablegroups and colocated tables #12186

Closed
yifanguan opened this issue Apr 18, 2022 · 3 comments
Assignees
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 Apr 18, 2022

Jira Link: [DB-381](https://yugabyte.atlassian.net/browse/DB-381)

Description

CREATE DATABASE colocation_test colocated = true;
\c colocation_test
CREATE TABLE tbl (h1 int, h2 int, r1 int, r2 int);
CREATE INDEX idx on tbl (h1 hash);
CREATE INDEX idx2 on tbl ((h1, h2) hash);
\d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 h1     | integer |           |          | 
 h2     | integer |           |          | 
 r1     | integer |           |          | 
 r2     | integer |           |          | 
Indexes:
    "idx" lsm (h1 HASH)
    "idx2" lsm ((h1, h2) HASH)

\c yugabyte
DROP DATABASE colocation_test;

On the other hand, localhost:7000 shows:
for colocation_test.idx

h1            | 10 | int32 NOT NULL NOT A PARTITION KEY
ybidxbasectid | 11 | binary NOT NULL NOT A PARTITION KEY

and

for colocation_test.idx2

h1            | 10 | int32 NOT NULL NOT A PARTITION KEY
h2            | 11 | int32 NOT NULL NOT A PARTITION KEY
ybidxbasectid | 12 | binary NOT NULL NOT A PARTITION KEY
@yifanguan yifanguan added area/ysql Yugabyte SQL (YSQL) status/awaiting-triage Issue awaiting triage labels Apr 18, 2022
@yifanguan
Copy link
Contributor Author

yifanguan commented Apr 18, 2022

The problem exists for TABLEGROUP as well.

CREATE TABLEGROUP tbl_group;
CREATE TABLE tbl_group_tbl (h1 int, h2 int, r1 int, r2 int, v1 int, v2 int,
  PRIMARY KEY (r1, r2)) TABLEGROUP tbl_group;
CREATE INDEX idx_tbl_group_tbl ON tbl_group_tbl (h1 hash);
\d tbl_group_tbl
           Table "public.tbl_group_tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 h1     | integer |           |          | 
 h2     | integer |           |          | 
 r1     | integer |           | not null | 
 r2     | integer |           | not null | 
 v1     | integer |           |          | 
 v2     | integer |           |          | 
Indexes:
    "tbl_group_tbl_pkey" PRIMARY KEY, lsm (r1 ASC, r2 ASC), tablegroup "tbl_group"
    "idx_tbl_group_tbl" lsm (h1 HASH), tablegroup "tbl_group"
Tablegroup: "tbl_group"

For idx_tbl_group_tbl, localhost:7000 shows:

r1             | 10 | int32 NOT NULL NOT A PARTITION KEY
ybidxbasectid  | 11 | binary NOT NULL NOT A PARTITION KEY

@yifanguan yifanguan self-assigned this Apr 19, 2022
@frozenspider frozenspider added this to Backlog in YSQL via automation Apr 20, 2022
@frozenspider frozenspider added this to To do in Colocation via automation Apr 20, 2022
@tverona1 tverona1 changed the title [YSQL] Wrong table description for colocated tables [YSQL] Wrong table description for tablegroups and colocated tables Apr 26, 2022
@tverona1
Copy link
Contributor

Confirm if this is a UI only issue

@tverona1 tverona1 changed the title [YSQL] Wrong table description for tablegroups and colocated tables [Phase 1][YSQL] Wrong table description for tablegroups and colocated tables May 17, 2022
@yugabyte-ci yugabyte-ci added kind/bug This issue is a bug priority/medium Medium priority issue labels May 17, 2022
@tverona1 tverona1 changed the title [Phase 1][YSQL] Wrong table description for tablegroups and colocated tables [Phase 1][YSQL][colocation] Wrong table description for tablegroups and colocated tables May 17, 2022
@yugabyte-ci yugabyte-ci removed the status/awaiting-triage Issue awaiting triage label Jun 17, 2022
yifanguan added a commit that referenced this issue Jul 18, 2022
…dexes and tablegroup indexes

Summary:
This diff explicitly disables the syntax to create a hash partitioned
colocated index/tablegroup index.

Consider two examples below:
```
CREATE TABLEGROUP tbl_group;
CREATE TABLE tbl(h1 int, h2 int, r1 int, r2 int) TABLEGROUP tbl_group;
CREATE INDEX idx ON tbl(h1 hash);
CREATE INDEX idx2 on tbl((h1, h2) hash);
\d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 h1     | integer |           |          |
 h2     | integer |           |          |
 r1     | integer |           |          |
 r2     | integer |           |          |
Indexes:
    "idx" lsm (h1 HASH), tablegroup "tbl_group"
    "idx2" lsm ((h1, h2) HASH), tablegroup "tbl_group"
Tablegroup: "tbl_group"
```
```
CREATE DATABASE colocation_test colocated = true;
\c colocation_test
CREATE TABLE tbl (h1 int, h2 int, r1 int, r2 int);
CREATE INDEX idx on tbl (h1 hash);
CREATE INDEX idx2 on tbl ((h1, h2) hash);
\d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 h1     | integer |           |          |
 h2     | integer |           |          |
 r1     | integer |           |          |
 r2     | integer |           |          |
Indexes:
    "idx" lsm (h1 HASH)
    "idx2" lsm ((h1, h2) HASH)

\c yugabyte
DROP DATABASE colocation_test;

```
The PG table descriptions of all colocated/tablegroup indexes created
in these two examples are hash-partitioned.
However, in reality, all colocated/tablegroup indexes created
in these two examples are range-partitioned.
The reason is we allow specifying hash columns for colocated/tablegroup indexes,
and on CatalogManager side, we change all hash columns to range columns in
`CreateTableRequestPB` in `CreateTable()`.
On Postgre's side, `index_create()` inserts an index tuple
with the specified HASH index option into `pg_index`.

Test Plan:
ybd --java-test org.yb.pgsql.TestPgRegressTablegroup
ybd --java-test org.yb.pgsql.TestPgRegressColocation

Reviewers: mihnea, tverona, alex

Reviewed By: alex

Subscribers: bogdan, yql

Differential Revision: https://phabricator.dev.yugabyte.com/D16702
@yifanguan
Copy link
Contributor Author

Issue resolved by commit: 2ad388f

YSQL automation moved this from Backlog to Done Jul 18, 2022
Colocation automation moved this from To do to Done Jul 18, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area/ysql Yugabyte SQL (YSQL) kind/bug This issue is a bug priority/medium Medium priority issue
Projects
YSQL
  
Done
Development

No branches or pull requests

3 participants