Re: Visibility Groups

On Thu, 2008-08-07 at 14:18 +0100, Richard Huxton wrote:
> Simon Riggs wrote:
> > On Thu, 2008-08-07 at 12:55 +0200, Jochem van Dieten wrote:
> >> On Thu, Aug 7, 2008 at 12:38 PM, Simon Riggs wrote:
> >>> I propose creating "Visibility Groups" that *explicitly* limit the
> >>> ability of a transaction to access data outside its visibility group(s).
> >> Doesn't every transaction need to access data from the catalogs?
> >> Wouldn't the inclusion of a catalogs visibility group in every
> >> transaction negate any potential benefits?
> >
> > True, but I don't see the catalogs as frequently updated data. The
> > objective is to isolate frequently updated tables from long running
> > statements that don't need to access them.
> >
> > Tables can be in multiple visibility groups, perhaps that wasn't clear.
> > When we seek to vacuum a table, we take the lowest xmin of any group it
> > was in when we took snapshot.
>
> I'm not sure if "visibility group" is the best name for this - I had to
> go away and think through what you meant about that last bit. Have I got
> this right?
>
> So - a "visibility group" is attached to a transaction.

Perhaps visibility_scope might be better name. See below.

> My long-running transaction T0 can restrict itself to <catalogues> and
> table "event_log".
>
> Various other transactions T1..Tn make no promises about what they are
> going to access. They all share the "null visibility group".

OK, good example.

> A table "user_emails" is in the "null visibility group" and can be
> vacuumed based on whatever the lowest xid of T1..Tn is.
>
> Table "event_log" is in both groups and can only be vacuumed based on
> T0..Tn (presumably T0 is the oldest, since that's the point of the
> exercise).
>
> An attempt to write to user_emails by T0 will fail with an error.

All above correct

The point of doing this is that *if* T0 becomes the oldest transaction
it will *not* interfere with removal of rows on "user_emails".

> An attempt to read from user_emails by T0 will be allowed?

No, reads must also be excluded otherwise MVCC will be violated.

> What happens if I'm in ISOLATION LEVEL SERIALIZABLE? Presumably the read
> is disallowed then too?

No, that's not relevant. That is your choice about how often you update
your snapshot of the database. The visibility group refers to the
*scope* of the snapshot, so the two things are orthogonal.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support