Re: Feature suggestions: "dead letter"-savepoint.

But what if there’s a bug making a call to the external service?  Most of the time, you’ll trap the error and set status to something sane, but what if there’s a crash-bug in the SDK implementing it, or some other situation where things go very bad?  The rocket might be fired, then the client dies, lock is released, another worker picks up the task, and repeats the process ad nausium.

Okay, so you’ll just update the row to say you’re trying to send it.  You set status=‘in-flight’ or some other status that’ll prevent the SELECT in other workers from picking up the task, and you commit that, so other workers won’t pick up the row if you die.  In the process though, you also loose the lock on the row.  You still want the row to be tied to you specifically, so you add a unique tag to the row, that later needs to be removed, so there’s more housekeeping.

The basic idea is to be able to say “If I go belly up, I want this stuff to happen”.

Depending on different needs, could be made persistent once the savepoint is taken, but for a lot of cases that wouldn’t really be needed.

There’s some room for variation as well, such as having it support only dropped connections, or also support turning errors and/or rollbacks into rollback to and commits of the savepoint.  Ideally configurable at the point the snapshot it taken, to easily support pr. snapshot variation.

I did for a few moments wonder if prepared transactions would be a better place for something like this.  It could allow for named independent transactions, but there’s a fairly big mismatch between the two concepts.  It also wouldn’t be too hard to use multiple named savepoints for effectively the same logic for most cases.  One advantage of prepared transactions is that it could perhaps also cover the case of a postgresql child dying, but that’s not exactly a common problem.  A huge dealbreaker though, is that the prepared transaction would very likely keep conflicting locks with the work to be done.