Consider a record with a counter field, which is to be decremented. When its value reaches zero (which is the common case), I want the record to be deleted. What is the most efficient way to do this in PostgreSQL?
The naive way involves two SQL statements and two searches in the table: a
SELECT to fetch the counter value, followed by a
DELETE or an
One alternative involves an
UPDATE … RETURNING, followed by a
DELETE only if the returned value of the counter is zero. However, this performs an unneeded record change in the case of a counter having a value of one, optimizing the uncommon case (counter has a value higher than one) at the expense of the expected common case.
Another alternative, which indeed optimizes the expected common case, involves a
DELETE … WHERE … AND counter = 1, followed by an
UPDATE when no deletion takes place.
Both alternatives may require a wasteful second search for the record in the table.
Can perhaps the two table searches be always avoided and the operation's efficiency increased by using a cursor? I haven't seen an example for this use case in the PostgreSQL documentation.