PostgreSQL: 'Record not found' scenarios when using RETURNING clause with DELETE
Quick question that's been bugging me - I've been banging my head against this for hours... I'm working on a personal project and I'm working with a frustrating scenario when trying to delete records from my PostgreSQL database using the `RETURNING` clause. I'm using PostgreSQL version 13.1, and the behavior I'm getting is `behavior: record not found` when I attempt to delete a row that I know exists. Here’s the code I’m using to delete a record: ```sql DELETE FROM users WHERE id = 123 RETURNING *; ``` Initially, I was using a transaction block because I wanted to ensure that the deletion was part of a larger atomic operation. Here’s a simplified version of the transaction: ```sql BEGIN; DELETE FROM users WHERE id = 123 RETURNING *; -- Other operations COMMIT; ``` The operation seems straightforward. However, sometimes when I execute the delete statement within the transaction, I receive the aforementioned behavior, even though I can confirm that a user with that ID exists prior to the delete operation. I've tried some debugging steps, such as running the delete statement outside of the transaction, and it works fine. I've also verified that no triggers or foreign key constraints are causing this scenario. The only unusual aspect is that the `users` table has a couple of foreign keys to other tables, but I don't think they would interfere given that I'm directly deleting a user. Could there be an scenario with how transactions handle visibility of records, or is there something else I might be missing? Any insights would be greatly appreciated! I'm working on a CLI tool that needs to handle this. Has anyone else encountered this? I'm working on a application that needs to handle this. How would you solve this? For context: I'm using Sql on Linux. Am I missing something obvious? I'm open to any suggestions.