Re: Using ALTER TABLESPACE in pg_dump

At 03:15 AM 19/10/2004, Bruce Momjian wrote:
>The only downside is that it prevents SQL-compliant CREATE syntax in
>dumps.

One idea that may be worth considering: we currently dump a complete SQL 
statement including a TABLESPACE clause, which makes it hard to allow 
pg_restore to have a 'no tablespaces' option (it is fixed by pg_dump).

To solve this, we should dump the table definition as a format string and 
dump the tablespace clause (ie. ' TABLESPACE ts') as a separate part of the 
table definition TOC entry. If the user wants the tablespace to be dumped, 
then we substitute the tablespace clause, otherwise a blank string. This 
could be a useful general approach in the future.

The main issue will be quoting the clause identifiers (if we use 
'%%tablespace%%' then we have to handle columns or tables whose names 
contain '%%tablespace%%'. Simplest would be to backslash quote all '%' and 
'\' then do subs.


Also, I like the option of a soft-tablespace option, but also liked the 
idea of the fake/logical/virtual tablespaces someone suggested earlier; if 
restoring into a database without a required tablespace, then create a 
virtual tablespace that points to pg_default.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                 |    --________--
PGP key available upon request,  |  /
and from pgp.mit.edu:11371       |/