>>> 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