Delete operation in transaction sometimes not deleting row in database

Hi, I'm calling a delete operation in a transaction that deletes an organization row in a Supabase Postgres database:
let data: unknown[] = [];
await db.transaction(async (tx) => {
// Update the organizationId of all related contacts to new id
await tx
.update(contact)
.set({ organizationId: input.newId })
.where(eq(contact.organizationId, input.id));
// Delete the organization with old id
data = await tx
.delete(organization)
.where(eq(organization.id, input.id))
.returning();
});
console.log("deleting organiation with id: ", input.id);
console.log("organization deleted: ", data);
let data: unknown[] = [];
await db.transaction(async (tx) => {
// Update the organizationId of all related contacts to new id
await tx
.update(contact)
.set({ organizationId: input.newId })
.where(eq(contact.organizationId, input.id));
// Delete the organization with old id
data = await tx
.delete(organization)
.where(eq(organization.id, input.id))
.returning();
});
console.log("deleting organiation with id: ", input.id);
console.log("organization deleted: ", data);
But sometimes if I delete the row immediately after creation, the console log says the row is deleted but it remains in the database. I need to call delete again for the row to be deleted. (Two deletions console logged below where only after the second delete log row is deleted from the database).
organization created: [
{
id: '9e0d585b-8284-4181-b7bf-6ec879700c6e',
name: '121',
createdAt: 2023-09-30T00:19:26.979Z,
updatedAt: 2023-09-30T00:19:26.979Z
}
]
organization deleted: [
{
id: '9e0d585b-8284-4181-b7bf-6ec879700c6e',
name: '121',
createdAt: 2023-09-30T00:19:26.979Z,
updatedAt: 2023-09-30T00:19:26.979Z
}
]
organization deleted: [
{
id: '9e0d585b-8284-4181-b7bf-6ec879700c6e',
name: '121',
createdAt: 2023-09-30T00:19:26.979Z,
updatedAt: 2023-09-30T00:19:26.979Z
}
]
organization created: [
{
id: '9e0d585b-8284-4181-b7bf-6ec879700c6e',
name: '121',
createdAt: 2023-09-30T00:19:26.979Z,
updatedAt: 2023-09-30T00:19:26.979Z
}
]
organization deleted: [
{
id: '9e0d585b-8284-4181-b7bf-6ec879700c6e',
name: '121',
createdAt: 2023-09-30T00:19:26.979Z,
updatedAt: 2023-09-30T00:19:26.979Z
}
]
organization deleted: [
{
id: '9e0d585b-8284-4181-b7bf-6ec879700c6e',
name: '121',
createdAt: 2023-09-30T00:19:26.979Z,
updatedAt: 2023-09-30T00:19:26.979Z
}
]
Is there an issue with how the transaction is written that is causing the problem? It seems that if I'm getting a console log back drizzle is telling me the object should be deleted. Thank you very much.
5 Replies
Bryan3
Bryan3OP14mo ago
Currently patched the issue by checking if object exists after the deleting and deleting it again if it still exists
await db.transaction(async (tx) => {
// Update the organizationId of all related contacts to new id
await tx
.update(contact)
.set({ organizationId: input.newId })
.where(eq(contact.organizationId, input.id));
// Delete the organization with old id
data = await tx
.delete(organization)
.where(eq(organization.id, input.id))
.returning();
});

// check if organization still exists
const deletedOrg = await db
.select({ id: organization.id })
.from(organization)
.where(eq(organization.id, input.id));

// call delete again if can still find organization
if (deletedOrg[0] && deletedOrg[0].id === input.id) {
console.log("contact not deleted yet, deleting again");
await db
.delete(organization)
.where(eq(organization.id, input.id))
.returning();
}
await db.transaction(async (tx) => {
// Update the organizationId of all related contacts to new id
await tx
.update(contact)
.set({ organizationId: input.newId })
.where(eq(contact.organizationId, input.id));
// Delete the organization with old id
data = await tx
.delete(organization)
.where(eq(organization.id, input.id))
.returning();
});

// check if organization still exists
const deletedOrg = await db
.select({ id: organization.id })
.from(organization)
.where(eq(organization.id, input.id));

// call delete again if can still find organization
if (deletedOrg[0] && deletedOrg[0].id === input.id) {
console.log("contact not deleted yet, deleting again");
await db
.delete(organization)
.where(eq(organization.id, input.id))
.returning();
}
Screw
Screw14mo ago
are you getting prepared statement issues in your database console where that is hosted
Bryan3
Bryan3OP14mo ago
I've found that I'm occasional getting this error in the Supabase postgres logs password authentication failed for user "postgres" Could this be causing the issue?
Boxer
Boxer12mo ago
did you ever fix this issue? I get this from time to time w/ supabase
Angelelz
Angelelz12mo ago
You might want to check if the underlying issue here might that you are using a connection pool and the queries are being executed in different connections
Want results from more Discord servers?
Add your server