Jim Nasby <Jim.Nasby@bluetreble.com> writes: > On 2/15/15 10:36 AM, Tom Lane wrote: >> I wonder if we couldn't achieve largely the same positive effects through >> adding a simple transaction-level timeout option.
> A common use-case is long-running reports hitting relatively stable data > in a database that also has tables with a high churn rate (ie: queue > tables). In those scenarios your only options right now are to suffer > huge amounts of bloat in the high-churn or not do your reporting. A > simple transaction timeout only "solves" this by denying you reporting > queries.
Agreed, but Kevin's proposal has exactly the same problem only worse, because (a) the reporting transactions might or might not fail (per Murphy, they'll fail consistently only when you're on deadline), and (b) if they do fail, there's nothing you can do short of increasing the slack db-wide.
> An idea that I've had on this would be some way to "lock down" the > tables that a long-running transaction could access. That would allow > vacuum to ignore any snapshots that transaction had for tables it wasn't > accessing. That's something that would be deterministic.
There might be something in that, but again it's not much like this patch. The key point I think we're both making is that nondeterministic failures are bad,
Based on my experience, a long transaction which is caused by a long-running
query is not so many. A common case I met is user start a transaction
but forget it to close it, I am thinking if we can do something for this situation.
Since most users will use Read Committed isolation level, so after a user
completes a query, the next query will use a fresh new snapshot, so there is no
need to block the oldest xmin because of this. will it be correct to advance the oldest
xmin in this case? If yes, what would be the blocker in implementing this feature?
