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

sql: new partiton @* doesn't work with table/poor error message #40387

Closed
awoods187 opened this issue Aug 30, 2019 · 1 comment · Fixed by #40709
Closed

sql: new partiton @* doesn't work with table/poor error message #40387

awoods187 opened this issue Aug 30, 2019 · 1 comment · Fixed by #40709
Assignees
@solongordon
Labels
A-partitioning C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)

Comments

@awoods187
Copy link
Contributor

awoods187 commented Aug 30, 2019 •
edited
Loading

I tried out the new @* syntax from #39357 (comment) via:

./cockroach demo movr --nodes 3 --demo-locality=region=us-east,az=1:region=us-central,az=1:region=us-west,az=2
ALTER TABLE rides PARTITION BY LIST (city) ( PARTITION new_york VALUES IN ('new york'), PARTITION chicago VALUES IN ('chicago'), PARTITION seattle VALUES IN ('seattle') );
ALTER INDEX rides_auto_index_fk_city_ref_users PARTITION BY LIST (city) ( PARTITION new_york VALUES IN ('new york'), PARTITION chicago VALUES IN ('chicago'), PARTITION seattle VALUES IN ('seattle') );
ALTER INDEX rides_auto_index_fk_vehicle_city_ref_vehicles PARTITION BY LIST (vehicle_city) ( PARTITION new_york VALUES IN ('new york'), PARTITION chicago VALUES IN ('chicago'), PARTITION seattle VALUES IN ('seattle') );

I observed:

ALTER PARTITION new_york OF TABLE movr.rides@* CONFIGURE ZONE USING constraints='[+region=us-east,+az=1]'; ALTER PARTITION chicago OF TABLE rides@* CONFIGURE ZONE USING constraints='[+region=us-central,+az=1]'; ALTER PARTITION seattle OF TABLE movr.rides@* CONFIGURE ZONE USING constraints='[+region=us-west,+az=2]';
invalid syntax: statement ignored: at or near "@": syntax error
DETAIL: source SQL:
ALTER PARTITION new_york OF TABLE movr.rides@* CONFIGURE ZONE USING constraints='[+region=us-east,+az=1]'
                                            ^
HINT: try \h ALTER PARTITION

It's confusing to me that we chose the INDEX over the TABLE here given that it applies to the entire table. I'd expect to either alias and be able to use either or at the very least to get a warning with the new syntax that you need to use INDEX when using the @* syntax.
@awoods187 awoods187 added A-partitioning C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) labels Aug 30, 2019
@awoods187 awoods187 mentioned this issue Aug 30, 2019
Closed
solongordon added a commit to solongordon/cockroach that referenced this issue Sep 12, 2019
@solongordon
sql: add a syntax hint for ALTER PARTITION
b2c438f 
If a user tries to modify multiple index partitions via ALTER PARTITION
... OF TABLE, they now receive a hint to use ALTER PARTITION ... OF
INDEX instead.

I also improved the help docs for `\h ALTER PARTITION` to specify how to
use the wildcard syntax.

Fixes cockroachdb#40387

Release note: None
@solongordon solongordon mentioned this issue Sep 12, 2019
Merged
craig bot pushed a commit that referenced this issue Sep 12, 2019
@solongordon
Merge #40709
7007e06 
40709: sql: add a syntax hint for ALTER PARTITION r=solongordon a=solongordon

If a user tries to modify multiple index partitions via ALTER PARTITION
... OF TABLE, they now receive a hint to use ALTER PARTITION ... OF
INDEX instead.

I also improved the help docs for `\h ALTER PARTITION` to specify how to
use the wildcard syntax.

Fixes #40387

Release note: None

Co-authored-by: Solon Gordon <solon@cockroachlabs.com>
@craig craig bot closed this as completed in 838cfa1 Sep 12, 2019
@awoods187
Copy link
Contributor Author

awoods187 commented Sep 12, 2019 •
edited
Loading

This looks great!

root@127.0.0.1:56179/movr> ALTER PARTITION new_york OF TABLE movr.rides@* CONFIGURE ZONE USING constraints='[+region=us-east,+az=1]'; ALTER PARTITION chicago OF TABLE rides@* CONFIGURE ZONE USING constraints='[+region=us-central,+az=1]'; ALTER PARTITION seattle OF TABLE movr.rides@* CONFIGURE ZONE USING constraints='[+region=us-west,+az=2]';
invalid syntax: statement ignored: at or near "configure": syntax error: index wildcard unsupported in ALTER PARTITION ... OF TABLE
DETAIL: source SQL:
ALTER PARTITION new_york OF TABLE movr.rides@* CONFIGURE ZONE USING constraints='[+region=us-east,+az=1]'
                                               ^
HINT: try ALTER PARTITION <partition> OF INDEX <tablename>@*

ajwerner pushed a commit to ajwerner/cockroach that referenced this issue Sep 13, 2019
@solongordon @ajwerner
sql: add a syntax hint for ALTER PARTITION
40309bc 
If a user tries to modify multiple index partitions via ALTER PARTITION
... OF TABLE, they now receive a hint to use ALTER PARTITION ... OF
INDEX instead.

I also improved the help docs for `\h ALTER PARTITION` to specify how to
use the wildcard syntax.

Fixes cockroachdb#40387

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

@solongordon solongordon

Labels
A-partitioning C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql: add a syntax hint for ALTER PARTITION solongordon/cockroach
2 participants
@solongordon @awoods187