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

[YSQL] accept and ignore "with storage parameters" for create table statements #870

Closed
ravimurthy opened this issue Feb 14, 2019 · 1 comment
Assignees
Labels
good first issue This is a good issue to start contributing! kind/enhancement This is an enhancement of an existing feature
Projects
Milestone

Comments

@ravimurthy
Copy link
Contributor

The "with clause" is used in create table statements to specify storage parameters. These parameters aren't relevant to tables stored in YB. However some standard utilities such as pgbench submit DDL statements with this clause.
YB should accept and ignore the "with clause" options

Example:-

15:34 $ ./pgbench -i -h localhost -p 5433 -U postgres postgres
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
ERROR:  WITH clause not supported yet
LINE 1: ...nt not null,bid int,tbalance int,filler char(84)) with (fill...
                                                             ^
HINT:  Please contact YugaByte for its release schedule.

The actual underlying table definition :-

create table pgbench_tellers(tid int not null,bid int,tbalance int,filler char(84)) with (fillfactor=100)

@ravimurthy ravimurthy added the kind/enhancement This is an enhancement of an existing feature label Feb 14, 2019
@ravimurthy ravimurthy added this to To do in YSQL via automation Feb 14, 2019
@WesleyW
Copy link
Contributor

WesleyW commented Jun 11, 2019

When integrating YW and YB, Play executes the following statement. It would be helpful to have a simplified WITH statement to handle this.

CREATE TABLE "public"."schema_version" (
    "installed_rank" INT NOT NULL,
    "version" VARCHAR(50),
    "description" VARCHAR(200) NOT NULL,
    "type" VARCHAR(20) NOT NULL,
    "script" VARCHAR(1000) NOT NULL,
    "checksum" INTEGER,
    "installed_by" VARCHAR(100) NOT NULL,
    "installed_on" TIMESTAMP NOT NULL DEFAULT now(),
    "execution_time" INTEGER NOT NULL,
    "success" BOOLEAN NOT NULL
) WITH (
  OIDS=FALSE
)

@m-iancu m-iancu added the good first issue This is a good issue to start contributing! label Jun 11, 2019
@ndeodhar ndeodhar assigned georgeklees and unassigned ndeodhar Jun 12, 2019
@ndeodhar ndeodhar added this to the v2.0 milestone Jun 12, 2019
@rao-vasireddy rao-vasireddy moved this from To do to In progress in YSQL Jun 19, 2019
yugabyte-ci pushed a commit that referenced this issue Jun 20, 2019
Summary: Added the handling of WITH clause options in CREATE TABLE, in accordance with the specification here: <https://groups.google.com/a/yugabyte.com/forum/#!topic/yql/gLFGgQZTwwQ> The yacc source file gram.y was modified to allow the WITH clause instead of rejecting it outright, and in the transformCreateStmt() function there are specific checks of the storage parameters.

Test Plan:
Begin by testing the examples in the Github issue:
<#870> The first should display `WARNING:  Storage parameter fillfactor is unsupported, ignoring`
but afterwards the table should still created. The second should succeed with no additional output. If `WITH (OIDS=FALSE)` is substituted for
`WITHOUT OIDS`, the same behavior should hold true. If the option is changed to `WITH (OIDS=TRUE)`, it should
fail with error `ERROR:  OIDs are not supported by this software`; the same should occur if `WITH (OIDS=TRUE)` becomes `WITH OIDS`.

One piece of imperfect output is when a nonexistent storage
parameter gets passed in the WITH clause; for instance, `create table pgbench_tellers(tid int not null,bid int,tbalance int,filler char(84)) with (fillfactor=100,hello=2)`
This will result in a warning that "hello" is not supported but then also an error that "hello" isn't a valid option, when
the intended behavior might be to only have the latter message. If this is an issue, the checking of
storage parameters can be moved elsewhere in the code, like `parseRelOptions()`

The Java test `TestPgRegressPgMisc` also has tests under `yb_create_table` that validate this newly-supported behavior.

Reviewers: bogdan, neha, mihnea

Reviewed By: mihnea

Subscribers: yql

Differential Revision: https://phabricator.dev.yugabyte.com/D6755
YSQL automation moved this from In progress to Done Jun 20, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
good first issue This is a good issue to start contributing! kind/enhancement This is an enhancement of an existing feature
Projects
YSQL
  
Done
Development

No branches or pull requests

5 participants