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
-------
* Win32
o 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 a
child before the signal handlers are installed. We don't have
this problem on unix because we fork and inherit the signal
handlers.
* Tablespace
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.
o fix case where template db already uses target tablespace
-- from database test
CREATE tablespace blah location '/bjm/tmp';
CREATE TABLE foo (i int) tablespace blah;
CREATE DATABASE foo tablespace blah template test;
ERROR: could not initialize database directory
DETAIL: 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
* Tablespace
o fix error message when creating objects in schema that has a
dropped tablespace as its default
* Win32
o disable readline-required psql options
o fix SSL compiles
o add binary version stamps
o fix signal-safe socket handler for SSL
o start pg_autovacuum easily
o remove log timezone string from log_line_prefix '%t'
o fix MinGW libpq to export only required symbols
o 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, Pennsylvania 19073
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.
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 variableI'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.
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, Pennsylvania 19073Tom 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 variableI'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 19073Gavin 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 variableI'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, Pennsylvania 19073One 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 location for 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 attempt to 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 variableI'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, Pennsylvania 19073
Dear Tom,
o remove non-portable TABLESPACE clause from CREATE TABLE and
use a new default_tablespace SET variableI'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
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 variableI'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, Pennsylvania 19073
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
Attachments:
shmem.win32.patchapplication/octet-stream; name=shmem.win32.patchDownload
*** ./shmem.c.orig Sun Aug 29 07:06:46 2004
--- ./shmem.c Mon Oct 18 16:35:32 2004
***************
*** 87,95 ****
HANDLE hmap;
char szShareMem[32];
DWORD dwRet;
s_segsize = size;
! sprintf(szShareMem, "sharemem.%d", memKey);
if (flag & IPC_CREAT)
{
--- 87,105 ----
HANDLE hmap;
char szShareMem[32];
DWORD dwRet;
+ OSVERSIONINFO osvi;
+ osvi.dwOSVersionInfoSize = sizeof(osvi);
+ osvi.dwPlatformId = VER_PLATFORM_WIN32_NT;
+ osvi.dwMajorVersion = 5;
+
+ GetVersionEx(&osvi);
+
s_segsize = size;
! if (osvi.dwPlatformId >= VER_PLATFORM_WIN32_NT && osvi.dwMajorVersion >= 5)
! sprintf(szShareMem, "Global\\sharemem.%d", memKey);
! else
! sprintf(szShareMem, "sharemem.%d", memKey);
if (flag & IPC_CREAT)
{
Import Notes
Resolved by subject fallback
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 19073Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
One additional idea for this item is to use CREATE to first create the
object, then move it using ALTER, and the ALTER might fail if the
tablespace doesn't exist.This seems fairly impractical, at least for indexes where there is no
way to do the ALTER before the object is filled with data.If we add a new SET variable and use it in pg_dump we will have to
support it forever even if there is no practical use for it.Yeah, that's one thing that bothers me.
One interesting side-affect of allowing tablespace specification to fail
is that it might give users enough control that we can mark this item as
done:Hmm, here's a variant idea: how about a GUC variable named something like
"soft_tablespace_specs" which when TRUE would mean that a nonexistent
tablespace name in a TABLESPACE clause is ignored (maybe with a WARNING)
rather than being an error, and so the object is created in whatever the
default tablespace for it would be. You wouldn't even necessarily want
to have pg_dump set this true for itself, but people could turn it on
when they needed to load a dump with wrong tablespace names in it.
(If we didn't have pg_dump turn it on automatically, then we'd not be
beholden to support it forever.)
That's a nice idea in that it doesn't require a SET for every object
that uses tablespace, and allows user control over tablespace failure.
The only downside is that it prevents SQL-compliant CREATE syntax in
dumps. I was thinking you could use ALTER just for tables but then the
tablespace failure rules would be different for tables and other
objects, which is unacceptable.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
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).
Does this actually fix the problem for you?
Because, as I have previously posted I think, it does *not* solve the
problem on any of my test machines. I still get the shmget() error
message when running from a TS session.
(Yes, I tried specifically with your patch, since I thought maybe I got
something wrong before)
I'm on Windows 2000 Server, tested both DC and non-DC machines. What are
you testing on?
Also, I don't really see how the visibility of the shmem segment
matters. We can't *create* the first instance of it, which should not
affect this at all. And if we passed that, all backends are still
execute in the same session, so there is no effect on it.
services.msc only interacts with the SCM, it has nothing at all to do
with shmem.
//Magnus
Import Notes
Resolved by subject fallback
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 |/
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).Does this actually fix the problem for you?
Because, as I have previously posted I think, it does *not* solve the
problem on any of my test machines. I still get the shmget() error
message when running from a TS session.
I think you are having another problem.
I can create it here (with or without the patch). I am running
W2000 5.00.2195 SP4. Maybe you are having a permissions problem?
I am using a user with near Administrator privs.
Also, I don't really see how the visibility of the shmem segment
matters.
If it really does not matter, please don't apply my patch Bruce.
(still do the rename though please)
We can't *create* the first instance of it, which should not
affect this at all. And if we passed that, all backends are still
execute in the same session, so there is no effect on it.
Yes it only matters if postmaster is started/trying to start from different
TS Sessions. I think we need to determine global existance of the shm segment
to get rid of old processes/segments.
services.msc only interacts with the SCM, it has nothing at all to do
with shmem.
I meant if "run as a service", which is the same TS session as the console.
Andreas
Import Notes
Resolved by subject fallback
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 solution
o Creating the tablespaces before the dump is restored is
a good solution for moving tablespaces, but as Tom pointed
out, it doesn't work well for non-super-user restores
o Moving the indexes can't be dont easily after they are
created because they are not zero-length files
o The soft-failure GUC option for non-existant tablespaces
is a hack just for use by pg_dump. It doesn't fix the
problem that the tablespace clause makes the SQL nonstandard.
And the best quote from the thread:
Philip Warner wrote:
<soapbox>
A fact I positively loath! Relying on the 'bluder-on-regardless' approach
is not something I'd like to enshrine.
</soapbox>
The 'bluder-on-regardless' phrase is very funny.
---------------------------------------------------------------------------
Philip Warner wrote:
At 05:41 AM 21/10/2004, Robert Treat wrote:
I've been thinking of an alternative solution that sounds very similar
to this. The idea is to output the CREATE TABLESPACE commands inside
pg_dump....
...
1) tablespace already exists
to fail, but since we no longer stop on error during restore,<soapbox>
A fact I positively loath! Relying on the 'bluder-on-regardless' approach
is not something I'd like to enshrine.
</soapbox>2) if you have to restore on a machine with a different disk layout,
give pg_restore a --override-tablespace command, which would substitute
pg_default tablespace into the creation command of any tablespaces that
get passed in. The bonus is that we would only have to parse on one
specific command rather than worry about parsing several different
commands. ...I'm still inclined to avoid any parsing if at all possible. We should hit
the code that generates the definitions (90% in pg_dump) and turn the
definitions into more intelligent templates.... I was thinking that you could
create two logical tablespaces on the same physical directory.This is basically the virtual/fake tablespace idea.
Sometimes I think it is worth stepping back from a problem and ask what
would we do if we had a clean slate, then use that to inform our current
set of decisions. I'd be very interested in other people's ideas, but my
thinking is:- we might not have a tablespace clause inside object definitions; we could
add a STORE TABLE xxx IN TABLESPACE ts statement. Not really practical
now; but worth bearing in mind as an approach for future non-standard syntax.
- we'd have a full set of ALTER xxx SET TABLESPACE commands which moved
relevant data etc. We have most of them.
- pg_dump would issue the alter commands after creating the object; OK, it
moves files, not very efficient, but using a 'SET MAGIC_TABLESPACE_VAR' has
previously been discounted as a solution.*If* you accept this as a good approach in an ideal world, then I think we
need to ask ourselves if we should implement the remaining ALTER commands
in 8.0 and be done with it.The other solutions: magic-tablespace-var, virtual-tablespaces...all seem
to add clunky functionality that will only be used in pg_dump. If we're
going to add something, I'd prefer not to add clutter.----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073At 12:38 PM 25/10/2004, Bruce Momjian wrote:
o Anything that works only for pg_restore and hence doesn't
work for ASCII dumps isn't an acceptable solution
Agree; but don't forget that an ascii dump is implemented almost
identically to "pg_dump | pg_restore", so when I refer to using pg_restore
in this thread it almost certainly applies to ascii dumps as well. Eg.
extra stuff in the TOC, and using the definition as a template *will*
produce the requested output in ascii dumps.
o Creating the tablespaces before the dump is restored is
a good solution for moving tablespaces, but as Tom pointed
out, it doesn't work well for non-super-user restores
And for users who want to create a single database with no extra
tablespaces (eg. development version vs. production instance).
o Moving the indexes can't be dont easily after they are
created because they are not zero-length files
Pity.
o The soft-failure GUC option for non-existant tablespaces
is a hack just for use by pg_dump. It doesn't fix the
problem that the tablespace clause makes the SQL nonstandard.
If we can adopt the move-after-create solution, then we really only have
two options:
- virtual tablespaces (which do seem kind of useful, especially for
development vs. production config where the local/personal dev version
can use the same script as a production DB but not need half a dozen TSs)
- magic-tablespace-var that behaves like the schema search path
Are there any others?
And the best quote from the thread:
Philip Warner wrote:
<soapbox>
A fact I positively loath! Relying on the 'bluder-on-regardless' approach
is not something I'd like to enshrine.
</soapbox>The 'bluder-on-regardless' phrase is very funny.
Fame at last! Even with the typo.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/
Philip Warner wrote:
If we can adopt the move-after-create solution, then we really only have
two options:- virtual tablespaces (which do seem kind of useful, especially for
development vs. production config where the local/personal dev version
can use the same script as a production DB but not need half a dozen TSs)- magic-tablespace-var that behaves like the schema search path
I was thinking we could have a var like schema search path that
specifies where we try to create the object:
SET tablespace_path = 'tblspc1, pg_default';
CREATE TABLE test(x int);
This combines the idea of pulling the TABLESPACE specification out of
the CREATE, and allows a fallback if the primary tablespace doesn't
exist.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce 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, Pennsylvania 19073Bruce 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, Pennsylvania 19073At 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 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
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, Pennsylvania 19073Tom Lane wrote:
I think a viable solution is to go with the latter (ie, for CREATE TABLE
foo(i int primary key) TABLESPACE ts; the index on i is created in
default_tablespace). However, I might be nice to be able to specify the
tablespace as part of the primary key clause. I say nice, but not
necessary.We already have that don't we?
create table foo (f1 int,
primary key (f1) using index tablespace its)
tablespace tts;The question is where to put foo_pkey when "using index tablespace"
isn't there but "tablespace" is.
I think that lacking a tablespace clause in the index section the
behavior of least surprise would be to use the outer tablespace
specification if it exists, and if not use the GUC variable for the
tablespace (basically the tablespace of the table for the index). We
already name the tablespace using our own name if we create it as part
of CREATE TABLE so it seems natural to also use the tablespace of the
table. The idea that a non-specified value defaults to the outer
level's default (tablespace) is a natural behavior people expect.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Dear 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 clause exists
Use it in pg_dump.
o Remove tablespace default for databases and schemas
Place objects as specified by the TABLESPACE clause or
default_tablespace. The database tablespace controls only
the system objects.
---------------------------------------------------------------------------
Tom Lane wrote:
Philip Warner <pjw@rhyme.com.au> writes:
At 08:00 AM 26/10/2004, Tom Lane wrote:
I don't want a GUC variable that actively changes the default
tablespace; at least not unless you want to abandon the current
mechanisms for default tablespace choices entirely, and go over to
making the GUC variable be the sole arbiter.Something consistent with Schemas does sound good to me; a tablespace
search path (or just single default), and support for a TABLESPACE clause
on table and INDEX definitions would be good.I can't see what a search path would be good for.
For the three largest databases I work on, the namespace/schema that a
table resides in is irrelevant to the tablespace that it should be stored
in. So default tablespaces on the schema are a bit of a pointless feature.
The ability to have the features of schemas: default tablespace for given
users, a GUC variable, and ACLs on tablespaces would be far more valuable.Another nice thing is that not having default tablespaces associated
with schemas eliminates that nasty issue about being able to drop such a
tablespace while the schema is still there.It seems like we still need some notion of a database's schema, to put
the system catalogs in, but perhaps that need not be the same as the
default schema for user tables created in the database?I'd be willing to jump this way if we can work out the
default-tablespace inconsistencies that Bruce has on the open items
list. Does anyone want to draft a concrete proposal? It seems like the
basic elements are:* A GUC variable named something like default_tablespace that
controls which TS objects are created in when there's
no explicit TABLESPACE clause. The factory default for this
would of course be pg_default. Otherwise it's settable just
like any other GUC var.* Get rid of TABLESPACE clause for CREATE SCHEMA, and
pg_namespace.nsptablespace (ooops, another initdb).* Need to define exactly what TABLESPACE clause for a database
controls; location of its catalogs of course, but anything else?* We could possibly say that a TABLESPACE clause attached to
CREATE TABLE determines the default tablespace for indexes
created by the same command; I'm not sure if this is a good
idea, or if the indexes should go into default_tablespace
absent a TABLESPACE clause attached directly to their defining
constraints. We certainly want default_tablespace to control
indexes created by separate commands, so there'd be some
inconsistency if we do the former.regards, tom lane
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073I 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, Pennsylvania 19073On 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, Pennsylvania 19073Added to open items:
* Add a GUC variable to control temporary and TOAST tablespace usage
---------------------------------------------------------------------------
Gavin Sherry wrote:
On Sun, 31 Oct 2004, Bruce Momjian wrote:
Tom Lane wrote:
I wrote:
I'd be willing to jump this way if we can work out the
default-tablespace inconsistencies that Bruce has on the open items
list.After further thought it seems to me that using a default_tablespace
GUC variable doesn't eliminate all the open issues. In particular
it is no help for the problem of merging two different tablespaces
during CREATE DATABASE, ie, creating a new DB with a dattablespace
that is different from the template DB's default when the template
DB already has some tables explicitly placed into that tablespace.
In this situation we have the problem that the cloned DB would
have pg_class rows with different references to the same tablespace
(either zero for the database default, or the explicit OID of the
tablespace). Among other things this would make it impossible to
use the cloned DB again as a template for CREATE DATABASE.Right. I would say 99% of people are using template1 as the template
for new databases, and if we clearly give an error message when they use
a database not in the default tablespace (which we do now), it seems
just fine. Let's see how many people complain and make adjustments in
8.1 if needed.I agree.
AFAICS this problem stems ultimately from the choice to have a
special representation (zero) in pg_class for the database's default
tablespace. The only way to really get rid of it would be to eliminate
that provision and say that pg_class.reltablespace is always the correct
explicit OID. What that would mean in turn is that we could not copy a
database and move its tables into a different tablespace, at least not
without very major work on CREATE DATABASE to make it alter pg_class
on-the-fly while copying.Agreed. That is just too much work for so little gain.
I agree. Although, I think having a createdb() with transaction semantics
and the ability to modify data on the fly would be useful -- not just for
tablespace handling. As you say, it is a fair bit of work, however.We might want to think about doing that eventually, but for now I'd
say that the restriction on merging tablespaces is just something
we have to live with. It's less annoying than not being able to
relocate a database, for sure.One downside that came up yesterday in a discussion is that once shemas
don't have default tablespaces we can't easily have default tablespaces
for toast and temporary table system schemas. Now we can't actually do
that now anyway because they are created by the system but it might
limit how we can control these in the future. I am just throwing this
out as a point.Neil has been talking to me about being able to set a tablespace for
temporary tables at or after create database time.I'm not sure about TOAST however. I considered the idea of adding
something to CREATE TABLE like TOASTSPACE <tablespace>, such that all
TOAST tables would be put in the 'toastspace'. But I think the syntax is
ugly and would confuse many users who do not know what toast is.Thanks,
Gavin
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce 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, Pennsylvania 19073Where 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 19073It makes no difference on any of my systems, so at least it doesn't
completely solve the problem. I haven't heard any confirmation on wether
it partially solves it.
//Magnus
Show quoted text
-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]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 servicesclient 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---------------------------(end of
broadcast)---------------------------
TIP 8: explain analyze is your friend
Import Notes
Resolved by subject fallback
It makes no difference on any of my systems, so at least it doesn't
completely solve the problem. I haven't heard any
confirmation on wether
it partially solves it.
It certainly does not solve any part of your problem. I think your problem
is a permissions problem.
It does however make sure the shmem is global, which I think we need for
detecting certain parallel startup (still running backend) situations.
Andreas
Import Notes
Resolved by subject fallback
o fix shared memory on Win2k terminal server
We might be able to just mark this as not supported.
The shmem code works in a terminal server session with or without the patch.
Magnus had a different problem, probably permissions. Since I do not have a
non admin user (on a TS server) I disabled the win32_is_admin check.
It is my opinion that we should allow pg to run as Admin on Windows, at least
with an override option. Services that run under a specified user are a headache
on Win32, because you need to store a password, and a lot of systems only have
one user.
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).
The decision is simple. If we want the shmem to be global on the machine we need
the patch. I think we want that, but can not give an authoritative answer.
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).
I think all agreed that this is good.
Andreas
Import Notes
Resolved by subject fallback
It is my opinion that we should allow pg to run as Admin on Windows, at least
with an override option. Services that run under a specified user are a headache
on Win32, because you need to store a password, and a lot of systems only have
one user.
Well I don't know that I agree with this. Probably the biggest
problem with Windows is not Windows, it is lazy admins.
You either want to do it right, or you don't. Most Windows admins
just want it to work as quickly as possible, throwing caution and
and common sense to the wind.
And no, I am not a Windows hater, I am just someone who used to
make a lot of money fixing the mistakes of all those lazy admins.
Sincerely,
Joshua D. Drake
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).The decision is simple. If we want the shmem to be global on the machine we need
the patch. I think we want that, but can not give an authoritative answer.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).I think all agreed that this is good.
Andreas
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQLo fix shared memory on Win2k terminal server
We might be able to just mark this as not supported.
The shmem code works in a terminal server session with or
without the patch.
Magnus had a different problem, probably permissions. Since I
do not have a non admin user (on a TS server) I disabled the
win32_is_admin check.
Ok. That explains why it didn't work for me - it wasn't intended to
solve the problem I was looking at.
It is my opinion that we should allow pg to run as Admin on
Windows, at least with an override option. Services that run
under a specified user are a headache on Win32, because you
need to store a password, and a lot of systems only have one user.
That is a different issue alltogether. But really - install from the
installer and it will handle these things for you. And keeping one extra
password in your safe storage is not going to make much difference...
But again, different issue.
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).
The decision is simple. If we want the shmem to be global on
the machine we need the patch. I think we want that, but can
not give an authoritative answer.
Right. Looking at the patch from that POV, it certainly seems
reasonable. The issue otherwise is that one pg installation running in a
TS session could conflict with one running as a service, for example.
(The postmaster-already-up-detection breaks)
(This only applies to commandline-started things, services *always* use
the global namespace by default)
Just one question about the actual implementation of the patch - why are
you setting the OS version *before* you call GetVersionEx()? It'll just
get overwritten...
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).
I think all agreed that this is good.
Yes. Definitly. For stuff in the global namespace it's definitly
preferred if you can relate to which app/server it belongs.
//Magnus
Import Notes
Resolved by subject fallback
Just one question about the actual implementation of the patch - why are
you setting the OS version *before* you call GetVersionEx()?
The Microsoft Example did a memset on the structure before calling void GetVersionEx().
Setting it to a version that needs the Global\ is only a safeguard against a failing
call. I guess the call cannot fail, and thus setting it before is not really needed.
It'll just get overwritten...
Yes, if the void call does not fail to work.
Andreas
Import Notes
Resolved by subject fallback
Just one question about the actual implementation of the
patch - why
are you setting the OS version *before* you call GetVersionEx()?
The Microsoft Example did a memset on the structure before
calling void GetVersionEx().
The docs only say you have tos et the dwOSVersionInfoSize member before
you call it, no need to memset.
Setting it to a version that needs the Global\ is only a
safeguard against a failing call. I guess the call cannot
fail, and thus setting it before is not really needed.
The call will return FALSE when it fails, and this should probably be
checked - missed that. It is *not* a void function, it is a BOOL
function.
It can fail. For example, if you pass it a size that is not supported by
the OS you are on (say a OSVERSIONINFOEX structure on a pre-sp6 NT4). I
don't think it can fail for other reasons than that, but I'm not sure.
It should probably be checked..
(See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sysinfo
/base/getversionex.asp)
//Magnus
Import Notes
Resolved by subject fallback
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
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 |/
Attachments:
TODO item removed:
* Allow database recovery where tablespaces can't be created
When a pg_dump is restored, all tablespaces will attempt to be created
in their original locations. If this fails, the user must be able to
adjust the restore process.
Not done yet, but it will be with SET default_tablespace.
I don't think we need "adjust" but rather default to the default
tablespace is just fine, and they can pre-create tablespaces in
different locations to adjust the restore anyway.
Great!
---------------------------------------------------------------------------
Philip Warner wrote:
At 06:19 AM 6/11/2004, Tom Lane wrote:
You had muttered something about wanting to add
a TOC entry field for this --- do you still want to do the work?
You can probably get it done faster than I could, but I dunno if you
have time at the moment. I'd like to get it in over the weekend so
that we can put out a new beta next week.Time is at a serious premium for me at the moment (I have several projects
all due about now); but I wrote a patch for this a few weeks back, so it
should not be a lot of work (unless pg_dump has changed in the last couple
of months).I will *try* to get it done by Monday morning your time, and will let you
know if I am going to miss this deadline as soon as I know.BTW, part of the backend changes was to stop emitting TABLESPACE
clauses in pg_get_indexdef() and pg_get_constraintdef() output,
so as of CVS tip pg_dump will in fact fail to restore index tablespaces
accurately. I assume this is the backend behavior you want, but
holler if not.Excellent. I assume that anything that can have a tablespace (database,
schema(?), table and index -- anything else?) should emit a 'set
default_tablespace="ts"' before creation (and that this will affect
auto-created indexes as appropriate, whatever that means).Thanks for all the work.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073FYI, we need tablespace_default to control this pg_dump output for a
primary key:
ALTER TABLE ONLY test2
ADD CONSTRAINT test2_pkey PRIMARY KEY (x);
---------------------------------------------------------------------------
Tom Lane wrote:
Philip Warner <pjw@rhyme.com.au> writes:
Time is at a serious premium for me at the moment (I have several projects
all due about now); but I wrote a patch for this a few weeks back, so it
should not be a lot of work (unless pg_dump has changed in the last couple
of months).If you have a preliminary patch, you could pass it along and I'll finish
it up.Excellent. I assume that anything that can have a tablespace (database,
schema(?), table and index -- anything else?) should emit a 'set
default_tablespace="ts"' before creation (and that this will affect
auto-created indexes as appropriate, whatever that means).default_tablespace will affect both tables and auto-created indexes.
But I was under the impression that pg_dump deliberately avoids
auto-creation of indexes... isn't each one split out as an ADD
CONSTRAINT operation?Schemas don't have tablespaces anymore.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Philip 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
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 19073Attachments:
/bjm/difftext/plainDownload
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 19073I 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.
Yes, please do that. I do not think there is a problem on TS other than some
missing permissions. The patch was only intended to avoid starting 2 postmasters
for the same datadir. The patch is not 100% since I was told that my check would
fail on Win95 and falsely assume Win95 allows a \ in the shmem name.
I think that would currently not matter, but it is unclean for the future.
Andreas
Import Notes
Resolved by subject fallback