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] Creating a temp index with tablespace should not be allowed #19368

Closed
1 task done
kai-franz opened this issue Sep 30, 2023 · 1 comment
Closed
1 task done

[YSQL] Creating a temp index with tablespace should not be allowed #19368

kai-franz opened this issue Sep 30, 2023 · 1 comment
Assignees
@kai-franz
Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue status/awaiting-triage Issue awaiting triage

Comments

@kai-franz
Copy link
Contributor

kai-franz commented Sep 30, 2023 •
edited by jira bot

Jira Link: DB-8167

Description

Temporary indexes (created by creating an index on a temp table) are not backed by DocDB storage, so specifying a tablespace for them is not meaningful.

Currently, creating a temporary index with tablespace results in a confusing error message:

yugabyte=# CREATE TABLESPACE regress_tblspace LOCATION '/data';
yugabyte=# CREATE TEMPORARY TABLE temptest (a INT);
yugabyte=# CREATE INDEX temp_idx_tblspc ON temptest(a) TABLESPACE regress_tblspace;
ERROR:  could not create directory "pg_tblspc/16384/PG_11_201809051/13301": No such file or directory

Attempting to drop the tablespace after this causes the client to hang:

yugabyte=# DROP TABLESPACE regress_tblspace;

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

  • I confirm this issue does not contain any sensitive information.
@kai-franz kai-franz added area/ysql Yugabyte SQL (YSQL) status/awaiting-triage Issue awaiting triage labels Sep 30, 2023
@kai-franz kai-franz self-assigned this Sep 30, 2023
@yugabyte-ci yugabyte-ci added kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue labels Sep 30, 2023
kai-franz added a commit that referenced this issue Oct 6, 2023
@kai-franz
[#19368] YSQL: Disallow creating temp index with tablespace
298e7d3 
Summary:
Throw an error if the user tries to do CREATE INDEX with TABLESPACE on a temp table.
Because temp tables are not backed by DocDB storage, they should not be allowed to
have a tablespace specified.

- Example:
```
yugabyte=# CREATE TABLESPACE regress_tblspace LOCATION '/data';
yugabyte=# CREATE TEMPORARY TABLE temptest (a INT);
yugabyte=# CREATE INDEX temp_idx_tblspc ON temptest(a) TABLESPACE regress_tblspace;
yugabyte=# DROP TABLESPACE regress_tblspace;
```
Before this change, the above commands would cause the client to hang. Now, it fails cleanly with the message `ERROR:  cannot set tablespace for temporary index`.
Jira: DB-8167

Test Plan: - Pg regress tests in `TestPgRegressTablespaces`

Reviewers: dsrinivasan

Reviewed By: dsrinivasan

Subscribers: jason, yql

Differential Revision: https://phorge.dev.yugabyte.com/D28612
@tverona1
Copy link
Contributor

Fixed

kai-franz added a commit that referenced this issue Jan 31, 2024
@kai-franz
[BACKPORT 2.20][#19368] YSQL: Disallow creating temp index with table…
2ddac5b 
…space

Summary:
Original commit: 298e7d3 / D28612
Throw an error if the user tries to do CREATE INDEX with TABLESPACE on a temp table.
Because temp tables are not backed by DocDB storage, they should not be allowed to
have a tablespace specified.

- Example:
```
yugabyte=# CREATE TABLESPACE regress_tblspace LOCATION '/data';
yugabyte=# CREATE TEMPORARY TABLE temptest (a INT);
yugabyte=# CREATE INDEX temp_idx_tblspc ON temptest(a) TABLESPACE regress_tblspace;
yugabyte=# DROP TABLESPACE regress_tblspace;
```
Before this change, the above commands would cause the client to hang. Now, it fails cleanly with the message `ERROR:  cannot set tablespace for temporary index`.
Jira: DB-8167

Test Plan: - Pg regress tests in `TestPgRegressTablespaces`

Reviewers: dsrinivasan

Reviewed By: dsrinivasan

Subscribers: yql, jason

Tags: #jenkins-ready

Differential Revision: https://phorge.dev.yugabyte.com/D32035
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@kai-franz kai-franz

Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue status/awaiting-triage Issue awaiting triage
Projects
None yet
Milestone
No milestone
Development

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