sql: move alter column type general support out of experimental

[RichardJCai]

Currently ALTER COLUMN TYPE for non-trivial casts is only supported experimentally.

Things to do:

• Acceptance testing

Enhancements:

• Add NOTICE for changes that are data-destructive (e.g reducing field width).
• Block data-destructive changes unless sql_safe_updates is false.
• sql: cannot perform other schema changes concurrently with alter column type #47137
• sql: ALTER COLUMN TYPE general for column that has a check constraint not supported. #48288
• sql: cannot perform ALTER TABLE t ALTER COLUMN TYPE in after CREATE TABLE t in the same txn #47537
• sql: ALTER COLUMN TYPE USING EXPRESSION not supported #47706
• sql: alter column type in transaction not supported #49351

Bugs:

• sql: ALTER COLUMN TYPE does not check computed column definitions #72457
• sql: ALTER COLUMN TYPE does not check partial index definitions #72456
• sql: ALTER COLUMN TYPE wipes out ON UPDATE clause #71571

Jira issue: CRDB-4238

Epic CRDB-25314

[blathers-crl]

Hi @RichardJCai, I've guessed the C-ategory of your issue and suitably labeled it. Please re-label if inaccurate.

While you're here, please consider adding an A- label to help keep our repository tidy.

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

[timgraham]

When running Django's test suite, I'm seeing FeatureNotSupported errors pointing to this issue that weren't present before 20.2 alpha 2.

• ALTER COLUMN TYPE from varchar to string is only supported experimentally
• ALTER COLUMN TYPE from int to int is only supported experimentally
• ALTER COLUMN TYPE from int2 to int2 is only supported experimentally

Is this a regression? I'll provide the exact SQL statements if needed.

[RichardJCai]

When running Django's test suite, I'm seeing FeatureNotSupported errors pointing to this issue that weren't present before 20.2 alpha 2.

• ALTER COLUMN TYPE from varchar to string is only supported experimentally
• ALTER COLUMN TYPE from int to int is only supported experimentally
• ALTER COLUMN TYPE from int2 to int2 is only supported experimentally

Is this a regression? I'll provide the exact SQL statements if needed.

Yeah this is a regression, while adding experimental support for other cases of alter column type, I wrapped all uses of alter column type with the error, will fix this soon.

[RichardJCai]

When running Django's test suite, I'm seeing FeatureNotSupported errors pointing to this issue that weren't present before 20.2 alpha 2.

• ALTER COLUMN TYPE from varchar to string is only supported experimentally
• ALTER COLUMN TYPE from int to int is only supported experimentally
• ALTER COLUMN TYPE from int2 to int2 is only supported experimentally

Is this a regression? I'll provide the exact SQL statements if needed.

Actually can you provide the statements please? Curious if you're providing an expression to alter the column type or not.

[timgraham]

ALTER TABLE "schema_author" ALTER COLUMN "name" TYPE text USING "name"::text;
ALTER TABLE "schema_uniquetest" ALTER COLUMN "year" TYPE bigint USING "year"::bigint;
ALTER TABLE "schema_smallintegerpk" ALTER COLUMN "i" TYPE smallint USING "i"::smallint;

[RichardJCai]

@timgraham actually this isn't necessarily a regression - previously the logic did not take into account the USING expression at all.
ALTER TABLE "schema_author" ALTER COLUMN "name" TYPE text USING "name"::text; was equivalent to just
ALTER TABLE "schema_author" ALTER COLUMN "name" TYPE text

Now if any expression is provided, we force a change to happen. The examples you gave, the expression is ignored and the ALTER TABLE is a no-op.

How important is this?

[timgraham]

Maybe Django doesn't need to add a USING clause (that code was copied from the PostgreSQL backend). Removing that resolves the crashes.

diff --git a/django_cockroachdb/schema.py b/django_cockroachdb/schema.py
index 11d90dc..ca2a9da 100644
--- a/django_cockroachdb/schema.py
+++ b/django_cockroachdb/schema.py
@@ -44,9 +44,6 @@ class DatabaseSchemaEditor(PostgresDatabaseSchemaEditor):

     def _alter_column_type_sql(self, model, old_field, new_field, new_type):
         self.sql_alter_column_type = 'ALTER COLUMN %(column)s TYPE %(type)s'
-        # Cast when data type changed.
-        if self._field_data_type(old_field) != self._field_data_type(new_field):
-            self.sql_alter_column_type += ' USING %(column)s::%(type)s'
         # Make ALTER TYPE with SERIAL make sense.
         # table = strip_quotes(model._meta.db_table)
         serial_fields_map = {'bigserial': 'bigint', 'serial': 'integer', 'smallserial': 'smallint'}

Incidentally, I added "SET enable_experimental_alter_column_type_general = true" and enabled some type change tests without issue.

The remaining type conversion test failures are covered by #47636.

[timgraham]

For what it's worth, here are the failures on PostgreSQL when I remove the USING clause. I guess CockroachDB does these casts implicitly.

======================================================================
ERROR: test_alter_text_field_to_date_field (schema.tests.SchemaTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.DatatypeMismatch: column "info" cannot be cast automatically to type date
HINT:  You might need to specify "USING info::date".


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/tim/code/django/tests/schema/tests.py", line 778, in test_alter_text_field_to_date_field
    editor.alter_field(Note, old_field, new_field, strict=True)
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 567, in alter_field
    old_db_params, new_db_params, strict)
  File "/home/tim/code/django/django/db/backends/postgresql/schema.py", line 154, in _alter_field
    new_db_params, strict,
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 717, in _alter_field
    params,
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 142, in execute
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "info" cannot be cast automatically to type date
HINT:  You might need to specify "USING info::date".


======================================================================
ERROR: test_alter_text_field_to_datetime_field (schema.tests.SchemaTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.DatatypeMismatch: column "info" cannot be cast automatically to type timestamp with time zone
HINT:  You might need to specify "USING info::timestamp with time zone".


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/tim/code/django/tests/schema/tests.py", line 794, in test_alter_text_field_to_datetime_field
    editor.alter_field(Note, old_field, new_field, strict=True)
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 567, in alter_field
    old_db_params, new_db_params, strict)
  File "/home/tim/code/django/django/db/backends/postgresql/schema.py", line 154, in _alter_field
    new_db_params, strict,
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 717, in _alter_field
    params,
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 142, in execute
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "info" cannot be cast automatically to type timestamp with time zone
HINT:  You might need to specify "USING info::timestamp with time zone".


======================================================================
ERROR: test_alter_text_field_to_time_field (schema.tests.SchemaTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.DatatypeMismatch: column "info" cannot be cast automatically to type time without time zone
HINT:  You might need to specify "USING info::time without time zone".


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/tim/code/django/tests/schema/tests.py", line 810, in test_alter_text_field_to_time_field
    editor.alter_field(Note, old_field, new_field, strict=True)
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 567, in alter_field
    old_db_params, new_db_params, strict)
  File "/home/tim/code/django/django/db/backends/postgresql/schema.py", line 154, in _alter_field
    new_db_params, strict,
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 717, in _alter_field
    params,
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 142, in execute
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "info" cannot be cast automatically to type time without time zone
HINT:  You might need to specify "USING info::time without time zone".


======================================================================
ERROR: test_char_field_with_db_index_to_fk (schema.tests.SchemaTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.DatatypeMismatch: column "char_field_id" cannot be cast automatically to type integer
HINT:  You might need to specify "USING char_field_id::integer".


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/tim/code/django/django/test/testcases.py", line 1229, in skip_wrapper
    return test_func(*args, **kwargs)
  File "/home/tim/code/django/tests/schema/tests.py", line 330, in test_char_field_with_db_index_to_fk
    editor.alter_field(AuthorCharFieldWithIndex, old_field, new_field, strict=True)
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 567, in alter_field
    old_db_params, new_db_params, strict)
  File "/home/tim/code/django/django/db/backends/postgresql/schema.py", line 154, in _alter_field
    new_db_params, strict,
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 717, in _alter_field
    params,
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 142, in execute
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "char_field_id" cannot be cast automatically to type integer
HINT:  You might need to specify "USING char_field_id::integer".


======================================================================
ERROR: test_text_field_with_db_index_to_fk (schema.tests.SchemaTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.DatatypeMismatch: column "text_field_id" cannot be cast automatically to type integer
HINT:  You might need to specify "USING text_field_id::integer".


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/tim/code/django/django/test/testcases.py", line 1229, in skip_wrapper
    return test_func(*args, **kwargs)
  File "/home/tim/code/django/django/test/testcases.py", line 1229, in skip_wrapper
    return test_func(*args, **kwargs)
  File "/home/tim/code/django/tests/schema/tests.py", line 345, in test_text_field_with_db_index_to_fk
    editor.alter_field(AuthorTextFieldWithIndex, old_field, new_field, strict=True)
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 567, in alter_field
    old_db_params, new_db_params, strict)
  File "/home/tim/code/django/django/db/backends/postgresql/schema.py", line 154, in _alter_field
    new_db_params, strict,
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 717, in _alter_field
    params,
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 142, in execute
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "text_field_id" cannot be cast automatically to type integer
HINT:  You might need to specify "USING text_field_id::integer".

[intech]

Text mismatch in primary key error with pg dialect, in crdb:

alter table "t1" alter column "id" drop not null - column "id" is in a primary index

in pg:

alter table "t1" alter column "id" drop not null - column "id" is in a primary key

Check on CockroachDB CCL v21.2.4 (x86_64-unknown-linux-gnu, built 2022/01/10 18:50:15, go1.16.6)
DDL:

CREATE TABLE public.t1 (
    id INT8 NOT NULL,
    rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
    CONSTRAINT t1_pkey PRIMARY KEY (id ASC),
    FAMILY "primary" (id, rowid)
)

In query:

SET enable_experimental_alter_column_type_general = true;
alter table "t1" alter column "id" type integer using ("id"::integer);

[knz]

Added the following two items to the bullet list at top, as per request from @dbist 👍

• Add NOTICE for changes that are data-destructive (e.g reducing field width).
• Block data-destructive changes unless sql_safe_updates is false.

[cornfeedhobo]

Any hope?