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
sql: cannot add a new sequence-populated column (or pg-compatible SERIAL) to an existing table, + bug #42508
Comments
cc @jordanlewis @RaduBerinde @lucy-zhang for triage. |
Also I'm thinking about this a bit and I really don't see why the backfiller cannot evaluate sequence operations. They are non-transactional, so it should really be possible. @jordanlewis what do you think? |
This patch fixes two things. 1) When the backfiller encounters a sequence operation it fails with a hard error. This and consequences is described further in issue cockroachdb#42508. To inform the user better, this patch links the error message to that issue. Additionally, this also ensures that telemetry usage is reported for the unimplemented feature. For example: ``` root@127.0.0.1:39861/movr> alter table t add column y int default nextval('s'); pq: nextval(): unimplemented: cannot backfill such sequence operation HINT: You have attempted to use a feature that is not yet implemented. See: cockroachdb#42508 ``` and ``` > begin; alter table t add column y int default nextval('s'); commit; pq: transaction committed but schema change aborted with error: (0A000): nextval(): unimplemented: cannot backfill such sequence operation HINT: You have attempted to use a feature that is not yet implemented. See: cockroachdb#42508 -- Some of the non-DDL statements may have committed successfully, but some of the DDL statement(s) failed. Manual inspection may be required to determine the actual state of the database. -- See: cockroachdb#42061 ``` 2) The other thing. Prior to this patch, an error "under" a backfill would be flattened into error code 42P15 (invalid schema), even if the schema was valid and the error was really about something encountered during the backfill. This patch fixes it. For example, ``` ALTER TABLE shopping ADD COLUMN c int AS (quantity::int // 0) STORED ``` Would previously error out with code 42P15, whereas the true error is 22012 (division by zero) which is reported now. Release note (sql change): CockroachDB will now properly refer to issue cockroachdb#42508 in the error message hint when a client attempts to add a sequence-based column to an existing table, which is an unimplemented feature. Release note (sql change): CockroachDB will now report an more accurate error message, hint and error code if/when an error is encountered while adding a new column.
Would it be possible to detect sequence-populated columns at compile time, and backport a fix using that approach? As I understand it, #42509 continues to detect the problem only at runtime (though of course it would still be an improvement). |
I agree with your proposal. That said, the main change in #42509 is removing the error flattening, to ensure that the more specific error codes bubble up no matter what. I think this should be merged first before the improvement you sugggest. |
This patch fixes two things. 1) When the backfiller encounters a sequence operation it fails with a hard error. This and consequences is described further in issue cockroachdb#42508. To inform the user better, this patch links the error message to that issue. Additionally, this also ensures that telemetry usage is reported for the unimplemented feature. For example: ``` root@127.0.0.1:39861/movr> alter table t add column y int default nextval('s'); pq: nextval(): unimplemented: cannot backfill such sequence operation HINT: You have attempted to use a feature that is not yet implemented. See: cockroachdb#42508 ``` and ``` > begin; alter table t add column y int default nextval('s'); commit; pq: transaction committed but schema change aborted with error: (0A000): nextval(): unimplemented: cannot backfill such sequence operation HINT: You have attempted to use a feature that is not yet implemented. See: cockroachdb#42508 -- Some of the non-DDL statements may have committed successfully, but some of the DDL statement(s) failed. Manual inspection may be required to determine the actual state of the database. -- See: cockroachdb#42061 ``` 2) The other thing. Prior to this patch, an error "under" a backfill would be flattened into error code 42P15 (invalid schema), even if the schema was valid and the error was really about something encountered during the backfill. This patch fixes it. For example, ``` ALTER TABLE shopping ADD COLUMN c int AS (quantity::int // 0) STORED ``` Would previously error out with code 42P15, whereas the true error is 22012 (division by zero) which is reported now. Release note (sql change): CockroachDB will now properly refer to issue cockroachdb#42508 in the error message hint when a client attempts to add a sequence-based column to an existing table, which is an unimplemented feature. Release note (sql change): CockroachDB will now report an more accurate error message, hint and error code if/when an error is encountered while adding a new column.
This patch fixes two things. 1) When the backfiller encounters a sequence operation it fails with a hard error. This and consequences is described further in issue cockroachdb#42508. To inform the user better, this patch links the error message to that issue. Additionally, this also ensures that telemetry usage is reported for the unimplemented feature. For example: ``` root@127.0.0.1:39861/movr> alter table t add column y int default nextval('s'); pq: nextval(): unimplemented: cannot backfill such sequence operation HINT: You have attempted to use a feature that is not yet implemented. See: cockroachdb#42508 ``` and ``` > begin; alter table t add column y int default nextval('s'); commit; pq: transaction committed but schema change aborted with error: (0A000): nextval(): unimplemented: cannot backfill such sequence operation HINT: You have attempted to use a feature that is not yet implemented. See: cockroachdb#42508 -- Some of the non-DDL statements may have committed successfully, but some of the DDL statement(s) failed. Manual inspection may be required to determine the actual state of the database. -- See: cockroachdb#42061 ``` 2) The other thing. Prior to this patch, an error "under" a backfill would be flattened into error code 42P15 (invalid schema), even if the schema was valid and the error was really about something encountered during the backfill. This patch fixes it. For example, ``` ALTER TABLE shopping ADD COLUMN c int AS (quantity::int // 0) STORED ``` Would previously error out with code 42P15, whereas the true error is 22012 (division by zero) which is reported now. Release note (sql change): CockroachDB will now properly refer to issue cockroachdb#42508 in the error message hint when a client attempts to add a sequence-based column to an existing table, which is an unimplemented feature. Release note (sql change): CockroachDB will now report an more accurate error message, hint and error code if/when an error is encountered while adding a new column.
42509: sqlbase: link to issue #42508 in sequence-related errors r=knz a=knz (I intend to back-port this to 19.2 and perhaps 19.1.) Informs #42508. Informs #42510. This patch fixes two things. 1) When the backfiller encounters a sequence operation it fails with a hard error. This and consequences is described further in issue #42508. To inform the user better, this patch links the error message to that issue. Additionally, this also ensures that telemetry usage is reported for the unimplemented feature. For example: ``` root@127.0.0.1:39861/movr> alter table t add column y int default nextval('s'); pq: nextval(): unimplemented: cannot backfill such sequence operation HINT: You have attempted to use a feature that is not yet implemented. See: #42508 ``` and ``` > begin; alter table t add column y int default nextval('s'); commit; pq: transaction committed but schema change aborted with error: (0A000): nextval(): unimplemented: cannot backfill such sequence operation HINT: You have attempted to use a feature that is not yet implemented. See: #42508 -- Some of the non-DDL statements may have committed successfully, but some of the DDL statement(s) failed. Manual inspection may be required to determine the actual state of the database. -- See: #42061 ``` 2) The other thing. Prior to this patch, an error "under" a backfill would be flattened into error code 42P15 (invalid schema), even if the schema was valid and the error was really about something encountered during the backfill. This patch fixes it. For example, ``` ALTER TABLE shopping ADD COLUMN c int AS (quantity::int // 0) STORED ``` Would previously error out with code 42P15, whereas the true error is 22012 (division by zero) which is reported now. Release note (sql change): CockroachDB will now properly refer to issue #42508 in the error message hint when a client attempts to add a sequence-based column to an existing table, which is an unimplemented feature. Release note (sql change): CockroachDB will now report an more accurate error message, hint and error code if/when an error is encountered while adding a new column. Co-authored-by: Raphael 'kena' Poss <knz@thaumogen.net>
release-19.2: sqlbase: link to issue #42508 in sequence-related errors
Faced the same issue today. Accidentally discovered, that it can be done, but in multiple steps. Furthermore, with update mytable set myseq = nextval('seq') where myseq is null; existing fields get filled. It would be nice to have that in docs. |
@jordanlewis |
Implementing ADD COLUMN in the declarative schema changer will help here, as the column backfiller will no longer be used. |
The index backfiller is not any better equipped to interact with sequences. |
We do at least clean up the back-references properly in the declarative schema changer |
any updates on this ? |
wait too |
Two problems in this issue
Because of the bug part, the severity is higher than if this was just a missing feature.
The limitation
Today the column backfiller is unable to execute sequence operations such as evaluating the
nextval()
function.This makes it impossible to add a column that uses a sequence-based DEFAULT to an existing table.
For example:
Or:
The bug
A large problem with this limitation is that the error is only detected at runtime, not at compile-time. This means that an attempt to add such a column inside an explicit txn will violate atomicity with the dreaded XXA00 error (see #42061)
(the INSERT was committed, but the ALTER failed: atomicity anomaly)
Jira issue: CRDB-5351
The text was updated successfully, but these errors were encountered: