Re: [HACKERS] Per-table freeze limit proposal - Mailing list pgsql-patches

Simon Riggs wrote:
> On Mon, 2005-11-14 at 23:40 -0300, Alvaro Herrera wrote:
>
> > The whole thing is pretty fragile is somebody manually updates a
> > catalog.  But we tell people not to do that, so it should be their
> > fault, right?
>
> Hmmmm...sounds scary. Cool ideas in the patch though.

Yeah, well, actually the problem is solved very easily by setting the
pg_database tuple manually, either to InvalidTransactionId or to the
minimum computed from pg_class.

> > The problem comes when this is done to template1, and it is copied to
> > another database after some millions of transactions have come and go --
> > it will seem like the database has suffered wraparound.  We would need
> > to vacuum it completely after copied for the stats to be accurate.
>
> I don't understand the issue, can you explain more? I see no problem. If
> an identical copy gives a problem then surely template1 should also.

Actually, template1 has the problem too.  The scenario is this:

- template1 is freezed.  datminxid <- X
- a long time passes, say INT_MAX * 0.75 transactions
- a new database D is created, which coming from template1 has datminxid=X
- the Xid counter is past the vacuum horizon for D.datminxid, so the
  system determines that the Xid counter could be wrapped already.
- The system automatically decides to stop accepting new transactions.

In fact there's no problem because in D, just like in template1, all
tuples are frozen.  How should we mark this on the catalogs?  I don't
see any way.

Note that setting relminxid = FrozenTransactionId is bogus in any case,
because even if we correctly lock and freeze the table, the next
transaction after the vacuum could insert a new tuple into the table.
But we don't want INSERT to be checking pg_class.relminxid!  (Or do we?)


Now, restating the problem, certainly template1 has the problem too.  In
fact we have a bigger problem: we are forcing all tables to be vacuumed
every so often, even if they have been completely frozen before!  This
is because setting relminxid = Frozen is really bogus.

> > I'm not sure what to do about that.  I think storing FrozenXid may not
> > actually be a totally bad idea.  Comments?
>
> Its not a totally bad idea, but it has some risk, which where
> transactions are concerned is not really acceptable.
>
> Perhaps we should reinstate VACUUM FULL FREEZE to do just a FREEZE with
> a table lock and skip all that moving data around.

Doesn't work either because of the argument above.

What about assuming that if somebody executes a database-wide FREEZE, he
knows what he is doing and thus we can mark datminxid as
FrozenTransactionId?

Sadly, I see all this as proof that the whole idea doesn't work.  It
seems better than the current state of the system, where we rely on the
user to do certain things, or on pgstat which is inherently inexact.
But there is a big hole in the whole reasoning which hasn't been filled
yet.

Any ideas welcome.  The idea of any insert/delete/update operation
checking a bit in the Relation and resetting relminxid to
TopTransactionId if it's marked Frozen is the only one I have right now.
What do people think about it?

--
Alvaro Herrera                                http://www.PlanetPostgreSQL.org
"No hay cielo posible sin hundir nuestras raíces
 en la profundidad de la tierra"                        (Malucha Pinto)