Re: [WIP] Add relminxid column to pg_class - Mailing list pgsql-patches

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:

> > 3. automatically change the first single-table VACUUM in a database into
> > a database-wide vacuum.  Additionally to the problems from the precedent
> > proposal, this one breaks principle of least surprise.
>
> This isn't as bad as all that.  Suppose we make a database-wide VACUUM
> FREEZE set relminxid = FrozenXid (or maybe InvalidXid would be better)
> for all the tables, and also put this into datminxid.  This value can
> *not* be combined with other relminxid values, and therefore the rule is
> that all other forms of VACUUM forcibly process every rel with this
> relminxid, setting relminxid to current XID counter; and then they can
> validly compute a minimum database-wide value of relminxid to put into
> datminxid.

Hmm ... interesting idea.

> > 4. mandate that not connectable databases must be vacuumed regularly,
> > just like any other.  This follows the KISS principle.
>
> If we went down that path, I think we'd essentially abandon the notion
> of VACUUM FREEZE altogether.  Which would certainly simplify the system,
> but I'm not sure it's a step forward.
>
> The big objection to either of these approaches is that they give up the
> possibility of suppressing vacuums permanently on large read-only
> tables, which is one of the goals that we had in mind for VACUUM FREEZE.

I think a better idea is to have a separate "is read only" bit in
pg_class.  A table with that bit set doesn't need vacuuming at all, and
needs not participate in the datminxid calculations.  So we abandon the
notion of VACUUM FREEZE; all databases need constant vacuuming; but big
tables can be set read only.

One problem I see with this new bit is that a table can only have it set
if it's correctly frozen; and how do we know if it is?  We would need to
set the relminxid to FrozenXid anyway :-(  Or maybe the flag can only be
set by a VACUUM command, say "VACUUM AND SET READ ONLY" ;-)  Or we could
dictate that VACUUM FREEZE sets the flag, and in order to write to the
table again you need to "ALTER TABLE SET WRITABLE" ...

On further though: we can't do it on VACUUM FREEZE, because it's not a
full vacuum and thus it doesn't have an exclusive lock on the table, so
someone else could be modifying it.  But what about creating a new
VACUUM mode which would lock the table and set the read-only flag?


> The data warehousing guys will be on our case if we put that idea
> permanently off the table.

We can serve them better with this new read only flag ;-)


> I remember we had decided against the idea of having the first
> modification of a frozen table change its relminxid, but I've forgotten
> what the rationale was ... do you remember?

No, we didn't -- what happened was that my first implementation was so
bad that I decided to silently crawl away from it ;-)

Actually in the end I decided not to explore that route further because
I wasn't sure how to deal with it from WAL.  If you freeze a table, then
"unfreeze" it and the system crashes, how does the modification reach
pg_class?  Do we emit another WAL entry; one for the heap_insert (or
whatever) and another for the pg_class modification?  Or does
heap_redo() take care of it with the heap_insert WAL entry?

Seriously, if this can be done, then I see it as the simplest route,
because we wouldn't need special flags, nor special commands, and it
would be automatically unset if somebody writes to the table.

(On the other hand, if we did this, it would be trivial to add a "read
only" flag, changeable with a simple ALTER TABLE that would only work if
the table is in frozen state.)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support