Issue adding an enum value and table reference, `sync_enum_values`
Closed this issue · 1 comments
brynnz22 commented
When adding a new enum value to a enum class called Product that is used in a column called type in a table called processedData, I get an error that relation "public.processeddata" does not exist, even though the table does exist.
I am wondering if this has to do with the fact that one of our enum values is also called processedData (like the table name).
Here is the migration code (it does other migrations):
Revision ID: ec8cca6e9182
Revises: 2f5f01ab75b9
Create Date: 2024-04-08 20:58:30.052426
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import UUID as pg_uuid
sa.UUID = pg_uuid
import analysisapi
from alembic_postgresql_enum import TableReference
# revision identifiers, used by Alembic.
revision = 'ec8cca6e9182'
down_revision = '2f5f01ab75b9'
branch_labels = None
depends_on = None
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.sync_enum_values('public', 'product', ['processedData', 'FTICRProduct', 'TomographyProduct', 'MicrobialBiomassProduct', 'NitrogenAnalysisProduct', 'PhosporousAnalysisProduct', 'pHProduct', 'ElementalAnalysisProduct', 'IonsAnalysisProduct', 'RespirationProduct', 'EnzymeProduct', 'TextureProduct', 'WEOMProduct', 'MAOMProduct', 'HydraulicPropertiesProduct', 'GWCMoistureProduct'],
[('processedData', 'type')],
enum_values_to_rename=[])
with op.batch_alter_table('processedData', schema=None) as batch_op:
batch_op.alter_column('flag',
existing_type=sa.VARCHAR(),
type_=sa.Enum('Below_Detection', 'High_Background', 'Out_of_Range', 'Null_Value', 'High_Value', 'Low_Value', name='processeddataflag'),
existing_nullable=True,
postgresql_using='flag::processeddataflag')
sa.Enum('Below_Detection', 'High_Background', 'Out_of_Range', 'Null_Value', 'High_Value', 'Low_Value', name='processeddataflag').create(op.get_bind())
op.create_table('MAOMProduct',
sa.Column('id', sa.UUID(), nullable=False),
sa.Column('proposal_id', sa.Numeric(), nullable=True),
sa.Column('sampling_set', sa.Numeric(), nullable=True),
sa.Column('core_section', sa.String(), nullable=True),
sa.Column('rep', sa.Numeric(), nullable=True),
sa.Column('sample_name', sa.String(), nullable=True),
sa.Column('maom_total_organic_carbon_id', sa.UUID(), nullable=True),
sa.Column('maom_total_nitrogen_id', sa.UUID(), nullable=True),
sa.ForeignKeyConstraint(['id'], ['processedData.id'], ),
sa.ForeignKeyConstraint(['maom_total_nitrogen_id'], ['quantityValue.id'], ),
sa.ForeignKeyConstraint(['maom_total_organic_carbon_id'], ['quantityValue.id'], ),
sa.PrimaryKeyConstraint('id')
)
with op.batch_alter_table('WEOMProduct', schema=None) as batch_op:
batch_op.add_column(sa.Column('proposal_id', sa.Numeric(), nullable=True))
batch_op.add_column(sa.Column('sampling_set', sa.Numeric(), nullable=True))
batch_op.add_column(sa.Column('sample_name', sa.String(), nullable=True))
batch_op.add_column(sa.Column('weom_total_organic_carbon_id', sa.UUID(), nullable=True))
batch_op.add_column(sa.Column('weom_total_nitrogen_id', sa.UUID(), nullable=True))
batch_op.alter_column('rep',
existing_type=sa.VARCHAR(),
type_=sa.Numeric(),
existing_nullable=True,
)
batch_op.create_foreign_key(None, 'quantityValue', ['weom_total_organic_carbon_id'], ['id'])
batch_op.create_foreign_key(None, 'quantityValue', ['weom_total_nitrogen_id'], ['id'])
batch_op.drop_column('unique_id')
batch_op.drop_column('tn_mg_per_g')
batch_op.drop_column('npoc_mg_per_')
batch_op.drop_column('npoc_mg_per_l')
batch_op.drop_column('tn_mg_per_l')
batch_op.drop_column('site')
batch_op.drop_column('core')
batch_op.drop_column('vol_l')
batch_op.drop_column('mass_g')
# ### end Alembic commands ###
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.sync_enum_values('public', 'product', ['processedData', 'FTICRProduct', 'TomographyProduct', 'MicrobialBiomassProduct', 'NitrogenAnalysisProduct', 'PhosporousAnalysisProduct', 'pHProduct', 'ElementalAnalysisProduct', 'IonsAnalysisProduct', 'RespirationProduct', 'EnzymeProduct', 'TextureProduct', 'WEOMProduct', 'HydraulicPropertiesProduct', 'GWCMoistureProduct'],
[('processedData', 'type')],
enum_values_to_rename=[])
with op.batch_alter_table('processedData', schema=None) as batch_op:
batch_op.alter_column('flag',
existing_type=sa.Enum('Below_Detection', 'High_Background', 'Out_of_Range', 'Null_Value', 'High_Value', 'Low_Value', name='processeddataflag'),
type_=sa.VARCHAR(),
existing_nullable=True)
with op.batch_alter_table('WEOMProduct', schema=None) as batch_op:
batch_op.add_column(sa.Column('mass_g', sa.VARCHAR(), autoincrement=False, nullable=True))
batch_op.add_column(sa.Column('vol_l', sa.VARCHAR(), autoincrement=False, nullable=True))
batch_op.add_column(sa.Column('core', sa.VARCHAR(), autoincrement=False, nullable=True))
batch_op.add_column(sa.Column('site', sa.VARCHAR(), autoincrement=False, nullable=True))
batch_op.add_column(sa.Column('tn_mg_per_l', sa.VARCHAR(), autoincrement=False, nullable=True))
batch_op.add_column(sa.Column('npoc_mg_per_l', sa.VARCHAR(), autoincrement=False, nullable=True))
batch_op.add_column(sa.Column('npoc_mg_per_', sa.VARCHAR(), autoincrement=False, nullable=True))
batch_op.add_column(sa.Column('tn_mg_per_g', sa.VARCHAR(), autoincrement=False, nullable=True))
batch_op.add_column(sa.Column('unique_id', sa.VARCHAR(), autoincrement=False, nullable=True))
batch_op.drop_constraint(None, type_='foreignkey')
batch_op.drop_constraint(None, type_='foreignkey')
batch_op.alter_column('rep',
existing_type=sa.Numeric(),
type_=sa.VARCHAR(),
existing_nullable=True)
batch_op.drop_column('weom_total_nitrogen_id')
batch_op.drop_column('weom_total_organic_carbon_id')
batch_op.drop_column('sample_name')
batch_op.drop_column('sampling_set')
batch_op.drop_column('proposal_id')
op.drop_table('MAOMProduct')
sa.Enum('Below_Detection', 'High_Background', 'Out_of_Range', 'Null_Value', 'High_Value', 'Low_Value', name='processeddataflag').drop(op.get_bind())
# ### end Alembic commands ###
Here is the error I am getting:
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
self.dialect.do_execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UndefinedTable: relation "public.processeddata" does not exist
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/local/bin/flask", line 8, in <module>
sys.exit(main())
File "/usr/local/lib/python3.10/site-packages/flask/cli.py", line 1063, in main
cli.main()
File "/usr/local/lib/python3.10/site-packages/click/core.py", line 1078, in main
rv = self.invoke(ctx)
File "/usr/local/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/usr/local/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/usr/local/lib/python3.10/site-packages/click/core.py", line 1434, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/usr/local/lib/python3.10/site-packages/click/core.py", line 783, in invoke
return __callback(*args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/click/decorators.py", line 33, in new_func
return f(get_current_context(), *args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/flask/cli.py", line 357, in decorator
return __ctx.invoke(f, *args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/click/core.py", line 783, in invoke
return __callback(*args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/flask_migrate/cli.py", line 150, in upgrade
_upgrade(directory, revision, sql, tag, x_arg)
File "/usr/local/lib/python3.10/site-packages/flask_migrate/__init__.py", line 111, in wrapped
f(*args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/flask_migrate/__init__.py", line 200, in upgrade
command.upgrade(config, revision, sql=sql, tag=tag)
File "/usr/local/lib/python3.10/site-packages/alembic/command.py", line 385, in upgrade
script.run_env()
File "/usr/local/lib/python3.10/site-packages/alembic/script/base.py", line 578, in run_env
util.load_python_file(self.dir, "env.py")
File "/usr/local/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file
module = load_module_py(module_id, path)
File "/usr/local/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 109, in load_module_py
spec.loader.exec_module(module) # type: ignore
File "<frozen importlib._bootstrap_external>", line 883, in exec_module
File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
File "/app/migrations/env.py", line 114, in <module>
run_migrations_online()
File "/app/migrations/env.py", line 108, in run_migrations_online
context.run_migrations()
File "<string>", line 8, in run_migrations
File "/usr/local/lib/python3.10/site-packages/alembic/runtime/environment.py", line 927, in run_migrations
self.get_context().run_migrations(**kw)
File "/usr/local/lib/python3.10/site-packages/alembic/runtime/migration.py", line 626, in run_migrations
step.migration_fn(**kw)
File "/app/migrations/versions/ec8cca6e9182_weom_maom_product.py", line 25, in upgrade
op.sync_enum_values('public', 'product', ['processedData', 'FTICRProduct', 'TomographyProduct', 'MicrobialBiomassProduct', 'NitrogenAnalysisProduct', 'PhosporousAnalysisProduct', 'pHProduct', 'ElementalAnalysisProduct', 'IonsAnalysisProduct', 'RespirationProduct', 'EnzymeProduct', 'TextureProduct', 'WEOMProduct', 'MAOMProduct', 'HydraulicPropertiesProduct', 'GWCMoistureProduct'],
File "<string>", line 8, in sync_enum_values
File "<string>", line 3, in sync_enum_values
File "/usr/local/lib/python3.10/site-packages/alembic_postgresql_enum/operations/sync_enum_values.py", line 147, in sync_enum_values
cls._set_enum_values(connection, schema, enum_name, new_values, table_references, enum_values_to_rename)
File "/usr/local/lib/python3.10/site-packages/alembic_postgresql_enum/operations/sync_enum_values.py", line 76, in _set_enum_values
cast_old_enum_type_to_new(connection,
File "/usr/local/lib/python3.10/site-packages/alembic_postgresql_enum/sql_commands/enum_type.py", line 58, in cast_old_enum_type_to_new
connection.execute(sqlalchemy.text(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1412, in execute
return meth(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 483, in _execute_on_connection
return connection._execute_clauseelement(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1635, in _execute_clauseelement
ret = self._execute_context(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1844, in _execute_context
return self._exec_single_context(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1984, in _exec_single_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
self.dialect.do_execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "public.processeddata" does not exist
[SQL: ALTER TABLE public.processedData ALTER COLUMN type TYPE public.product
USING type::text::public.product```
RustyGuard commented
Root of this problem is that All identifiers (including column names) that are not double-quoted are converted to lower case in PostgreSQL. Your table is called processedData and traceback says that public.processeddata does not exists.
I will add quotes to sql command to fix this