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!