TypeORM - duplicate key value violates unique constraint (deferrable fk)

Viewed 3332

I have the following situation.

export default class BucketEntity extends Entity {
  @Column({
    type: "enum",
    enum: BucketType,
    default: BucketType.Notes,
  })
  type: BucketType;

  @Column()
  title: string;

  @OneToOne(() => BucketEntity, {
    nullable: true,
    onDelete: "NO ACTION",
    deferrable: "INITIALLY DEFERRED",
  })
  @JoinColumn()
  prev: Promise<BucketEntity | null>;

  @Column({ nullable: true })
  prevId: number | null;

  @OneToOne(() => BucketEntity, {
    nullable: true,
    onDelete: "NO ACTION",
    deferrable: "INITIALLY DEFERRED",
  })
  @JoinColumn()
  next: Promise<BucketEntity | null>;

  @Column({ nullable: true })
  nextId: number | null;
}

This is a Bucket entity, which has two one-one relations to itself. Think doubly linked list. Each Bucket has a prev and a next ref. Those relations have the deferrable: true flag, which means I'm getting a FK constraints that are deferrable.

Here's the actual table, built out of this entity:

                                         Table "public.dashboardBuckets"
  Column   |             Type             | Collation | Nullable |                    Default
-----------+------------------------------+-----------+----------+------------------------------------------------
 id        | integer                      |           | not null | nextval('"dashboardBuckets_id_seq"'::regclass)
 createdAt | timestamp without time zone  |           | not null | now()
 updatedAt | timestamp without time zone  |           | not null | now()
 type      | "dashboardBuckets_type_enum" |           | not null | 'Notes'::"dashboardBuckets_type_enum"
 title     | character varying            |           | not null |
 prevId    | integer                      |           |          |
 nextId    | integer                      |           |          |
Indexes:
    "PK_6823bada941d24553e040989058" PRIMARY KEY, btree (id)
    "REL_9fc9b030d495660236b8d00074" UNIQUE CONSTRAINT, btree ("nextId")
    "REL_ea16688a9b632de7758cf696b5" UNIQUE CONSTRAINT, btree ("prevId")
Foreign-key constraints:
    "FK_9fc9b030d495660236b8d00074a" FOREIGN KEY ("nextId") REFERENCES "dashboardBuckets"(id) DEFERRABLE INITIALLY DEFERRED
    "FK_ea16688a9b632de7758cf696b52" FOREIGN KEY ("prevId") REFERENCES "dashboardBuckets"(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE ""dashboardBuckets"" CONSTRAINT "FK_9fc9b030d495660236b8d00074a" FOREIGN KEY ("nextId") REFERENCES "dashboardBuckets"(id) DEFERRABLE INITIALLY DEFERRED
    TABLE ""dashboardBuckets"" CONSTRAINT "FK_ea16688a9b632de7758cf696b52" FOREIGN KEY ("prevId") REFERENCES "dashboardBuckets"(id) DEFERRABLE INITIALLY DEFERRED

I do an update on the nextId/prevId fields in a transaction like this:

      const qr = getConnection().createQueryRunner();
      await qr.connect();
      await qr.startTransaction();
      await qr.query("SET CONSTRAINTS ALL DEFERRED;");

      try {
        // Update bucket prev/next refs ------------------------------------ //
        // await getConnection().transaction(async (t) => {
        let next, prev, oldPrev, oldNext;
        if (prevId !== undefined && nextId !== undefined) {
          const repo = qr.manager.getRepository(BucketEntity);

          // olds
          const oldPrevId = bucket.prevId;
          const oldNextId = bucket.nextId;
          oldPrev =
            bucket.prevId === null
              ? null
              : await repo.findOne(bucket.prevId, {
                  relations: ["prev", "next"],
                });
          oldNext =
            bucket.nextId === null
              ? null
              : await repo.findOne(bucket.nextId, {
                  relations: ["prev", "next"],
                });

          // update
          bucket.prevId = prevId;
          bucket.nextId = nextId;

          if (prevId !== null) {
            prev =
              prevId === oldNextId
                ? oldNext
                : await repo.findOne(prevId, {
                    relations: ["prev", "next"],
                  });
            if (prev === undefined) throw new BucketDoesntExistError();
            if (prev !== null) prev.nextId = bucket.id;
          }

          if (nextId !== null) {
            next =
              nextId === oldPrevId
                ? oldPrev
                : await repo.findOne(nextId, {
                    relations: ["prev", "next"],
                  });
            if (next === undefined) throw new BucketDoesntExistError();
            if (next !== null) next.prevId = bucket.id;
          }

          // update oldPrev/oldNext
          if (oldPrev) {
            oldPrev.nextId = oldNextId;
          }
          if (oldNext) {
            oldNext.prevId = oldPrevId;
          }
        }
        // ----------------------------------------------------------------- //
        if (oldNext) {
          await qr.manager.update(
            BucketEntity,
            { id: oldNext.id },
            { prevId: oldNext.prevId, nextId: oldNext.nextId }
          );
        }
        if (oldPrev) {
          await qr.manager.update(
            BucketEntity,
            { id: oldPrev.id },
            { prevId: oldPrev.prevId, nextId: oldPrev.nextId }
          );
        }
        if (next) {
          await qr.manager.update(
            BucketEntity,
            { id: next.id },
            { prevId: next.prevId, nextId: next.nextId }
          );
        }
        if (prev) {
          await qr.manager.update(
            BucketEntity,
            { id: prev.id },
            { prevId: prev.prevId, nextId: prev.nextId }
          );
        }

        await qr.manager.update(
          BucketEntity,
          { id: bucket.id },
          { prevId: bucket.prevId, nextId: bucket.nextId }
        );

        await qr.commitTransaction();
      } catch (error) {
        console.log("------------------------------------- error", error);
        await qr.rollbackTransaction();
      } finally {
        await qr.release();
      }

I'm getting the following error:

error QueryFailedError: duplicate key value violates unique constraint "REL_ea16688a9b632de7758cf696b5"
    at new QueryFailedError (/Users/work/code/dashify-server/src/error/QueryFailedError.ts:9:9)
    at Query.callback (/Users/work/code/dashify-server/src/driver/postgres/PostgresQueryRunner.ts:220:30)
    at Query.handleError (/Users/work/code/dashify-server/node_modules/pg/lib/query.js:128:19)
    at Client._handleErrorMessage (/Users/work/code/dashify-server/node_modules/pg/lib/client.js:335:17)
    at Connection.emit (events.js:315:20)
    at Connection.EventEmitter.emit (domain.js:483:12)
    at /Users/work/code/dashify-server/node_modules/pg/lib/connection.js:115:12
    at Parser.parse (/Users/work/code/dashify-server/node_modules/pg-protocol/src/parser.ts:102:9)
    at Socket.<anonymous> (/Users/work/code/dashify-server/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (events.js:315:20) {
  length: 237,
  severity: 'ERROR',
  code: '23505',
  detail: 'Key ("prevId")=(1) already exists.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'public',
  table: 'dashboardBuckets',
  column: undefined,
  dataType: undefined,
  constraint: 'REL_ea16688a9b632de7758cf696b5',
  file: 'nbtinsert.c',
  line: '656',
  routine: '_bt_check_unique',
  query: 'UPDATE "dashboardBuckets" SET "prevId"=$1, "nextId"=$2 WHERE id=$3;',
  parameters: [ 1, null, 3 ]
}

This happens in a transaction with deferred constraints. Shouldn't it be waiting until the transaction is commited and then throw the error if there are any duplicates? This happens right after the first update query. The DB is postgresql.

Any ideas on what do I do wrong?

Thanks!

1 Answers

I experienced a similar situation when I had forgotten to run one of my commands within the transaction, ie by not using the query runner manager when I should have been.

The hint was in the SQL logs where I saw a second START TRANSACTION command nested within the first:

query: START TRANSACTION
query: INSERT INTO ...
query: INSERT INTO ...
query: START TRANSACTION
query: INSERT INTO ...
query: COMMIT
query failed: COMMIT

This led to the records in the initial transaction not being available in the nested transaction, thus causing postgres to throw constraint errors.

Related