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.

`INITIALLY IMMEDIATE` is the default for `DEFERRABLE` as documented [here](https://www.postgresql.org/docs/current/sql-createtable.html). If you use `initially="DEFERRED"` does it render `DEFERRABLE INITIALLY DEFERRED`? — Gord Thompson, Jun 14 '22 at 19:21
@GordThompson, Yes! You're correct. **Nothing shown**: implicitly means NOT DEFERRABLE **DEFERRABLE**: DEFERRABLE INITIALLY IMMEDIATE **DEFERRABLE INITIALLY DEFERRED** is only explicitly explained. Thank you for your comment! — pincoin, Jun 15 '22 at 02:14

score 3 · Accepted Answer · answered Jun 14 '22 at 21:15

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'

Alembic(SQLAlchemy) `deferrable=True, initially="IMMEDIATE",` CREATE TABLE generated by Alembic `DEFERRABLE` The result from your query in pgAdmin4. `"auth_user_groups_group_id_fkey" "auth_group" "DEFERRABLE INITIALLY IMMEDIATE"` It was a kind of notation issue. Thank you for your query. It's quite helpful to understand the issues. — pincoin, Jun 15 '22 at 02:12

Alembic 1.8 + DEFERRABLE INITIALLY IMMEDIATE

1 Answers1