Re: pg_multixact not getting truncated

On 11/21/14, 12:51 PM, Josh Berkus wrote:
> On 11/21/2014 10:44 AM, Josh Berkus wrote:
>> Greg,
>>
>>
>>> This is actually the way it used to be. It was changed because it was
>>> discovered there was some case where an unfrozen xid would end up in
>>> template0 anyways and for some reason it was hard to be sure to avoid it. I
>>> don't recall exactly what the situation was that triggered it but the
>>> argument was made then that it was safest to just include template0 in
>>> autovacuum rather than depend on getting this 100% right and risk
>>> corruption.
>>
>> Right, and that was fine before pg_multixact, because even with 500m
>> XIDs in the bank, pg_clog is still pretty small. The problem is that
>> with the same number of multixacts, pg_multixact is around *16GB* in size.
>>
>> Thing is, template0 is just there as a check on users messing up
>> template1. Having that kind if precaution causing repeated operational
>> problems for users is not good design. Maybe we should just get rid of
>> template0 and come up with some other mechanism to reset template1 to
>> bare-bones state.
>
> Or and even simpler solution: provide a way for the superuser to
> manually vacuum template0 *without* needing to update pg_database.

AIUI, this is only an issue because evin if you completely freeze a normal database you can't set frozenxid or minmxid to Frozen because that will be wrong as soon as any DML happens and we don't want to screw with a real-time update to pg_class and pg_database. But any database you can't connect to is clearly a special case.

What if we allowed you to vacuum a database you couldn't connect to, and while scanning such a database tracked whether each rel was completely frozen? Because no one else could have connected we know that no new (M)XIDs could have been created in that database.

Is there any fundamental reason a vacuum from one database couldn't vacuum relations in another database, so long as no one could be connected to it? I'm sure there's some sanity checks that would need to be modified...

We'd need something more sophisticated than datcanconnect for this to work as well, since we'd need to prevent anyone from copying a database while being vacuumed, as well as preventing anyone from changing datcanconnect while the vacuum is running.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com