Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[YSQL] Support DEFERRABLE constraints #4700

Open
m-iancu opened this issue Jun 7, 2020 · 1 comment
Open

[YSQL] Support DEFERRABLE constraints #4700

m-iancu opened this issue Jun 7, 2020 · 1 comment
Assignees
Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue roadmap-tracking-issue This issue tracks a major roadmap item, and usually appears in the roadmap list.
Milestone

Comments

@m-iancu
Copy link
Contributor

m-iancu commented Jun 7, 2020

Jira Link: DB-2213
This is a master issue that keeps track of all deferrable constraints variants.
The check mark means that the statement is already supported.

Feature Status Tracking Issue Comments
Foreign Key DEFERRABLE INITIALLY IMMEDIATE #3995
Foreign Key DEFERRRABLE INITIALLY DEFERRED #3995
Unique/Primary key DEFERRABLE INITIALLY IMMEDIATE ⬜️ #1709
Unique/Primary key DEFERRABLE INITIALLY DEFERRED ⬜️ #1709
Constraint triggers ⬜️ Constraint triggers not yet supported.
Exclusion constraint ⬜️ Exclusion constraints not yet supported.

Dependent ecosystem projects:

Project Status Issues
Django ⬜️ #4054

Analytics

@m-iancu m-iancu added the area/ysql Yugabyte SQL (YSQL) label Jun 7, 2020
@m-iancu m-iancu added this to the Future milestone Jun 7, 2020
@rkarthik007 rkarthik007 added the roadmap-tracking-issue This issue tracks a major roadmap item, and usually appears in the roadmap list. label Jul 24, 2020
@chirag-yb chirag-yb modified the milestones: Future, v2.2.x Aug 11, 2020
@m-iancu m-iancu added this to YQL-beta Nov 30, 2021
@yugabyte-ci yugabyte-ci added kind/bug This issue is a bug priority/medium Medium priority issue labels Jun 9, 2022
@bllewell
Copy link
Contributor

The graphic above claims that deferred FK constraints work. Issue #13009 shows that they don't work in one of the examples that text books and bloggers frequently use to illustrate why deferred FK constraints are essential:

the optional (or mandatory) one-to-one relationship

Moreover, especially for the case that the PKs of the tables are automatically generated, proper practice recommends a stored procedure encapsulation of the three SQLs (insert returning, insert returning, and update using the returned values) that must be done in a single transaction.

As of this comment's timestamp, example code for this use case works faultlessly in vanilla PG. But it fails in YB:

  • In the pure SQL implementation for the mandatory 1:1 case, you get a nonsensical error message that fails the txn.
  • In the PG/pgSQL implementation for both the optional and the mandatory 1:1 case, you get a crash and the connection to the server is lost.

@yugabyte-ci yugabyte-ci added kind/enhancement This is an enhancement of an existing feature and removed kind/bug This issue is a bug labels Sep 14, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue roadmap-tracking-issue This issue tracks a major roadmap item, and usually appears in the roadmap list.
Projects
Status: No status
Development

No branches or pull requests

6 participants