Re: augmenting MultiXacts to improve foreign keys

On Tue, Sep 13, 2011 at 9:27 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
>> I wonder if it's a mistake to be thinking about solving this problem
>> by extending the MultiXact mechanism.  Pushing xmax out-of-line so
>> that we have room to store tuple information seems expensive,
>> especially because there's no convenient way to undo it once the locks
>> are old enough not to be interesting any more.  The current system
>> works because we never need both pieces of information at the same
>> time, but that's not going to be true any more.
>
> Hmm, it doesn't look that way to me: whenever you lock a row, all
> previous lockers that are gone can now be forgotten.  Locks that are old
> enough not to be interesting, are constantly and automatically gone.

Yes... but as you pointed out, you'll still have to keep the
MultiXacts around for a long time, because you have no way of knowing
for certain whether there might be an xmax referring to them.

> The only reason that multixact now needs to persist beyond currently
> running transaction is the chance that there might be an "update xmax"
> hiding somewhere; and tuples marked with those are going to be removed
> by vacuum anyway.  (I have been thinking that long before vacuum, we
> could remove the multixact and replace it with a plain Xid, if the
> lockers are all gone -- which is another part of your "undo it once the
> locks are old enough".)

You could do that, but odds are it won't buy you much. Generally by
the time you could do this, you'll either be able to mark xmax invalid
or truncate the tuple to a dead line pointer. The exception would be
the case where xmax outlives all the lockers, but I'm not sure whether
it's worth having code just to cater to that case.

> The expensive bit is the reason why I used a hint bit to mark this
> possibility; we distinguish the cheap case of locked-but-not-updated
> from the expensive one of locked-and-updated with hint bits, so the
> cheap case stays cheap; and the expensive one requires a bit more work,
> yes, but this brings more concurrency overall.

Right. I don't think what you're trying to do is necessarily bad; I
was just casting around for other solutions. From an efficiency
standpoint, I can see two things to worry about: first, keeping
multixacts around for longer consumes more disk space than not doing
so, and most of the time, that disk space will be wasted; and second,
we'll need to deference multixact XIDs more frequently, and that's not
free. Now, it may well be that both of those problems are
sufficiently small that we needn't worry about them.

>> I'm wondering if it would be possible to modify the main lock manager,
>> or create a special-purpose tuple lock manager, to record all tuple
>> locks, both awaited and granted.  You'd need to make sure that if
>> there were more than a few locks the information could spill to disk
>> somehow, and you'd also need to make sure that you didn't pull that
>> information in from disk more often than necessary - i.e. you should
>> try to keep enough summary info in memory to determine whether there
>> *might* be a conflicting lock that spilled out, so that you only need
>> to go examine the spilled data if there's a possibility that you might
>> find something interesting there.
>
> This is where we started, back when we were creating SELECT FOR SHARE:
> trying to spill the lock table.  That idea went down in flames; consider
> that someone might request to block an entire huge table, and you're in
> trouble.

Yeah, that's not much fun. On the other hand, our current
representation involves dirtying every page in the table, which isn't
much fun either. I'm not sure that this couldn't be made to work -
with some highly-compressed lock representation - but I can see why
you're not interested in trying it.

I wish I had some better ideas here, but I think this is just a tough
problem and it may be that any solution will be somewhat messy.

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