I’m not sure how I got into this situation, but recently our application cannot delete any row from one particular table. I checked and we cannot even delete it from psql shell. This is the error:

vnms=# delete from alarm_action where id = 12345678;
ERROR:  Delete is pending, will be blocked

(12345678 is an invalid, non-existent ID. The error comes back immediately with no pause)

It looks to me there’s some kind of “Delete is pending” marker associated with the alarm_action table, because I can delete rows from other tables just fine. But I’m not sure where that marker is. I tried to search the Internet in vain for the error message. I then tried to download postgresql source code from github but can’t find that error message either.

I also:

• Restarted postgres, no change.
• Renamed the database to another name, no change.
• Make backup, then drop the database, then create a new database and restore the backup to it. This refreshes all the oid, but the error message remains.
• At the same time, I see the same error message if I delete from the alarm_action table in both the old (but renamed) database, and the new database. Since they have different oid, it appears the persistency is not due to oid.
• I check pg_stat_activity, it’s empty
• I check pg_locks, all the locks are accounted for. I even forcefully remove all the locks with pg_terminate_backend(). This refreshes all the pid and oid of the locks, but still the same error message remains. That tells me the error doesn’t have anything to do with pg_locks.

The error “Delete is pending” is also peculiar, because it’s specifically about some deletion. If it’s just a lock, it either will hang for a while (while waiting for a lock) or come back with something like “Unable to obtain lock”.

I’m also sure this is Postgres’s internal work, and not due to another table in our database keeping this persistent state. Not that we know how even if we want to!

Does anyone have any clue what this message “Delete is pending, will be blocked” is about? How is it tracked and how it can be unblocked? Thanks.