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?

MaxGabriel · Accepted Answer · 2019-03-04 22:15:42Z

6

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;).

answered Mar 4, 2019 at 22:15

MaxGabriel

7,7154 gold badges38 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related