Why is my Postgres DELETE deleting zero rows, when one exists?

Question

I have a table transaction_metadata with a row with primary key: ac98435e-3eb3-11e9-8a32-4713408df551, which shows up in a SELECT statement.

In a transaction, deleting it (DELETE FROM transaction_metadata WHERE id = 'ac98435e-3eb3-11e9-8a32-4713408df551') says that it deletes 0 rows. Later in the transaction, deleting a row that the transaction_metadata row referenced fails due to breaking a foreign key constraint.

How could this be? Why would the DELETE say it deleted 0 rows when it exists in the SELECT, and foreign key constraints say it exists?

Answer 1

It turned out the issue was we had a trigger, where every time an operation was performed on a transaction_metadata row, we inserted a row into a transaction_metadata_history table that recorded the change. This table had a foreign key constraint on transaction_metadata, which must have caused the DELETE to not work.

As a workaround, we turned off triggers for the console session (SET session_replication_role = replica;), ran the DELETEs, then turned them back on (SET session_replication_role = DEFAULT;).