sql: within a transaction, dropping the foreign key constraint followed by dropping the referenced table doesn't work

[giangpham712]

Describe the problem

Within a transaction, dropping the foreign key constraint followed by dropping the referenced table doesn't seem to work

To Reproduce

Create two tables with the following schema

CREATE TABLE public."Owners" (
  "Id" STRING NOT NULL,
  "Name" STRING NULL,
  "Version" INT8 NOT NULL,
  CONSTRAINT "PK_Owners" PRIMARY KEY ("Id" ASC)
)

CREATE TABLE public."Blogs" (
  "Id" UUID NOT NULL,
  "Order" INT8 NOT NULL,
  "OwnerId" STRING NULL,
  "Version" INT8 NOT NULL,
  CONSTRAINT "PK_Blogs" PRIMARY KEY ("Id" ASC),
  CONSTRAINT "FK_Blogs_Owners_OwnerId" FOREIGN KEY ("OwnerId") REFERENCES public."Owners"("Id"),
  INDEX "IX_Blogs_OwnerId" ("OwnerId" ASC)
)

Try to drop the foreign key constraint FK_Blogs_Owners_OwnerId and then drop the two tables in the same transaction as follow

BEGIN;
ALTER TABLE public."Blogs" DROP CONSTRAINT "FK_Blogs_Owners_OwnerId";
DROP TABLE public."Owners";
DROP TABLE public."Blogs";
COMMIT;

The following error is returned

2BP01: "Owners" is referenced by foreign key from table "Blogs"

Additional context

This is affecting some tests in efcore.pg library

@fqazi @rafiss

Jira issue: CRDB-30285

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-foundations (found keywords: ALTER TABLE)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[fqazi]

This is a limitation of the legacy schema changer, but it won't be eliminated in the short term. In general, we don't fully support running schema changes in transactions: https://www.cockroachlabs.com/docs/stable/online-schema-changes#limitations. So, there will be weird bugs like this

[rafiss]

@giangpham712 since this is affecting some tests, but we can't fix this soon, I think you should skip those tests for CRDB.