investigation: activerecord tests see FK references remain after table has been dropped

[rafiss]

Describe the problem

From @keithdoggett

I'm working on the ActiveRecord 7 branch and a lot of tests that were passing are now failing, but it seems like an issue on CockroachDB.

In the fixtures_test file there's 3 tables that are needed for most tests parrots, parrots_pirates, and parrots_treasures. The pirates and treasures tables are also defined, but they're created before our tests run.
Here's the sql to create each table:

CREATE TABLE "parrots" ("id" bigserial primary key, "name" character varying, "color" character varying, "parrot_sti_class" character varying, "killer_id" int, "updated_count" int DEFAULT '0', "integer" int DEFAULT '0', "created_at" timestamp, "created_on" timestamp, "updated_at" timestamp, "updated_on" timestamp)

CREATE TABLE "parrots_pirates" ("parrot_id" bigint, "pirate_id" bigint, CONSTRAINT "fk_rails_65e99344dc"
FOREIGN KEY ("parrot_id")
  REFERENCES "parrots" ("id")
, CONSTRAINT "fk_rails_8c00c26daf"
FOREIGN KEY ("pirate_id")
  REFERENCES "pirates" ("id")
)

CREATE TABLE "parrots_treasures" ("parrot_id" bigint, "treasure_id" bigint, CONSTRAINT "fk_rails_2a549fd937"
FOREIGN KEY ("parrot_id")
  REFERENCES "parrots" ("id")
, CONSTRAINT "fk_rails_df7e5cc9cf"
FOREIGN KEY ("treasure_id")
  REFERENCES "treasures" ("id")
)

Before each test we drop then recreate each of these tables and this snippet is causing errors

      conn.drop_table :parrots_pirates, if_exists: true
      conn.drop_table :parrots_treasures, if_exists: true
      p conn.tables
      conn.drop_table :parrots, if_exists: true

the conn.drop_table :parrots, if_exists: true line raises this error:

ActiveRecord::StatementInvalid: PG::InternalError: ERROR:  "parrots" is referenced by foreign key from table "parrot_treasures"

So it seems like parrots_pirates and parrots_treasures haven't been dropped, but the p conn.tables line shows that neither of those tables are in the database when parrots is dropped, so it should work.

In short: seems like foreign key references are hanging around after a table has been removed.

Expected behavior
In the example above, there should be no error when dropping parrots.

Environment:

• CockroachDB version: v21.1.9
• Client app: Rails 7

Additional context

We have seen similar errors happen during CI as a sporadic flake: https://teamcity.cockroachdb.com/buildConfiguration/Cockroach_ScratchProjectPutTcExperimentsInHere_ActiveRecordTestSuite/3445596?buildTab=log&focusLine=1135&linesState=525

In this CI test, the FK constraints are dropped, then a bunch of data is inserted in an order that would violate the constraints. However, there is a FK constraint violation even though the constraints were dropped.

@ajwerner had some suggestions around leasing

To Reproduce

Reproduces some of the time by running CI linked above.

Jira issue: CRDB-10608

[ajwerner]

Do we get a debug zip when this fails? That'd be rad.

It shouldn't be a leasing thing.

[ajwerner]

Alright, if this comes up again or is easy to repro, please pass along data.