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

Allow no-op alter table DDLs to succeed #23889

Closed
yugabyte-ci opened this issue Sep 11, 2024 · 0 comments
Closed

Allow no-op alter table DDLs to succeed #23889

yugabyte-ci opened this issue Sep 11, 2024 · 0 comments
Assignees
@OlegLoginov
Labels
2024.2 Backport Required area/ysql Yugabyte SQL (YSQL) jira-originated kind/enhancement This is an enhancement of an existing feature priority/highest Highest priority issue

Comments

@yugabyte-ci
Copy link
Contributor

yugabyte-ci commented Sep 11, 2024 •
edited by jira bot
Loading

Jira Link: DB-12793
@yugabyte-ci yugabyte-ci added jira-originated kind/bug This issue is a bug priority/highest Highest priority issue status/awaiting-triage Issue awaiting triage area/ysql Yugabyte SQL (YSQL) labels Sep 11, 2024
@yugabyte-ci yugabyte-ci removed the status/awaiting-triage Issue awaiting triage label Oct 1, 2024
@yugabyte-ci yugabyte-ci added kind/enhancement This is an enhancement of an existing feature and removed kind/bug This issue is a bug labels Oct 1, 2024
@OlegLoginov OlegLoginov mentioned this issue Oct 13, 2024
Open
OlegLoginov added a commit that referenced this issue Oct 14, 2024
@OlegLoginov
[#23895,#23889] YSQL: Enable UNLOGGED syntax and SET/RESET storage pa…
085544d 
…rams as no-op

Summary:
The following YSQL syntax is now allowed, but it's handled as no-op with a warning:
```
CREATE UNLOGGED TABLE <tbl> ... ;
ALTER TABLE <tbl> SET UNLOGGED;
ALTER TABLE <tbl> SET LOGGED;
ALTER TABLE <tbl> SET (<storage_param>=<value>);
ALTER TABLE <tbl> RESET (<storage_param>);

SELECT ... INTO UNLOGGED TABLE <tbl> FROM <src_tbl>;

CREATE UNLOGGED MATERIALIZED VIEW <mv> ;
```
In fact the `UNLOGGED` keyword is ignored. (It's handled as `LOGGED` default persistence.)

Examples:
```
# create unlogged table t1 (i int);
NOTICE:  unlogged option is currently ignored in YugabyteDB, all non-temp tables will be logged
CREATE TABLE
```
```
# alter table t1 set unlogged;
NOTICE:  unlogged option is currently ignored in YugabyteDB, all non-temp tables will be logged
ALTER TABLE
```
```
# alter table t1 set logged;
ALTER TABLE
```
```
# alter table t1 set (fillfactor=5);
NOTICE:  storage parameters are currently ignored in YugabyteDB
ERROR:  value 5 out of bounds for option "fillfactor"
DETAIL:  Valid values are between "10" and "100".
```
```
# alter table t1 set (fillfactor=10);
NOTICE:  storage parameters are currently ignored in YugabyteDB
ALTER TABLE
```
```
# alter table t1 reset (fillfactor);
NOTICE:  storage parameters are currently ignored in YugabyteDB
ALTER TABLE
```
```
# SELECT * INTO UNLOGGED TABLE t2 FROM t1;
NOTICE:  unlogged option is currently ignored in YugabyteDB, all non-temp tables will be logged
SELECT 0
```
```
# CREATE UNLOGGED VIEW unlogged_test_view AS SELECT * FROM test;
ERROR:  views cannot be unlogged because they do not have storage
```
```
# CREATE UNLOGGED MATERIALIZED VIEW mv1 AS SELECT i FROM t1;
ERROR:  materialized views cannot be UNLOGGED
```
```
# CREATE UNLOGGED SEQUENCE sequence_testx;
ERROR:  unlogged sequences are not supported
```

Test Plan:
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressContribPostgresFdw#schedule
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressFeature#testPgRegressFeature
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressGin#testPgRegressGin
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressPartitions#misc
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressPgMisc#testPgRegressPgMisc
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressPgMiscIndependent#testPgRegressPgMiscIndependent
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressMatview#testPgRegressMatview
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressMisc#testPgRegressMiscSerial
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressTable#testPgRegressTable

Fixed in the latest PG15 code-base:
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressWindow#testPgRegressWindow
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressPgTypesString#schedule
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressPgTypesNumeric#schedule
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressPgAuth#schedule

Reviewers: mihnea, fizaa

Reviewed By: fizaa

Subscribers: yql

Differential Revision: https://phorge.dev.yugabyte.com/D38524
OlegLoginov added a commit that referenced this issue Oct 18, 2024
@OlegLoginov
[BACKPORT 2024.2][#23895,#23889] YSQL: Enable UNLOGGED syntax and SET…
c4f2e0d 
…/RESET storage params as no-op

Summary:
Original diff: D38524 / 085544d

The following YSQL syntax is now allowed, but it's handled as no-op with a warning:
```
CREATE UNLOGGED TABLE <tbl> ... ;
ALTER TABLE <tbl> SET UNLOGGED;
ALTER TABLE <tbl> SET LOGGED;
ALTER TABLE <tbl> SET (<storage_param>=<value>);
ALTER TABLE <tbl> RESET (<storage_param>);

SELECT ... INTO UNLOGGED TABLE <tbl> FROM <src_tbl>;

CREATE UNLOGGED MATERIALIZED VIEW <mv> ;
```
In fact the `UNLOGGED` keyword is ignored. (It's handled as `LOGGED` default persistence.)

Examples:
```
# create unlogged table t1 (i int);
NOTICE:  unlogged option is currently ignored in YugabyteDB, all non-temp tables will be logged
CREATE TABLE
```
```
# alter table t1 set unlogged;
NOTICE:  unlogged option is currently ignored in YugabyteDB, all non-temp tables will be logged
ALTER TABLE
```
```
# alter table t1 set logged;
ALTER TABLE
```
```
# alter table t1 set (fillfactor=5);
NOTICE:  storage parameters are currently ignored in YugabyteDB
ERROR:  value 5 out of bounds for option "fillfactor"
DETAIL:  Valid values are between "10" and "100".
```
```
# alter table t1 set (fillfactor=10);
NOTICE:  storage parameters are currently ignored in YugabyteDB
ALTER TABLE
```
```
# alter table t1 reset (fillfactor);
NOTICE:  storage parameters are currently ignored in YugabyteDB
ALTER TABLE
```
```
# SELECT * INTO UNLOGGED TABLE t2 FROM t1;
NOTICE:  unlogged option is currently ignored in YugabyteDB, all non-temp tables will be logged
SELECT 0
```
```
# CREATE UNLOGGED VIEW unlogged_test_view AS SELECT * FROM test;
ERROR:  views cannot be unlogged because they do not have storage
```
```
# CREATE UNLOGGED MATERIALIZED VIEW mv1 AS SELECT i FROM t1;
ERROR:  materialized views cannot be UNLOGGED
```
```
# CREATE UNLOGGED SEQUENCE sequence_testx;
ERROR:  unlogged sequences are not supported
```

Test Plan:
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressContribPostgresFdw#schedule
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressFeature#testPgRegressFeature
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressGin#testPgRegressGin
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressPartitions#misc
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressPgMisc#testPgRegressPgMisc
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressPgMiscIndependent#testPgRegressPgMiscIndependent
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressMatview#testPgRegressMatview
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressMisc#testPgRegressMiscSerial
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressTable#testPgRegressTable

Fixed in the latest PG15 code-base:
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressWindow#testPgRegressWindow
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressPgTypesString#schedule
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressPgTypesNumeric#schedule
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressPgAuth#schedule

Reviewers: mihnea, fizaa

Reviewed By: fizaa

Subscribers: yql

Differential Revision: https://phorge.dev.yugabyte.com/D39160
@OlegLoginov OlegLoginov mentioned this issue Dec 3, 2024
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@OlegLoginov OlegLoginov

Labels
2024.2 Backport Required area/ysql Yugabyte SQL (YSQL) jira-originated kind/enhancement This is an enhancement of an existing feature priority/highest Highest priority issue
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@m-iancu @yugabyte-ci @OlegLoginov