Re: Allow "snapshot too old" error, to prevent bloat

On Wed, Feb 18, 2015 at 4:57 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> >> But max_standby_streaming_delay, max_standby_archive_delay and
> >> hot_standby_feedback are among the most frequent triggers for
> >> questions and complaints that I/we see.
> >>
> > Agreed.
> > And a really bad one used to be vacuum_defer_cleanup_age, because
> > of confusing units amongst other things. Which in terms seems
> > fairly close to Kevins suggestions, unfortunately.
>
> Particularly my initial suggestion, which was to base snapshot
> "age" it on the number of transaction IDs assigned. Does this look
> any better to you if it is something that can be set to '20min' or
> '1h'? Just to restate, that would not automatically cancel the
> snapshots past that age; it would allow vacuum of any tuples which
> became "dead" that long ago, and would cause a "snapshot too old"
> message for any read of a page modified more than that long ago
> using a snapshot which was older than that.
>
>
I like this thought. One of the first things I do in a new Pg environment
is setup a cronjob that watches pg_stat_activity and terminates most
backends over N minutes in age (about 5x the length of normal work) with an
exception for a handful of accounts doing backups and other maintenance
operations. This prevents a stuck client from jamming up the database.

Would pg_dump be able to opt-out of such a restriction?

regards,

Rod