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

Bryan3OP•15mo 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•15mo ago

are you getting prepared statement issues in your database console where that is hosted

Bryan3OP•15mo 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•13mo ago

did you ever fix this issue? I get this from time to time w/ supabase

Angelelz•13mo 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

Gaming

Programming

Delete operation in transaction sometimes not deleting row in database