sql: support ALTER TABLE ... ALTER COLUMN ... {ADD | SET} GENERATED ... #110010

giangpham712 · 2023-09-05T11:44:03Z

Is your feature request related to a problem? Please describe.
support ALTER TABLE ... ALTER COLUMN ... {ADD | SET} GENERATED ...

Describe the solution you'd like
Similar to PostgreSQL

ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] }

Also, the related syntax should be covered:
ALTER TABLE "People" ALTER COLUMN "Id" SET INCREMENT BY 2

https://www.postgresql.org/docs/current/sql-altertable.html

Additional context
This affects efcore.pg tests

@fqazi

Jira issue: CRDB-31217

The text was updated successfully, but these errors were encountered:

blathers-crl · 2023-09-05T11:44:05Z

Hello, I am Blathers. I am here to help you get the issue triaged.

I have CC'd a few people who may be able to assist you:

@cockroachdb/sql-foundations (found keywords: ALTER TABLE)

If we have not gotten back to your issue within a few business days, you can try the following:

Join our community slack channel and ask on #cockroachdb.
Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

fqazi · 2023-09-05T13:15:20Z

@giangpham712 Let's skip this test for now, and we can definitely add this syntax, but not realistic for 23.1

andrew-delph · 2023-12-08T17:30:10Z

Working on this.

andrew-delph · 2023-12-08T17:36:31Z

It seems there are some other commands that can be included such as "DROP IDENTITY"

Part of: cockroachdb#110010 This change adds a new command, 'ALTER TABLE t ALTER COLUMN c SET GENERATED ALWAYS' and 'ALTER TABLE t ALTER COLUMN c SET GENERATED BY DEFAULT', which changes the 'GeneratedAsIdentityType' of that column's definition, respectively. Release note (sql change): Identity columns can have their IdentityType altered by running 'ALTER TABLE t ALTER COLUMN c SET GENERATED ALWAYS' or 'ALTER TABLE t ALTER COLUMN c SET GENERATED BY DEFAULT'.

Informs: cockroachdb#110010 This change adds a new command, 'ALTER TABLE ALTER [COLUMN] DROP IDENTITY [ IF EXISTS ]', which allows an IDENTITY column to drop its Identity constraint and related sequence. 'IF EXISTS' is optional and will provide a notice if the column is not an identity. Release note (sql change): Identity Columns can drop the Identity constraint by running 'ALTER TABLE t ALTER COLUMN c DROP IDENTITY [ IF EXISTS ]'.

115889: sql: Allow Identity Column to Change Generation Type r=rafiss a=andrew-delph sql: Allow Alter Column to ADD and SET Identity Informs: #110010 This change adds a new command, 'ALTER TABLE ALTER [COLUMN] <colname> ADD GENERATED { ALWAYS | BY DEFAULT } [( <opt_sequence_option_list> )]' and 'ALTER TABLE ALTER [COLUMN] <colname> SET GENERATED { ALWAYS | BY DEFAULT }', which allows a column to become an Identity or change the generation type of the the identity. Release note (sql change): Columns can have become an IdentityType by running 'ALTER TABLE t ALTER COLUMN c ADD GENERATED ALWAYS [( <opt_sequence_option_list> )]' or 'ALTER TABLE t ALTER COLUMN c ADD GENERATED BY DEFAULT [( <opt_sequence_option_list> )]'. Identity columns can have their IdentityType altered by running 'ALTER TABLE t ALTER COLUMN c SET GENERATED ALWAYS' or 'ALTER TABLE t ALTER COLUMN c SET GENERATED BY DEFAULT'. 119799: sql: fix a couple of memory leaks around memory monitors r=yuzefovich a=yuzefovich See individual commits for details. Epic: None Release note (bug fix): A slow memory leak that can accumulate when opening many new connections has been fixed. The bug is present in 22.2.9+ and 23.1+ versions. 120141: roachprod: only register services when applicable r=renatolabs,srosenberg a=herkolategan Previously, DNS services for clusters would be registered in all cases. This can lead to some scenarios where `roachprod` is unable to correctly determine the port of a service. When a cluster is created in a custom GCP project (non-default project) the `roachprod` garbage collector will destroy the records as it determines it has nothing to tie the resources to since it has no visibility of the custom project. As we currently do not test virtual clusters on AWS or Azure, these have also been excluded, because these providers do not specify using the Google Cloud DNS Provider yet. For that reason, there is no point in trying to register services. Epic: None Release Note: None Co-authored-by: Andrew Delph <[email protected]> Co-authored-by: Yahor Yuzefovich <[email protected]> Co-authored-by: Herko Lategan <[email protected]>

Informs: cockroachdb#110010 This change introduces the 'ALTER [COLUMN] column_name SET sequence_option | RESTART [ [ WITH ] restart ] }' command. This enhancement allows for the modification of identity column sequence options, thereby providing more precise control over the behavior of identity columns. Release note (sql change): Identity columns now support enhanced sequence management through the 'ALTER [COLUMN] column_name SET sequence_option' and 'ALTER [COLUMN] column_name RESTART [WITH restart]' commands. This update facilitates the fine-tuning of identity column sequences.

119432: sql: Add support for altering sequence options identity columns r=fqazi a=andrew-delph Informs: #110010 This change introduces the 'ALTER [COLUMN] column_name SET sequence_option | RESTART [ [ WITH ] restart ] }' command. This enhancement allows for the modification of identity column sequence options, thereby providing more precise control over the behavior of identity columns. Release note (sql change): Identity columns now support enhanced sequence management through the 'ALTER [COLUMN] column_name SET sequence_option' and 'ALTER [COLUMN] column_name RESTART [WITH restart]' commands. This update facilitates the fine-tuning of identity column sequences. 120135: admission: adjust token computation during WAL failover r=aadityasondhi a=sumeerbhola During WAL failover, possibly caused by a disk stall in the primary location, flushes and compactions can also be stalled. This can cause admission control to compute artificially low token counts for compaction bandwidth out of L0 (if L0 has elevated score), and flush tokens (which are meant to prevent memtable write stalls). The solution outlined here detects WAL failover by looking at increases in the pebble metric WAL.Failover.SecondaryWriteDuration. If an increase happened in the last 15s interval (the token computation interval), the current flush and compaction bytes are ignored for the purpose of smoothing and therefore ignored for computing tokens. For regular work, the previous smoothed compaction tokens continue to be used, and flush tokens are unlimited. For elastic work, the tokens are reduced to near zero. An alternative is to allow unlimited tokens during the stall, but it runs the risk of over-admitting. We allow this alternative to be configured by changing the cluster setting admission.wal.failover.unlimited_tokens.enabled to true. Informs cockroachdb/pebble#3230 Informs CRDB-35401 Epic: none Release note (ops change): The cluster setting admission.wal.failover.unlimited_tokens.enabled can be set to true to cause unlimited admission tokens during WAL failover. This should not be changed without consulting admission control team since the default, which preserves the token counts from the preceding non-WAL-failover interval, is expected to be safer. Co-authored-by: Andrew Delph <[email protected]> Co-authored-by: sumeerbhola <[email protected]>

Informs: cockroachdb#110010 This change adds a new command, 'ALTER TABLE ALTER [COLUMN] DROP IDENTITY [ IF EXISTS ]', which allows an IDENTITY column to drop its Identity constraint and related sequence. 'IF EXISTS' is optional and will provide a notice if the column is not an identity. Release note (sql change): Identity Columns can drop the Identity constraint and related sequence by running: -'ALTER TABLE t ALTER COLUMN c DROP IDENTITY'. Option 'IF EXISTS' can be added to skip the command if the column in not an identity: -'ALTER TABLE t ALTER COLUMN c DROP IDENTITY [ IF EXISTS ]'.

119263: sql: Allow Alter Column Drop Identity Command r=annrpom a=andrew-delph Informs: #110010 This change adds a new command, 'ALTER TABLE ALTER [COLUMN] DROP IDENTITY [ IF EXISTS ]', which allows an ID column to drop the related its Identity constraint and related sequence. 'IF EXISTS' is optional and will provide a notice if the column is not an identity. Release note (sql change): Identity Columns can drop the Identity constraint by running 'ALTER TABLE t ALTER COLUMN c DROP IDENTITY [ IF EXISTS ]'. 120893: changefeedccl: skip `TestChangefeedWithSimpleDistributionStrategy` r=andyyang890 a=rickystewart This test is flaky. See #120870 Epic: none Release note: None Co-authored-by: Andrew Delph <[email protected]> Co-authored-by: Ricky Stewart <[email protected]>

giangpham712 added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label Sep 5, 2023

blathers-crl bot added O-community Originated from the community X-blathers-triaged blathers was able to find an owner labels Sep 5, 2023

giangpham712 changed the title ~~sql: support ALTER TABLE ... ALTER COLUMN ... ADD GENERATED ...~~ sql: support ALTER TABLE ... ALTER COLUMN ... {ADD | SET} GENERATED ... Sep 5, 2023

fqazi added T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions) A-tools-efcore labels Sep 5, 2023

fqazi mentioned this issue Sep 5, 2023

sql: support ALTER TABLE ... ALTER COLUMN ... SET INCREMENT BY... #110011

Closed

rafiss added the A-sql-pgcompat Semantic compatibility with PostgreSQL label Sep 5, 2023

andrew-delph mentioned this issue Dec 8, 2023

sql: Allow Identity Column to Change Generation Type #115889

Merged

andrew-delph mentioned this issue Dec 13, 2023

Null Values in Identity Column Attributes When Created Without Sequence Options #116327

Closed

craig bot closed this as completed in 387e464 Mar 11, 2024

github-project-automation bot added this to SQL Foundations Aug 28, 2024

github-project-automation bot moved this to Done in SQL Foundations Aug 28, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

sql: support ALTER TABLE ... ALTER COLUMN ... {ADD | SET} GENERATED ... #110010

sql: support ALTER TABLE ... ALTER COLUMN ... {ADD | SET} GENERATED ... #110010

giangpham712 commented Sep 5, 2023 •

edited by fqazi

Loading

blathers-crl bot commented Sep 5, 2023

fqazi commented Sep 5, 2023

andrew-delph commented Dec 8, 2023

andrew-delph commented Dec 8, 2023

sql: support ALTER TABLE ... ALTER COLUMN ... {ADD | SET} GENERATED ... #110010

sql: support ALTER TABLE ... ALTER COLUMN ... {ADD | SET} GENERATED ... #110010

Comments

giangpham712 commented Sep 5, 2023 • edited by fqazi Loading

blathers-crl bot commented Sep 5, 2023

fqazi commented Sep 5, 2023

andrew-delph commented Dec 8, 2023

andrew-delph commented Dec 8, 2023

giangpham712 commented Sep 5, 2023 •

edited by fqazi

Loading