>>> Simon Riggs wrote: > Currently, we calculate a single OldestXmin across all snapshots on the > assumption that any transaction might access any table. > > I propose creating "Visibility Groups" that *explicitly* limit the > ability of a transaction to access data outside its visibility group(s). > By default, visibility_groups would be NULL, implying potential access > to all tables. > > Once set, any attempt to lock an object outside of a transactions > defined visibility_groups will result in an error: > ERROR attempt to lock table outside of visibility group(s): foo > HINT you need to set a different value for visibility_groups > A transaction can only ever reduce or restrict its visibility_groups, it > cannot reset or add visibility groups. > > This would give us the ability to explicitly prevent long running > statements from interfering with updates of critical tables, when those > tables are not accessed. I assume this means that tables outside of all transactions' visibility groups could be vacuumed? This is something which I would rarely use, but it might have been useful just this week. Tom wanted an EXPLAIN ANALYZE of a query which is very long running, but only SELECTs from two tables, and updates nothing. While the costing, if consistent with other queries on the box, suggest that the query would run for years, I was willing to give it a week or two to run just to see if it would finish in that time, but that would have left the database horribly bloated across many tables. As it is a standby machine which might need to be brought into production at any time, that wasn't feasible. This would have let me make the try. > We might like the visibility group to be set automatically but that > seems like a harder problem. I do not propose to solve that here. It certainly seems hard (impossible?) for the general case, but when there is an implicit transaction, which is guaranteed to be a single statement, it seems like an implicit visibility group might not be out of the question, and that might help in a lot of situations. -Kevin
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers