Re: Using ALTER TABLESPACE in pg_dump

On Tue, 2004-10-19 at 21:06, Philip Warner wrote:
> At 04:20 AM 20/10/2004, Tom Lane wrote:
> >Nope. I can break that trivially, eg:
>
> Thats why in my first message I mentioned escaping and unescaping all '%'
> in the deinition.
>
>
> >There's also the nontrivial matter of how pg_dump would decide where to
> >insert the %%tablespace%% string into the CREATE INDEX command in the
> >first place.
>
> I'd vote against parsing, and add a parameter to get_indexdef.
>
>
> >If we're going to add code to parse CREATE INDEX and
> >insert the tablespace in the correct place, meseems it'd be better to
> >insert it on the pg_restore side.
>
> But if we have to parse, I'd add it in pg_dump so all items that are
> relevant can be dumped with '%%tablespace%%'. pg_dump still constructs
> CREATE TABLE statements, so that is the natural place to add the tablespace
> marker and avoid parsing for tables.
>

I've been thinking of an alternative solution that sounds very similar
to this. The idea is to output the CREATE TABLESPACE commands inside
pg_dump to guarantee that all tablespaces used by schema objects would
exist. A couple trouble scenarios would be
1) tablespace already exists - this causes the CREATE TABLESPACE command
to fail, but since we no longer stop on error during restore, the
restore can continue and subsequent object creation should be fine.
2) if you have to restore on a machine with a different disk layout,
give pg_restore a --override-tablespace command, which would substitute
pg_default tablespace into the creation command of any tablespaces that
get passed in. The bonus is that we would only have to parse on one
specific command rather than worry about parsing several different
commands. This would allow the tablespace to exist, so any subsequent
commands referring to it would not fail.

** update **

While writing up this email I tried to explain the idea to Kris Jurka on
irc, and may have found fatal flaw... I was thinking that you could
create two logical tablespaces on the same physical directory. So that
tablespace fred and wilma could both be at the same location as
pg_default, but according to the docs I'm not sure this is true:

"The directory that will be used for the tablespace. The directory must
be empty and must be owned by the PostgreSQL system user. The directory
must be specified by an absolute path name."

OTOH looking at a copy of an 8.0 database I see the following:

template1=# select * from pg_tablespace;
spcname | spcowner | spclocation | spcacl
------------+----------+-------------+--------
pg_default | 1 | |
pg_global | 1 | |
(2 rows)

so istm there is nothing preventing pg_tablespace from having multiple
spcname using the same spclocation, however the create tablespace
command certainly wont allow it as it stands now. I'm not entirely sure
why we couldn't allow CREATE TABLESPACE to accept a variable of
pg_default which mean to just create the tablespace logically but use
the same physical location of pg_default... or possibly some other
workable solution... istm the idea is 90% of the way there, perhaps
someone can see the last few parts?

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL