Constraint not found when adding on_delete
Closed this issue · 18 comments
Hello,
I have a Postgres database created with peewee and peewee migrations (actually two of them, one staging and one production).
I recently added a new migration, I needed to add on_delete=CASCADE to some many to many and one to many relations.
Now, that worked super fine on staging, but then on production, when running pem migrate, it complains that it cannot find the foreign key.
This is the output of the pem show command
SQL> ALTER TABLE "group_entity_through" DROP CONSTRAINT "<FOREIGN KEY>" []
SQL> DROP INDEX "groupentitythrough_entity_id" []
SQL> ALTER TABLE "group_entity_through" DROP CONSTRAINT "<FOREIGN KEY>" []
SQL> CREATE INDEX "groupentitythrough_group_id" ON "group_entity_through" ("group_id") []
SQL> ALTER TABLE "group_entity_through" ADD FOREIGN KEY ("group_id") REFERENCES "group" ("id") ON DELETE CASCADE []
SQL> CREATE INDEX "groupentitythrough_entity_id" ON "group_entity_through" ("entity_id") []
SQL> ALTER TABLE "group_entity_through" ADD FOREIGN KEY ("entity_id") REFERENCES "entity" ("entity_id") ON DELETE CASCADE []
PY> set_done('0012_migration_202201181039')
and this is the trace after executing the migration
Traceback (most recent call last):
File "/home/user/project/python3.7/site-packages/peewee.py", line 3099, in execute_sql
cursor.execute(sql, params or ())
psycopg2.errors.UndefinedObject: constraint "<FOREIGN KEY>" of relation "group_entity_through" does not exist
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/home/user/project/python3.7/site-packages/peewee_migrations/migrator.py", line 759, in run
op.run()
File "/home/user/project/python3.7/site-packages/peewee_migrations/migrator.py", line 1114, in run
self.database.execute(self.obj, scope=peewee.SCOPE_VALUES)
File "/home/user/project/python3.7/site-packages/peewee.py", line 3112, in execute
return self.execute_sql(sql, params, commit=commit)
File "/home/user/project/python3.7/site-packages/peewee.py", line 3106, in execute_sql
self.commit()
File "/home/user/project/python3.7/site-packages/peewee.py", line 2873, in __exit__
reraise(new_type, new_type(exc_value, *exc_args), traceback)
File "/home/user/project/python3.7/site-packages/peewee.py", line 183, in reraise
raise value.with_traceback(tb)
File "/home/user/project/python3.7/site-packages/peewee.py", line 3099, in execute_sql
cursor.execute(sql, params or ())
peewee.ProgrammingError: constraint "<FOREIGN KEY>" of relation "group_entity_through" does not exist
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/home/user/project/python3.7/site-packages/peewee.py", line 3099, in execute_sql
cursor.execute(sql, params or ())
psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "//home/user/project/venv/bin/pem", line 8, in <module>
sys.exit(run())
File "/home/user/project/python3.7/site-packages/peewee_migrations/cli.py", line 347, in run
cli()
File "/home/user/project/python3.7/site-packages/click/core.py", line 1128, in __call__
return self.main(*args, **kwargs)
File "/home/user/project/python3.7/site-packages/click/core.py", line 1053, in main
rv = self.invoke(ctx)
File "/home/user/project/python3.7/site-packages/click/core.py", line 1659, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/home/user/project/python3.7/site-packages/click/core.py", line 1395, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/home/user/project/python3.7/site-packages/click/core.py", line 754, in invoke
return __callback(*args, **kwargs)
File "/home/user/project/python3.7/site-packages/click/decorators.py", line 26, in new_func
return f(get_current_context(), *args, **kwargs)
File "/home/user/project/python3.7/site-packages/peewee_migrations/cli.py", line 339, in migrate
step.run(fake=fake)
File "/home/user/project/python3.7/site-packages/peewee_migrations/migrator.py", line 761, in run
raise Exception(str(e), op.description)
File "/home/user/project/python3.7/site-packages/peewee_migrations/migrator.py", line 1121, in description
obj = ctx.sql(obj)
File "/home/user/project/python3.7/site-packages/peewee.py", line 606, in sql
return obj.__sql__(self)
File "/home/user/project/python3.7/site-packages/peewee_migrations/migrator.py", line 1317, in __sql__
value = value()
File "/home/user/project/python3.7/site-packages/peewee_migrations/migrator.py", line 1393, in get_name
cursor = self.database.execute_sql(sql, params)
File "/home/user/project/python3.7/site-packages/peewee.py", line 3106, in execute_sql
self.commit()
File "/home/user/project/python3.7/site-packages/peewee.py", line 2873, in __exit__
reraise(new_type, new_type(exc_value, *exc_args), traceback)
File "/home/user/project/python3.7/site-packages/peewee.py", line 183, in reraise
raise value.with_traceback(tb)
File "/home/user/project/python3.7/site-packages/peewee.py", line 3099, in execute_sql
cursor.execute(sql, params or ())
peewee.InternalError: current transaction is aborted, commands ignored until end of transaction block
Thank you in advance
Hi! Need the code to reproduce.
Well, first of all thanks for the blazing fast reply. Here is the piece of code for that
import logging
import datetime
from peewee import (Model, CharField, BooleanField,
DateTimeField, TimestampField,
DoubleField, IntegerField,
ForeignKeyField, ManyToManyField)
from common.database import get_db
class BaseModel(Model):
created = DateTimeField(default=datetime.datetime.now)
modified = DateTimeField()
def save(self, *args, **kwargs):
self.modified = datetime.datetime.now()
return super(BaseModel, self).save(*args, **kwargs)
class Meta:
database = get_db()
class OwnedBaseModel(BaseModel):
user_id = CharField(null=False, index=True, max_length=36)
email = CharField(null=True, default='')
class Entity(OwnedBaseModel):
entity_id = CharField(null=False, primary_key=True)
name = CharField(null=False)
class Group(OwnedBaseModel):
name = CharField(null=False)
endpoint_id = CharField(unique=True, max_length=36, null=True)
### on_delete was added here!
entities = ManyToManyField(Entity, backref='groups', on_delete='CASCADE')
class Meta:
indexes = (
(('name', 'user_id', ), True),
)Also, I'd like to stress once again that this worked super smooth on staging, but not on prod. I did a sql dump schema only of both, and everything seems ok
It's work for me.
pem show:
[ ] 0002_migration_202201191856:
[*] SQL> ALTER TABLE "group_entity_through" DROP CONSTRAINT "group_entity_through_group_id_fkey" []
[*] SQL> DROP INDEX "groupentitythrough_group_id" []
[*] SQL> ALTER TABLE "group_entity_through" DROP CONSTRAINT "group_entity_through_entity_id_fkey" []
[*] SQL> DROP INDEX "groupentitythrough_entity_id" []
[*] SQL> CREATE INDEX "groupentitythrough_group_id" ON "group_entity_through" ("group_id") []
[*] SQL> ALTER TABLE "group_entity_through" ADD FOREIGN KEY ("group_id") REFERENCES "group" ("id") ON DELETE CASCADE []
[*] SQL> CREATE INDEX "groupentitythrough_entity_id" ON "group_entity_through" ("entity_id") []
[*] SQL> ALTER TABLE "group_entity_through" ADD FOREIGN KEY ("entity_id") REFERENCES "entity" ("entity_id") ON DELETE CASCADE []
[*] PY> set_done('0002_migration_202201191856')It looks like there is a divergent between migrations and bases in production and stage.
Receiving <FOREIGN KEY> for the closest next migration in watch means that FK does not exist (was not created, was created with different name, or was renamed)
Yeah no I think I've found the issue, on prod we are using a different schema than public.
This is weird tho, never have I had to specify it before.
Is there a way to set it in the connecting config?
For psycopg2 is options keyword.
conn = psycopg2.connect(..., options=f'-c search_path={schema}') Should be the same for peewee database instance.
I don't know if it willl work. But to my mind it's a problem if you have different schemas in stage and production.
Migrator should know the schema name to find FK.
Yes that's exactly what I meant in my last comment, different schemas :) I have now the problem that the schema is embedded into the migration file as a parameter of the meta class, but I will find a workaround.
Will post an update when done but that should do the trick.
Thanks!
Just a final note, migrations so far run without this being an issue
Setting the schema and re-creating the scenario now give the expected migration file Metas
class Meta:
table_name = "device_entity_through"
schema = "mycustomeschema"But when I run it with pem show, the params in here
def _drop_foreign_key_constraint(self, model, field):
params = (model._meta.table_name,
model._meta.schema or 'public',
field.rel_model._meta.table_name,
field.rel_model._meta.schema or 'public',
field.column_name,
field.rel_field.column_name)
def get_name():
sql = (
"SELECT tc.constraint_name "
"FROM information_schema.table_constraints AS tc "
"JOIN information_schema.key_column_usage AS kcu "
"ON (tc.constraint_name = kcu.constraint_name AND "
"tc.constraint_schema = kcu.constraint_schema) "
"JOIN information_schema.constraint_column_usage AS ccu "
"ON (ccu.constraint_name = tc.constraint_name AND "
"ccu.constraint_schema = tc.constraint_schema) "
"WHERE "
"tc.constraint_type = 'FOREIGN KEY' AND "
"tc.table_name = %s AND "
"tc.table_schema = %s AND "
"ccu.table_name = %s AND "
"ccu.table_schema = %s AND "
"kcu.column_name = %s AND "
"ccu.column_name = %s"
)
import pudb
pudb.set_trace()
cursor = self.database.execute_sql(sql, params)
result = cursor.fetchall()
return peewee.Entity(result[0][0] if result else '<FOREIGN KEY>')
return (LazyQuery().literal('ALTER TABLE ')
.sql(model._meta.table)
.literal(' DROP CONSTRAINT ')
.sql(get_name))
And yeah, I know it's a super old version :) will do an upgrade soon
Just added --explicit_schema option. Try it from repo.
pem show --explicit_schema myschema
pem migrate --explicit_schema myschema
If it works I will make release to pypi.
Any way, looks like it works. Made a release.
So, if you have another default schema (search_path) in connection string you should set explicit_schema argument for show and migrate commands. In this case Migrator will know where to look for indexes.
Wow, that was fast! I can't test untill tomorrow unfortunately but I'm fairly sure that will work.
Real MVP!! Thank you so much mate, will post an update tomorrow :)
And another great option. Auto-detect default schema from search_path.
If you don't set explicit_schema, SHOW search_path will be executed, and selected as a default schema ($user schema is not supported any way and will be ignored, but it's not your case)
Hey, I've been testing around, I am not sure whether you should set the explicit schema also on the rel_schema here https://github.com/aachurin/peewee_migrations/blob/master/peewee_migrations/migrator.py#L1593
I have tried locally and made it possible to identify the correct schema, nevertheless, I have the feeling that this is just over complicating the matter for everybody - my setup is particular with the different default schema.
In my opinion this should be handled by the connection manager, not the migration (but I also might be wrong). I will maybe submit a PR or fork if I have time to dig more and think of a more elegant solution.
Hey, I've been testing around, I am not sure whether you should set the explicit schema also on the rel_schema here https://github.com/aachurin/peewee_migrations/blob/master/peewee_migrations/migrator.py#L1593
Fixed!
I have tried locally and made it possible to identify the correct schema, nevertheless, I have the feeling that this is just over complicating the matter for everybody - my setup is particular with the different default schema.
In my opinion this should be handled by the connection manager, not the migration (but I also might be wrong). I will maybe submit a PR or fork if I have time to dig more and think of a more elegant solution.
Already done it here:
peewee_migrations/peewee_migrations/migrator.py
Line 1468 in 3ffe9e6
Yeah saw that about the default schema, problem is,in the next few lines you pop "$user" but it should actually be replace with the current user. Because that's exactly my case, and probably will be others too.
Example: my user for connection is nick, then the schema is also nick (replacement for that user var over there).
But anyway, using the explicit schema did the trick for now :) again, thank you!
Try it now:
peewee_migrations/peewee_migrations/migrator.py
Line 1468 in f426e25
Yeah that's a good approach and works for me too! (Although I did fix it using the explicit schema for now, as I said).
Thank you!