sql: FK options (deferrable, etc)

[knz]

Is your feature request related to a problem? Please describe.

FK options are tuning knob that help developers decide when the FK relations become active. They are useful to enable fast import of known-consistent data.

Describe the solution you'd like

Support DEFERRABLE, NOT DEFERRABLE, INITIALLY DEFERRED, INITIALLY IMMEDIATE like postgresql.

Epic: CRDB-9559

Jira issue: CRDB-4783

[tim-o]

Zendesk ticket #3474 has been linked to this issue.

[awoods187]

This is used by the Hive metadata store

[jordanlewis]

Hi @RaduBerinde, I added this to the optimizer board, just letting you know. This feels like far future, but seems like we'd definitely need some optimizer support to defer the checks - or would it mostly be in execution? We could move the post queries to a special slot and execute only at the end of the txn.

[RaduBerinde]

It'd definitely require more work. We can't just defer the same check query we do today - some later statement might "undo" a mutation, and the check would fail incorrectly.

[martinrode]

Unfortunately, it is not on our short-to-medium term roadmap.

Without DEFERRED foreign keys I don't see a way to INSERT circularly referenced data without an extra update.

You can do all the mutations in a single statement using WITH:
WITH cte AS (INSERT INTO ab VALUES (1, 1) RETURNING a) INSERT INTO ab VALUES (2, 2);
The FKs will be checked after all mutations in the statement are performed.

I am not sure if this is going to work, as we have two tables:

• "table"
• "column"

"table" has a FK to "column" and "column" has an FK to table. Now all "table" rows and all "column" rows must be instertable in a single transaction. the FKs are pointing to IDs which are filled before we insert the data.

[RaduBerinde]

You can insert in both tables in a single statement using WITH.

[martinrode]

For syntax, can you maybe accept & ignore

ALTER TABLE "mapping_field_easydb"
  ADD FOREIGN KEY (objecttype_id) REFERENCES "objecttype" (id) ON DELETE RESTRICT
    DEFERRABLE INITIALLY IMMEDIATE

I believe you have INITIALLY DEFERRED but not INITIALLY IMMEDIATE

[knz]

@martinrode currently any form of the DEFERRABLE clause is rejected.

We are not keen to accept+ignore it silently, because the semantic "contract" is that:

• deferrable clauses can be deferred with the SET CONSTRAINT statement, which is not currently supported, and
• whether a constraint is deferrable or not must be stored in the schema, because once we start supporting SET CONSTRAINT in the future we will need to distinguish those created with DEFERRABLE from those created with (implicit) NON DEFERRABLE.

I would say it may be worth supporting DEFERRABLE INITIALLY IMMEDIATE without SET CONSTRAINT as a first step, but that step must include an annotation in the logical schema to remember the setting.

[RaduBerinde]

What is the difference between NOT DEFERRABLE and DEFERRABLE INITIALLY IMMEDIATE? (other than being able to SET CONSTRAINT)?

[knz]

@RaduBerinde there are a couple differences, described across several pg doc pages:

https://www.postgresql.org/docs/current/sql-createtable.html

"FOREIGN KEY": the referenced columns must be the columns of a non-deferrable unique or primary key constraint in the referenced table.

"DEFERRABLE" vs "NON DEFERRABLE": Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable. Note that deferrable constraints cannot be used as conflict arbitrators in an INSERT statement that includes an ON CONFLICT DO UPDATE clause.

https://www.postgresql.org/docs/current/sql-altertable.html

this option (DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER) excludes internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints

USING INDEX records the old values of the columns covered by the named index, which must be unique, not partial, not deferrable

https://www.postgresql.org/docs/current/sql-set-constraints.html

SET CONSTRAINTS with a list of constraint names changes the mode of just those constraints (which must all be deferrable).

When SET CONSTRAINTS changes the mode of a constraint from DEFERRED to IMMEDIATE, the new mode takes effect retroactively: any outstanding data modifications that would have been checked at the end of the transaction are instead checked during the execution of the SET CONSTRAINTS command.

Uniqueness and exclusion constraints that have not been declared DEFERRABLE are also checked immediately.

[martinrode]

What is the difference between NOT DEFERRABLE and DEFERRABLE INITIALLY IMMEDIATE? (other than being able to SET CONSTRAINT)?

No difference, if the RDBMS does not support deferrable keys. So it's simply a matter of accepting the syntax.

An FK can be deferrable and if it is, it is per default deferred (INITIALLY DEFERRED) or not (INITIALLY IMMEDIATE). Marking an FK as deferrable allows to use "SET CONSTRAINTS ALL DEFERRED" (if the RDBMS supports it).

[knz]

No difference, if the RDBMS does not support deferrable keys.

The situation is more complicated if the RDBMS may accept deferrable keys at a later stage. As explained above, in that case we cannot let future SET CONSTRAINTS statements affect tables created today without the DEFERRABLE clause.

Similarly, we will need to let future SET CONSTRAINTS statement accept to affect tables created today with the DEFERRABLE clause.

So we need to remember the clause even though SET CONSTRAINT is not supported yet.

[martinrode]

So we need to remember the clause even though SET CONSTRAINT is not supported yet.

My remark assumed that you might want to simply support the syntax to start with (and ignore it, like you do with INITIALLY DEFERRED)

[knz]

My remark assumed that you might want to simply support the syntax to start with (and ignore it, like you do with INITIALLY DEFERRED)

"INITIALLY DEFERRED" is not being ignored today. It generates an error with a link to this issue.

[palfrey]

This blocks Django support as that wants to do ALTER TABLE "auth_permission" ADD CONSTRAINT "auth_permission_content_type_id_2f476e4b_fk_django_co" FOREIGN KEY ("content_type_id") REFERENCES "django_content_type" ("id") DEFERRABLE INITIALLY DEFERRED

(At least with Postgres, although I've now spotted https://github.com/cockroachdb/django-cockroachdb)

[loonfly]

Hi awsome guys,

using django, as of today, still need this package?
or can use it with django "ENGINE": "django.db.backends.postgresql",?

(At least with Postgres, although I've now spotted https://github.com/cockroachdb/django-cockroachdb)

[abhi-kr-2100]

./manage.py migrate fails if using django-allauth:

Operations to perform:
  Apply all migrations: account, admin, auth, authtoken, contenttypes, leasikApp, sessions, socialaccount
Running migrations:
  Applying account.0001_initial...Traceback (most recent call last):
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.FeatureNotSupported: at or near "deferred": syntax error: unimplemented: this syntax
DETAIL:  source SQL:
ALTER TABLE "account_emailaddress" ADD CONSTRAINT "account_emailaddress_user_id_2c513194_fk_auth_user_id" FOREIGN KEY ("user_id") REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED
                                                                                                                                                                                     ^
HINT:  You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/31632/v21.2


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "./manage.py", line 22, in <module>
    main()
  File "./manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/core/management/__init__.py", line 446, in execute_from_command_line
    utility.execute()
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/core/management/__init__.py", line 440, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/core/management/base.py", line 414, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/core/management/base.py", line 460, in execute
    output = self.handle(*args, **options)
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/core/management/base.py", line 98, in wrapped
    res = handle_func(*args, **kwargs)
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 290, in handle
    post_migrate_state = executor.migrate(
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/db/migrations/executor.py", line 131, in migrate
    state = self._migrate_all_forwards(
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/db/migrations/executor.py", line 163, in _migrate_all_forwards
    state = self.apply_migration(
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/db/migrations/executor.py", line 251, in apply_migration
    migration_recorded = True
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 157, in __exit__
    self.execute(sql)
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 192, in execute
    cursor.execute(sql, params)
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/db/backends/utils.py", line 103, in execute
    return super().execute(sql, params)
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/abhishek/.work_on_home_virtualenvs/leasik-iCbC7nbs/lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.NotSupportedError: at or near "deferred": syntax error: unimplemented: this syntax
DETAIL:  source SQL:
ALTER TABLE "account_emailaddress" ADD CONSTRAINT "account_emailaddress_user_id_2c513194_fk_auth_user_id" FOREIGN KEY ("user_id") REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED
                                                                                                                                                                                     ^
HINT:  You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/31632/v21.2

[arturs706]

Running migrations:
Applying auth.0001_initial...Traceback (most recent call last):
File "/opt/homebrew/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute
return self.cursor.execute(sql, params)
psycopg2.errors.FeatureNotSupported: at or near "deferred": syntax error: unimplemented: this syntax
DETAIL: source SQL:
ALTER TABLE "auth_permission" ADD CONSTRAINT "auth_permission_content_type_id_2f476e4b_fk_django_co" FOREIGN KEY ("content_type_id") REFERENCES "django_content_type" ("id") DEFERRABLE INITIALLY DEFERRED
^
HINT: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/31632/v21.2

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/Users/arturs/Desktop/webdev/booking_service/manage.py", line 22, in
main()
File "/Users/arturs/Desktop/webdev/booking_service/manage.py", line 18, in main
execute_from_command_line(sys.argv)
File "/opt/homebrew/lib/python3.9/site-packages/django/core/management/init.py", line 446, in execute_from_command_line
utility.execute()
File "/opt/homebrew/lib/python3.9/site-packages/django/core/management/init.py", line 440, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/opt/homebrew/lib/python3.9/site-packages/django/core/management/base.py", line 414, in run_from_argv
self.execute(*args, **cmd_options)
File "/opt/homebrew/lib/python3.9/site-packages/django/core/management/base.py", line 460, in execute
output = self.handle(*args, **options)
File "/opt/homebrew/lib/python3.9/site-packages/django/core/management/base.py", line 98, in wrapped
res = handle_func(*args, **kwargs)
File "/opt/homebrew/lib/python3.9/site-packages/django/core/management/commands/migrate.py", line 290, in handle
post_migrate_state = executor.migrate(
File "/opt/homebrew/lib/python3.9/site-packages/django/db/migrations/executor.py", line 131, in migrate
state = self._migrate_all_forwards(
File "/opt/homebrew/lib/python3.9/site-packages/django/db/migrations/executor.py", line 163, in _migrate_all_forwards
state = self.apply_migration(
File "/opt/homebrew/lib/python3.9/site-packages/django/db/migrations/executor.py", line 251, in apply_migration
migration_recorded = True
File "/opt/homebrew/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 157, in exit
self.execute(sql)
File "/opt/homebrew/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 192, in execute
cursor.execute(sql, params)
File "/opt/homebrew/lib/python3.9/site-packages/django/db/backends/utils.py", line 103, in execute
return super().execute(sql, params)
File "/opt/homebrew/lib/python3.9/site-packages/django/db/backends/utils.py", line 67, in execute
return self._execute_with_wrappers(
File "/opt/homebrew/lib/python3.9/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/opt/homebrew/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute
return self.cursor.execute(sql, params)
File "/opt/homebrew/lib/python3.9/site-packages/django/db/utils.py", line 91, in exit
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/opt/homebrew/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute
return self.cursor.execute(sql, params)
django.db.utils.NotSupportedError: at or near "deferred": syntax error: unimplemented: this syntax
DETAIL: source SQL:
ALTER TABLE "auth_permission" ADD CONSTRAINT "auth_permission_content_type_id_2f476e4b_fk_django_co" FOREIGN KEY ("content_type_id") REFERENCES "django_content_type" ("id") DEFERRABLE INITIALLY DEFERRED
^
HINT: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/31632/v21.2

[arturs706]

I am getting such an error

[joewalk102]

I'm also getting the DEFERRABLE INITIALLY DEFERRED related error. Django 4.0.4 and django-cockroachdb 4.0.1. I followed the steps in the guide and ensured the engine being used was django_cockroachdb.

UPDATE: Stupid mistake, I set "engine" in Django DB settings instead of "ENGINE". Problem fixed.

[jonohill]

I've encountered this with Vaultwarden (Bitwarden server implementation), which attempts to SET CONSTRAINTS ALL DEFERRED before running migrations.

[luginbash]

I've ran into this when deploying dpaste

dpaste_1  | ALTER TABLE "dpaste_snippet" ADD CONSTRAINT "dpaste_snippet_parent_id_db3826b1_fk_dpaste_snippet_id" FOREIGN KEY ("parent_id") REFERENCES "dpaste_snippet" ("id") DEFERRABLE INITIALLY DEFERRED

[Paillat-dev]

Getting the same w doctrine migrations, I really hope this will be supported at some point