Cannot TRUNCATE self-referential table

[pmooney-socraticworks]

Describe the bug

Given a table that has a foreign key which references other entries in the same table, TRUNCATE [table] CASCADE; command results in RangeError: Maximum call stack size exceeded

original: RangeError: Maximum call stack size exceeded
  
  💥 This is a nasty error, which was unexpected by pg-mem. Also known "a bug" 😁 Please file an issue !
  
  *️⃣ Failed SQL statement: TRUNCATE "customer" CASCADE;
  
  👉 You can file an issue at https://github.com/oguimbal/pg-mem along with a way to reproduce this error (if you can), and  the stacktrace:
  
  
      at ...node_modules/pg-mem/src/constraints/foreign-key.ts:186:37
      at MemoryTable.truncate (...node_modules/pg-mem/src/table.ts:493:13)
      at ...node_modules/pg-mem/src/constraints/foreign-key.ts:186:28
      at MemoryTable.truncate (...node_modules/pg-mem/src/table.ts:493:13)
      at ...node_modules/pg-mem/src/constraints/foreign-key.ts:186:28,
     (repeats forver)
     {
    location: { start: 0, end: 0 },
    sql: 'TRUNCATE "table" CASCADE',
    parameters: undefined,
    [Symbol(errorDetailsIncluded)]: true
  },
  sql: 'TRUNCATE "table" CASCADE',
  parameters: undefined
}

To Reproduce

CREATE TABLE IF NOT EXISTS "comment" ("id" UUID , "parent_comment_id" UUID REFERENCES "comment" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, "ref_id" UUID REFERENCES "task" ("id") ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY ("id"));;

Note: The above CREATE statement does not work in pg-mem but does in Postgres v 14.4.

For some reason pg-mem does not recognize that the foreign key is referring to a primary key (therefore already guaranteed unique), and complains with this error:

there is no unique constraint matching given keys for referenced table "comment"

It's a separate bug, but to get past this, you can add "UNIQUE" to the column when creating...

CREATE TABLE IF NOT EXISTS "comment" ("id" UUID UNIQUE , "parent_comment_id" UUID REFERENCES "comment" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));

pg-mem version

2.6.3

[jiayang26]

@pmooney-socraticworks I'm seeing a similar issue on 2.6.3 there is no unique constraint matching given keys for referenced table

But instead of primary key, it fails to recognize unique key defined as CONSTRAINT "UQ_xxx" UNIQUE ("abc").

An example

CREATE TABLE "person" (
   "id" SERIAL NOT NULL, 
   "guid" character varying(36) NOT NULL, 
   CONSTRAINT "UQ_fe9e55385c1d6c3d99ae1b25658" UNIQUE ("guid"), 
   CONSTRAINT "PK_5fdaf670315c4b7e70cce85daa3" PRIMARY KEY ("id")
)

ALTER TABLE "applicant" ADD CONSTRAINT "FK_aa2194ab7038eca69ee723c5089" FOREIGN KEY ("person_guid") REFERENCES "person"("guid") ON DELETE NO ACTION ON UPDATE NO ACTION`)

For above schema example, it gives me QueryFailedError: there is no unique constraint matching given keys for referenced table "person" when altering "applicant" table, which makes me wonder that the unique constraint defined on the 'person' table was not correctly recognized by pg-mem.

[BenStirrup]

Any update regarding this issue ?