Thread: Open Items
I have annotated the open items:
---------------------------------------------------------------------------
PostgreSQL 8.0 Open Items =========================
Current version at http://candle.pha.pa.us/cgi-bin/pgopenitems.
Changes
-------
* Win32o fix query cancel in psql
This requires a new thread to handle the control-C signal
o fix shared memory on Win2k terminal server
We might be able to just mark this as not supported.
o Handle "lost signals" on backend startup (eg. shutdown, config file changes, etc); signals are SIG_DFL on startup
The problem here is that the postmaster might send signals to achild before the signal handlers are installed. We
don'thavethis problem on unix because we fork and inherit the signalhandlers.
* Tablespaceo fix ambiguity for objects using default tablespaces
CREATE TABLE test(a int4) tablespace loc;CREATE UNIQUE INDEX test_a_idx3 ON test(a) tablespace pg_default;
Index test_a_idx3 has a tablespace value of zero. pg_get_indexdef()thinks the index is in loc even though it is in
pg_default. Andif you move test the index is thought to move too. The confusionis that there are not separate defaults
fordatabase/schema/table.
o fix case where template db already uses target tablespace
-- from database testCREATE tablespace blah location '/bjm/tmp';CREATE TABLE foo (i int) tablespace blah;CREATE
DATABASEfoo tablespace blah template test;ERROR: could not initialize database directoryDETAIL: Directory
"/u/pgsql/data/pg_tblspc/17232/17235"already exists.
o remove non-portable TABLESPACE clause from CREATE TABLE and use a new default_tablespace SET variable
This same method is used for WITH/WITHOUT OIDS.
* allow libpq to check parameterized data types
* adjust bgwriter defaults, allow disabling
* synchonize supported encodings and docs
Completed Since Previoius Beta
------------------------------
* cleanup FRONTEND use in /port, malloc, elog
* update encoding list to include win1250
* make pgxs install by default
* Tablespaceo fix error message when creating objects in schema that has a dropped tablespace as its default
* Win32o disable readline-required psql optionso fix SSL compileso add binary version stampso fix signal-safe socket
handlerfor SSLo start pg_autovacuum easilyo remove log timezone string from log_line_prefix '%t'o fix MinGW libpq to
exportonly required symbolso fix MSVC build to export SSL symbols
-- 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,
Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes: > o fix ambiguity for objects using default tablespaces > CREATE TABLE test(a int4) tablespace loc; > CREATE UNIQUE INDEX test_a_idx3 ON test(a) tablespace pg_default; > Index test_a_idx3 has a tablespace value of zero. pg_get_indexdef() > thinks the index is in loc even though it is in pg_default. And > if you move test the index is thought to move too. The confusion > is that there are not separate defaults for database/schema/table. I don't think it's nearly as complicated as all that; it's just a thinko in pg_get_indexdef. http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/ruleutils.c.diff?r1=1.182&r2=1.183 regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> o remove non-portable TABLESPACE clause from CREATE TABLE and
> use a new default_tablespace SET variable
I'm coming around to the conclusion that this is simply a bad idea.
The problem with having such a SET variable is that it plays hob with
the existing definition about where schemas and tables get a default
tablespace from. Which source wins (the database or schema default
tablespace, or the SET variable)? And why? The only really clean way
to have a SET variable for this is to forget about schema- or
table-based defaults. Do we want to do that? (Hey, it'd solve the
problem with schema tablespaces being droppable, because there wouldn't
*be* any such thing as a schema's tablespace anymore. But on the whole
this seems like a step backward in usability.)
What we might want to do is invent a --notablespace option for pg_dump,
comparable to --noowner, to let someone make a dump that contains no
TABLESPACE clauses.
regards, tom lane
On Sun, 17 Oct 2004, Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > o remove non-portable TABLESPACE clause from CREATE TABLE and > > use a new default_tablespace SET variable > > I'm coming around to the conclusion that this is simply a bad idea. I feel the same way for more or less the reasons you outline. > What we might want to do is invent a --notablespace option for pg_dump, > comparable to --noowner, to let someone make a dump that contains no > TABLESPACE clauses. That's a useful feature but I'm not sure it solves the problem people originally put (to me at least). User has data in a tablespace on a seperate device. The device crashes fatally and the user needs to restore the database. All the user's dumps contain tablespace clauses because the user did not anticipate the device dying. This, I think, is why people wanted to either ignore tablespace clauses, have an override or something else. I still think, however, that a workable solution is to bring up a new system, create the tablespaces on some online partition, and pg_restore the dump. pg_dump does not dump CREATE TABLESPACE so we wont encounter problems there. Have I missed something there? (Highly likely as I am still pre-coffee). Gavin
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > o fix ambiguity for objects using default tablespaces > > > CREATE TABLE test(a int4) tablespace loc; > > CREATE UNIQUE INDEX test_a_idx3 ON test(a) tablespace pg_default; > > > Index test_a_idx3 has a tablespace value of zero. pg_get_indexdef() > > thinks the index is in loc even though it is in pg_default. And > > if you move test the index is thought to move too. The confusion > > is that there are not separate defaults for database/schema/table. > > I don't think it's nearly as complicated as all that; it's just a thinko > in pg_get_indexdef. > > http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/ruleutils.c.diff?r1=1.182&r2=1.183 OK, I see that now. Item marked as done. -- 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, Pennsylvania19073
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > o remove non-portable TABLESPACE clause from CREATE TABLE and > > use a new default_tablespace SET variable > > I'm coming around to the conclusion that this is simply a bad idea. > > The problem with having such a SET variable is that it plays hob with > the existing definition about where schemas and tables get a default > tablespace from. Which source wins (the database or schema default > tablespace, or the SET variable)? And why? The only really clean way > to have a SET variable for this is to forget about schema- or > table-based defaults. Do we want to do that? (Hey, it'd solve the > problem with schema tablespaces being droppable, because there wouldn't > *be* any such thing as a schema's tablespace anymore. But on the whole > this seems like a step backward in usability.) Agreed, a step backwards, but see below. > What we might want to do is invent a --notablespace option for pg_dump, > comparable to --noowner, to let someone make a dump that contains no > TABLESPACE clauses. Yea, that would work, but we went through so much work to allow SQL standard DDL statements, and it seems a shame to break it just for tablespaces. And, having it be a separate SET would also allow the tablespace creation to fail and still get the objects created. (If the explicit_tablespace doesn't exist during CREATE, we throw a warning. This would contrast with a create _failure_ when the tablespace doesn't exist and you say 'TABLESPACE t1' in CREATE.) So there were actually two uses for this, one for standards compliance, and the other was for flexibility in restoring to a system where the tablespaces can't be created. The SET could give us different behavior (warning vs. error) which would be useful for pg_dump. Could we call it "explicit_tablespace" and when it is "", it is the default, but when it isn't it is just like using 'TABLESPACE t1' in the CREATE, but throws a warning instead of an error if the tablespace doesn't exist? My assumption is that it would not be like the default_with_oids variable usage by pg_dump because it would be reset to '' (default) by pg_dump after each time it is used. I assume explicit_tablespace would always override the schema or database tablespace because it is "explicit". In fact this would partially fix the TODO we have:* Allow database recovery where tablespaces can't be created When a pg_dumpis restored, all tablespaces will attempt to be created in their original locations. If this fails, the user mustbe able to adjust the restore process. -- 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, Pennsylvania19073
Gavin Sherry wrote: > On Sun, 17 Oct 2004, Tom Lane wrote: > > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > o remove non-portable TABLESPACE clause from CREATE TABLE and > > > use a new default_tablespace SET variable > > > > I'm coming around to the conclusion that this is simply a bad idea. > > I feel the same way for more or less the reasons you outline. > > > What we might want to do is invent a --notablespace option for pg_dump, > > comparable to --noowner, to let someone make a dump that contains no > > TABLESPACE clauses. > > That's a useful feature but I'm not sure it solves the problem people > originally put (to me at least). User has data in a tablespace on a > seperate device. The device crashes fatally and the user needs to restore > the database. All the user's dumps contain tablespace clauses because the > user did not anticipate the device dying. This, I think, is why people > wanted to either ignore tablespace clauses, have an override or something > else. See my posting about using 'explicit_tablespace' and having it issue a warning if it doesn't exist during create. > I still think, however, that a workable solution is to bring up a new > system, create the tablespaces on some online partition, and pg_restore > the dump. pg_dump does not dump CREATE TABLESPACE so we wont encounter > problems there. pg_dumpall does dump CREATE TABLESPACE. It depends if he is using pg_dump on individual databases or pg_dumpall. If he is using pg_dump he is also pg_dumpall --globals-only and the global restore is where he can made the adjustments. Of course, the 'explicit_tablespace' works with pg_dumpall too because everything destined for a non-existant tablespace goes into the default for that object. -- 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, Pennsylvania19073
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. The only problem with that is this TODO item:
o Allow databases and schemas to be moved to different tablespaces
One complexity is whether moving a schema should move all existing schema objects or just define the
locationfor future object creation.
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.
(ALTER was originally part of the open item but I removed it thinking we
might not have space to load the table in the default location, but I
forgot we create it empty and could move it before we load it.)
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:* Allow database recovery where tablespaces can't be created When a pg_dump is restored, all tablespaces will
attemptto be created in their original locations. If this fails, the user must be able to adjust the restore
process.
One idea would be that users could create any tablespaces or objects
they want to change before they do the restore and the restore would use
their new configuration and just error/skip the items they already
created. (This would also behave well if you load the dump and say stop
on any errors.)
For example, if you want to eliminate a tablespace, you just don't
create the directory and load your dump. Tablespace create will fail,
and all objects that use that tablespace will fail their ALTER and will
remain in their default locations. This actually seems less error-prone
than the idea of them manually changing things in the dump file.
---------------------------------------------------------------------------
Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > o remove non-portable TABLESPACE clause from CREATE TABLE and
> > > use a new default_tablespace SET variable
> >
> > I'm coming around to the conclusion that this is simply a bad idea.
> >
> > The problem with having such a SET variable is that it plays hob with
> > the existing definition about where schemas and tables get a default
> > tablespace from. Which source wins (the database or schema default
> > tablespace, or the SET variable)? And why? The only really clean way
> > to have a SET variable for this is to forget about schema- or
> > table-based defaults. Do we want to do that? (Hey, it'd solve the
> > problem with schema tablespaces being droppable, because there wouldn't
> > *be* any such thing as a schema's tablespace anymore. But on the whole
> > this seems like a step backward in usability.)
>
> Agreed, a step backwards, but see below.
>
> > What we might want to do is invent a --notablespace option for pg_dump,
> > comparable to --noowner, to let someone make a dump that contains no
> > TABLESPACE clauses.
>
> Yea, that would work, but we went through so much work to allow SQL
> standard DDL statements, and it seems a shame to break it just for
> tablespaces.
>
> And, having it be a separate SET would also allow the tablespace
> creation to fail and still get the objects created. (If the
> explicit_tablespace doesn't exist during CREATE, we throw a warning.
> This would contrast with a create _failure_ when the tablespace doesn't
> exist and you say 'TABLESPACE t1' in CREATE.)
>
> So there were actually two uses for this, one for standards compliance,
> and the other was for flexibility in restoring to a system where the
> tablespaces can't be created. The SET could give us different behavior
> (warning vs. error) which would be useful for pg_dump.
>
> Could we call it "explicit_tablespace" and when it is "", it is the
> default, but when it isn't it is just like using 'TABLESPACE t1' in the
> CREATE, but throws a warning instead of an error if the tablespace
> doesn't exist?
>
> My assumption is that it would not be like the default_with_oids
> variable usage by pg_dump because it would be reset to '' (default) by
> pg_dump after each time it is used. I assume explicit_tablespace would
> always override the schema or database tablespace because it is
> "explicit".
>
> In fact this would partially fix the TODO we have:
>
> * 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.
>
>
> --
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go 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,
Pennsylvania19073
Dear Tom, >> o remove non-portable TABLESPACE clause from CREATE TABLE and >> use a new default_tablespace SET variable > > I'm coming around to the conclusion that this is simply a bad idea. I agree that the "set" approach is error prone. Another idea was to issue an "ALTER" after the CREATE. That would move the empty table from one tablespace to another, at small cost. If it fails, it is simply ignored by the restoration process, but the table was already created so it exists. > What we might want to do is invent a --notablespace option for pg_dump, > comparable to --noowner, to let someone make a dump that contains no > TABLESPACE clauses. (1) --notablespace would be useful, but it would not fix the problem I had in mind, i.e. the transfer (possibly aftera crash) of data to another base which would not have these tablespaces. If the disk is crashed, I cannot redothe pg_dump. (2) thus it would help to be able to decide this at "restore" time. I think that one of the implementation idea was tostore the information into some headers. (3) possible current workaround for the desperate admin: (a) create fake tablespaces as necessary... (b) pg_restore... | sed 's/TABLESPACE .*//' | psql ... Have a nice day, -- Fabien Coelho - coelho@cri.ensmp.fr
I just posted this idea with a new heading. ALTER seems like the right approach, but notice we need to complete another TODO item to allow tablespace movement of databases and schemas. --------------------------------------------------------------------------- Fabien COELHO wrote: > > Dear Tom, > > >> o remove non-portable TABLESPACE clause from CREATE TABLE and > >> use a new default_tablespace SET variable > > > > I'm coming around to the conclusion that this is simply a bad idea. > > I agree that the "set" approach is error prone. > > Another idea was to issue an "ALTER" after the CREATE. > > That would move the empty table from one tablespace to another, at small > cost. If it fails, it is simply ignored by the restoration process, > but the table was already created so it exists. > > > What we might want to do is invent a --notablespace option for pg_dump, > > comparable to --noowner, to let someone make a dump that contains no > > TABLESPACE clauses. > > (1) --notablespace would be useful, but it would not fix the problem > I had in mind, i.e. the transfer (possibly after a crash) of data > to another base which would not have these tablespaces. If the disk > is crashed, I cannot redo the pg_dump. > > (2) thus it would help to be able to decide this at "restore" time. > I think that one of the implementation idea was to store the > information into some headers. > > (3) possible current workaround for the desperate admin: > (a) create fake tablespaces as necessary... > (b) pg_restore ... | sed 's/TABLESPACE .*//' | psql ... > > Have a nice day, > > -- > Fabien Coelho - coelho@cri.ensmp.fr > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- 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, Pennsylvania19073
> o fix shared memory on Win2k terminal server > > We might be able to just mark this as not supported. I have attached a patch that I think fixes this. The problem I saw and fixed is, that the shmem created in a terminal services client is not visible to the console (or services.msc). It was necessary to differenciate OS versions, this might be better put elsewhere. I think in addition the system global name "sharemem.1" should be made more pg specific, like "PostgreSQL.1". I have not done this since a new compile would not detect a running old beta. But now would be the time (or never). Andreas
Attachment
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.)
regards, tom lane
Agreed on the memory name change and I will do it when I apply the
patch.
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---------------------------------------------------------------------------
Zeugswetter Andreas DAZ SD wrote:
>
> > o fix shared memory on Win2k terminal server
> >
> > We might be able to just mark this as not supported.
>
> I have attached a patch that I think fixes this. The problem I saw
> and fixed is, that the shmem created in a terminal services client is not
> visible to the console (or services.msc).
>
> It was necessary to differenciate OS versions, this might be better put
> elsewhere.
>
> I think in addition the system global name "sharemem.1" should be made more
> pg specific, like "PostgreSQL.1". I have not done this since a new compile
> would not detect a running old beta. But now would be the time (or never).
>
> Andreas
Content-Description: shmem.win32.patch
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
--
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 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, Pennsylvania19073
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 |/
Philip Warner <pjw@rhyme.com.au> 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
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 |/
Philip Warner <pjw@rhyme.com.au> 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
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 |/
Philip Warner <pjw@rhyme.com.au> 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
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 |/
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
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 |/
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 solutiono Creatingthe 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 restoreso Moving the indexes can't be dont easily after they are created becausethey are not zero-length fileso The soft-failure GUC option for non-existant tablespaces is a hack just for useby 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, Pennsylvania19073
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/personaldev 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 |/
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, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> 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
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
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.
regards, tom lane
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, Pennsylvania19073
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.
regards, tom lane
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, Pennsylvania19073
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 |/
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 thatcontrols which TS objects are created in when there'sno
explicitTABLESPACE clause. The factory default for thiswould of course be pg_default. Otherwise it's settable
justlikeany other GUC var.
* Get rid of TABLESPACE clause for CREATE SCHEMA, andpg_namespace.nsptablespace (ooops, another initdb).
* Need to define exactly what TABLESPACE clause for a databasecontrols; location of its catalogs of course, but
anythingelse?
* We could possibly say that a TABLESPACE clause attached toCREATE TABLE determines the default tablespace for
indexescreatedby the same command; I'm not sure if this is a goodidea, or if the indexes should go into
default_tablespaceabsenta TABLESPACE clause attached directly to their definingconstraints. We certainly want
default_tablespaceto controlindexes created by separate commands, so there'd be someinconsistency if we do the former.
regards, tom lane
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 |/
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
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.
(BTW, since we stole that syntax from Oracle, maybe we should check what
they do...)
regards, tom lane
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
Gavin Sherry <swm@linuxworld.com.au> 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
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, Pennsylvania19073
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, Pennsylvania19073
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
Added to open items list:
* Tablespace o add new GUC default_tablespace to control object creation when no explicit TABLESPACE
clauseexists
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
controlsonly 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,
Pennsylvania19073
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
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, Pennsylvania19073
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
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, Pennsylvania19073
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, Pennsylvania19073
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
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, Pennsylvania19073
Where are we on this patch? --------------------------------------------------------------------------- Zeugswetter Andreas DAZ SD wrote: > > > o fix shared memory on Win2k terminal server > > > > We might be able to just mark this as not supported. > > I have attached a patch that I think fixes this. The problem I saw > and fixed is, that the shmem created in a terminal services client is not > visible to the console (or services.msc). > > It was necessary to differenciate OS versions, this might be better put > elsewhere. > > I think in addition the system global name "sharemem.1" should be made more > pg specific, like "PostgreSQL.1". I have not done this since a new compile > would not detect a running old beta. But now would be the time (or never). > > Andreas Content-Description: shmem.win32.patch [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- 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, 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
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 |/
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
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 usermust 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, Pennsylvania19073
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, Pennsylvania19073
Philip Warner <pjw@rhyme.com.au> 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
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 |/
Attachment
Zeugswetter Andreas DAZ SD wrote: > I think in addition the system global name "sharemem.1" should be made more > pg specific, like "PostgreSQL.1". I have not done this since a new compile > would not detect a running old beta. But now would be the time (or never). Change made and attached. I assume there are other ways we detect an active backend on Windows and that shared memory name is only one of them. I do think we need this fix before we put out a final release so the next beta is the best time to do it. -- 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 Index: src/backend/port/win32/shmem.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/port/win32/shmem.c,v retrieving revision 1.8 diff -c -c -r1.8 shmem.c *** src/backend/port/win32/shmem.c 29 Aug 2004 05:06:46 -0000 1.8 --- src/backend/port/win32/shmem.c 12 Nov 2004 16:53:28 -0000 *************** *** 89,95 **** DWORD dwRet; s_segsize = size; ! sprintf(szShareMem, "sharemem.%d", memKey); if (flag & IPC_CREAT) { --- 89,95 ---- DWORD dwRet; s_segsize = size; ! sprintf(szShareMem, "PostgreSQL.%d", memKey); if (flag & IPC_CREAT) {
I am going to discard these emails. We haven't solve the Win32 terminal server problem and I think it needs to be moved to the TODO list instead. --------------------------------------------------------------------------- Zeugswetter Andreas DAZ SD wrote: > > > o fix shared memory on Win2k terminal server > > > > We might be able to just mark this as not supported. > > I have attached a patch that I think fixes this. The problem I saw > and fixed is, that the shmem created in a terminal services client is not > visible to the console (or services.msc). > > It was necessary to differenciate OS versions, this might be better put > elsewhere. > > I think in addition the system global name "sharemem.1" should be made more > pg specific, like "PostgreSQL.1". I have not done this since a new compile > would not detect a running old beta. But now would be the time (or never). > > Andreas Content-Description: shmem.win32.patch [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- 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