Christopher Kings-Lynne [EMAIL PROTECTED] writes:
Has anyone tested inheritance with tablespaces? ie. child in different
tablespace to parent, select query that goes over both...?
They're at completely different levels of the system ... I'd be as
surprised to hear of a bug here as to hear
Dear Philip,
Actually I was thinking of a little more than a setting to ignore errors;
we would need to:
- modify pg_dump to store the tablespace name as a separate
part of the TOC entry, NOT as part of the CREATE TABLE.
- modify pg_restore to issue 'set default tablespace
At 06:14 PM 20/08/2004, Fabien COELHO wrote:
This prior SET option looks much better and cleaner. Maybe the TOC entry
update is not really necessary if the SET is separate?
I'd prefer if it was separate since we want to minimize the number of
multi-statement TOC entries...I think. A new TOC entry
Dear Philip,
I can give a hand about the implementation over the week-end, [...]
I'm happy to do the pg_dump changes, assuming Tom gets the SET stuff sorted
out. But would appreciate it if you could do some testing.
Ok. Just tell me.
As European/American/Asian timezones are involved, it
At 03:14 PM 20/08/2004, Tom Lane wrote:
If we attempt
to reload this mess with a different default tablespace for the parent
object, what happens to the child in each case?
ISTM that for a table create with CREATE TABLE...TABLESPACE we should try
to preserve the tablespace when doing a
If the location doesn't exist will postgresql try to create it? istm
it could do this and if it fails then you are no worse off, but if it
were to succeed you're that much better off.
Yea, I assume if you can't create the tablespace you put everything for
that tablespace in the default
Dear Bruce,
Bruce - pg_dump TODO for --no-tablespace or something?
Uh, TODO already has:
* 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
In point of fact I think this discussion is much ado about nothing, as
there is already a workaround
Just call that a kludge as it means that the admin is expected to create
as many dummy and unknown (if you have a custom dump file) tablespaces as
necessary to please pg_restore.
These
Fabien COELHO [EMAIL PROTECTED] writes:
Just call that a kludge as it means that the admin is expected to create
as many dummy and unknown (if you have a custom dump file)
tablespaces
There are any number of ways to find it out --- read the output of
pg_restore -s, or just try the restore and
At 12:21 AM 20/08/2004, Tom Lane wrote:
You can give it a new paint job in 8.1, if you like.
To side-step the issue, is there a tablespace equivalent of a default schema?
Could we 'set default tablespace xxx', then have pg_dump/restore use a
'create table' that does not refer to the tablespace?
Tom Lane wrote:
Fabien COELHO [EMAIL PROTECTED] writes:
Just call that a kludge as it means that the admin is expected to create
as many dummy and unknown (if you have a custom dump file)
tablespaces
There are any number of ways to find it out --- read the output of
pg_restore -s, or
Philip Warner wrote:
At 12:21 AM 20/08/2004, Tom Lane wrote:
You can give it a new paint job in 8.1, if you like.
To side-step the issue, is there a tablespace equivalent of a default schema?
Could we 'set default tablespace xxx', then have pg_dump/restore use a
'create table' that does
At 02:33 AM 20/08/2004, Bruce Momjian wrote:
Could we 'set default tablespace xxx', then have pg_dump/restore use a
'create table' that does not refer to the tablespace?
That is what I was assuming. You can't retroactively change the dump
file during restore so we would have some SET varaiable
Philip Warner wrote:
At 02:33 AM 20/08/2004, Bruce Momjian wrote:
Could we 'set default tablespace xxx', then have pg_dump/restore use a
'create table' that does not refer to the tablespace?
That is what I was assuming. You can't retroactively change the dump
file during restore so we
At 12:37 PM 20/08/2004, Bruce Momjian wrote:
But that doesn't fix ascii dumps loaded via psql.
It does; the ascii dump file is generated by exactly the same technique as
pg_restore.
Internally, pg_dump builds a TOC, then calls RestoreArchive to dump the
text. It was designed this way for a
Philip Warner [EMAIL PROTECTED] writes:
At 12:37 PM 20/08/2004, Bruce Momjian wrote:
But that doesn't fix ascii dumps loaded via psql.
It does; the ascii dump file is generated by exactly the same technique as
pg_restore.
Right. Philip's suggestion would essentially use the same technique
At 01:09 PM 20/08/2004, Tom Lane wrote:
It seemed like a reasonable idea to me...
Do we have a SET DEFAULT TABLESPACE? Can we add one for this release? If
not, we probably need to go with the ALTER TABLE. Although a SET DEFAULT
TABLESPACE would be convenent in general.
On Fri, Aug 20, 2004 at 01:26:39PM +1000, Philip Warner wrote:
At 01:09 PM 20/08/2004, Tom Lane wrote:
It seemed like a reasonable idea to me...
Do we have a SET DEFAULT TABLESPACE? Can we add one for this release? If
not, we probably need to go with the ALTER TABLE. Although a SET DEFAULT
Alvaro Herrera wrote:
On Fri, Aug 20, 2004 at 01:26:39PM +1000, Philip Warner wrote:
At 01:09 PM 20/08/2004, Tom Lane wrote:
It seemed like a reasonable idea to me...
Do we have a SET DEFAULT TABLESPACE? Can we add one for this release? If
not, we probably need to go with the ALTER
Alvaro Herrera [EMAIL PROTECTED] writes:
The problem with ALTER TABLE is that it can be hugely expensive, I think.
As long as you did it before loading any data, it wouldn't be too bad.
But certainly a preceding SET would be cheaper than pushing even
zero-size files around.
I don't have any
At 01:47 PM 20/08/2004, Tom Lane wrote:
But someone
needs to take a close look at pg_dump's logic to see if this can work.
Not sure where the issues lie, but anything that can reside in a tablespace
(table, index,...anything else?), needs to dump it's definition without
reference to a
One point here is the handling of index tablespaces. I added TABLESPACE
as part of pg_get_indexdef output, but we'd need a different solution
if we want to go down this path. Maybe it's not a problem given this
idea about where pg_dump is going to specify tablespace. But someone
needs to take a
What have I missed? I can do the pg_dump stuff if noone else wants to.
I'm all of a sudden really busy :( Extra karate at nights + new
responsibilities at work, so my plan on doing the stuff listed for
pg_dump under TODO (specifically comments on index and composite type
columns) is rather
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
One point here is the handling of index tablespaces. I added TABLESPACE
as part of pg_get_indexdef output, but we'd need a different solution
if we want to go down this path.
Another parameter to pg_get_indexdef() :(
Actually I think we'd
At 02:27 PM 20/08/2004, Tom Lane wrote:
Actually I think we'd just revert the ruleutils.c
Just to confirm; it's only tables and indexes that have tablespaces, and I
can issue some kind of SET command. Any idea of the syntax?
As an aside: should a database be allowed to have a default tablespace?
Philip Warner [EMAIL PROTECTED] writes:
Just to confirm; it's only tables and indexes that have tablespaces, and I
can issue some kind of SET command. Any idea of the syntax?
As an aside: should a database be allowed to have a default tablespace?
Well, tables and indexes definitely have
Actually I think we'd just revert the ruleutils.c change that showed
TABLESPACE in pg_get_indexdef. The real question is to be sure that
pg_dump could get along without it. If Philip wants to fix pg_dump,
I'm content to just stay out of his way ;-)
Well my original patch did without it, someone
We already have some TODO items about sorting out exactly how the
defaulting behavior works here. In particular, what if anything is the
difference between a child object inheriting a default tablespace TS,
and explicitly saying TABLESPACE TS in its definition? If we attempt
to reload this mess
Dear Christopher,
Allow database recovery where tablespaces can't be created
How is that at all a problem?
It is enough a small problem to be put in the todo list.
It's no different to the requirement to have installed all your contrib
.so's before running your restore, what's so
At restore time it doesn't do anything since pg_dumpall is a text format
only.
I'm not thinking about pg_dumpall but pg_dump/pg_restore.
Tablespaces are dumped by pg_dumpall, not pg_dump.
Chris
---(end of broadcast)---
TIP 4: Don't 'kill -9' the
I'm not thinking about pg_dumpall but pg_dump/pg_restore.
Tablespaces are dumped by pg_dumpall, not pg_dump.
If so, indeed it would be a non-issue. However,
shell pg_dump coelho | grep TABLESPACE
CREATE SCHEMA test AUTHORIZATION coelho TABLESPACE test;
TABLESPACE appears in a basic pg_dump
shell pg_dump coelho | grep TABLESPACE
CREATE SCHEMA test AUTHORIZATION coelho TABLESPACE test;
TABLESPACE appears in a basic pg_dump SQL output. If the test tablespace
does not exist, the command will fail, and so my whole restoration.
Thus I still stick to my opinion;-)
Your complaint was that
Dear Christopher,
TABLESPACE appears in a basic pg_dump SQL output. If the test tablespace
does not exist, the command will fail, and so my whole restoration.
Thus I still stick to my opinion;-)
Your complaint was that you need a way of continuing a restore if the
_tablespace_ cannot
On Wednesday 18 August 2004 04:39, Christopher Kings-Lynne wrote:
shell pg_dump coelho | grep TABLESPACE
CREATE SCHEMA test AUTHORIZATION coelho TABLESPACE test;
TABLESPACE appears in a basic pg_dump SQL output. If the test
tablespace does not exist, the command will fail, and so my
Dear Robert,
Chris, help me understand this will you?
I'm not Chris, but it looks like Robert may eventually share my concerns,
so I'm happy not to be alone on this one ;-)
On my production system I have a few very large tables I want to move
into their own tablespace so I can but them a a
It is a little bit different because a schema, a table or a function are
database application issues and are normally addressed by pg_dump and
pg_restore, although tablespaces are more an administration issue wrt disk
layout and the like, which are likely to be different from one machine to
On Wednesday 18 August 2004 21:39, you wrote:
Christopher Kings-Lynne wrote:
It is a little bit different because a schema, a table or a function
are database application issues and are normally addressed by pg_dump
and pg_restore, although tablespaces are more an administration issue
Robert Treat [EMAIL PROTECTED] writes:
If the location doesn't exist will postgresql try to create it? istm it could
do this and if it fails then you are no worse off, but if it were to succeed
you're that much better off.
I think this would be fairly pointless. In most of the practical
Robert Treat wrote:
On Wednesday 18 August 2004 21:39, you wrote:
Christopher Kings-Lynne wrote:
It is a little bit different because a schema, a table or a function
are database application issues and are normally addressed by pg_dump
and pg_restore, although tablespaces are more
Fabien COELHO wrote:
(3) psql auto completion does not have CREATE/DROP TABLESPACE in
its list.
I have already posted a patch for
this(http://candle.pha.pa.us/mhonarc/patches/msg0.html) and afaik it
is on Bruce's Beta-TODO list too.
Stefan
---(end of
(3) psql auto completion does not have CREATE/DROP TABLESPACE in
its list.
I have already posted a patch for
this(http://candle.pha.pa.us/mhonarc/patches/msg0.html)
Good. I should have checked the pending patch queue.
and afaik it is on Bruce's Beta-TODO list too.
Argh, I
Fabien COELHO wrote:
Dear hackers,
Some minor comments about the new tablespace feature in 8.0beta1:
It seems to me that tablespaces and sequences are not yet prefectly
integrated.
(1) the CREATE SEQUENCE foo TABLESPACE disk2 syntax does not seem
to be implemented.
(2) when
Fabien COELHO wrote:
(3) psql auto completion does not have CREATE/DROP TABLESPACE in
its list.
I have already posted a patch for
this(http://candle.pha.pa.us/mhonarc/patches/msg0.html)
Good. I should have checked the pending patch queue.
and afaik it is on Bruce's
Dear Bruce,
(1) the CREATE SEQUENCE foo TABLESPACE disk2 syntax does not seem
to be implemented.
(2) when creating an implicit sequence with SERIAL, the sequence
is created in the tablespace of the schema/database, not the one
of the table, although indexes are added to
Fabien COELHO wrote:
Dear Bruce,
(1) the CREATE SEQUENCE foo TABLESPACE disk2 syntax does not seem
to be implemented.
(2) when creating an implicit sequence with SERIAL, the sequence
is created in the tablespace of the schema/database, not the one
of the table,
and afaik it is on Bruce's Beta-TODO list too.
Argh, I missed this one! Is it somewhere on line?
Yep, URL at the top:
Quite an unexpected location! thanks for the pointer.
Current version at ftp://momjian.postgresql.org/pub/postgresql/open_items.
IMVHO, I think the following todo item
(1) the CREATE SEQUENCE foo TABLESPACE disk2 syntax does not seem
to be implemented.
This is intentional. Sequences are not large enough to need to be
pushed around among multiple tablespaces. Also, if we did allow
sequences to be associated with tablespaces, we'd be precluding
We decided it didn't make much sense to allow the on-row sequences to be
anywhere but the default tablespace.
Hmmm...
I can understand the performance/utility rationale, but I don't like the
lack of orthogonality on principle. I like elegance;-) As a sequence looks
a lot like a table, I guess it
IMVHO, I think the following todo item should make it for 8.0:
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
49 matches
Mail list logo