Alembic 1.8 + DEFERRABLE INITIALLY IMMEDIATE

Question

I am now using Alembic 1.8, SQLAlchemy 1.4 and PostgreSQL

I would like to set my FK constraints as "DEFERRABLE INITIALLY IMMEDIATE".

I passed the FK options as shown below:

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

It generates my "create table" SQL like this:

    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 "DEFERRABLE INITIALLY IMMEDIATE" instead of "DEFERRABLE".

Please, let me know how to make the constraint as "DEFERRABLE INITIALLY IMMEDIATE".

Thank you.

Answer 1

This is an issue (or design choice) of your DDL -> text generator -not with sqlalchemy.

The actual constraint information Postgres uses is stored in the table pg_catalog.pg_constraint. If you take a look at the docs for the pg_constraint table, you'll notice that the concept of deferability is (v7.2-v15+) controlled entirely by the two boolean columns condeferrable and condeferred.

Thus, if a constraint is DEFERRABLE and it's not INITIALLY DEFERRED (checked end of transaction), it can only be INITIALLY IMMEDIATE (checked end of statement).

If you want to be absolutely certain, you can run this simple query:

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'