Re: Large Object Location in 7.3

I expect (actually hope) to have thousands and thousands of blob/clobs
in the db I am designing.
I would like such largeobjects to be stored in their own file system.
Someone had said that there
might be support for "tablespaces/locations" in 7.3. Is there a
description somewhere of this work
or a "spec"?

Thanks

Richard

Richard Emberson wrote:
>
> I expect (actually hope) to have thousands and thousands of blob/clobs
> in the db I am designing.
> I would like such largeobjects to be stored in their own file system.
> Someone had said that there
> might be support for "tablespaces/locations" in 7.3. Is there a
> description somewhere of this work
> or a "spec"?

Sure, find the oid of pg_largeobject and symlink that to another file
system. You need to do that toast table and any indexes for the table
too.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:

> Richard Emberson wrote:
> >
> > I expect (actually hope) to have thousands and thousands of blob/clobs
> > in the db I am designing.
> > I would like such largeobjects to be stored in their own file system.
> > Someone had said that there
> > might be support for "tablespaces/locations" in 7.3. Is there a
> > description somewhere of this work
> > or a "spec"?
>
> Sure, find the oid of pg_largeobject and symlink that to another file
> system. You need to do that toast table and any indexes for the table
> too.
>

Can this be done within a PL/pgsql function or does one have to stop the
database (everytime a
user enters a new blob) and do it by hand or external script?

Also, this involves copying the blob (largeobject file) to the other file
system which means that
that the file was first created and written, and then had to be copied.
Twice the work. Is there a way
so that it only has to be written once?

>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Richard Emberson wrote:
> Bruce Momjian wrote:
>
> > Richard Emberson wrote:
> > >
> > > I expect (actually hope) to have thousands and thousands of blob/clobs
> > > in the db I am designing.
> > > I would like such largeobjects to be stored in their own file system.
> > > Someone had said that there
> > > might be support for "tablespaces/locations" in 7.3. Is there a
> > > description somewhere of this work
> > > or a "spec"?
> >
> > Sure, find the oid of pg_largeobject and symlink that to another file
> > system. You need to do that toast table and any indexes for the table
> > too.
> >
>
> Can this be done within a PL/pgsql function or does one have to stop the
> database (everytime a
> user enters a new blob) and do it by hand or external script?

pg_largeobject contains all large objects, at least in 7.1 and earlier.
Don't remember about 7.0. Just has to be done once per database, with
system down.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Richard Emberson wrote:
>> I expect (actually hope) to have thousands and thousands of blob/clobs
>> in the db I am designing.
>> I would like such largeobjects to be stored in their own file system.

> Sure, find the oid of pg_largeobject and symlink that to another file
> system. You need to do that toast table and any indexes for the table
> too.

If Richard's envisioning more than 1GB of large objects, I don't think
he's going to be very satisfied with manual symlinking.

This does bring up an interesting point: the tablespace schemes we've
discussed so far don't allow system catalogs to be moved out of the
default tablespace for a database. That doesn't bother me for most
of the system catalogs ... but pg_largeobject seems like it might be
an exception.

regards, tom lane

Tom Lane wrote:

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Richard Emberson wrote:
> >> I expect (actually hope) to have thousands and thousands of blob/clobs
> >> in the db I am designing.
> >> I would like such largeobjects to be stored in their own file system.
>
> > Sure, find the oid of pg_largeobject and symlink that to another file
> > system. You need to do that toast table and any indexes for the table
> > too.
>
> If Richard's envisioning more than 1GB of large objects, I don't think
> he's going to be very satisfied with manual symlinking.

The system I am designing it is hoped will have 100s of GBs of large
objects, a whole
coda file system full of them. I believe that I can partition the DB into
subsets (multiple
instance of postgresql) so that each instance might have only 10GBs.

>
>
> This does bring up an interesting point: the tablespace schemes we've
> discussed so far don't allow system catalogs to be moved out of the
> default tablespace for a database. That doesn't bother me for most
> of the system catalogs ... but pg_largeobject seems like it might be
> an exception.
>
> regards, tom lane