Re: why pg_class.relfrozenxid needs to be updated for frozen tables (where all rows have xmin=2)?

On 2/2/15 9:37 PM, Slava Mudry wrote:
>
> On Mon, Feb 2, 2015 at 5:52 PM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com
> <mailto:Jim(dot)Nasby(at)bluetreble(dot)com>> wrote:
>
> On 2/2/15 7:36 PM, Jim Nasby wrote:
>
>
> Currently the fact that it needs to go back to old tables
> and FTS them
> every 2B transactions (or rely on autovacuum for this) and
> you can't do
> anything about it (like permanently freeze the tables) seems
> like a big
> scalability issue. Does it not?
>
>
> Unfortunately it's not terribly easy to fix this. The problem is
> if we
> try to play games here, we must have a 100% reliable method for
> changing
> relfrozenxid as soon as someone inserts a new tuple in the
> relation. It
> might be possible to tie this into the visibility map, but no
> one has
> looked at this yet.
>
> Perhaps you'd be willing to investigate this, or sponsor the work?
>
> I'll see what I can do. Will talk to folks at pgDay in a month.
>
>
> Oh, there is another possibility that's been discussed: read-only
> tables. If we had the ability to mark a table read-only, then a
> VACUUM FREEZE on such a table would be able to set that table's
> relfrozenxid to FrozenTransactionId and prevent any further attempts
> at vacuuming. This might be easier than trying to do something
> automatic.
>
> I think if we could log "last update/delete/insert" timestamp for a
> table - we could use that to freeze tables that are not changed.

A timestamp wouldn't work; you need to have an exact XID.

Even if it did work you still have the same problem: there's a huge,
hairy race condition between what vacuum is trying to do and any DML.

> I also wonder how pg_database.datfrozenxid is set? Is it equal to the
> oldest pg_class.relfrozenxid for that database?

Correct.

> I ask because I am willing to give a try and update relfrozenxid for the
> tables that are never updated and frozen. Currently we are looking at
> 8-hour downtime to vacuum the whole db in single-user mode. High
> availability is more important that data loss in my case. [I still don't
> want to lose data, but it won't be the end of world if it happens].

Why are you trying to go into single user mode? There's no reason to do
that.

Forcing relfrozenxid to 2 might work, but you're certainly playing with
fire.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com