Re: Allow "snapshot too old" error, to prevent bloat - Mailing list pgsql-hackers

<p dir="ltr">On Feb 19, 2015 10:31 PM, "Kevin Grittner" <<a
href="mailto:kgrittn@ymail.com">kgrittn@ymail.com</a>>wrote:<br /> > > What about having the long running
snapshotsdeclare their working<br /> > > set, and then only take them into account for global xmin for<br /> >
>relations that are in the working set? Like a SET TRANSACTION WORKING<br /> > > SET command. This way the
erroris deterministic, vacuum on the high<br /> > > churn tables doesn't have to wait for the old transaction
delayto<br /> > > expire and we avoid a hard to tune GUC (what do I need to set<br /> > >
old_snapshot_thresholdto, to reduce bloat while not having "normal"<br /> > > transactions abort).<br /> ><br
/>> Let me make sure I understand your suggestion.  You are suggesting<br /> > that within a transaction you can
declarea list of tables which<br /> > should get the "snapshot too old" error (or something like it) if a<br /> >
pageis read which was modified after the snapshot was taken?<br /> > Snapshots within that transaction would not
constrainthe effective<br /> > global xmin for purposes of vacuuming those particular tables?<p dir="ltr">Sorry, I
shouldhave been clearer. I'm proposing that a transaction can declare what tables it will access. After that the
transactionwill constrain xmin for only those tables. Accessing any other table would give an error immediately.<p
dir="ltr">>If I'm understanding your proposal, that would help some of the<br /> > cases the "snapshot too old"
caseaddresses, but it would not<br /> > handle the accidental "idle in transaction" case (e.g., start a<br /> >
repeatableread transaction, run one query, then sit idle<br /> > indefinitely).  I don't think it would work quite
aswell for some<br /> > of the other cases I've seen, but perhaps it could be made to fit<br /> > if we could
figureout the accidental "idle in transaction" case.<p dir="ltr">Accidental idle in transaction seems better handled by
justterminating transactions older than some timeout. This is already doable externally, but an integrated solution
wouldbe nice if we could figure out how the permissions for setting such a timeout work.<p dir="ltr">> I also think
itmight be hard to develop a correct global xmin for<br /> > vacuuming a particular table with that solution.  How
doyou see<br /> > that working?<p dir="ltr">I'm imagining the long running transaction would register it's xmin in a
sharedmap keyed by relation for each referenced relation and remove the xmin from procarray. Vacuum would access this
mapby relation, determine the minimum and use that if it's earlier than the global xmin. I'm being deliberately vague
hereabout the datastructure in shared memory as I don't have a great idea what to use there. It's somewhat similar to
thelock table in that in theory the size is unbounded, but in practice it's expected to be relatively tiny.<p
dir="ltr">Regards,<br/> Ants Aasma<br />