Re: Multiple Storage per Tablespace, or Volumes

On Mon, 2007-02-19 at 17:35 -0300, Alvaro Herrera wrote:
> Andrew Sullivan wrote:
> > On Mon, Feb 19, 2007 at 10:33:24AM -0500, Tom Lane wrote:
> > > Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > > > Somehow this seems like implementing RAID within postgres,
> > >
> > > RAID and LVM too. I can't get excited about re-inventing those wheels
> > > when perfectly good implementations already exist for us to sit on top of.
> >
> > Ok, warning, this is a "you know what would be sweet" moment.
> >
> > What would be nice is to be able to detach one of the volumes, and
> > know the span of the data in there without being able to access the
> > data.
> >
> > The problem that a lot of warehouse operators have is something like
> > this: "We know we have all this data, but we don't know what we will
> > want to do with it later. So keep it all. I'll get back to you when
> > I want to know something."
>
> You should be able to do that with tablespaces and VACUUM FREEZE, the
> point of the latter being that you can take the disk containing the
> "read only" data offline, and still have the data readable after
> plugging it back in, no matter how far along the transaction ID counter
> is.

Doesn't work anymore because VACUUM FREEZE doesn't (and can't) take a
full table lock, so somebody can always update data after a data block
has been frozen. That can lead to putting a table onto read-only media
when it still needs vacuuming, which is a great way to break the DB. It
also doesn't freeze still visible data, so there's no easy way of doing
this. Waiting until the VF is the oldest Xid is prone to deadlock as
well.

Ideally, we'd have a copy to read-only media whilst freezing, as an
atomic operation, with some guarantees that it will actually have frozen
*everything*, or fail:

ALTER TABLE SET TABLESPACE foo READONLY;

Can we agree that as a TODO item?

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com