Hooks before/after function creation about alembic_utils HOT 7 CLOSED

Given that check constraints may only reference the current row, the limit on complexity is pretty low. You can avoid the problem by defining the logic directly in the check constraint rather than in a function.

for example

-- Example table with check constraint
create table foo(
	id serial primary key,
	-- Ensure that bar has value > 5
	bar int constraint ck check (bar > 5)
);

-- Add a row that satisfies the constraint
insert into foo(bar) values (6);

-- Remove existing constraint
alter table foo drop constraint ck;

-- Add new constraint that is violated by existing row
alter table foo add constraint ck check (bar > 10);

/*
Result: ERROR:  check constraint "ck" is violated by some row
*/

But, if you're sure you want to use a function

-- Set up your function
create function gte_thresh(val int) returns bool
as $$ select val > 5 $$ language sql;

-- Example table using your function
create table foo(
	id serial primary key,
	bar int constraint ck check (gte_thresh(bar))
);

-- Add a row that satisfies the constraint
insert into foo(bar) values (6);

-- Update check constraint function so that an existing record fails
create or replace function gte_thresh(val int) returns bool
as $$ select val > 10 $$ language sql;
-- There is now an existing row that violates the check constraint

You can then drop and recreate the check constraint to get the behavior you want by adding this to the bottom of your migration

-- Remove existing constraint
alter table foo drop constraint ck;
-- Add new constraint that is violated by existing row
alter table foo add constraint ck check (gte_thresh(bar));

/*
Result: ERROR:  check constraint "ck" is violated by some row
*/

from alembic_utils.

Ahh, I didn‘t make my point clear enough...
The point are autogenerated migrations.
My target is:

• I want to change the constraint
• So I change the function
• I run alembic revision --autogenerate
• A migration is generated, where the function is updated, the check constraint dropped and recreated

And now I‘m looking for an easy way to register the check constraint with the function.
And before digging deeper into your code to see what happens, I thought I ask if you see a quick and easy way.

All because check constraints are not included in auto generation at the moment.

Thanks,
Adrian

from alembic_utils.

Check constraints do work with autogenerate in alembic core if you provide a name for them.

Why adding names is necessary is outlined here:
https://alembic.sqlalchemy.org/en/latest/naming.html

That would be the only way to get it working in a way you'll be happy with right now

Example:

class Foo(Base):

    __tablename__ = 'foo'
    __table_args__ = (
        CheckConstraint("bar > 10", name="ck_foo_bar"),
    )

    id = Column(Integer, primary_key=True)
    bar = Column(Integer)

Longer term I plan to use pg_depend to optionally identify, drop and recreate dependent objects during migrations with an api similar to

def upgrade():
    my_view = PGView(...)
   
    with defer_dependent_objects(my_view):
        op.replace_entity(my_view)

To make migrating views with dependencies easier. The dependency resolver required for that functionality would likely also work for your use case.

I'll let you know if that materializes

from alembic_utils.

No, it isn‘t as far as I‘m aware, that‘s the point. Or did I misunderstand something terribly wrong? I would be happy if that was the case 😄

sqlalchemy/alembic#508

So I guess I have to take the long way...

from alembic_utils.

Interesting link

I was wrong that updates to check constraints are detected. That make sense given that they're written as strings

But that wouldn't solve your problem if the logic is hidden behind a function because the check constraint wouldn't change when the function changes and alembic doesn't resolve dependencies.

Or was your "put it behind a function" approach a workaround to changes in check constraints not being detected?

from alembic_utils.

Yes exactly. This was the idea. Put the logic into a function and add hooks that drop and recreate constraints that depend on the function.
At the moment I do that manually.

from alembic_utils.

Closing as out-of-scope

from alembic_utils.