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 |/