sql: alter column type in transaction not supported

[RichardJCai]

With the current schema changer infrastructure, it's difficult to support alter column type in transactions. This is because the computed column swap doesn't actually occur until the transaction is committed. Thus any references to the column being altered will be done on the original column and not the column after the swap.

Here is an example with adding a constraint to an altered column

root@127.0.0.1:61263/movr> ALTER TABLE t ADD CONSTRAINT x_unique UNIQUE (x);
root@127.0.0.1:61263/movr> BEGIN;
ALTER TABLE t ALTER COLUMN x TYPE STRING;
ALTER TABLE t ADD COLUMN y INT;
ALTER TABLE t ADD COLUMN z INT;
ALTER TABLE t ADD CONSTRAINT x_unique UNIQUE (x);
BEGIN

Time: 101µs

NOTICE: ALTER COLUMN TYPE changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes; some writes to the altered column may be rejected until the schema change is finalized
ALTER TABLE

Time: 5.336ms

ALTER TABLE

Time: 9.576ms

ALTER TABLE

Time: 4.358ms

ALTER TABLE

Time: 3.898ms

root@127.0.0.1:61263/movr  OPEN> commit;
ERROR: transaction committed but schema change aborted with error: (XXUUU): verify-idx-count: column-id "1" does not exist
SQLSTATE: XXA00

Allowing alter column type in transactions will likely depend on supporting transactional schema changes in general.

Similarly, ALTER COLUMN TYPE cannot be used in combination with other ALTER TABLE statements since this runs the statements inside a transaction.

Example:
ALTER TABLE t ALTER COLUMN x TYPE STRING, ALTER COLUMN x SET NOT NULL;

Jira issue: CRDB-4235
Epic CRDB-25314

[Anticom]

Any updates on this?

[vy-ton]

@Anticom This won't be fixed in the upcoming 21.1 release but our team is working on the underlying changes needed to support hopefully later in 2021.

[nickelnickel]

Checking in on this one, it's come up again in an attempted migration from 20.1 to 21.1

[gnat]

ALTER transactions are used by the Piccolo ORM automatic migration tool.

Would be a great lightweight tool for free, open source migrations that aren't SQLAlchemy. 😉

[cjoelrun]

Also creeps up with Django migrations with AlterField.

from django.db import migrations, models

class Migration(migrations.Migration):
        migrations.CreateModel(
            name='model1',
            fields=[
                ('field1', models.DateField()),
            ]

This causes psycopg2.errors.FeatureNotSupported: unimplemented: ALTER COLUMN TYPE cannot be used in combination with other ALTER TABLE commands

from django.db import migrations, models

class Migration(migrations.Migration):

    dependencies = [
        ('app', '0001_auto_20220903_1024'),
    ]

    operations = [
        migrations.AlterField(
            model_name='model1',
            name='field1',
            field=models.DateTimeField(blank=True, null=True),
        ),
    ]

[ewassef]

Where are we on this? running v22.2 and still getting this error. is there a cluster setting we can turn on?

[janpio]

Same problem for Prisma: prisma/prisma#20557

Is there a workaround we could implement to somehow get these migrations done anyway?
Do we have to skip the transaction completely for this type of column change?

[gnat]

The short term fix is don't do the migration in a transaction.

For better ORM compatibility, I think there's an argument to be made for CRDB to just ignore the transaction part for schema changes- as I don't see a real fix any time soon, but it's a pressing issue for a lot of ORM's.

[Lord-Y]

Hello guys, were are we on this issue? ALTER COLUMN TYPE is something pretty common. Can we have a priorisation on this issue?

[HaraldTR]

+1 for prioritization on this

[mari-arondeus]

I know my org is still waiting for this before we dump our old postgres ha cluster. Aaaaany day now ;)

[ValentinKardzhaliev]

Is there any progress on this issue?

[Kolawole99]

+1 for prioritizing this issue.

Would need this to migrate from Postgres to Cockroach.

[SwastikGorai]

Is this issue resolved yet??

[arantesxyz]

+1

[brandan-schmitz]

Is there any hope of getting this done? It has been several years now after it has been said it was being worked on and was supposed to be done later in 2021. There are many applications out there that use this in their migrations, and when CockroachDB claims to be PostgreSQL compatible but then does not work for things like this, it makes it really hard to use for things!

[adityadeshmukh369]

any updates on the issue ?