Alembic 1.8 + DEFERRABLE INITIALLY IMMEDIATE

Question

我现在使用Alembic 1.8, SQLAlchemy 1.4和PostgreSQL

我想把我的FK约束设置为"deferred INITIALLY IMMEDIATE">

我传递了FK选项，如下所示:

sa.Column(
"group_id",
sa.BigInteger,
sa.ForeignKey(
"auth_group.id",
onupdate="CASCADE",
ondelete="CASCADE",
deferrable=True,
initially="IMMEDIATE"
),
index=True,
),

生成我的"create table"如下所示:

CONSTRAINT auth_user_groups_group_id_fkey FOREIGN KEY (group_id)
REFERENCES public.auth_group (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
DEFERRABLE

I expected " deferred INITIALLY IMMEDIATE"而不是"DEFERRABLE"

请告诉我如何将约束设置为"deferred INITIALLY IMMEDIATE">

谢谢。

Answer 1

这是您的DDL的问题(或设计选择)->文本生成器-不使用sqlalchemy。

Postgres实际使用的约束信息存储在表pg_catalog.pg_constraint中。如果您看一下pg_constraint表的文档，您会注意到可传递性的概念(v7.2-v15+)完全由两个布尔列condeferrable和condeferred控制。

因此，如果约束是DEFERRABLE而不是INITIALLY DEFERRED(已检查的事务结束)，则只能是INITIALLY IMMEDIATE(已检查的语句结束)。

如果你想要绝对确定，你可以运行这个简单的查询:

SELECT
pgc.conname constraint_name,
pgc.confrelid::regclass tbl_name,
CASE
WHEN pgc.condeferrable
THEN
CASE
WHEN pgc.condeferred
THEN 'DEFERRABLE INITIALLY DEFERRED'
ELSE 'DEFERRABLE INITIALLY IMMEDIATE'
END
ELSE 'NOT DEFERRABLE'
END deferrability
FROM
pg_catalog.pg_constraint pgc
WHERE
conname = 'auth_user_groups_group_id_fkey'