Re: should there be a hard-limit on the number of transactions pending undo?

On Fri, Jul 19, 2019 at 3:12 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> On 2019-07-19 14:50:22 -0400, Robert Haas wrote:
> > On Fri, Jul 19, 2019 at 2:04 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > > It doesn't seem that hard - and kind of required for robustness
> > > independent of the decision around "completeness" - to find a way to use
> > > the locks already held by the prepared transaction.
> >
> > I'm not wild about finding more subtasks to put on the must-do list,
> > but I agree it's doable.
>
> Isn't that pretty inherently required? How are otherwise ever going to
> be able to roll back a transaction that holds an AEL on a relation it
> also modifies? I might be standing on my own head here, though.

I think you are. If a transaction holds an AEL on a relation it also
modifies, we still only need something like RowExclusiveLock to roll
it back. If we retain the transaction's locks until undo is complete,
we will not deadlock, but we'll also hold AccessExclusiveLock for a
long time. If we release the transaction's locks, we can perform the
undo in the background with only RowExclusiveLock, which is full of
win. Even you insist that the undo task should acquire the same lock
the relation has, which seems entirely excessive to me, that hardly
prevents undo from being applied. Once the original transaction has
released its locks, those locks are released, and the undo system can
acquire those locks the next time the relation isn't busy (or when it
gets to the head of the lock queue).

As far as I can see, the only reason why you would care about this is
to make the back-pressure system effective against prepared
transactions. Different people may want that more or less, but I have
a little trouble with the idea that it is a hard requirement.

> Well, then perhaps that admin ought not to constantly terminate
> connections... I was thinking that new connections wouldn't be forced
> to do that if there were still a lot of headroom regarding
> #transactions-to-be-rolled-back. And if undo workers kept up, you'd
> also not hit this.

Sure, but cascading failure scenarios suck.

> > Hmm, that's not a bad idea. So the transactions would have to "count"
> > the moment they insert their first undo record, which is exactly the
> > right thing anyway.
> >
> > Hmm, but what about transactions that are only touching unlogged tables?
>
> Wouldn't we throw all that UNDO away in a crash restart? There's no
> underlying table data anymore, after all.
>
> And for proper shutdown checkpoints they could just be included.

On thirty seconds thought, that sounds like it would work.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company