Comparing computed fields throws warn

[hevalhazalkurt]

I'm not sure if this is a bug or a mistake caused by me, but I wanted to share it here because I couldn't find a proper solution.

I have a computed tsvector field in my database as you can see in the section of the migration file and model I shared below.

def upgrade():
    op.create_table(
        "a_table",
        sa.Column("Column1", sa.String(), nullable=False),
        sa.Column("Column2", sa.ARRAY(sa.String(), dimensions=1), nullable=True),
        sa.Column("Column3", sa.ARRAY(sa.String(), dimensions=1), nullable=True),
        sa.Column("Column4", sa.ARRAY(sa.String(), dimensions=1), nullable=True),
        sa.Column("Column5", sa.ARRAY(sa.String(), dimensions=1), nullable=True),
        sa.Column("Column6", sa.ARRAY(sa.String(), dimensions=1), nullable=True),
    )
    op.execute(
        """
        CREATE OR REPLACE FUNCTION immutable_array_to_string(text[], text)
        RETURNS text as $$ SELECT array_to_string($1, $2); $$
        LANGUAGE sql IMMUTABLE"""
    )
    op.execute(
        """
        ALTER TABLE elementsearchindex
        ADD COLUMN new_column tsvector GENERATED ALWAYS
        AS (to_tsvector('english', Column1
                        || ' ' || immutable_array_to_string(coalesce(Column2, '{}'), ' ')
                        || ' ' || immutable_array_to_string(coalesce(Column3, '{}'), ' ')
                        || ' ' || immutable_array_to_string(coalesce(Column4, '{}'), ' ')
                        || ' ' || immutable_array_to_string(coalesce(Column5, '{}'), ' ')
                        || ' ' || immutable_array_to_string(coalesce(Column6, '{}'), ' ')
                        )
                    ) STORED
        """
    )

class TSVector(TypeDecorator):
    impl = TSVECTOR

class ATable(Base):
    Column1 = Column(String, nullable=False, index=True)
    Column2 = Column(ARRAY(String, dimensions=1), nullable=True, index=True)
    Column3 = Column(ARRAY(String, dimensions=1), nullable=True, index=True)
    Column4 = Column(ARRAY(String, dimensions=1), nullable=True, index=True)
    Column5 = Column(ARRAY(String, dimensions=1), nullable=True, index=True)
    Column6 = Column(ARRAY(String, dimensions=1), nullable=True, index=True)

    new_column = Column(
        TSVector(),
        Computed(
            """to_tsvector('english', Column1
                        || ' ' || immutable_array_to_string(coalesce(Column2, '{}'), ' ')
                        || ' ' || immutable_array_to_string(coalesce(Column3, '{}'), ' ')
                        || ' ' || immutable_array_to_string(coalesce(Column4, '{}'), ' ')
                        || ' ' || immutable_array_to_string(coalesce(Column5, '{}'), ' ')
                        || ' ' || immutable_array_to_string(coalesce(Column6, '{}'), ' ')
                        )""",
            persisted=True,
        ),
        nullable=True,
        index=True,
    )

I do not have any problems during the upgrade or downgrade. Likewise, the system is working properly. But whenever I want to do any database update I see the following warning.

alembic/autogenerate/compare.py:1090: UserWarning: Computed default on a_table.new_field cannot be modified

I can see the need of throwing message when there is a change but no idea why comparison think there are some changes.

Versions.

• OS: Monterey 12.1
• Python: 3.10.6
• Alembic: 1.8.1
• SQLAlchemy: 1.4.35
• Database: Postgres 14

Thank you!

[CaselIT]

Hi,

It probably thinks that the sql of the computed column has changed, likely because postgresql returns the code formatted differently and the current comparison logic is not supporting this different formatting.

If in the migration instead of adding the column with op.execute( you add it defining it in the table, like you do in the file, do you have the same issue?

[zzzeek]

this occurs in compare which means it has to do with the metadata definition.

if you have additional information on how to reproduce this and can provide full examples of the models in question that are conflicting with the state of the database, feel free to reopen.

[asparagusbeef]

if you have additional information on how to reproduce this and can provide full examples of the models in question that are conflicting with the state of the database, feel free to reopen.

I am having this issue as well.

text_file = Table(
    'text_file', meta, 
    Column('id', Integer, primary_key=True),
    Column('text', LONGTEXT, nullable=False),
    Column('word_count', Integer, Computed(
        "CHAR_LENGTH(TRIM(REGEXP_REPLACE(text, '\\\\s+', ' '))) - "
        "CHAR_LENGTH(REPLACE(TRIM(REGEXP_REPLACE(text, '\\\\s+', ' ')), ' ', '')) + 1", 
        persisted=True
    ), nullable=False),
    Column('title', String(255), nullable=False),
    Column('created_at', DateTime, default=func.utc_timestamp(), nullable=False),
)

SHOW CREATE TABLE text_file; gives:

CREATE TABLE `text_file` (
  `id` int NOT NULL AUTO_INCREMENT,
  `text` longtext NOT NULL,
  `word_count` int GENERATED ALWAYS AS (((char_length(trim(regexp_replace(`text`,_utf8mb4'\\s+',_utf8mb4' '))) - char_length(replace(trim(regexp_replace(`text`,_utf8mb4'\\s+',_utf8mb4' ')),_utf8mb4' ',_utf8mb4''))) + 1)) STORED NOT NULL,
  `title` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

And I am getting

[2024-07-17 00:29:18] INFO | alembic.autogenerate.compare: compare._compare_nullable:891 | Detected NOT NULL on column 'text_file.word_count'
C:\Users\me\OneDrive\Documents\App\app\services\db\venv\Lib\site-packages\alembic\autogenerate\compare.py:1034: UserWarning: Computed default on text_file.word_count cannot be modified
  util.warn("Computed default on %s.%s cannot be modified" % (tname, came))

Should I open a new issue?

[CaselIT]

there's already an issue to change/scale back the compare that's used in postgresql, so that should help once done