Re: Hot Standby query cancellation and Streaming Replication integration

Bruce Momjian wrote:
> 5 Early cleanup of data still visible to the current query's
> snapshot
>
> #5 could be handled by using vacuum_defer_cleanup_age on the master.
> Why is vacuum_defer_cleanup_age not listed in postgresql.conf?
>

I noticed that myself and fired off a corrective patch to Simon
yesterday, he said it was intentional but not sure why that is yet.
We'll sort that out.

You are correct that my suggestion is targeting primarily #5 on this
list. There are two problems with the possible solutions using that
parameter though:

-vacuum_defer_cleanup_age is set in a unit that people cannot be
expected to work in--transactions ids. The UI is essentially useless,
and there's no obvious way how to make a better one. The best you can do
will still be really fragile.

-If you increase vacuum_defer_cleanup_age, it's active all the time.
You're basically making every single transaction that could be cleaned
up pay for the fact that a query *might* be running on the standby it
needs to avoid.

You can think of the idea of passing an xmin back from the standby as
being like an auto-tuning vacuum_defer_cleanup_age. It's 0 when no
standby queries are running, but grows in size to match longer ones. And
you don't have to have to know anything to set it correctly; just toggle
on the proposed "feedback xid from the standby" feature and you're safe.

Expecting that anyone will ever set vacuum_defer_cleanup_age correctly
in the field in its current form is pretty unreasonable I think. Since
there's no timestamp-based memory of past xid activity, it's difficult
to convert it to that form instead, and I think something in terms of
time is what people would like to set this in.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us