Bruce Momjian writes:
One additional idea for this item is to use CREATE to first create the
object, then move it using ALTER, and the ALTER might fail if the
tablespace doesn't exist.
This seems fairly impractical, at least for indexes where there is no
way to do the ALTER before the object is filled with data.
If we add a new SET variable and use it in pg_dump we will have to
support it forever even if there is no practical use for it.
Yeah, that's one thing that bothers me.
One interesting side-affect of allowing tablespace specification to fail
is that it might give users enough control that we can mark this item as
done:
Hmm, here's a variant idea: how about a GUC variable named something like
"soft_tablespace_specs" which when TRUE would mean that a nonexistent
tablespace name in a TABLESPACE clause is ignored (maybe with a WARNING)
rather than being an error, and so the object is created in whatever the
default tablespace for it would be. You wouldn't even necessarily want
to have pg_dump set this true for itself, but people could turn it on
when they needed to load a dump with wrong tablespace names in it.
(If we didn't have pg_dump turn it on automatically, then we'd not be
beholden to support it forever.)

regards, tom lane

Search Discussions

  • Bruce Momjian at Oct 18, 2004 at 5:16 pm

    Tom Lane wrote:
    Bruce Momjian <pgman@candle.pha.pa.us> writes:
    One additional idea for this item is to use CREATE to first create the
    object, then move it using ALTER, and the ALTER might fail if the
    tablespace doesn't exist.
    This seems fairly impractical, at least for indexes where there is no
    way to do the ALTER before the object is filled with data.
    If we add a new SET variable and use it in pg_dump we will have to
    support it forever even if there is no practical use for it.
    Yeah, that's one thing that bothers me.
    One interesting side-affect of allowing tablespace specification to fail
    is that it might give users enough control that we can mark this item as
    done:
    Hmm, here's a variant idea: how about a GUC variable named something like
    "soft_tablespace_specs" which when TRUE would mean that a nonexistent
    tablespace name in a TABLESPACE clause is ignored (maybe with a WARNING)
    rather than being an error, and so the object is created in whatever the
    default tablespace for it would be. You wouldn't even necessarily want
    to have pg_dump set this true for itself, but people could turn it on
    when they needed to load a dump with wrong tablespace names in it.
    (If we didn't have pg_dump turn it on automatically, then we'd not be
    beholden to support it forever.)
    That's a nice idea in that it doesn't require a SET for every object
    that uses tablespace, and allows user control over tablespace failure.

    The only downside is that it prevents SQL-compliant CREATE syntax in
    dumps. I was thinking you could use ALTER just for tables but then the
    tablespace failure rules would be different for tables and other
    objects, which is unacceptable.

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Philip Warner at Oct 19, 2004 at 12:51 am

    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 |/
  • Tom Lane at Oct 19, 2004 at 5:06 pm

    Philip Warner writes:
    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.
    I think the tricky part of that would be inserting the tablespace clause
    in the right place; for CREATE INDEX this seems to require nontrivial
    parsing. (Both the index column definitions and the WHERE clause could
    be arbitrarily complicated expressions.) If we can get around that part
    then this wouldn't be too hard.
    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.
    Given that tablespaces are fundamentally only directories, there isn't
    any particularly strong reason to not just make a real tablespace. You
    aren't going to constrain space allocation or anything by having another
    directory in/alongside $PGDATA. So I think the "virtual tablespace"
    idea is basically pointless.

    The real crux of all this, I think, is "what if I want to restore as
    a non-superuser, and so I don't have privilege to create tablespaces
    to match what the dump wants?" The soft-failure option provides an
    answer here, but creating either real or virtual tablespaces wouldn't
    fly. A "--notablespace" option in pg_restore would solve it too, but
    only if you'd done an -Fc or -Ft dump; with a plain text dump you
    still got trouble.

    regards, tom lane
  • Philip Warner at Oct 19, 2004 at 5:19 pm

    At 03:06 AM 20/10/2004, Tom Lane wrote:
    I think the tricky part of that would be inserting the tablespace clause
    in the right place; for CREATE INDEX this seems to require nontrivial
    parsing. (Both the index column definitions and the WHERE clause could
    be arbitrarily complicated expressions.) If we can get around that part
    then this wouldn't be too hard.
    I may be missing something here; I was assuming that pg_dump would dump
    would build the CREATE INDEX/TABLE/etc commands with the %%tablespace%%
    already embedded. pg_restore would not need to do any parsing. Or is there
    something I don't understand?





    ----------------------------------------------------------------
    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 |/
  • Tom Lane at Oct 19, 2004 at 5:25 pm

    Philip Warner writes:
    At 03:06 AM 20/10/2004, Tom Lane wrote:
    I think the tricky part of that would be inserting the tablespace clause
    in the right place; for CREATE INDEX this seems to require nontrivial
    parsing. (Both the index column definitions and the WHERE clause could
    be arbitrarily complicated expressions.) If we can get around that part
    then this wouldn't be too hard.
    I may be missing something here; I was assuming that pg_dump would dump
    would build the CREATE INDEX/TABLE/etc commands with the %%tablespace%%
    already embedded. pg_restore would not need to do any parsing. Or is there
    something I don't understand?
    Maybe there's something I don't understand. How are you expecting
    pg_restore to control whether it outputs the command with a TABLESPACE
    clause embedded or not, if pg_dump has already built the command string
    that way? I thought you were envisioning that pg_restore would insert,
    or not insert, a TABLESPACE clause into a command that didn't initially
    have one.

    regards, tom lane
  • Philip Warner at Oct 19, 2004 at 6:06 pm

    At 03:25 AM 20/10/2004, Tom Lane wrote:
    Maybe there's something I don't understand. How are you expecting
    pg_restore to control whether it outputs the command with a TABLESPACE
    clause embedded or not, if pg_dump has already built the command string
    that way?
    This will only work if we modify the dump format (a new version) of
    dump/restore; the TOC entry for a table would have:

    DEFINITION: CREATE TABLE fred ... %%tablespace%% ...
    TABLESPACE: ' TABLESPACE t'

    pg_restore would read these, and use the settings from the command line to
    either substitute an empty string or the TABLESPACE text for %%tablespace%%
    in the DEFINTION.

    Same would apply for indexes etc.




    ----------------------------------------------------------------
    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 |/
  • Tom Lane at Oct 19, 2004 at 6:20 pm

    Philip Warner writes:
    DEFINITION: CREATE TABLE fred ... %%tablespace%% ...
    TABLESPACE: ' TABLESPACE t'
    pg_restore would read these, and use the settings from the command line to
    either substitute an empty string or the TABLESPACE text for %%tablespace%%
    in the DEFINTION.
    Nope. I can break that trivially, eg:

    CREATE INDEX fooi ON foo (f1) WHERE upper(f1) < ' %%tablespace%%';

    Not very probable, maybe, but you can't just do a blind sed-style
    substitution.

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

    regards, tom lane
  • Philip Warner at Oct 20, 2004 at 1:07 am

    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.




    ----------------------------------------------------------------
    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 |/
  • Robert Treat at Oct 20, 2004 at 7:42 pm

    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
  • Philip Warner at Oct 21, 2004 at 1:08 am

    At 05:41 AM 21/10/2004, Robert Treat wrote:
    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....
    ...
    1) tablespace already exists
    to fail, but since we no longer stop on error during restore,
    <soapbox>
    A fact I positively loath! Relying on the 'bluder-on-regardless' approach
    is not something I'd like to enshrine.
    </soapbox>

    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. ...
    I'm still inclined to avoid any parsing if at all possible. We should hit
    the code that generates the definitions (90% in pg_dump) and turn the
    definitions into more intelligent templates.


    ... I was thinking that you could
    create two logical tablespaces on the same physical directory.
    This is basically the virtual/fake tablespace idea.


    Sometimes I think it is worth stepping back from a problem and ask what
    would we do if we had a clean slate, then use that to inform our current
    set of decisions. I'd be very interested in other people's ideas, but my
    thinking is:

    - we might not have a tablespace clause inside object definitions; we could
    add a STORE TABLE xxx IN TABLESPACE ts statement. Not really practical
    now; but worth bearing in mind as an approach for future non-standard syntax.
    - we'd have a full set of ALTER xxx SET TABLESPACE commands which moved
    relevant data etc. We have most of them.
    - pg_dump would issue the alter commands after creating the object; OK, it
    moves files, not very efficient, but using a 'SET MAGIC_TABLESPACE_VAR' has
    previously been discounted as a solution.

    *If* you accept this as a good approach in an ideal world, then I think we
    need to ask ourselves if we should implement the remaining ALTER commands
    in 8.0 and be done with it.

    The other solutions: magic-tablespace-var, virtual-tablespaces...all seem
    to add clunky functionality that will only be used in pg_dump. If we're
    going to add something, I'd prefer not to add clutter.




    ----------------------------------------------------------------
    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 |/
  • Bruce Momjian at Oct 25, 2004 at 2:39 am
    I have read through this thread hoping that a solution would be found
    but I see we are still poking. My ideas:

    o Anything that works only for pg_restore and hence doesn't
    work for ASCII dumps isn't an acceptable solution
    o Creating the tablespaces before the dump is restored is
    a good solution for moving tablespaces, but as Tom pointed
    out, it doesn't work well for non-super-user restores
    o Moving the indexes can't be dont easily after they are
    created because they are not zero-length files
    o The soft-failure GUC option for non-existant tablespaces
    is a hack just for use by pg_dump. It doesn't fix the
    problem that the tablespace clause makes the SQL nonstandard.

    And the best quote from the thread:

    Philip Warner wrote:
    <soapbox>
    A fact I positively loath! Relying on the 'bluder-on-regardless' approach
    is not something I'd like to enshrine.
    </soapbox>
    The 'bluder-on-regardless' phrase is very funny.

    ---------------------------------------------------------------------------

    Philip Warner wrote:
    At 05:41 AM 21/10/2004, Robert Treat wrote:

    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....
    ...
    1) tablespace already exists
    to fail, but since we no longer stop on error during restore,
    <soapbox>
    A fact I positively loath! Relying on the 'bluder-on-regardless' approach
    is not something I'd like to enshrine.
    </soapbox>

    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. ...
    I'm still inclined to avoid any parsing if at all possible. We should hit
    the code that generates the definitions (90% in pg_dump) and turn the
    definitions into more intelligent templates.


    ... I was thinking that you could
    create two logical tablespaces on the same physical directory.
    This is basically the virtual/fake tablespace idea.


    Sometimes I think it is worth stepping back from a problem and ask what
    would we do if we had a clean slate, then use that to inform our current
    set of decisions. I'd be very interested in other people's ideas, but my
    thinking is:

    - we might not have a tablespace clause inside object definitions; we could
    add a STORE TABLE xxx IN TABLESPACE ts statement. Not really practical
    now; but worth bearing in mind as an approach for future non-standard syntax.
    - we'd have a full set of ALTER xxx SET TABLESPACE commands which moved
    relevant data etc. We have most of them.
    - pg_dump would issue the alter commands after creating the object; OK, it
    moves files, not very efficient, but using a 'SET MAGIC_TABLESPACE_VAR' has
    previously been discounted as a solution.

    *If* you accept this as a good approach in an ideal world, then I think we
    need to ask ourselves if we should implement the remaining ALTER commands
    in 8.0 and be done with it.

    The other solutions: magic-tablespace-var, virtual-tablespaces...all seem
    to add clunky functionality that will only be used in pg_dump. If we're
    going to add something, I'd prefer not to add clutter.




    ----------------------------------------------------------------
    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 |/
    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Philip Warner at Oct 25, 2004 at 3:29 am

    At 12:38 PM 25/10/2004, Bruce Momjian wrote:

    o Anything that works only for pg_restore and hence doesn't
    work for ASCII dumps isn't an acceptable solution
    Agree; but don't forget that an ascii dump is implemented almost
    identically to "pg_dump | pg_restore", so when I refer to using pg_restore
    in this thread it almost certainly applies to ascii dumps as well. Eg.
    extra stuff in the TOC, and using the definition as a template *will*
    produce the requested output in ascii dumps.

    o Creating the tablespaces before the dump is restored is
    a good solution for moving tablespaces, but as Tom pointed
    out, it doesn't work well for non-super-user restores
    And for users who want to create a single database with no extra
    tablespaces (eg. development version vs. production instance).

    o Moving the indexes can't be dont easily after they are
    created because they are not zero-length files
    Pity.

    o The soft-failure GUC option for non-existant tablespaces
    is a hack just for use by pg_dump. It doesn't fix the
    problem that the tablespace clause makes the SQL nonstandard.
    If we can adopt the move-after-create solution, then we really only have
    two options:

    - virtual tablespaces (which do seem kind of useful, especially for
    development vs. production config where the local/personal dev version
    can use the same script as a production DB but not need half a dozen TSs)

    - magic-tablespace-var that behaves like the schema search path

    Are there any others?

    And the best quote from the thread:

    Philip Warner wrote:
    <soapbox>
    A fact I positively loath! Relying on the 'bluder-on-regardless' approach
    is not something I'd like to enshrine.
    </soapbox>
    The 'bluder-on-regardless' phrase is very funny.

    Fame at last! Even with the typo.




    ----------------------------------------------------------------
    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 |/
  • Bruce Momjian at Oct 25, 2004 at 2:29 pm

    Philip Warner wrote:
    If we can adopt the move-after-create solution, then we really only have
    two options:

    - virtual tablespaces (which do seem kind of useful, especially for
    development vs. production config where the local/personal dev version
    can use the same script as a production DB but not need half a dozen TSs)

    - magic-tablespace-var that behaves like the schema search path
    I was thinking we could have a var like schema search path that
    specifies where we try to create the object:

    SET tablespace_path = 'tblspc1, pg_default';
    CREATE TABLE test(x int);

    This combines the idea of pulling the TABLESPACE specification out of
    the CREATE, and allows a fallback if the primary tablespace doesn't
    exist.

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Tom Lane at Oct 25, 2004 at 2:34 pm

    Bruce Momjian writes:
    I was thinking we could have a var like schema search path that
    specifies where we try to create the object:
    SET tablespace_path = 'tblspc1, pg_default';
    CREATE TABLE test(x int);
    This combines the idea of pulling the TABLESPACE specification out of
    the CREATE, and allows a fallback if the primary tablespace doesn't
    exist.
    ... and takes us even further away from the notion that the default
    tablespace is determined by the parent object (database or schema).

    I think that we have a clean, understandable, easy-to-use tablespace
    behavior now, and we should not muck it up for abstract second-order
    goals like having portable dumps for databases that were created
    unportably in the first place.

    regards, tom lane
  • Fabien COELHO at Oct 25, 2004 at 4:18 pm
    Dear Tom,
    [...]
    This combines the idea of pulling the TABLESPACE specification out of
    the CREATE, and allows a fallback if the primary tablespace doesn't
    exist.
    ... and takes us even further away from the notion that the default
    tablespace is determined by the parent object (database or schema).

    I think that we have a clean, understandable, easy-to-use tablespace
    behavior now, and we should not muck it up for abstract second-order
    goals like having portable dumps for databases that were created
    unportably in the first place.
    I disagree on the view that being able to restore a database on another
    machine after a crash is an "abstract second-order goal";-)

    ISTM that the core business of a database is to help organize and protect
    data, and it is plainly that. You just wish you won't need it, so it is
    somehow "abstract", but when and if you need it, it is not "second-order"
    at all;-) and it is much too late to redo the dump.

    When a machine crashes, usually I did not foresee how it will crash, and
    whether I will or will not be able to restore on the same machine, with or
    without the same tablespaces... It depends on what went wrong.

    Thus ISTM that having the ability to fix that at restore time is simply
    what is needed, when it is needed.

    Now I do agree that having a straight behavior is a much better thing.

    The "ALTER ... TABLESPACE ..." generated by restore from some headers
    seems the right simple solution to me, but the alter syntax is not fully
    implemented AFAICR:-(

    Completing the implementation for the missing parts (ALTER DATABASE... and
    ALTER SCHEMA... ?), feature/beta freeze or not, would seem the reasonnable
    path to me.

    I'm sorry I don't have time to develop and submit a patch...

    Have a nice day,

    --
    Fabien Coelho - coelho@cri.ensmp.fr
  • Tom Lane at Oct 25, 2004 at 4:53 pm

    Fabien COELHO writes:
    I disagree on the view that being able to restore a database on another
    machine after a crash is an "abstract second-order goal";-)
    ISTM that the core business of a database is to help organize and protect
    data, and it is plainly that. You just wish you won't need it, so it is
    somehow "abstract", but when and if you need it, it is not "second-order"
    at all;-) and it is much too late to redo the dump.
    So you create some tablespaces by hand. Big deal. This objection is
    not strong enough to justify an ugly, klugy definition for where tables
    get created.

    If tablespaces had to be associated with physically distinct devices
    then there would be merit in your concerns, but they are only
    directories and so there is no reason that you cannot create the same
    set of tablespace names on your new machine that you had on your old.

    regards, tom lane
  • Bruce Momjian at Oct 25, 2004 at 9:44 pm

    Tom Lane wrote:
    Fabien COELHO <coelho@cri.ensmp.fr> writes:
    I disagree on the view that being able to restore a database on another
    machine after a crash is an "abstract second-order goal";-)
    ISTM that the core business of a database is to help organize and protect
    data, and it is plainly that. You just wish you won't need it, so it is
    somehow "abstract", but when and if you need it, it is not "second-order"
    at all;-) and it is much too late to redo the dump.
    So you create some tablespaces by hand. Big deal. This objection is
    not strong enough to justify an ugly, klugy definition for where tables
    get created.

    If tablespaces had to be associated with physically distinct devices
    then there would be merit in your concerns, but they are only
    directories and so there is no reason that you cannot create the same
    set of tablespace names on your new machine that you had on your old.
    I am confused. I thought Tom's argument was that we shouldn't add an
    overly complex tablespace SET variable just to prevent the non-standard
    TABLESPACE in CREATE, which I can understand. However, the text above
    seems to indicate we don't need an 'ignore tablespace specification if
    it does not exist' which I think we do need for cases where we want to
    restore on to a system that doesn't use tablespaces or for
    non-super-user restores.

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Tom Lane at Oct 25, 2004 at 10:01 pm

    Bruce Momjian writes:
    I am confused. I thought Tom's argument was that we shouldn't add an
    overly complex tablespace SET variable just to prevent the non-standard
    TABLESPACE in CREATE, which I can understand. However, the text above
    seems to indicate we don't need an 'ignore tablespace specification if
    it does not exist' which I think we do need for cases where we want to
    restore on to a system that doesn't use tablespaces or for
    non-super-user restores.
    I'm willing to live with a "soft error" type of GUC variable for those
    cases. I don't want a GUC variable that actively changes the default
    tablespace; at least not unless you want to abandon the current
    mechanisms for default tablespace choices entirely, and go over to
    making the GUC variable be the sole arbiter. (Which would be consistent
    with the way we handle selection of which schema to create in, so I'm
    not necessarily against it.) I guess what I'm trying to say is I don't
    want a hodgepodge design, because I think it'll be confusing and
    unusable.

    regards, tom lane
  • Bruce Momjian at Oct 25, 2004 at 10:14 pm

    Tom Lane wrote:
    Bruce Momjian <pgman@candle.pha.pa.us> writes:
    I am confused. I thought Tom's argument was that we shouldn't add an
    overly complex tablespace SET variable just to prevent the non-standard
    TABLESPACE in CREATE, which I can understand. However, the text above
    seems to indicate we don't need an 'ignore tablespace specification if
    it does not exist' which I think we do need for cases where we want to
    restore on to a system that doesn't use tablespaces or for
    non-super-user restores.
    I'm willing to live with a "soft error" type of GUC variable for those
    cases. I don't want a GUC variable that actively changes the default
    tablespace; at least not unless you want to abandon the current
    mechanisms for default tablespace choices entirely, and go over to
    making the GUC variable be the sole arbiter. (Which would be consistent
    with the way we handle selection of which schema to create in, so I'm
    not necessarily against it.) I guess what I'm trying to say is I don't
    want a hodgepodge design, because I think it'll be confusing and
    unusable.
    Agreed. My tablespace path idea would be very hard to understand if
    combined with the existing db/schema/table default rules. I can't
    decide which is the best approach. Don't indexes default to the schema
    of the table rather than the schema path, so they aren't 100% controlled
    by the search path?

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Philip Warner at Oct 25, 2004 at 11:15 pm

    At 08:00 AM 26/10/2004, Tom Lane wrote:
    I don't want a GUC variable that actively changes the default
    tablespace; at least not unless you want to abandon the current
    mechanisms for default tablespace choices entirely, and go over to
    making the GUC variable be the sole arbiter.
    Something consistent with Schemas does sound good to me; a tablespace
    search path (or just single default), and support for a TABLESPACE clause
    on table and INDEX definitions would be good.

    For the three largest databases I work on, the namespace/schema that a
    table resides in is irrelevant to the tablespace that it should be stored
    in. So default tablespaces on the schema are a bit of a pointless feature.
    The ability to have the features of schemas: default tablespace for given
    users, a GUC variable, and ACLs on tablespaces would be far more valuable.




    ----------------------------------------------------------------
    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 |/
  • Tom Lane at Oct 25, 2004 at 11:29 pm

    Philip Warner writes:
    At 08:00 AM 26/10/2004, Tom Lane wrote:
    I don't want a GUC variable that actively changes the default
    tablespace; at least not unless you want to abandon the current
    mechanisms for default tablespace choices entirely, and go over to
    making the GUC variable be the sole arbiter.
    Something consistent with Schemas does sound good to me; a tablespace
    search path (or just single default), and support for a TABLESPACE clause
    on table and INDEX definitions would be good.
    I can't see what a search path would be good for.
    For the three largest databases I work on, the namespace/schema that a
    table resides in is irrelevant to the tablespace that it should be stored
    in. So default tablespaces on the schema are a bit of a pointless feature.
    The ability to have the features of schemas: default tablespace for given
    users, a GUC variable, and ACLs on tablespaces would be far more valuable.
    Another nice thing is that not having default tablespaces associated
    with schemas eliminates that nasty issue about being able to drop such a
    tablespace while the schema is still there.

    It seems like we still need some notion of a database's schema, to put
    the system catalogs in, but perhaps that need not be the same as the
    default schema for user tables created in the database?

    I'd be willing to jump this way if we can work out the
    default-tablespace inconsistencies that Bruce has on the open items
    list. Does anyone want to draft a concrete proposal? It seems like the
    basic elements are:

    * A GUC variable named something like default_tablespace that
    controls which TS objects are created in when there's
    no explicit TABLESPACE clause. The factory default for this
    would of course be pg_default. Otherwise it's settable just
    like any other GUC var.

    * Get rid of TABLESPACE clause for CREATE SCHEMA, and
    pg_namespace.nsptablespace (ooops, another initdb).

    * Need to define exactly what TABLESPACE clause for a database
    controls; location of its catalogs of course, but anything else?

    * We could possibly say that a TABLESPACE clause attached to
    CREATE TABLE determines the default tablespace for indexes
    created by the same command; I'm not sure if this is a good
    idea, or if the indexes should go into default_tablespace
    absent a TABLESPACE clause attached directly to their defining
    constraints. We certainly want default_tablespace to control
    indexes created by separate commands, so there'd be some
    inconsistency if we do the former.

    regards, tom lane
  • Philip Warner at Oct 25, 2004 at 11:47 pm

    At 09:28 AM 26/10/2004, Tom Lane wrote:
    I can't see what a search path would be good for.
    Nothing at this stage.

    It seems like we still need some notion of a database's schema,
    Yes.

    I'd be willing to jump this way if we can work out the
    default-tablespace inconsistencies that Bruce has on the open items
    list.
    I'll have a look in the next 18 hours...

    * A GUC variable named something like default_tablespace that
    controls which TS objects are created in when there's
    no explicit TABLESPACE clause. The factory default for this
    would of course be pg_default. Otherwise it's settable just
    like any other GUC var.
    Agree.

    * Get rid of TABLESPACE clause for CREATE SCHEMA, and
    pg_namespace.nsptablespace (ooops, another initdb).
    Agree.

    * Need to define exactly what TABLESPACE clause for a database
    controls; location of its catalogs of course, but anything else?
    Nothing else would be my call; make it like the tablespace on tables.

    * We could possibly say that a TABLESPACE clause attached to
    CREATE TABLE determines the default tablespace for indexes
    created by the same command;
    This is a hard one. We need ALTER INDEX or STORE INDEX or whatever if we
    can't nicely put each index in it's own tablespace. We're only talking PKs
    aren't we? I'll have to think about this.




    ----------------------------------------------------------------
    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 |/
  • Bruce Momjian at Oct 26, 2004 at 3:49 am

    Philip Warner wrote:
    At 09:28 AM 26/10/2004, Tom Lane wrote:

    I can't see what a search path would be good for.
    Nothing at this stage.
    The idea of a tablespace search path was that restores could specify a
    fallback if the tablespace doesn't exist, but it seems easier for the
    SET to just fail because the tablespace doesn't exist and the object
    goes into the default location.

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Tom Lane at Nov 5, 2004 at 7:20 pm
    Philip,
    I've just committed the backend changes involved in setting up a
    "default_tablespace" GUC variable for pg_dump to use, but I didn't
    do anything to convert pg_dump to doing so instead of using explicit
    TABLESPACE clauses. You had muttered something about wanting to add
    a TOC entry field for this --- do you still want to do the work?
    You can probably get it done faster than I could, but I dunno if you
    have time at the moment. I'd like to get it in over the weekend so
    that we can put out a new beta next week.

    BTW, part of the backend changes was to stop emitting TABLESPACE
    clauses in pg_get_indexdef() and pg_get_constraintdef() output,
    so as of CVS tip pg_dump will in fact fail to restore index tablespaces
    accurately. I assume this is the backend behavior you want, but
    holler if not.

    regards, tom lane
  • Philip Warner at Nov 6, 2004 at 2:28 am

    At 06:19 AM 6/11/2004, Tom Lane wrote:
    You had muttered something about wanting to add
    a TOC entry field for this --- do you still want to do the work?
    You can probably get it done faster than I could, but I dunno if you
    have time at the moment. I'd like to get it in over the weekend so
    that we can put out a new beta next week.
    Time is at a serious premium for me at the moment (I have several projects
    all due about now); but I wrote a patch for this a few weeks back, so it
    should not be a lot of work (unless pg_dump has changed in the last couple
    of months).

    I will *try* to get it done by Monday morning your time, and will let you
    know if I am going to miss this deadline as soon as I know.

    BTW, part of the backend changes was to stop emitting TABLESPACE
    clauses in pg_get_indexdef() and pg_get_constraintdef() output,
    so as of CVS tip pg_dump will in fact fail to restore index tablespaces
    accurately. I assume this is the backend behavior you want, but
    holler if not.
    Excellent. I assume that anything that can have a tablespace (database,
    schema(?), table and index -- anything else?) should emit a 'set
    default_tablespace="ts"' before creation (and that this will affect
    auto-created indexes as appropriate, whatever that means).

    Thanks for all the work.



    ----------------------------------------------------------------
    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 |/
  • Tom Lane at Nov 6, 2004 at 3:37 am

    Philip Warner writes:
    Time is at a serious premium for me at the moment (I have several projects
    all due about now); but I wrote a patch for this a few weeks back, so it
    should not be a lot of work (unless pg_dump has changed in the last couple
    of months).
    If you have a preliminary patch, you could pass it along and I'll finish
    it up.
    Excellent. I assume that anything that can have a tablespace (database,
    schema(?), table and index -- anything else?) should emit a 'set
    default_tablespace="ts"' before creation (and that this will affect
    auto-created indexes as appropriate, whatever that means).
    default_tablespace will affect both tables and auto-created indexes.
    But I was under the impression that pg_dump deliberately avoids
    auto-creation of indexes... isn't each one split out as an ADD
    CONSTRAINT operation?

    Schemas don't have tablespaces anymore.

    regards, tom lane
  • Philip Warner at Nov 6, 2004 at 5:00 am

    At 02:37 PM 6/11/2004, Tom Lane wrote:
    If you have a preliminary patch, you could pass it along and I'll finish
    it up.
    Attached. It has some trivial-looking rejects on current CVS. Let me know
    if you would prefer me to do the work, or want some testing done. It was
    tested (in terms of output validity) with 8.0b1.



    ----------------------------------------------------------------
    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 |/
  • Tom Lane at Nov 6, 2004 at 7:37 pm

    Philip Warner writes:
    Attached. It has some trivial-looking rejects on current CVS. Let me know
    if you would prefer me to do the work, or want some testing done. It was
    tested (in terms of output validity) with 8.0b1.
    Applied with minor cleanups.

    regards, tom lane
  • Bruce Momjian at Nov 6, 2004 at 5:43 am
    FYI, we need tablespace_default to control this pg_dump output for a
    primary key:

    ALTER TABLE ONLY test2
    ADD CONSTRAINT test2_pkey PRIMARY KEY (x);

    ---------------------------------------------------------------------------

    Tom Lane wrote:
    Philip Warner <pjw@rhyme.com.au> writes:
    Time is at a serious premium for me at the moment (I have several projects
    all due about now); but I wrote a patch for this a few weeks back, so it
    should not be a lot of work (unless pg_dump has changed in the last couple
    of months).
    If you have a preliminary patch, you could pass it along and I'll finish
    it up.
    Excellent. I assume that anything that can have a tablespace (database,
    schema(?), table and index -- anything else?) should emit a 'set
    default_tablespace="ts"' before creation (and that this will affect
    auto-created indexes as appropriate, whatever that means).
    default_tablespace will affect both tables and auto-created indexes.
    But I was under the impression that pg_dump deliberately avoids
    auto-creation of indexes... isn't each one split out as an ADD
    CONSTRAINT operation?

    Schemas don't have tablespaces anymore.

    regards, tom lane

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Bruce Momjian at Nov 6, 2004 at 5:39 am
    TODO item removed:

    * Allow database recovery where tablespaces can't be created

    When a pg_dump is restored, all tablespaces will attempt to be created
    in their original locations. If this fails, the user must be able to
    adjust the restore process.

    Not done yet, but it will be with SET default_tablespace.

    I don't think we need "adjust" but rather default to the default
    tablespace is just fine, and they can pre-create tablespaces in
    different locations to adjust the restore anyway.

    Great!

    ---------------------------------------------------------------------------

    Philip Warner wrote:
    At 06:19 AM 6/11/2004, Tom Lane wrote:
    You had muttered something about wanting to add
    a TOC entry field for this --- do you still want to do the work?
    You can probably get it done faster than I could, but I dunno if you
    have time at the moment. I'd like to get it in over the weekend so
    that we can put out a new beta next week.
    Time is at a serious premium for me at the moment (I have several projects
    all due about now); but I wrote a patch for this a few weeks back, so it
    should not be a lot of work (unless pg_dump has changed in the last couple
    of months).

    I will *try* to get it done by Monday morning your time, and will let you
    know if I am going to miss this deadline as soon as I know.

    BTW, part of the backend changes was to stop emitting TABLESPACE
    clauses in pg_get_indexdef() and pg_get_constraintdef() output,
    so as of CVS tip pg_dump will in fact fail to restore index tablespaces
    accurately. I assume this is the backend behavior you want, but
    holler if not.
    Excellent. I assume that anything that can have a tablespace (database,
    schema(?), table and index -- anything else?) should emit a 'set
    default_tablespace="ts"' before creation (and that this will affect
    auto-created indexes as appropriate, whatever that means).

    Thanks for all the work.



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


    ---------------------------(end of broadcast)---------------------------
    TIP 8: explain analyze is your friend
    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Gavin Sherry at Oct 26, 2004 at 12:02 am

    On Mon, 25 Oct 2004, Tom Lane wrote:

    Philip Warner <pjw@rhyme.com.au> writes:
    At 08:00 AM 26/10/2004, Tom Lane wrote:
    I don't want a GUC variable that actively changes the default
    tablespace; at least not unless you want to abandon the current
    mechanisms for default tablespace choices entirely, and go over to
    making the GUC variable be the sole arbiter.
    Something consistent with Schemas does sound good to me; a tablespace
    search path (or just single default), and support for a TABLESPACE clause
    on table and INDEX definitions would be good.
    I can't see what a search path would be good for.
    I agree.
    For the three largest databases I work on, the namespace/schema that a
    table resides in is irrelevant to the tablespace that it should be stored
    in. So default tablespaces on the schema are a bit of a pointless feature.
    The ability to have the features of schemas: default tablespace for given
    users, a GUC variable, and ACLs on tablespaces would be far more valuable.
    Another nice thing is that not having default tablespaces associated
    with schemas eliminates that nasty issue about being able to drop such a
    tablespace while the schema is still there.
    Hmmm.. despite that problem, I was rather fond of schema default
    tablespaces because they allow DBAs to set a policy for a particular
    schema. The cases I've discussed with people so far are things
    like creating a schema for a (closed source) application and associating
    that with a tablespace. There by, all new objects created will be in that
    tablespace without the need for DBA intervention. Its not necessary, but
    its nice I think.
    It seems like we still need some notion of a database's schema, to put
    the system catalogs in, but perhaps that need not be the same as the
    default schema for user tables created in the database?
    By schema here, do you mean tablespace?
    I'd be willing to jump this way if we can work out the
    default-tablespace inconsistencies that Bruce has on the open items
    list. Does anyone want to draft a concrete proposal? It seems like the
    basic elements are:

    * A GUC variable named something like default_tablespace that
    controls which TS objects are created in when there's
    no explicit TABLESPACE clause. The factory default for this
    would of course be pg_default. Otherwise it's settable just
    like any other GUC var.

    * Get rid of TABLESPACE clause for CREATE SCHEMA, and
    pg_namespace.nsptablespace (ooops, another initdb).

    * Need to define exactly what TABLESPACE clause for a database
    controls; location of its catalogs of course, but anything else?
    This could be a bit messy (from a user's point of view). There are two
    meanings (according to your plan): 1) the tablespace clause is the default
    for the catalogs AND for newly created objects (we set default_tablespace
    in datconfig); OR, 2) it only sets the tablespace for the catalogs. (You
    could say that it just sets the default tablespace for new objects, but
    then how do you set the catalog tablespace).

    I guess (1) makes sense but it limits people. If we do (2), we have two
    options: a) User needs to ALTER DATABASE SET default_table.. b) we add a
    new key work. I think (b) is ugly.
    * We could possibly say that a TABLESPACE clause attached to
    CREATE TABLE determines the default tablespace for indexes
    created by the same command; I'm not sure if this is a good
    idea, or if the indexes should go into default_tablespace
    absent a TABLESPACE clause attached directly to their defining
    constraints. We certainly want default_tablespace to control
    indexes created by separate commands, so there'd be some
    inconsistency if we do the former.
    I think a viable solution is to go with the latter (ie, for CREATE TABLE
    foo(i int primary key) TABLESPACE ts; the index on i is created in
    default_tablespace). However, I might be nice to be able to specify the
    tablespace as part of the primary key clause. I say nice, but not
    necessary.
    regards, tom lane
    Thanks,

    Gavin
  • Tom Lane at Oct 26, 2004 at 12:07 am

    Gavin Sherry writes:
    Hmmm.. despite that problem, I was rather fond of schema default
    tablespaces because they allow DBAs to set a policy for a particular
    schema. The cases I've discussed with people so far are things
    like creating a schema for a (closed source) application and associating
    that with a tablespace. There by, all new objects created will be in that
    tablespace without the need for DBA intervention. Its not necessary, but
    its nice I think.
    On the other hand, driving it from a GUC variable would allow you to
    easily set a per-user default, which might be at least as useful.
    It seems like we still need some notion of a database's schema, to put
    the system catalogs in, but perhaps that need not be the same as the
    default schema for user tables created in the database?
    By schema here, do you mean tablespace?
    Sorry, fingers faster than brain obviously. Time to take a break...
    I think a viable solution is to go with the latter (ie, for CREATE TABLE
    foo(i int primary key) TABLESPACE ts; the index on i is created in
    default_tablespace). However, I might be nice to be able to specify the
    tablespace as part of the primary key clause. I say nice, but not
    necessary.
    We already have that don't we?

    create table foo (f1 int,
    primary key (f1) using index tablespace its)
    tablespace tts;

    The question is where to put foo_pkey when "using index tablespace"
    isn't there but "tablespace" is.

    (BTW, since we stole that syntax from Oracle, maybe we should check what
    they do...)

    regards, tom lane
  • Gavin Sherry at Oct 26, 2004 at 12:26 am

    On Mon, 25 Oct 2004, Tom Lane wrote:

    Gavin Sherry <swm@linuxworld.com.au> writes:
    Hmmm.. despite that problem, I was rather fond of schema default
    tablespaces because they allow DBAs to set a policy for a particular
    schema. The cases I've discussed with people so far are things
    like creating a schema for a (closed source) application and associating
    that with a tablespace. There by, all new objects created will be in that
    tablespace without the need for DBA intervention. Its not necessary, but
    its nice I think.
    On the other hand, driving it from a GUC variable would allow you to
    easily set a per-user default, which might be at least as useful.
    It seems like we still need some notion of a database's schema, to put
    the system catalogs in, but perhaps that need not be the same as the
    default schema for user tables created in the database?
    By schema here, do you mean tablespace?
    Sorry, fingers faster than brain obviously. Time to take a break...
    I think a viable solution is to go with the latter (ie, for CREATE TABLE
    foo(i int primary key) TABLESPACE ts; the index on i is created in
    default_tablespace). However, I might be nice to be able to specify the
    tablespace as part of the primary key clause. I say nice, but not
    necessary.
    We already have that don't we?

    create table foo (f1 int,
    primary key (f1) using index tablespace its)
    tablespace tts;

    The question is where to put foo_pkey when "using index tablespace"
    isn't there but "tablespace" is.
    Hah. I wasn't sure if that ever got in -- guess I should have checked.
    (BTW, since we stole that syntax from Oracle, maybe we should check what
    they do...)
    As an aside -- I'm not quite sure we stole the syntax from Oracle. Oracle
    has *a lot* more functionality and nothing like the parent's tablespace
    system.

    Basically, more than one database object can be stored in a single data
    file in oracle. A tablespace is a group of such files. You can have two
    files in a tablespace in diferent locations. That is, tablespace foo might
    consist of /data1/a.dat and /data2/b.dat.

    So, when you create a new database, you can determine where the 'system
    catalogs' are by setting the datafile location for the system catalog
    tablespaces. You can *also* set a default tablespace for the database --
    default in the sense that all newly created objects with no explicit
    tablespace clause are created in the tablespace. With an exception as
    follows: Oracle relies heavily on the concept of a user's default
    tablespace. Interestingly, this is what you just mentioned above :-).

    Gavin
  • Tom Lane at Oct 26, 2004 at 12:53 am

    Gavin Sherry writes:
    [ ... in Oracle: ]
    So, when you create a new database, you can determine where the 'system
    catalogs' are by setting the datafile location for the system catalog
    tablespaces. You can *also* set a default tablespace for the database --
    default in the sense that all newly created objects with no explicit
    tablespace clause are created in the tablespace. With an exception as
    follows: Oracle relies heavily on the concept of a user's default
    tablespace. Interestingly, this is what you just mentioned above :-).
    So if we went with a GUC-driven approach, we could emulate both of those
    things easily, because people could set the default_tablespace GUC
    variable in either ALTER DATABASE or ALTER USER. This is starting to
    sound like a win.

    regards, tom lane
  • Bruce Momjian at Oct 26, 2004 at 3:53 am

    Tom Lane wrote:
    I think a viable solution is to go with the latter (ie, for CREATE TABLE
    foo(i int primary key) TABLESPACE ts; the index on i is created in
    default_tablespace). However, I might be nice to be able to specify the
    tablespace as part of the primary key clause. I say nice, but not
    necessary.
    We already have that don't we?

    create table foo (f1 int,
    primary key (f1) using index tablespace its)
    tablespace tts;

    The question is where to put foo_pkey when "using index tablespace"
    isn't there but "tablespace" is.
    I think that lacking a tablespace clause in the index section the
    behavior of least surprise would be to use the outer tablespace
    specification if it exists, and if not use the GUC variable for the
    tablespace (basically the tablespace of the table for the index). We
    already name the tablespace using our own name if we create it as part
    of CREATE TABLE so it seems natural to also use the tablespace of the
    table. The idea that a non-specified value defaults to the outer
    level's default (tablespace) is a natural behavior people expect.

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Bruce Momjian at Oct 29, 2004 at 8:46 pm
    Added to open items list:

    * Tablespace
    o add new GUC default_tablespace to control object creation when
    no explicit TABLESPACE clause exists

    Use it in pg_dump.

    o Remove tablespace default for databases and schemas

    Place objects as specified by the TABLESPACE clause or
    default_tablespace. The database tablespace controls only
    the system objects.



    ---------------------------------------------------------------------------

    Tom Lane wrote:
    Philip Warner <pjw@rhyme.com.au> writes:
    At 08:00 AM 26/10/2004, Tom Lane wrote:
    I don't want a GUC variable that actively changes the default
    tablespace; at least not unless you want to abandon the current
    mechanisms for default tablespace choices entirely, and go over to
    making the GUC variable be the sole arbiter.
    Something consistent with Schemas does sound good to me; a tablespace
    search path (or just single default), and support for a TABLESPACE clause
    on table and INDEX definitions would be good.
    I can't see what a search path would be good for.
    For the three largest databases I work on, the namespace/schema that a
    table resides in is irrelevant to the tablespace that it should be stored
    in. So default tablespaces on the schema are a bit of a pointless feature.
    The ability to have the features of schemas: default tablespace for given
    users, a GUC variable, and ACLs on tablespaces would be far more valuable.
    Another nice thing is that not having default tablespaces associated
    with schemas eliminates that nasty issue about being able to drop such a
    tablespace while the schema is still there.

    It seems like we still need some notion of a database's schema, to put
    the system catalogs in, but perhaps that need not be the same as the
    default schema for user tables created in the database?

    I'd be willing to jump this way if we can work out the
    default-tablespace inconsistencies that Bruce has on the open items
    list. Does anyone want to draft a concrete proposal? It seems like the
    basic elements are:

    * A GUC variable named something like default_tablespace that
    controls which TS objects are created in when there's
    no explicit TABLESPACE clause. The factory default for this
    would of course be pg_default. Otherwise it's settable just
    like any other GUC var.

    * Get rid of TABLESPACE clause for CREATE SCHEMA, and
    pg_namespace.nsptablespace (ooops, another initdb).

    * Need to define exactly what TABLESPACE clause for a database
    controls; location of its catalogs of course, but anything else?

    * We could possibly say that a TABLESPACE clause attached to
    CREATE TABLE determines the default tablespace for indexes
    created by the same command; I'm not sure if this is a good
    idea, or if the indexes should go into default_tablespace
    absent a TABLESPACE clause attached directly to their defining
    constraints. We certainly want default_tablespace to control
    indexes created by separate commands, so there'd be some
    inconsistency if we do the former.

    regards, tom lane
    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Tom Lane at Oct 31, 2004 at 8:46 pm

    I wrote:
    I'd be willing to jump this way if we can work out the
    default-tablespace inconsistencies that Bruce has on the open items
    list.
    After further thought it seems to me that using a default_tablespace
    GUC variable doesn't eliminate all the open issues. In particular
    it is no help for the problem of merging two different tablespaces
    during CREATE DATABASE, ie, creating a new DB with a dattablespace
    that is different from the template DB's default when the template
    DB already has some tables explicitly placed into that tablespace.
    In this situation we have the problem that the cloned DB would
    have pg_class rows with different references to the same tablespace
    (either zero for the database default, or the explicit OID of the
    tablespace). Among other things this would make it impossible to
    use the cloned DB again as a template for CREATE DATABASE.

    AFAICS this problem stems ultimately from the choice to have a
    special representation (zero) in pg_class for the database's default
    tablespace. The only way to really get rid of it would be to eliminate
    that provision and say that pg_class.reltablespace is always the correct
    explicit OID. What that would mean in turn is that we could not copy a
    database and move its tables into a different tablespace, at least not
    without very major work on CREATE DATABASE to make it alter pg_class
    on-the-fly while copying.

    We might want to think about doing that eventually, but for now I'd
    say that the restriction on merging tablespaces is just something
    we have to live with. It's less annoying than not being able to
    relocate a database, for sure.

    Despite this, the default_tablespace GUC variable seems more attractive
    than what we have now. Last call for objections ...

    regards, tom lane
  • Bruce Momjian at Oct 31, 2004 at 11:43 pm

    Tom Lane wrote:
    I wrote:
    I'd be willing to jump this way if we can work out the
    default-tablespace inconsistencies that Bruce has on the open items
    list.
    After further thought it seems to me that using a default_tablespace
    GUC variable doesn't eliminate all the open issues. In particular
    it is no help for the problem of merging two different tablespaces
    during CREATE DATABASE, ie, creating a new DB with a dattablespace
    that is different from the template DB's default when the template
    DB already has some tables explicitly placed into that tablespace.
    In this situation we have the problem that the cloned DB would
    have pg_class rows with different references to the same tablespace
    (either zero for the database default, or the explicit OID of the
    tablespace). Among other things this would make it impossible to
    use the cloned DB again as a template for CREATE DATABASE.
    Right. I would say 99% of people are using template1 as the template
    for new databases, and if we clearly give an error message when they use
    a database not in the default tablespace (which we do now), it seems
    just fine. Let's see how many people complain and make adjustments in
    8.1 if needed.
    AFAICS this problem stems ultimately from the choice to have a
    special representation (zero) in pg_class for the database's default
    tablespace. The only way to really get rid of it would be to eliminate
    that provision and say that pg_class.reltablespace is always the correct
    explicit OID. What that would mean in turn is that we could not copy a
    database and move its tables into a different tablespace, at least not
    without very major work on CREATE DATABASE to make it alter pg_class
    on-the-fly while copying.
    Agreed. That is just too much work for so little gain.
    We might want to think about doing that eventually, but for now I'd
    say that the restriction on merging tablespaces is just something
    we have to live with. It's less annoying than not being able to
    relocate a database, for sure.
    One downside that came up yesterday in a discussion is that once shemas
    don't have default tablespaces we can't easily have default tablespaces
    for toast and temporary table system schemas. Now we can't actually do
    that now anyway because they are created by the system but it might
    limit how we can control these in the future. I am just throwing this
    out as a point.
    Despite this, the default_tablespace GUC variable seems more attractive
    than what we have now. Last call for objections ...
    Sounds good.

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Gavin Sherry at Nov 1, 2004 at 12:05 am

    On Sun, 31 Oct 2004, Bruce Momjian wrote:

    Tom Lane wrote:
    I wrote:
    I'd be willing to jump this way if we can work out the
    default-tablespace inconsistencies that Bruce has on the open items
    list.
    After further thought it seems to me that using a default_tablespace
    GUC variable doesn't eliminate all the open issues. In particular
    it is no help for the problem of merging two different tablespaces
    during CREATE DATABASE, ie, creating a new DB with a dattablespace
    that is different from the template DB's default when the template
    DB already has some tables explicitly placed into that tablespace.
    In this situation we have the problem that the cloned DB would
    have pg_class rows with different references to the same tablespace
    (either zero for the database default, or the explicit OID of the
    tablespace). Among other things this would make it impossible to
    use the cloned DB again as a template for CREATE DATABASE.
    Right. I would say 99% of people are using template1 as the template
    for new databases, and if we clearly give an error message when they use
    a database not in the default tablespace (which we do now), it seems
    just fine. Let's see how many people complain and make adjustments in
    8.1 if needed.
    I agree.
    AFAICS this problem stems ultimately from the choice to have a
    special representation (zero) in pg_class for the database's default
    tablespace. The only way to really get rid of it would be to eliminate
    that provision and say that pg_class.reltablespace is always the correct
    explicit OID. What that would mean in turn is that we could not copy a
    database and move its tables into a different tablespace, at least not
    without very major work on CREATE DATABASE to make it alter pg_class
    on-the-fly while copying.
    Agreed. That is just too much work for so little gain.
    I agree. Although, I think having a createdb() with transaction semantics
    and the ability to modify data on the fly would be useful -- not just for
    tablespace handling. As you say, it is a fair bit of work, however.
    We might want to think about doing that eventually, but for now I'd
    say that the restriction on merging tablespaces is just something
    we have to live with. It's less annoying than not being able to
    relocate a database, for sure.
    One downside that came up yesterday in a discussion is that once shemas
    don't have default tablespaces we can't easily have default tablespaces
    for toast and temporary table system schemas. Now we can't actually do
    that now anyway because they are created by the system but it might
    limit how we can control these in the future. I am just throwing this
    out as a point.
    Neil has been talking to me about being able to set a tablespace for
    temporary tables at or after create database time.

    I'm not sure about TOAST however. I considered the idea of adding
    something to CREATE TABLE like TOASTSPACE <tablespace>, such that all
    TOAST tables would be put in the 'toastspace'. But I think the syntax is
    ugly and would confuse many users who do not know what toast is.

    Thanks,

    Gavin
  • Bruce Momjian at Nov 1, 2004 at 12:27 am

    Gavin Sherry wrote:
    One downside that came up yesterday in a discussion is that once shemas
    don't have default tablespaces we can't easily have default tablespaces
    for toast and temporary table system schemas. Now we can't actually do
    that now anyway because they are created by the system but it might
    limit how we can control these in the future. I am just throwing this
    out as a point.
    Neil has been talking to me about being able to set a tablespace for
    temporary tables at or after create database time.

    I'm not sure about TOAST however. I considered the idea of adding
    something to CREATE TABLE like TOASTSPACE <tablespace>, such that all
    TOAST tables would be put in the 'toastspace'. But I think the syntax is
    ugly and would confuse many users who do not know what toast is.
    I think we need to add temp_tablespace and toast_tablespace GUC
    variables to deal with this, perhaps for 8.1.

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Tom Lane at Nov 1, 2004 at 2:19 am

    Bruce Momjian writes:
    Gavin Sherry wrote:
    I'm not sure about TOAST however. I considered the idea of adding
    something to CREATE TABLE like TOASTSPACE <tablespace>, such that all
    TOAST tables would be put in the 'toastspace'. But I think the syntax is
    ugly and would confuse many users who do not know what toast is.
    I think we need to add temp_tablespace and toast_tablespace GUC
    variables to deal with this, perhaps for 8.1.
    A tablespace for temp tables is okay, but I'm fairly dubious about the
    idea of a "toast tablespace". The current behavior is that a toast
    table is automatically placed into the same tablespace as its parent,
    and that seems exactly right to me. It's certainly the right thing from
    the point of view of users who do not understand TOAST and expect all of
    a table's data to get put where they said to put the table.

    regards, tom lane
  • Bruce Momjian at Nov 1, 2004 at 2:25 am
    OK, TODO updated:

    * Add a GUC variable to control the tablespace for temporary
    objects

    ---------------------------------------------------------------------------

    Tom Lane wrote:
    Bruce Momjian <pgman@candle.pha.pa.us> writes:
    Gavin Sherry wrote:
    I'm not sure about TOAST however. I considered the idea of adding
    something to CREATE TABLE like TOASTSPACE <tablespace>, such that all
    TOAST tables would be put in the 'toastspace'. But I think the syntax is
    ugly and would confuse many users who do not know what toast is.
    I think we need to add temp_tablespace and toast_tablespace GUC
    variables to deal with this, perhaps for 8.1.
    A tablespace for temp tables is okay, but I'm fairly dubious about the
    idea of a "toast tablespace". The current behavior is that a toast
    table is automatically placed into the same tablespace as its parent,
    and that seems exactly right to me. It's certainly the right thing from
    the point of view of users who do not understand TOAST and expect all of
    a table's data to get put where they said to put the table.

    regards, tom lane
    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Bruce Momjian at Nov 1, 2004 at 12:29 am
    Added to open items:

    * Add a GUC variable to control temporary and TOAST tablespace usage


    ---------------------------------------------------------------------------

    Gavin Sherry wrote:
    On Sun, 31 Oct 2004, Bruce Momjian wrote:

    Tom Lane wrote:
    I wrote:
    I'd be willing to jump this way if we can work out the
    default-tablespace inconsistencies that Bruce has on the open items
    list.
    After further thought it seems to me that using a default_tablespace
    GUC variable doesn't eliminate all the open issues. In particular
    it is no help for the problem of merging two different tablespaces
    during CREATE DATABASE, ie, creating a new DB with a dattablespace
    that is different from the template DB's default when the template
    DB already has some tables explicitly placed into that tablespace.
    In this situation we have the problem that the cloned DB would
    have pg_class rows with different references to the same tablespace
    (either zero for the database default, or the explicit OID of the
    tablespace). Among other things this would make it impossible to
    use the cloned DB again as a template for CREATE DATABASE.
    Right. I would say 99% of people are using template1 as the template
    for new databases, and if we clearly give an error message when they use
    a database not in the default tablespace (which we do now), it seems
    just fine. Let's see how many people complain and make adjustments in
    8.1 if needed.
    I agree.
    AFAICS this problem stems ultimately from the choice to have a
    special representation (zero) in pg_class for the database's default
    tablespace. The only way to really get rid of it would be to eliminate
    that provision and say that pg_class.reltablespace is always the correct
    explicit OID. What that would mean in turn is that we could not copy a
    database and move its tables into a different tablespace, at least not
    without very major work on CREATE DATABASE to make it alter pg_class
    on-the-fly while copying.
    Agreed. That is just too much work for so little gain.
    I agree. Although, I think having a createdb() with transaction semantics
    and the ability to modify data on the fly would be useful -- not just for
    tablespace handling. As you say, it is a fair bit of work, however.
    We might want to think about doing that eventually, but for now I'd
    say that the restriction on merging tablespaces is just something
    we have to live with. It's less annoying than not being able to
    relocate a database, for sure.
    One downside that came up yesterday in a discussion is that once shemas
    don't have default tablespaces we can't easily have default tablespaces
    for toast and temporary table system schemas. Now we can't actually do
    that now anyway because they are created by the system but it might
    limit how we can control these in the future. I am just throwing this
    out as a point.
    Neil has been talking to me about being able to set a tablespace for
    temporary tables at or after create database time.

    I'm not sure about TOAST however. I considered the idea of adding
    something to CREATE TABLE like TOASTSPACE <tablespace>, such that all
    TOAST tables would be put in the 'toastspace'. But I think the syntax is
    ugly and would confuse many users who do not know what toast is.

    Thanks,

    Gavin
    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Fabien COELHO at Oct 26, 2004 at 6:24 am
    Dear Tom,
    ISTM that the core business of a database is to help organize and protect
    data, and it is plainly that. You just wish you won't need it, so it is
    somehow "abstract", but when and if you need it, it is not "second-order"
    at all;-) and it is much too late to redo the dump.
    So you create some tablespaces by hand. Big deal.
    I agree that is is doable this way, although not really nice.
    This objection is not strong enough to justify an ugly, klugy definition
    for where tables get created.
    I do also agree about this.

    My real point is that while reading the thread quickly, I was afraid the
    problem would not be better addressed at all in the coming release.

    It seems that I was wrong as it does not look to be the case.
    Any fix instead of nothing is fair enough for me.

    Thanks for your answer, have a nice day,

    --
    Fabien Coelho - coelho@cri.ensmp.fr

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedOct 18, '04 at 4:27p
activeNov 6, '04 at 7:37p
posts45
users6
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase