Re: WORM and Read Only Tables (v0.1) - Mailing list pgsql-hackers

On Tue, 2007-12-11 at 14:25 -0500, Andrew Sullivan wrote:
> On Tue, Dec 11, 2007 at 11:12:46AM +0000, Simon Riggs wrote:
> > 
> > Read-Only Tables
> > ----------------
> 
> In the past when this topic came up, there was some discussion of doing this
> at a level somewhere below the table horizon.  There are a number of nasty
> limitations for partitions currently (not the least of which is that real
> uniqueness guarantees are impractical), so allowing users to specify some
> segment of the table to be "read only" without imposing it on the whole
> table would be awful nice.  I seem to recall Jan had an idea on how to do
> it, but I could be wrong.
> 
> Also, doing this at the tuple, rather than table-wide, level might lead to
> additional capabilities in this area:

Seems fair comment.

I'll move forward my thoughts on partitioning, so we can decide whether
to do things this way, or below the table horizon as you say. I've got
some detailed notes on this already which showed it was roughly balanced
between the two ways. I'll write it up so we can see; the answer might
change during the writing.

> > Attach
> > ------
> > 
> > Writing tables on one system and then moving that data to other systems
> > is fairly common. If we supported read-only tables then you might
> > consider how you would publish new versions to people.
> 
> Some time ago I was speculating on pie-in-the-sky features I might like in
> Postgres, and it was something like this attach.  But the idea was somehow
> related to the read-only tuples.
> 
> In my specific case, I have piles and piles of mostly useless data.
> Sometimes, however, some of that data is possibly useful in retrospect.  So
> the suggestion was to have tables that could be mostly offline -- archived
> somewhere -- but for which we had enough metadata online to say, "You have
> some data that might match in catalog C.  Go mount it, and I'll check."  I
> think this is subtly different from the attach case you're outlining?

Yes it is, but I had hoped that what you're asking for is catered for
here.

If you have a hierarchical storage manager, you can just call access the
file, whereupon the actual file will be de-archived to allow access. Or
maybe you have it on MAID storage, so we spin up the disks to allow
access to the files. So I guess I was expecting the de-archive to be
automated at the file system level.
http://en.wikipedia.org/wiki/Hierarchical_Storage_Management
Regrettably, I'm not aware of an open source HSM, though XFS has some
hooks in it that mention this. That's an old IBM term, so some people
might refer to this concept as tiered storage. 

Since I was planning to modify smgr to allow different kinds of
tablespaces it should be possible to make the file issue some kind of a
call out to mount the appropriate files. What would that call out look
like? Would that be a DMAPI/XDSM impelementation, or something simpler
as was used for PITR, or a roll-your-own plug-in hook?

I can see I'll have to re-wire smgr_nblocks() for non-md smgrs to access
pg_class.relpages rather than issue a seek, which will need to issue an
open. That way we can do planning without actually accessing the table.
(Maybe that's presuming we dealing with tables, oh well).

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com