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.

Sign up for GitHub

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

Jump to bottom

sql: FK options (deferrable, etc) #31632

Open
knz opened this issue Oct 19, 2018 · 27 comments
Open

sql: FK options (deferrable, etc) #31632

knz opened this issue Oct 19, 2018 · 27 comments
Labels
A-schema-changes A-sql-pgcompat Semantic compatibility with PostgreSQL A-sql-syntax Issues strictly related to the SQL grammar, with no semantic aspect C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) O-community Originated from the community T-sql-queries SQL Queries Team X-anchored-telemetry The issue number is anchored by telemetry references.

Comments

@knz
Copy link
Contributor

knz commented Oct 19, 2018 •
edited by cockroach-jira-scripts
Loading

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
@knz knz added A-sql-pgcompat Semantic compatibility with PostgreSQL C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) labels Oct 19, 2018
@knz knz added X-anchored-telemetry The issue number is anchored by telemetry references. A-schema-changes O-community Originated from the community labels Nov 22, 2018
@knz knz mentioned this issue Nov 22, 2018
Closed
knz added a commit to knz/cockroach that referenced this issue Nov 22, 2018
@knz
sql: make ALTER CONSTRAINT reference cockroachdb#31632
bd1e425 
Release note: None
knz added a commit to knz/cockroach that referenced this issue Nov 26, 2018
@knz
sql: make ALTER CONSTRAINT reference cockroachdb#31632
a104195 
Release note: None
knz added a commit to knz/cockroach that referenced this issue Nov 28, 2018
@knz
sql: make ALTER CONSTRAINT reference cockroachdb#31632
6a19f86 
Release note: None
@tim-o
Copy link
Contributor

tim-o commented Jul 8, 2019

Zendesk ticket #3474 has been linked to this issue.

jordanlewis added a commit to jordanlewis/cockroach that referenced this issue Oct 2, 2019
@jordanlewis
roachtest: add test that aggregates orm blacklist failures
cebc811 
The spreadsheet we discussed is unwieldy - hard to edit and impossible to keep
up to date. If we write down blacklists in code, then we can use an approach
like this to always have an up to date aggregation.

So far it seems like there's just a lot of unknowns to categorize still.

The output today:

```
=== RUN   TestBlacklists
 648: unknown                                                (unknown)
 493: cockroachdb#5807   (sql: Add support for TEMP tables)
 151: cockroachdb#17511  (sql: support stored procedures)
  86: cockroachdb#26097  (sql: make TIMETZ more pg-compatible)
  56: cockroachdb#10735  (sql: support SQL savepoints)
  55: cockroachdb#32552  (multi-dim arrays)
  55: cockroachdb#26508  (sql: restricted DDL / DML inside transactions)
  52: cockroachdb#32565  (sql: support optional TIME precision)
  39: cockroachdb#243    (roadmap: Blob storage)
  33: cockroachdb#26725  (sql: support postgres' API to handle blob storage (incl lo_creat, lo_from_bytea))
  31: cockroachdb#27793  (sql: support custom/user-defined base scalar (primitive) types)
  24: cockroachdb#12123  (sql: Can't drop and replace a table within a transaction)
  24: cockroachdb#26443  (sql: support user-defined schemas between database and table)
  20: cockroachdb#21286  (sql: Add support for geometric types)
  18: cockroachdb#6583   (sql: explicit lock syntax (SELECT FOR {SHARE,UPDATE} {skip locked,nowait}))
  17: cockroachdb#22329  (Support XA distributed transactions in CockroachDB)
  16: cockroachdb#24062  (sql: 32 bit SERIAL type)
  16: cockroachdb#30352  (roadmap:when CockroachDB  will support cursor?)
  12: cockroachdb#27791  (sql: support RANGE types)
   8: cockroachdb#40195  (pgwire: multiple active result sets (portals) not supported)
   8: cockroachdb#6130   (sql: add support for key watches with notifications of changes)
   5: Expected Failure                                       (unknown)
   5: cockroachdb#23468  (sql: support sql arrays of JSONB)
   5: cockroachdb#40854  (sql: set application_name from connection string)
   4: cockroachdb#35879  (sql: `default_transaction_read_only` should also accept 'on' and 'off')
   4: cockroachdb#32610  (sql: can't insert self reference)
   4: cockroachdb#40205  (sql: add non-trivial implementations of FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR NO KEY SHARE)
   4: cockroachdb#35897  (sql: unknown function: pg_terminate_backend())
   4: cockroachdb#4035   (sql/pgwire: missing support for row count limits in pgwire)
   3: cockroachdb#27796  (sql: support user-defined DOMAIN types)
   3: cockroachdb#3781   (sql: Add Data Type Formatting Functions)
   3: cockroachdb#40476  (sql: support `FOR {UPDATE,SHARE} {SKIP LOCKED,NOWAIT}`)
   3: cockroachdb#35882  (sql: support other character sets)
   2: cockroachdb#10028  (sql: Support view queries with star expansions)
   2: cockroachdb#35807  (sql: INTERVAL output doesn't match PG)
   2: cockroachdb#35902  (sql: large object support)
   2: cockroachdb#40474  (sql: support `SELECT ... FOR UPDATE OF` syntax)
   1: cockroachdb#18846  (sql: Support CIDR column type)
   1: cockroachdb#9682   (sql: implement computed indexes)
   1: cockroachdb#31632  (sql: FK options (deferrable, etc))
   1: cockroachdb#24897  (sql: CREATE OR REPLACE VIEW)
   1: pass?                                                  (unknown)
   1: cockroachdb#36215  (sql: enable setting standard_conforming_strings to off)
   1: cockroachdb#32562  (sql: support SET LOCAL and txn-scoped session variable changes)
   1: cockroachdb#36116  (sql: psychopg: investigate how `'infinity'::timestamp` is presented)
   1: cockroachdb#26732  (sql: support the binary operator: <int> / <float>)
   1: cockroachdb#23299  (sql: support coercing string literals to arrays)
   1: cockroachdb#36115  (sql: psychopg: investigate if datetimetz is being returned instead of datetime)
   1: cockroachdb#26925  (sql: make the CockroachDB integer types more compatible with postgres)
   1: cockroachdb#21085  (sql: WITH RECURSIVE (recursive common table expressions))
   1: cockroachdb#36179  (sql: implicity convert date to timestamp)
   1: cockroachdb#36118  (sql: Cannot parse '24:00' as type time)
   1: cockroachdb#31708  (sql: support current_time)
```

Release justification: non-production change
Release note: None
jordanlewis added a commit to jordanlewis/cockroach that referenced this issue Oct 24, 2019
@jordanlewis
roachtest: add test that aggregates orm blacklist failures
4010eac 
The spreadsheet we discussed is unwieldy - hard to edit and impossible to keep
up to date. If we write down blacklists in code, then we can use an approach
like this to always have an up to date aggregation.

So far it seems like there's just a lot of unknowns to categorize still.

The output today:

```
=== RUN   TestBlacklists
 648: unknown                                                (unknown)
 493: cockroachdb#5807   (sql: Add support for TEMP tables)
 151: cockroachdb#17511  (sql: support stored procedures)
  86: cockroachdb#26097  (sql: make TIMETZ more pg-compatible)
  56: cockroachdb#10735  (sql: support SQL savepoints)
  55: cockroachdb#32552  (multi-dim arrays)
  55: cockroachdb#26508  (sql: restricted DDL / DML inside transactions)
  52: cockroachdb#32565  (sql: support optional TIME precision)
  39: cockroachdb#243    (roadmap: Blob storage)
  33: cockroachdb#26725  (sql: support postgres' API to handle blob storage (incl lo_creat, lo_from_bytea))
  31: cockroachdb#27793  (sql: support custom/user-defined base scalar (primitive) types)
  24: cockroachdb#12123  (sql: Can't drop and replace a table within a transaction)
  24: cockroachdb#26443  (sql: support user-defined schemas between database and table)
  20: cockroachdb#21286  (sql: Add support for geometric types)
  18: cockroachdb#6583   (sql: explicit lock syntax (SELECT FOR {SHARE,UPDATE} {skip locked,nowait}))
  17: cockroachdb#22329  (Support XA distributed transactions in CockroachDB)
  16: cockroachdb#24062  (sql: 32 bit SERIAL type)
  16: cockroachdb#30352  (roadmap:when CockroachDB  will support cursor?)
  12: cockroachdb#27791  (sql: support RANGE types)
   8: cockroachdb#40195  (pgwire: multiple active result sets (portals) not supported)
   8: cockroachdb#6130   (sql: add support for key watches with notifications of changes)
   5: Expected Failure                                       (unknown)
   5: cockroachdb#23468  (sql: support sql arrays of JSONB)
   5: cockroachdb#40854  (sql: set application_name from connection string)
   4: cockroachdb#35879  (sql: `default_transaction_read_only` should also accept 'on' and 'off')
   4: cockroachdb#32610  (sql: can't insert self reference)
   4: cockroachdb#40205  (sql: add non-trivial implementations of FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR NO KEY SHARE)
   4: cockroachdb#35897  (sql: unknown function: pg_terminate_backend())
   4: cockroachdb#4035   (sql/pgwire: missing support for row count limits in pgwire)
   3: cockroachdb#27796  (sql: support user-defined DOMAIN types)
   3: cockroachdb#3781   (sql: Add Data Type Formatting Functions)
   3: cockroachdb#40476  (sql: support `FOR {UPDATE,SHARE} {SKIP LOCKED,NOWAIT}`)
   3: cockroachdb#35882  (sql: support other character sets)
   2: cockroachdb#10028  (sql: Support view queries with star expansions)
   2: cockroachdb#35807  (sql: INTERVAL output doesn't match PG)
   2: cockroachdb#35902  (sql: large object support)
   2: cockroachdb#40474  (sql: support `SELECT ... FOR UPDATE OF` syntax)
   1: cockroachdb#18846  (sql: Support CIDR column type)
   1: cockroachdb#9682   (sql: implement computed indexes)
   1: cockroachdb#31632  (sql: FK options (deferrable, etc))
   1: cockroachdb#24897  (sql: CREATE OR REPLACE VIEW)
   1: pass?                                                  (unknown)
   1: cockroachdb#36215  (sql: enable setting standard_conforming_strings to off)
   1: cockroachdb#32562  (sql: support SET LOCAL and txn-scoped session variable changes)
   1: cockroachdb#36116  (sql: psychopg: investigate how `'infinity'::timestamp` is presented)
   1: cockroachdb#26732  (sql: support the binary operator: <int> / <float>)
   1: cockroachdb#23299  (sql: support coercing string literals to arrays)
   1: cockroachdb#36115  (sql: psychopg: investigate if datetimetz is being returned instead of datetime)
   1: cockroachdb#26925  (sql: make the CockroachDB integer types more compatible with postgres)
   1: cockroachdb#21085  (sql: WITH RECURSIVE (recursive common table expressions))
   1: cockroachdb#36179  (sql: implicity convert date to timestamp)
   1: cockroachdb#36118  (sql: Cannot parse '24:00' as type time)
   1: cockroachdb#31708  (sql: support current_time)
```

Release justification: non-production change
Release note: None
@awoods187
Copy link
Contributor

This is used by the Hive metadata store

craig bot pushed a commit that referenced this issue Nov 7, 2019
@jordanlewis
Merge #41252
27fd417 
41252: roachtest: add test that aggregates orm blacklist failures r=jordanlewis a=jordanlewis

The spreadsheet we discussed is unwieldy - hard to edit and impossible to keep
up to date. If we write down blacklists in code, then we can use an approach
like this to always have an up to date aggregation.

So far it seems like there's just a lot of unknowns to categorize still.

The output today:

```
=== RUN   TestBlacklists
 648: unknown                                                (unknown)
 493: #5807   (sql: Add support for TEMP tables)
 151: #17511  (sql: support stored procedures)
  86: #26097  (sql: make TIMETZ more pg-compatible)
  56: #10735  (sql: support SQL savepoints)
  55: #32552  (multi-dim arrays)
  55: #26508  (sql: restricted DDL / DML inside transactions)
  52: #32565  (sql: support optional TIME precision)
  39: #243    (roadmap: Blob storage)
  33: #26725  (sql: support postgres' API to handle blob storage (incl lo_creat, lo_from_bytea))
  31: #27793  (sql: support custom/user-defined base scalar (primitive) types)
  24: #12123  (sql: Can't drop and replace a table within a transaction)
  24: #26443  (sql: support user-defined schemas between database and table)
  20: #21286  (sql: Add support for geometric types)
  18: #6583   (sql: explicit lock syntax (SELECT FOR {SHARE,UPDATE} {skip locked,nowait}))
  17: #22329  (Support XA distributed transactions in CockroachDB)
  16: #24062  (sql: 32 bit SERIAL type)
  16: #30352  (roadmap:when CockroachDB  will support cursor?)
  12: #27791  (sql: support RANGE types)
   8: #40195  (pgwire: multiple active result sets (portals) not supported)
   8: #6130   (sql: add support for key watches with notifications of changes)
   5: Expected Failure                                       (unknown)
   5: #23468  (sql: support sql arrays of JSONB)
   5: #40854  (sql: set application_name from connection string)
   4: #35879  (sql: `default_transaction_read_only` should also accept 'on' and 'off')
   4: #32610  (sql: can't insert self reference)
   4: #40205  (sql: add non-trivial implementations of FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR NO KEY SHARE)
   4: #35897  (sql: unknown function: pg_terminate_backend())
   4: #4035   (sql/pgwire: missing support for row count limits in pgwire)
   3: #27796  (sql: support user-defined DOMAIN types)
   3: #3781   (sql: Add Data Type Formatting Functions)
   3: #40476  (sql: support `FOR {UPDATE,SHARE} {SKIP LOCKED,NOWAIT}`)
   3: #35882  (sql: support other character sets)
   2: #10028  (sql: Support view queries with star expansions)
   2: #35807  (sql: INTERVAL output doesn't match PG)
   2: #35902  (sql: large object support)
   2: #40474  (sql: support `SELECT ... FOR UPDATE OF` syntax)
   1: #18846  (sql: Support CIDR column type)
   1: #9682   (sql: implement computed indexes)
   1: #31632  (sql: FK options (deferrable, etc))
   1: #24897  (sql: CREATE OR REPLACE VIEW)
   1: pass?                                                  (unknown)
   1: #36215  (sql: enable setting standard_conforming_strings to off)
   1: #32562  (sql: support SET LOCAL and txn-scoped session variable changes)
   1: #36116  (sql: psychopg: investigate how `'infinity'::timestamp` is presented)
   1: #26732  (sql: support the binary operator: <int> / <float>)
   1: #23299  (sql: support coercing string literals to arrays)
   1: #36115  (sql: psychopg: investigate if datetimetz is being returned instead of datetime)
   1: #26925  (sql: make the CockroachDB integer types more compatible with postgres)
   1: #21085  (sql: WITH RECURSIVE (recursive common table expressions))
   1: #36179  (sql: implicity convert date to timestamp)
   1: #36118  (sql: Cannot parse '24:00' as type time)
   1: #31708  (sql: support current_time)
```

Release justification: non-production change
Release note: None

Co-authored-by: Jordan Lewis <[email protected]>
@jordanlewis
Copy link
Member

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
Copy link
Member

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.

@luginbash
Copy link

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

cedi added a commit to cedi-dev/k8smanifests that referenced this issue Apr 9, 2023
@cedi
Revert "update nautobot to use CockroachDB"
7069c02 
This reverts commit 1d34a35.

Turns out: nautobot requires some of the edgecases that CockroachDB does
not support.
See [#31632](cockroachdb/cockroach#31632)
@jiqiang90 jiqiang90 mentioned this issue Apr 11, 2023
Closed
5 tasks
@mgartner mgartner moved this to New Backlog in SQL Queries Jul 24, 2023
@rafiss rafiss mentioned this issue Jan 20, 2024
Merged
@Paillat-dev
Copy link

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

@Paillat-dev Paillat-dev mentioned this issue Feb 7, 2024
Open
@gerwim
Copy link

gerwim commented May 8, 2024

Same issue, running Flagsmith (which uses Django).

nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Aug 20, 2024
@nvanbenschoten
sql: add max_prepared_transactions variable
38034c3 
Informs cockroachdb#22329.

This commit adds the max_prepared_transactions session variable, returning a
value of math.MaxInt32 to indicate that there is no limit on the number of
prepared transactions that can be created.

This change has the effect of unblocking the pgjdbc XADataSourceTest test
suite, which was previously failing during initialization. Is also includes
a small patch to work around cockroachdb#31632 (unsupported deferrable qualifier) so
that each test in the XA test suite actually runs (and fails).

Release note: None
@nvanbenschoten nvanbenschoten mentioned this issue Aug 20, 2024
Merged
nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Aug 20, 2024
@nvanbenschoten
sql: add max_prepared_transactions variable
e892b8e 
Informs cockroachdb#22329.

This commit adds the max_prepared_transactions session variable, returning a
value of math.MaxInt32 to indicate that there is no limit on the number of
prepared transactions that can be created.

This change has the effect of unblocking the pgjdbc XADataSourceTest test
suite, which was previously failing during initialization. Is also includes
a small patch to work around cockroachdb#31632 (unsupported deferrable qualifier) so
that each test in the XA test suite actually runs (and fails).

Release note: None
nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Aug 21, 2024
@nvanbenschoten
roachtest: fix pgjdbc XA test suite
367d81a 
Informs cockroachdb#22329.

This commit fixes the pgjdbc blocklist to reflect the current state of
support for XA transactions in CockroachDB. With the newly added support
for two-phase commit transactions, all tests in the XADataSourceTest
test suite now pass except for `mappingOfConstraintViolations`, which is
blocked by cockroachdb#31632 (DEFERRABLE foreign keys).

Release note: None
@nvanbenschoten nvanbenschoten mentioned this issue Aug 21, 2024
Merged
nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Dec 11, 2024
@nvanbenschoten
sql: add max_prepared_transactions variable
af706c9 
Informs cockroachdb#22329.

This commit adds the max_prepared_transactions session variable, returning a
value of math.MaxInt32 to indicate that there is no limit on the number of
prepared transactions that can be created.

This change has the effect of unblocking the pgjdbc XADataSourceTest test
suite, which was previously failing during initialization. Is also includes
a small patch to work around cockroachdb#31632 (unsupported deferrable qualifier) so
that each test in the XA test suite actually runs (and fails).

Release note: None
nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Dec 12, 2024
@nvanbenschoten
sql: add max_prepared_transactions variable
e383180 
Informs cockroachdb#22329.

This commit adds the max_prepared_transactions session variable, returning a
value of math.MaxInt32 to indicate that there is no limit on the number of
prepared transactions that can be created.

This change has the effect of unblocking the pgjdbc XADataSourceTest test
suite, which was previously failing during initialization. Is also includes
a small patch to work around cockroachdb#31632 (unsupported deferrable qualifier) so
that each test in the XA test suite actually runs (and fails).

Release note: None
craig bot pushed a commit that referenced this issue Dec 12, 2024
@nvanbenschoten
Merge #129295 #129298
0243bd9 
129295: sql: add max_prepared_transactions variable r=rafiss a=nvanbenschoten

Informs #22329.

This commit adds the `max_prepared_transactions` session variable, returning a value of math.MaxInt32 to indicate that there is no limit on the number of prepared transactions that can be created.

This change has the effect of unblocking the pgjdbc `XADataSourceTest` test suite, which was previously failing during initialization. Is also includes a small patch to work around #31632 (unsupported deferrable qualifier) so that each test in the XA test suite actually runs (and fails, for the most part).

Release note: None

129298: sql: add parser support for XA transactions r=rafiss a=nvanbenschoten

Informs #22329.

This commit adds new syntax for `PREPARE TRANSACTION <gid>`, `COMMIT PREPARED <gid>`, and `ROLLBACK PREPARED <gid>`, for parity with Postgres support.

Handling of the syntax is currently unimplemented.

Release note: None

Co-authored-by: Nathan VanBenschoten <[email protected]>
craig bot pushed a commit that referenced this issue Dec 12, 2024
@nvanbenschoten
Merge #129295
209068f 
129295: sql: add max_prepared_transactions variable r=rafiss a=nvanbenschoten

Informs #22329.

This commit adds the `max_prepared_transactions` session variable, returning a value of math.MaxInt32 to indicate that there is no limit on the number of prepared transactions that can be created.

This change has the effect of unblocking the pgjdbc `XADataSourceTest` test suite, which was previously failing during initialization. Is also includes a small patch to work around #31632 (unsupported deferrable qualifier) so that each test in the XA test suite actually runs (and fails, for the most part).

Release note: None

Co-authored-by: Nathan VanBenschoten <[email protected]>
craig bot pushed a commit that referenced this issue Dec 12, 2024
@nvanbenschoten
Merge #129295 #129297 #129298
78a785d 
129295: sql: add max_prepared_transactions variable r=rafiss a=nvanbenschoten

Informs #22329.

This commit adds the `max_prepared_transactions` session variable, returning a value of math.MaxInt32 to indicate that there is no limit on the number of prepared transactions that can be created.

This change has the effect of unblocking the pgjdbc `XADataSourceTest` test suite, which was previously failing during initialization. Is also includes a small patch to work around #31632 (unsupported deferrable qualifier) so that each test in the XA test suite actually runs (and fails, for the most part).

Release note: None

129297: sql: create system.prepared_transactions table r=rafiss a=nvanbenschoten

Informs #22329.

This commit adds a new `system.prepared_transactions` table, which will be used to track prepared transactions. The table includes all of the information needed to implement XA two-phase commit transactions, along with additional metadata needed to implement `pg_catalog.pg_prepared_xacts`.

Release note: None

129298: sql: add parser support for XA transactions r=rafiss a=nvanbenschoten

Informs #22329.

This commit adds new syntax for `PREPARE TRANSACTION <gid>`, `COMMIT PREPARED <gid>`, and `ROLLBACK PREPARED <gid>`, for parity with Postgres support.

Handling of the syntax is currently unimplemented.

Release note: None

Co-authored-by: Nathan VanBenschoten <[email protected]>
craig bot pushed a commit that referenced this issue Dec 12, 2024
@nvanbenschoten
Merge #129295
31957b1 
129295: sql: add max_prepared_transactions variable r=rafiss a=nvanbenschoten

Informs #22329.

This commit adds the `max_prepared_transactions` session variable, returning a value of math.MaxInt32 to indicate that there is no limit on the number of prepared transactions that can be created.

This change has the effect of unblocking the pgjdbc `XADataSourceTest` test suite, which was previously failing during initialization. Is also includes a small patch to work around #31632 (unsupported deferrable qualifier) so that each test in the XA test suite actually runs (and fails, for the most part).

Release note: None

Co-authored-by: Nathan VanBenschoten <[email protected]>
craig bot pushed a commit that referenced this issue Dec 12, 2024
@nvanbenschoten
Merge #129295 #129297 #129298
1a038d8 
129295: sql: add max_prepared_transactions variable r=rafiss a=nvanbenschoten

Informs #22329.

This commit adds the `max_prepared_transactions` session variable, returning a value of math.MaxInt32 to indicate that there is no limit on the number of prepared transactions that can be created.

This change has the effect of unblocking the pgjdbc `XADataSourceTest` test suite, which was previously failing during initialization. Is also includes a small patch to work around #31632 (unsupported deferrable qualifier) so that each test in the XA test suite actually runs (and fails, for the most part).

Release note: None

129297: sql: create system.prepared_transactions table r=rafiss a=nvanbenschoten

Informs #22329.

This commit adds a new `system.prepared_transactions` table, which will be used to track prepared transactions. The table includes all of the information needed to implement XA two-phase commit transactions, along with additional metadata needed to implement `pg_catalog.pg_prepared_xacts`.

Release note: None

129298: sql: add parser support for XA transactions r=rafiss a=nvanbenschoten

Informs #22329.

This commit adds new syntax for `PREPARE TRANSACTION <gid>`, `COMMIT PREPARED <gid>`, and `ROLLBACK PREPARED <gid>`, for parity with Postgres support.

Handling of the syntax is currently unimplemented.

Release note: None

Co-authored-by: Nathan VanBenschoten <[email protected]>
nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Dec 13, 2024
@nvanbenschoten
roachtest: fix pgjdbc XA test suite
02b663d 
Informs cockroachdb#22329.

This commit fixes the pgjdbc blocklist to reflect the current state of
support for XA transactions in CockroachDB. With the newly added support
for two-phase commit transactions, all tests in the XADataSourceTest
test suite now pass except for `mappingOfConstraintViolations`, which is
blocked by cockroachdb#31632 (DEFERRABLE foreign keys).

Release note: None
nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Dec 13, 2024
@nvanbenschoten
roachtest: fix pgjdbc XA test suite
bd66628 
Informs cockroachdb#22329.

This commit fixes the pgjdbc blocklist to reflect the current state of
support for XA transactions in CockroachDB. With the newly added support
for two-phase commit transactions, all tests in the XADataSourceTest
test suite now pass except for `mappingOfConstraintViolations`, which is
blocked by cockroachdb#31632 (DEFERRABLE foreign keys).

Release note: None
nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Dec 13, 2024
@nvanbenschoten
roachtest: fix pgjdbc XA test suite
c722328 
Informs cockroachdb#22329.

This commit fixes the pgjdbc blocklist to reflect the current state of
support for XA transactions in CockroachDB. With the newly added support
for two-phase commit transactions, all tests in the XADataSourceTest
test suite now pass except for `mappingOfConstraintViolations`, which is
blocked by cockroachdb#31632 (DEFERRABLE foreign keys).

Release note: None
nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Dec 13, 2024
@nvanbenschoten
roachtest: fix pgjdbc XA test suite
353d041 
Informs cockroachdb#22329.

This commit fixes the pgjdbc blocklist to reflect the current state of
support for XA transactions in CockroachDB. With the newly added support
for two-phase commit transactions, all tests in the XADataSourceTest
test suite now pass except for `mappingOfConstraintViolations`, which is
blocked by cockroachdb#31632 (DEFERRABLE foreign keys).

Release note: None
nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Dec 17, 2024
@nvanbenschoten
roachtest: fix pgjdbc XA test suite
f2acda2 
Informs cockroachdb#22329.

This commit fixes the pgjdbc blocklist to reflect the current state of
support for XA transactions in CockroachDB. With the newly added support
for two-phase commit transactions, all tests in the XADataSourceTest
test suite now pass except for `mappingOfConstraintViolations`, which is
blocked by cockroachdb#31632 (DEFERRABLE foreign keys).

Release note: None
nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Dec 17, 2024
@nvanbenschoten
roachtest: fix pgjdbc XA test suite
59b4dfd 
Informs cockroachdb#22329.

This commit fixes the pgjdbc blocklist to reflect the current state of
support for XA transactions in CockroachDB. With the newly added support
for two-phase commit transactions, all tests in the XADataSourceTest
test suite now pass except for `mappingOfConstraintViolations`, which is
blocked by cockroachdb#31632 (DEFERRABLE foreign keys).

Release note: None
nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Dec 18, 2024
@nvanbenschoten
roachtest: fix pgjdbc XA test suite
6e2aea5 
Informs cockroachdb#22329.

This commit fixes the pgjdbc blocklist to reflect the current state of
support for XA transactions in CockroachDB. With the newly added support
for two-phase commit transactions, all tests in the XADataSourceTest
test suite now pass except for `mappingOfConstraintViolations`, which is
blocked by cockroachdb#31632 (DEFERRABLE foreign keys).

Release note: None
craig bot pushed a commit that referenced this issue Dec 18, 2024
@nvanbenschoten
Merge #129448
694ca19 
129448: sql: implement XA transaction SQL statements r=rafiss a=nvanbenschoten

Informs #22329.
Closes #137325.

This PR implements the statement handling for `PREPARE TRANSACTION <gid>`, `COMMIT PREPARED <gid>`, and `ROLLBACK PREPARED <gid>`, for parity with Postgres support. The implementation is based on the Postgres documentation and the Postgres source code.

The PR then implements the `pg_catalog.pg_prepared_xacts` system catalog table, which reads from `system.prepared_transactions`. This adds the remaining PG compatibility, so that XA in CockroachDB looks near-identical to XA in PostgreSQL.

Finally, the PR fixes the `pgjdbc` blocklist to reflect the current state of support for XA transactions in CockroachDB. With the newly added support for two-phase commit transactions, all tests in the `XADataSourceTest` test suite now pass except for `mappingOfConstraintViolations`, which is blocked by #31632 (DEFERRABLE foreign keys).

Release note (sql change): XA transaction support allows CockroachDB to participate in distributed transaction with other resources (e.g. databases, message queues, etc) using a two phase commit protocol.

Co-authored-by: Nathan VanBenschoten <[email protected]>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
A-schema-changes A-sql-pgcompat Semantic compatibility with PostgreSQL A-sql-syntax Issues strictly related to the SQL grammar, with no semantic aspect C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) O-community Originated from the community T-sql-queries SQL Queries Team X-anchored-telemetry The issue number is anchored by telemetry references.
Projects
SQL Queries
Status: Backlog
Milestone
No milestone
Development

No branches or pull requests