Re: Help me recovering data

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> All in all, I figure that odds are very high that if someone isn't
> vacuuming in the rest of the transaction id space, either the transaction
> rate is high enough that 100,000 warning may not be enough or they aren't
> going to pay attention anyway and the howitzer might not be bad.

Yeah. It's easy to imagine scenarios where the majority of the warnings
go into the bit bucket (because they are going to noninteractive client
applications that just ignore NOTICE messages). So I think it's
appropriate to be delivering the warnings for a good long time, in hopes
that someone at least occasionally fires up psql and happens to actually
see them. Something like 100K or 1M transactions feels about right
to me.

Pulling the failure trigger with 100K transactions still to go is surely
overly conservative, but compared to the size of the ID space it is not
worth noticing.

As far as the actual implementation, I was envisioning adding a limiting
XID variable and a database name variable to shared memory (protected by
the same LWLock that protects the nextXID counter). These would
be computed and loaded during the bootstrap process, right after we
finish WAL replay if any. It would probably cost us one XID to do this
(though maybe it could be done without running a real transaction? This
ties in with my thoughts about replacing GetRawDatabaseInfo with a flat
file...), but one XID per postmaster start attempt is hopefully not
gonna kill us. Subsequently, any VACUUM that updates a datfrozenxid
entry in pg_database would update these variables to reflect the new
safe limit and the name of the database with the currently oldest
datfrozenxid. This would allow a very cheap comparison during
GetNewTransactionId to see if we are near enough to generate a warning:
WARNING: database "foo" must be vacuumed within 58372 transactions
or past the limit and generate an error:
ERROR: database is shut down to avoid wraparound data loss in database "foo"
HINT: Stop the postmaster and use a standalone backend to VACUUM in "foo".
In the error case, we could error out *without* advancing nextXID,
so that even automated clients continually retrying failed transactions
couldn't blow past the safety margin.

regards, tom lane