Re: tablespace and sequences?

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 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 the tablespace
of the table. It would seem more logical to put it in
the same table space as the table by default?

(3) psql auto completion does not have "CREATE/DROP TABLESPACE" in
its list.

Maybe these non-important issues could be added to the TODO list.
I've noticed some todos about tablespaces, but not these.

Have a nice day,

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

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/msg00000.html) and afaik it
is on Bruce's Beta-TODO list too.

Stefan

> > (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/msg00000.html)

Good. I should have checked the pending patch queue.

> and afaik it is on Bruce's Beta-TODO list too.

Argh, I missed this one! Is it somewhere on line?

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

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 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 the tablespace
> of the table. It would seem more logical to put it in
> the same table space as the table by default?

We decided it didn't make much sense to allow the on-row sequences to be
anywhere but the default tablespace.

> (3) psql auto completion does not have "CREATE/DROP TABLESPACE" in
> its list.
>
> Maybe these non-important issues could be added to the TODO list.
> I've noticed some todos about tablespaces, but not these.

Yep, in patch queue.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Fabien COELHO 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/msg00000.html)
>
> Good. I should have checked the pending patch queue.
>
> > 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:

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

P O S T G R E S Q L

8 . 0 O P E N I T E M S

Current version at ftp://momjian.postgresql.org/pub/postgresql/open_items.

Changes
-------
* Win32
o add binary version stamps?
o fix signal-safe socket handler for SSL
o fix query cancel in psql (?)
o report correct errno codes from native Windows system calls
o shorten timezone for %t log_line_prefix
o start pg_autovacuum easily
o fix users who's timezones are not recognized
o allow installed locales rather than hardcoded one
o update encoding list to include win1250
o synchonize supported encodings and docs
* fix oid2name for tablespaces
* allow libpq to check parameterized data types
* make pgxs install the default
* add xid to log_line_prefix for PITR
* add psql tab completion for tablespaces
* cleanup FRONTEND use in /port, malloc, elog
* fix recovery of DROP TABLESPACE after checkpoint
* fix ambiguity for objects using default tablespaces
* fix case where template db already uses target tablespace
* determine proper crash recovery/logging for pg_subtrans
* remove to_char(interval) if we initdb
* have plpython reject pseudotype arguments because it crashes
* add i386 solaris spinlock code

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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 the tablespace
> > of the table. It would seem more logical to put it in
> > the same table space as the table by default?
>
> 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 should not be that hard to have it anyway.

Well, just my little opinion, and not a big issue.

Thanks for your answer.

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

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, although indexes are added to the tablespace
> > > of the table. It would seem more logical to put it in
> > > the same table space as the table by default?
> >
> > 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 should not be that hard to have it anyway.
>
> Well, just my little opinion, and not a big issue.

I can't remember why we didn't just make it orthoginal.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> > > 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 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 adjust the restore process.

Indeed, if someone step to 8.0, make some use of tablespace, and connot
move its databases because of this issue, I guess she will not going to be
happy at all... I guess something like "--ignore-tablespace" at the
restoration phase would be good. At the dump phase it would be a minimum.

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> writes:
> (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 other
implementation changes that are on the wish-list (such as storing all
sequences in a single system table, instead of needing a separate disk
file for each one).

The original patch actually had support for specifying a tablespace for
a sequence. That was deliberately removed, and it's not going to go
back in later.

regards, tom lane

> > (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 other
> implementation changes that are on the wish-list (such as storing all
> sequences in a single system table, instead of needing a separate disk
> file for each one).

That is a point.

As for the semantics, sequences have a tablespace anyway, which is the
default tablespace of the schema as it seems, and it appears in pg_class,
so it is already implemented somewhere, no doubt about that.

As for the syntax, you could decide to ignore the tablespace part of the
syntax if such evolution would require it, maybe with some warning for the
user that part of its query is no more up to date...

It would no more a big deal than dropping "LOCATION" from "CREATE
DATABASE", which is not a upward compatible change and was performed
remorselessly anyway.

> The original patch actually had support for specifying a tablespace for
> a sequence. That was deliberately removed, and it's not going to go
> back in later.

Too bad for elegance and orthogonality.

Thanks for your clear answer anyway;-)

Have a nice day,

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

>>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 should not be that hard to have it anyway.
>
> Well, just my little opinion, and not a big issue.
>
> Thanks for your answer.

Well then, should you be able to move composite types to other
tablespaces as well??

> 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 adjust the restore process.
>
> Indeed, if someone step to 8.0, make some use of tablespace, and connot
> move its databases because of this issue, I guess she will not going to be
> happy at all... I guess something like "--ignore-tablespace" at the
> restoration phase would be good. At the dump phase it would be a minimum.

How is that at all a problem? It's no different to the requirement to
have installed all your contrib .so's before running your restore,
what's so hard about making a few dirs? It's also no different to the
old database locations support. Personally, I think it's a non-issue.

It's also impossible to do as you suggest and have a --ignore-tablespace
flag. All it could do is at dump time to dump NO tablespace, which is
NOT what you want. At restore time it doesn't do anything since
pg_dumpall is a text format only.

Chris

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 hard about making a few
> dirs? It's also no different to the old database locations support.
> Personally, I think it's a non-issue.

Well, maybe.

I think the .so comparison is not fully appropriate, as the installation
is quite generic an issue, possibly addressed by packaging or some
scripting.

As for pg_dump/pg_restore, they are performed at the database level.
In the previous situation with "LOCATION", one had to handle the issue of
creating the database before a restoration.

Now with tablespace the issue is more specific, and it is possibly
embedded at the SQL level output by pg_dump/pg_restore, on which one has
much less control.

Well, maybe you suggest I can do some "| sed 's/TABLESPACE \w+//g' |" as a
kludge somewhere, or create dummy tablespaces even if I have only one
disk. That does not look really good, and I won't know what is needed by
looking at a pg_dump compressed generated file.

Maybe the right answer is that disks are now large and cheap, so who will
need tablespace anyway? So indeed there is no problem;-)

> It's also impossible to do as you suggest and have a --ignore-tablespace
> flag.

I was not arguing about implementation, but about a desirable feature for
a basic database admin.

Anyway, I think it could be implemented, possibly with some twicking in
the format, or with some setting on the server side.

Now I agree that any other feature which provide the ability to handle
this "non" issue would be welcome, I won't stick on this particular
option.

> All it could do is at dump time to dump NO tablespace, which is
> NOT what you want.

Wow! you know what I may want although I even don't know;-)

If I want to move a database from one server to another, I'm not sure the
disk layout and tablespace issues will have been handled the same way on
both machines. So some help to handle these issues would be welcome.

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

Have a nice day,

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

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

> > 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 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;-)

Have a nice day,

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

> 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 you need a way of continuing a restore if the
_tablespace_ cannot be created. ie. If the directory does not exist.

If you have objects in a tablespace, then too bad. It's no different to
if the schema the object in doesn't exist. Or the table the data is in
doesn't exist. Or the functin the view references doesn't exist.

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 be created. ie. If the directory does not exist.

Indeed it is possible that I was not clear enough!

The issue I feel should be addressed is the ability to restore a database
while ignoring tablespace issues, not only their creation but also their
uses.

> If you have objects in a tablespace, then too bad.

Well, ISTM that it is the problem I'm discussing...

If I cannot restore a base I see that as a problem, which is indeed a lack
of humour from my side.

> It's no different to if the schema the object in doesn't exist.
> Or the table the data is in doesn't exist.
> Or the functin the view references doesn't exist.

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
another (compare with I obviously want the same schema/table/function for
my application). So the notion of dump/restore of a tablespace need
some careful thinking.

But maybe I'm just stupid to dream that I could restore or transfer my
data even if I used a tablespace somewhere? ;-)

It looks that we don't have the same perspective about database
administration.

Anyway, have a nice day,

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

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 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 be created. ie. If the directory does not exist.
>
> If you have objects in a tablespace, then too bad. It's no different to
> if the schema the object in doesn't exist. Or the table the data is in
> doesn't exist. Or the functin the view references doesn't exist.
>

Chris, help me understand this will you? 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 very large disk, and a couple frequently updated tables I would like to
move into their own tablespace so i can put them on their own (small, raid
oriented) disk. I need to do all this from a physical side of things for
performance and administration in production, but when I create test
databases for developers/testing, I don't want to have to recreate the same
physical layout on every system.... it sounds like you are saying that is the
case... or maybe I am misreading you?

--
Robert Treat
Build A Better Lamp :: Linux Apache {middleware} PostgreSQL

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 very large disk, and a
> couple frequently updated tables I would like to move into their own
> tablespace so i can put them on their own (small, raid oriented) disk.
> I need to do all this from a physical side of things for performance and
> administration in production, but when I create test databases for
> developers/testing, I don't want to have to recreate the same physical
> layout on every system....

What you describe is basically the reason why I'm advocating, quite
unsuccessfully at the time, that pg_dump/pg_restore should deal with
tablespace in some careful and appropriate manner even in coming 8.0.

> it sounds like you are saying that is the case...

It is indeed the case and the reason for my query about the todo item. The
current status is that you cannot restore a dump if tablespaces where used
if the same tablespaces do not exist in the target system. So it is fine
if you want to restore on the same system, but not on another one. You
would have to create them artificially or to edit them out of the script
if you want a transfer on a different system.

Have a nice day,

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

> 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
> another (compare with I obviously want the same schema/table/function for
> my application). So the notion of dump/restore of a tablespace need
> some careful thinking.
>
> But maybe I'm just stupid to dream that I could restore or transfer my
> data even if I used a tablespace somewhere? ;-)

OK, perhaps. It it not easy to implement however, since the tablespace
clause on indexes comes from the pg_get_indexdef() function and isn't
added by pg_dump.

Bruce - pg_dump TODO for --no-tablespace or something?

Chris

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 wrt disk
> > layout and the like, which are likely to be different from one machine to
> > another (compare with I obviously want the same schema/table/function for
> > my application). So the notion of dump/restore of a tablespace need
> > some careful thinking.
> >
> > But maybe I'm just stupid to dream that I could restore or transfer my
> > data even if I used a tablespace somewhere? ;-)
>
> OK, perhaps. It it not easy to implement however, since the tablespace
> clause on indexes comes from the pg_get_indexdef() function and isn't
> added by pg_dump.
>
> 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 able to
adjust the restore process.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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
> > > wrt disk layout and the like, which are likely to be different from one
> > > machine to another (compare with I obviously want the same
> > > schema/table/function for my application). So the notion of
> > > dump/restore of a tablespace need some careful thinking.
> > >
> > > But maybe I'm just stupid to dream that I could restore or transfer my
> > > data even if I used a tablespace somewhere? ;-)
> >
> > OK, perhaps. It it not easy to implement however, since the tablespace
> > clause on indexes comes from the pg_get_indexdef() function and isn't
> > added by pg_dump.
> >
> > 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 able to
> adjust the restore process.

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.

--
Robert Treat
Build A Better Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> 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
tablespace scenarios I can think of, the tablespace directory probably
lives within a root-owned directory (eg, a filesystem root directory).
That's why CREATE TABLESPACE expects the directory to have been made
already.

In point of fact I think this discussion is much ado about nothing,
as there is already a workaround that is about as simple as anything
that we would likely be able to substitute. Suppose the dump contains
"CREATE TABLESPACE t1 LOCATION '/foo/bar'" and for some reason /foo/bar
is no longer an appropriate location. All the DBA need do is select
a location that *is* suitable and create tablespace t1 at that location.
Then run the restore. The create tablespace command will fail on
duplicate name, but the tablespace is there and all the subsequent
operations will be just fine.

Of course we need to document this procedure, but we'd have to document
any other approach as well...

regards, tom lane

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 an administration issue
> > > > wrt disk layout and the like, which are likely to be different from one
> > > > machine to another (compare with I obviously want the same
> > > > schema/table/function for my application). So the notion of
> > > > dump/restore of a tablespace need some careful thinking.
> > > >
> > > > But maybe I'm just stupid to dream that I could restore or transfer my
> > > > data even if I used a tablespace somewhere? ;-)
> > >
> > > OK, perhaps. It it not easy to implement however, since the tablespace
> > > clause on indexes comes from the pg_get_indexdef() function and isn't
> > > added by pg_dump.
> > >
> > > 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 able to
> > adjust the restore process.
>
> 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 tablespace.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Tom Lane wrote:
> Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> 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
> tablespace scenarios I can think of, the tablespace directory probably
> lives within a root-owned directory (eg, a filesystem root directory).
> That's why CREATE TABLESPACE expects the directory to have been made
> already.
>
> In point of fact I think this discussion is much ado about nothing,
> as there is already a workaround that is about as simple as anything
> that we would likely be able to substitute. Suppose the dump contains
> "CREATE TABLESPACE t1 LOCATION '/foo/bar'" and for some reason /foo/bar
> is no longer an appropriate location. All the DBA need do is select
> a location that *is* suitable and create tablespace t1 at that location.
> Then run the restore. The create tablespace command will fail on
> duplicate name, but the tablespace is there and all the subsequent
> operations will be just fine.
>
> Of course we need to document this procedure, but we'd have to document
> any other approach as well...

OK, but is the DBA going to be able to easily find the tablespaces the
dump uses?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

If your talking about a restoration, the answer is NO.

It just fails, because on "CREATE TABLE foo ... TABLESPACE bla" the table
will not be created if tablespace bla does not exists, and so the
restoration will fail.

This is the current situation, and that's why I'm arguing in the void;-)

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

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 able to
> adjust the restore process.

Sure.

I was advocating for this TODO item to be moved to the "beta" TODO for
coming 8.0, so it would be for the other list...

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

> 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 useless tablespaces just create a mess in the database, that I will
have to clean afterwards... if I can!

Then bad news, ISTM that altering the tablespace of an index, a sequence
or a schema is not implemented. So I'll have to move the files and links
around, and update manually the catalog entries, or possibly drop and
recreate all indexes... I hope I won't have large objects around, because
it might look really bad then. What a nice piece of restoration;-)

So basically I'll have created stupid directories and tablespaces and
there is no way to fix them afterwards even if they are meaningless:-(

> that is about as simple as anything that we would likely be able to
> substitute.

I really think a better job can and should be done, at least from the user
perspective.

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> 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 observe the errors.
Besides which, we are talking here about the output of pg_dumpall,
which is currently always text.

> Then bad news, ISTM that altering the tablespace of an index, a sequence
> or a schema is not implemented.

Wrong, unnecessary, and trivial respectively.

I see this request as being exactly on a par with requests to make
pg_dumpall output restore into a different set of databases, or
into a different set of schemas than what was dumped from. Sure,
it would be convenient sometimes. But it's not *necessary* and it's
not something to be starting in on when we're already well into beta.

Could we have less straw-man-bashing and more discussion of the minimum
necessary solution for this problem? It's long past time to be gilding
the lily for 8.0. You can give it a new paint job in 8.1, if you like.

regards, tom lane

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?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/

Tom Lane wrote:
> Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> 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 observe the errors.
> Besides which, we are talking here about the output of pg_dumpall,
> which is currently always text.
>
> > Then bad news, ISTM that altering the tablespace of an index, a sequence
> > or a schema is not implemented.
>
> Wrong, unnecessary, and trivial respectively.
>
> I see this request as being exactly on a par with requests to make
> pg_dumpall output restore into a different set of databases, or
> into a different set of schemas than what was dumped from. Sure,
> it would be convenient sometimes. But it's not *necessary* and it's
> not something to be starting in on when we're already well into beta.

I don't think it is the same because a dump can be restored on any
system. This is a case where the operating system has to be set up for
the restore to work completely.

> Could we have less straw-man-bashing and more discussion of the minimum
> necessary solution for this problem? It's long past time to be gilding
> the lily for 8.0. You can give it a new paint job in 8.1, if you like.

You certainly can argue that it is too late to be doing this during
beta. I always felt this was a feature we needed for 8.0 personally but
the urgency among the group is coming pretty late.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Philip Warner 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 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 you would set
before doing the restore that said to handle create tablespace errors.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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,

> > as many dummy and unknown ... tablespaces
>
> There are any number of ways to find it out --- read the output of
> "pg_restore -s", or just try the restore and observe the errors.

Ok, you're right on this point.

But I'm looking for something cleaner than grepping pg_restore output...

> Besides which, we are talking here about the output of pg_dumpall,
> which is currently always text.

AFAIC, I was talking about pg_dump in this thread.

> > Then bad news, ISTM that altering the tablespace of an index, a sequence
> > or a schema is not implemented.
>
> Wrong, unnecessary, and trivial respectively.

(1) wrong one: you'll have to update or clarify the documentation;-)
no ALTER INDEX... or do you mean DROP/CREATE INDEX?
Manually updating pg_class won't move the files.

(2) unnecessary one: if a sequence is in a tablespace that I want to
drop (maybe I need to change the disk), it seems necessary.
I might DROP/CREATE, which might interact with the application...

(3) trivial one: I guess you mean update pg_namespace by hand? Sure.

For all cases I was talking about an "ALTER" syntax. Manual DROP/CREATE
or UPDATE, or moving files, is not a nice option.

> I see this request as being exactly on a par with requests to make
> pg_dumpall output restore into a different set of databases, or
> into a different set of schemas than what was dumped from.

A schema is an application issue. The application does not change if I
move or restore it.

Changing the database is easy with pg_dump/pg_restore, which is my
concern.

However a tablespace is an administration issue. It is likely to change
from server to server.

ISTM that it is quite different.

> Sure, it would be convenient sometimes. But it's not *necessary*

My point is that it *is* necessary (meaning really useful). As it seems
that I cannot convince people, it surely mean that I'm just wrong about
that very point;-)

> Could we have less straw-man-bashing

I'm not sure about what this means, but I'm sorry if it means that my tone
is not appropriate. I'm just trying to convince.

> and more discussion of the minimum necessary solution for this problem?

I can also do that;-) I was beginning by trying to convince people that
the problem exists and should be addressed before 8.0 is out.

. solution 0a

hack manually the SQL stream out of pg_restore:
pg_restore ... | sed 's/TABLESPACE [a-zA-Z0-9_]*//g' | psql ...

. solution 0b

dummy tablespaces just to please pg_restore.
ISTM that it are hard to reverse/clean afterwards.

sh> pg_restore ... | grep 'ERROR: tablespace'
sh> mkdir ts1 ts2 ts3 ts4 ts5
pg> create tablespace "some-name" location "ts1"; ...
sh> pg_restore ...

. solution 1a

pg_dump --ignore-tablespace option so that TABLESPACE are not
appended at all in the dump. I guess the implementation is easy.

. solution 1b

pg_restore --ignore-tablespace would be even better because you don't
need to think about it a dump time (say I saved the base, the hard crashes
but I have to restore it elsewhere), but I guess the implementation
is not really simple and may require to change the dump format. Maybe
with the server cooperation as in next proposal.

. solution 2

add some server setting on restoration so that wrong/all tablespace
directives are simply ignored, instead of leading to an error.

. solution 3

separate object creation and tablespace specification statements
in pg_dump/pg_restore, so that tablespace-related failures do not
prevent object restoration. It needs the ALTER syntax.

CREATE TABLE foo ... TABLESPACE x;
vs
CREATE TABLE foo ....;
ALTER TABLE foo SET TABLESPACE x; -- may fail, but foo exists anyway

I like v3 better. I don't like "workarounds" v0a and v0b. My taste;-)

> It's long past time to be gilding the lily for 8.0. You can give it a
> new paint job in 8.1, if you like.

My feeling is that it is really useful for all people that would use
tablespace with 8.0. and will try to move/restore databases. Maybe too few
people to care.

As for the time, I thought a beta was meant for testing features and
reporting issues. I'm just doing that!

Thanks anyway for your answers and your time, have a nice day,

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

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 you would set
>before doing the restore that said to handle create tablespace errors.

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 xxxx'
before restoring a table OR, per Fabiens suggestion, issue
an ALTER TABLE after the create.

Then table-space related errors will not stop a table being created.

----------------------------------------------------------------
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:
> 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 you would set
> >before doing the restore that said to handle create tablespace errors.
>
> 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 xxxx'
> before restoring a table OR, per Fabiens suggestion, issue
> an ALTER TABLE after the create.
>
> Then table-space related errors will not stop a table being created.

But that doesn't fix ascii dumps loaded via psql.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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 bunch of reasons, and one was to avoid
too much difference between the output of each format. Which is why it is
very unlikely that "pg_dump -Fc | pg_restore" would produce output
substantially different from that of "pg_dump".

So, as long as pg_dump puts the CREATE TABLE and ALTER TABLE in different
commands, they will appear as such in the text file.

----------------------------------------------------------------
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(at)rhyme(dot)com(dot)au> 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
that we previously adopted for portability of WITH/WITHOUT OIDS --- if
the "SET" fails, it won't stop the table from being created. (Note we
have to be careful that the semantics of the SET actually cause the
error to occur on the SET and not later on the CREATE. But that's
doable.)

It seemed like a reasonable idea to me...

regards, tom lane

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.

----------------------------------------------------------------
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 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
> TABLESPACE would be convenent in general.

The problem with ALTER TABLE is that it can be hugely expensive, I think.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Thou shalt check the array bounds of all strings (indeed, all arrays), for
surely where thou typest "foo" someone someday shall type
"supercalifragilisticexpialidocious" (5th Commandment for C programmers)

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 TABLE. Although a SET DEFAULT
> > TABLESPACE would be convenent in general.
>
> The problem with ALTER TABLE is that it can be hugely expensive, I think.

I was thinking that too, but I assume they are creating the table empty,
moving it to another tablespace, then loading it.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> 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 problem with adding a SET variable at this stage of the
game, if everyone agrees it's an appropriate solution.

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 close look at pg_dump's logic to see if this can work.

regards, tom lane

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 tablespace, and pg_dump needs to be modified to dump the
tablespace name in the TOC entry, and pg_restore needs to maintain
'current' tablespace the same way it does schemas. Backend then needs to
obey the variable setting.

What have I missed? I can do the pg_dump stuff if noone else wants to.

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

> 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 close look at pg_dump's logic to see if this can work.

Another parameter to pg_get_indexdef() :(

Chris

> 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 lagging. If you feel like doing those, let me know
and I won't bother.

Chris

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> 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 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 ;-)

regards, tom lane

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 | __---_____
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(at)rhyme(dot)com(dot)au> 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 tablespaces. Schemas have
default tablespaces that their child objects inherit, though there is
no storage associated with the schema itself. Databases have default
tablespaces that (a) their child objects inherit, and (b) the system
catalogs of that database live in.

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 with a different default tablespace for the parent
object, what happens to the child in each case?

regards, tom lane

> 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 can copy that code.

Chris

> 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 with a different default tablespace for the parent
> object, what happens to the child in each case?

Has anyone tested inheritance with tablespaces? ie. child in different
tablespace to parent, select query that goes over both...?

Chris

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> 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 that integer addition
fails if the operands are from different tablespaces.

regards, tom lane

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 xxxx'
> before restoring a table OR, per Fabiens suggestion, issue
> an ALTER TABLE after the create.

This prior SET option looks much better and cleaner. Maybe the TOC entry
update is not really necessary if the SET is separate?

If the SET fails, what tablespace is expected to be chose? pg_global? I
guess the SET would be mandatory, that is it would supercede other
defaults such as chose the tablespace of the SCHEMA? Also, should there be
provision for unsetting?

I can give a hand about the implementation over the week-end, esp. as I'm
the one taking a stand on this issue. However I do not know much about
pg_dump format and issues, so I'm not sure I'm the best person for a quick
and clean implementation.

Have a nice day,

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

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 is close to zero
cost. Reformatting the TOC to include the tablespace name is more
expensive, but there are a few things I'd like to add, so it's worth it.

>If the SET fails, what tablespace is expected to be chose?

Good question. Is there a name for the normal/default/whatever tablespace?
Tom may need to implement:

SET DEFAULT TABLESPACE AS FRED
SET DEFAULT TABLESPACE DEFAULT

or something less tacky, but allowing for the default to be derived from
the schema & database rather than the last SET command. The pg_dump will
need to check the result of the SET command and reset the tablespace if it
fails...and probably die if that fails.

>I can give a hand about the implementation over the week-end, esp. as I'm
>the one taking a stand on this issue. However I do not know much about
>pg_dump format and issues, so I'm not sure I'm the best person for a quick
>and clean implementation.

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.

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

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 can go around the clock.

Good night,

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

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 dump/restore. If the table
'inherited' it's tablespace, then a dump/restore should do nothing (ie. not
issue a SET TABLESPACE).

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

Dear hackers,

> I'm happy to do the pg_dump changes, assuming Tom gets the SET stuff sorted
> out.

ISTM that the tablespace handling or ignoring in pg_dump/pg_restore is
still an open issue in current CVS head... waiting for a proper
implementation after the brain-storming on what seemed to be the
consensus, that is to output a separate

SET DEFAULT TABLESPACE somewhere;

before object creations in the dump/restore command flow.

I've noticed that the item does not seem to appear in Bruce's list, thus
I'm afraid it might be lost for 8.0 where I think it belongs... hence this
little reminder.

Have a nice day,

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

At 06:31 PM 1/09/2004, Fabien COELHO wrote:
>I've noticed that the item does not seem to appear in Bruce's list, thus
>I'm afraid it might be lost for 8.0 where I think it belongs... hence this
>little reminder.

Sounds good; I've implemented using SET in pg_dump/restore, just waiting
for the command to work. If it's not there by beta3, I'll just use ALTER
commands.

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

> Sounds good; I've implemented using SET in pg_dump/restore, just waiting
> for the command to work. If it's not there by beta3, I'll just use ALTER
> commands.

Did you deal with the pg_get_indexdef problem where it automaticlaly
adds the tablespace in index definitions?

Chris

At 08:53 PM 1/09/2004, Christopher Kings-Lynne wrote:
>Did you deal with the pg_get_indexdef problem where it automaticlaly adds
>the tablespace in index definitions?

No; the SET stuff is not there, and Tom said he'd deal with the backend
side of things when he gets a chance. Won't be 'till beta2.

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

At 10:51 PM 1/09/2004, Philip Warner wrote:

>Won't be 'till beta2.

...sorry, beta3

>

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

Fabien COELHO wrote:
>
> Dear hackers,
>
> > I'm happy to do the pg_dump changes, assuming Tom gets the SET stuff sorted
> > out.
>
> ISTM that the tablespace handling or ignoring in pg_dump/pg_restore is
> still an open issue in current CVS head... waiting for a proper
> implementation after the brain-storming on what seemed to be the
> consensus, that is to output a separate
>
> SET DEFAULT TABLESPACE somewhere;
>
> before object creations in the dump/restore command flow.
>
> I've noticed that the item does not seem to appear in Bruce's list, thus
> I'm afraid it might be lost for 8.0 where I think it belongs... hence this
> little reminder.

It isn't on the open items list because it isn't a _must_ fix for 8.0,
though it is still in my mailbox. As I remember it is to allow objects
to be created when the schema doesn't exist, and for creating more
portable pg_dump CREATE statements. If someone wants to fix that, they
have to get it working and get agreement to put it in during beta.

It is on the TODO list (the missing schemas part).

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Hi guys i was looking for the
http://developer.postgresql.org/todo.php in order to
view what things are you posponing for later versions
but the entire developer.postgresql.org site is down.

By the way, will be a way in postgresql 8 to add a
column in a middle of a table. just curious.

thanx in advance,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

Jaime Casanova wrote:

> Hi guys i was looking for the
> http://developer.postgresql.org/todo.php in order to
> view what things are you posponing for later versions
> but the entire developer.postgresql.org site is down.
>
> By the way, will be a way in postgresql 8 to add a
> column in a middle of a table. just curious.

No IIRC. The core doesn't think this is a valid feature.
I had in the past my reasons to ask for it too. If you
have yours may be...

Regards
Gaetano Mendola

Centuries ago, Nostradamus foresaw when systemguards(at)yahoo(dot)com (Jaime Casanova) would write:
> By the way, will be a way in postgresql 8 to add a
> column in a middle of a table. just curious.

What do you mean by "in a middle of a table?" A relation is simply a
set of attributes that _don't_ forcibly have an order, because sets
are unordered.
--
output = reverse("gro.gultn" "@" "enworbbc")
http://cbbrowne.com/info/nonrdbms.html
"What we need is either less corruption, or more chance to participate
in it." -- Unknown

Christopher Browne wrote:
> Centuries ago, Nostradamus foresaw when systemguards(at)yahoo(dot)com (Jaime Casanova) would write:
>
>>By the way, will be a way in postgresql 8 to add a
>>column in a middle of a table. just curious.
>
>
> What do you mean by "in a middle of a table?" A relation is simply a
> set of attributes that _don't_ forcibly have an order, because sets
> are unordered.

Means the order that \d show the columns ( for example ).

Regards
Gaetano Mendola

I've always considered a table definition to be an unordered hash so
the concept of putting something in the "middle of a table" from that
view doesn't seem to have a point.

Just my $0.02

Shahbaz Javeed

On Sat, 04 Sep 2004 21:44:24 +0200, Gaetano Mendola <mendola(at)bigfoot(dot)com> wrote:
> Christopher Browne wrote:
> > Centuries ago, Nostradamus foresaw when systemguards(at)yahoo(dot)com (Jaime Casanova) would write:
> >
> >>By the way, will be a way in postgresql 8 to add a
> >>column in a middle of a table. just curious.
> >
> >
> > What do you mean by "in a middle of a table?" A relation is simply a
> > set of attributes that _don't_ forcibly have an order, because sets
> > are unordered.
>
> Means the order that \d show the columns ( for example ).
>
>
> Regards
> Gaetano Mendola
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Shahbaz Javeed

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Shahbaz Javeed wrote:
| I've always considered a table definition to be an unordered hash so
| the concept of putting something in the "middle of a table" from that
| view doesn't seem to have a point.
|
| Just my $0.02

I already wrote my motivation, and I'll do again.
In our development environment we have a .sql file
for each table, view, function. The schema creation
is Makefile driven and the development three is
divided in packages / projects. Each project and
packages are composed by various components.
I try to organize the table definition grouping the
homogeneous field together:

Suppose that this is the definition of a table:

CREATE TABLE foo (
a1 INTEGER,
~ a2 VARCHAR,
~ ...
~ an FLOAT,
~ b1 ....
~ ...
~ bn ...
);

some time ( fortunately not so often ) you need to add
a field to that table that for homogeneity is a(n+1).
Some time is also not possible to split foo in two tables
for various reasons:

~ - is not possible define multitable constraint, of course
~ is possible with triggers but is more work: a function
~ trigger to maintain and test.

~ - is not possible to define a multitable index and no
~ work around for this, in my knowledge at least.

What we do actually is put that field at the end and not in
his logical place, this because before to apply the new schema
in production I do a diff between the production schema and the
development schema. You can argue that is the diff tool that must
be fixed however is hard to work with a table in production that
is dumped/displayed by various tools: pgaccess, dbvisualizer,
pgadminII/III, enterprise architect, visio,... in a different
way your development table.

This is why in our coding rule we have
- New fields must be added at the end.

Regards
Gaetano Mendola

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBOt717UpzwH2SGd4RAuuZAJwKkDVpwgRNI0vQJ4TC7qllQjH5XgCgmrKv
kqwKVVO3ha+FUsQHggyfyvU=
=z9y4
-----END PGP SIGNATURE-----

On Sep 4, 2004, at 10:07 PM, Christopher Browne wrote:

> Centuries ago, Nostradamus foresaw when systemguards(at)yahoo(dot)com (Jaime
> Casanova) would write:
>> By the way, will be a way in postgresql 8 to add a
>> column in a middle of a table. just curious.
>
> What do you mean by "in a middle of a table?" A relation is simply a
> set of attributes that _don't_ forcibly have an order, because sets
> are unordered.

In the SQL spec, columns are ordered, iirc, as sad as that is. Writing
application code that depends on column order is asking for pain and
suffering.

Michael Glaesemann
grzm myrealbox com

Michael Glaesemann wrote:

>
> On Sep 4, 2004, at 10:07 PM, Christopher Browne wrote:
>
>> Centuries ago, Nostradamus foresaw when systemguards(at)yahoo(dot)com (Jaime
>> Casanova) would write:
>>
>>> By the way, will be a way in postgresql 8 to add a
>>> column in a middle of a table. just curious.
>>
>>
>> What do you mean by "in a middle of a table?" A relation is simply a
>> set of attributes that _don't_ forcibly have an order, because sets
>> are unordered.
>
>
> In the SQL spec, columns are ordered, iirc, as sad as that is. Writing
> application code that depends on column order is asking for pain and
> suffering.

All we agree on this, the only reason this feature is needed is to continue
to have a well table structure, see my last post.

Regards
Gaetano Mendola

Bruce Momjian schrieb:
> Fabien COELHO wrote:
>>Dear hackers,
>>>I'm happy to do the pg_dump changes, assuming Tom gets the SET stuff sorted
>>>out.
>>
>>ISTM that the tablespace handling or ignoring in pg_dump/pg_restore is
>>still an open issue in current CVS head... waiting for a proper
>>implementation after the brain-storming on what seemed to be the
>>consensus, that is to output a separate
>>
>> SET DEFAULT TABLESPACE somewhere;
>>
>>before object creations in the dump/restore command flow.
>>
>>I've noticed that the item does not seem to appear in Bruce's list, thus
>>I'm afraid it might be lost for 8.0 where I think it belongs... hence this
>>little reminder.
>
> It isn't on the open items list because it isn't a _must_ fix for 8.0,
> though it is still in my mailbox. As I remember it is to allow objects
> to be created when the schema doesn't exist, and for creating more
> portable pg_dump CREATE statements. If someone wants to fix that, they
> have to get it working and get agreement to put it in during beta.
>
> It is on the TODO list (the missing schemas part).

But the regression test fails: (the only failing test against cvs HEAD)
This is not only a pg_dump/pg_restore issue, or?

-- Will fail with bad path
CREATE TABLESPACE badspace LOCATION '/no/such/location';
ERROR: could not set permissions on directory "/no/such/location": No
such file or directory
-- No such tablespace
CREATE TABLE bar (i int) TABLESPACE nosuchspace;
ERROR: tablespace "nosuchspace" does not exist
-- Fail, not empty
DROP TABLESPACE testspace;
ERROR: tablespace "testspace" is not empty
DROP SCHEMA testschema CASCADE;
NOTICE: drop cascades to table testschema.foo
-- Should succeed
DROP TABLESPACE testspace;

=>

***************
*** 38,45 ****
ERROR: tablespace "nosuchspace" does not exist
-- Fail, not empty
DROP TABLESPACE testspace;
! ERROR: tablespace "testspace" is not empty
DROP SCHEMA testschema CASCADE;
! NOTICE: drop cascades to table testschema.foo
-- Should succeed
DROP TABLESPACE testspace;
--- 41,49 ----
ERROR: tablespace "nosuchspace" does not exist
-- Fail, not empty
DROP TABLESPACE testspace;
! ERROR: tablespace "testspace" does not exist
DROP SCHEMA testschema CASCADE;
! ERROR: schema "testschema" does not exist
-- Should succeed
DROP TABLESPACE testspace;
+ ERROR: tablespace "testspace" does not exist

======================================================================
--
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/

On Mon, 4 Oct 2004, Reini Urban wrote:

> But the regression test fails: (the only failing test against cvs HEAD)
> This is not only a pg_dump/pg_restore issue, or?
>
> -- Will fail with bad path
> CREATE TABLESPACE badspace LOCATION '/no/such/location';
> ERROR: could not set permissions on directory "/no/such/location": No
> such file or directory
> -- No such tablespace
> CREATE TABLE bar (i int) TABLESPACE nosuchspace;
> ERROR: tablespace "nosuchspace" does not exist
> -- Fail, not empty
> DROP TABLESPACE testspace;
> ERROR: tablespace "testspace" is not empty
> DROP SCHEMA testschema CASCADE;
> NOTICE: drop cascades to table testschema.foo
> -- Should succeed
> DROP TABLESPACE testspace;
>
> =>
>
> ***************
> *** 38,45 ****
> ERROR: tablespace "nosuchspace" does not exist
> -- Fail, not empty
> DROP TABLESPACE testspace;
> ! ERROR: tablespace "testspace" is not empty
> DROP SCHEMA testschema CASCADE;
> ! NOTICE: drop cascades to table testschema.foo
> -- Should succeed
> DROP TABLESPACE testspace;
> --- 41,49 ----
> ERROR: tablespace "nosuchspace" does not exist
> -- Fail, not empty
> DROP TABLESPACE testspace;
> ! ERROR: tablespace "testspace" does not exist
> DROP SCHEMA testschema CASCADE;
> ! ERROR: schema "testschema" does not exist
> -- Should succeed
> DROP TABLESPACE testspace;
> + ERROR: tablespace "testspace" does not exist

I cannot recreate on Linux. What platform, etc, are you on?

Gavin

Gavin Sherry schrieb:
> On Mon, 4 Oct 2004, Reini Urban wrote:
>>But the regression test fails: (the only failing test against cvs HEAD)
>>This is not only a pg_dump/pg_restore issue, or?
>>
>>-- Will fail with bad path
>>CREATE TABLESPACE badspace LOCATION '/no/such/location';
>>ERROR: could not set permissions on directory "/no/such/location": No
>>such file or directory
>>-- No such tablespace
>>CREATE TABLE bar (i int) TABLESPACE nosuchspace;
>>ERROR: tablespace "nosuchspace" does not exist
>>-- Fail, not empty
>>DROP TABLESPACE testspace;
>>ERROR: tablespace "testspace" is not empty
>>DROP SCHEMA testschema CASCADE;
>>NOTICE: drop cascades to table testschema.foo
>>-- Should succeed
>>DROP TABLESPACE testspace;
>>
>>=>
>>
>>***************
>>*** 38,45 ****
>> ERROR: tablespace "nosuchspace" does not exist
>> -- Fail, not empty
>> DROP TABLESPACE testspace;
>>! ERROR: tablespace "testspace" is not empty
>> DROP SCHEMA testschema CASCADE;
>>! NOTICE: drop cascades to table testschema.foo
>> -- Should succeed
>> DROP TABLESPACE testspace;
>>--- 41,49 ----
>> ERROR: tablespace "nosuchspace" does not exist
>> -- Fail, not empty
>> DROP TABLESPACE testspace;
>>! ERROR: tablespace "testspace" does not exist
>> DROP SCHEMA testschema CASCADE;
>>! ERROR: schema "testschema" does not exist
>> -- Should succeed
>> DROP TABLESPACE testspace;
>>+ ERROR: tablespace "testspace" does not exist
>
>
> I cannot recreate on Linux. What platform, etc, are you on?

hmm, I'll investigate then.

postgresql latest CVS with 2 minor shlib building patches left
(added -lpgport)
cygwin-1.5.11
gcc-3.4.1

--
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/

On Mon, 4 Oct 2004, Reini Urban wrote:

> Gavin Sherry schrieb:
> > On Mon, 4 Oct 2004, Reini Urban wrote:
> >>But the regression test fails: (the only failing test against cvs HEAD)
> >>This is not only a pg_dump/pg_restore issue, or?
> >>
> >>-- Will fail with bad path
> >>CREATE TABLESPACE badspace LOCATION '/no/such/location';
> >>ERROR: could not set permissions on directory "/no/such/location": No
> >>such file or directory
> >>-- No such tablespace
> >>CREATE TABLE bar (i int) TABLESPACE nosuchspace;
> >>ERROR: tablespace "nosuchspace" does not exist
> >>-- Fail, not empty
> >>DROP TABLESPACE testspace;
> >>ERROR: tablespace "testspace" is not empty
> >>DROP SCHEMA testschema CASCADE;
> >>NOTICE: drop cascades to table testschema.foo
> >>-- Should succeed
> >>DROP TABLESPACE testspace;
> >>
> >>=>
> >>
> >>***************
> >>*** 38,45 ****
> >> ERROR: tablespace "nosuchspace" does not exist
> >> -- Fail, not empty
> >> DROP TABLESPACE testspace;
> >>! ERROR: tablespace "testspace" is not empty
> >> DROP SCHEMA testschema CASCADE;
> >>! NOTICE: drop cascades to table testschema.foo
> >> -- Should succeed
> >> DROP TABLESPACE testspace;
> >>--- 41,49 ----
> >> ERROR: tablespace "nosuchspace" does not exist
> >> -- Fail, not empty
> >> DROP TABLESPACE testspace;
> >>! ERROR: tablespace "testspace" does not exist
> >> DROP SCHEMA testschema CASCADE;
> >>! ERROR: schema "testschema" does not exist
> >> -- Should succeed
> >> DROP TABLESPACE testspace;
> >>+ ERROR: tablespace "testspace" does not exist
> >
> >
> > I cannot recreate on Linux. What platform, etc, are you on?
>
> hmm, I'll investigate then.
>
> postgresql latest CVS with 2 minor shlib building patches left
> (added -lpgport)
> cygwin-1.5.11
> gcc-3.4.1

Hmm.. sounds like we're trying to support tablespaces on a system which
doesn't actually support symlinks (in the way we need them). Can any of
the windows guys help?

Gavin

Gavin Sherry schrieb:
> On Mon, 4 Oct 2004, Reini Urban wrote:
>>>I cannot recreate on Linux. What platform, etc, are you on?
>>
>>hmm, I'll investigate then.
>>
>>postgresql latest CVS with 2 minor shlib building patches left
>> (added -lpgport)
>>cygwin-1.5.11
>>gcc-3.4.1
>
> Hmm.. sounds like we're trying to support tablespaces on a system which
> doesn't actually support symlinks (in the way we need them). Can any of
> the windows guys help?

Found the error:
gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes
-Wmissing-declarations -I../../../src/include -DBUILDING_DLL -c -o
tablespace.o tablespace.c

no HAVE_SYMLINK defined, though CYGWIN should added -DHAVE_SYMLINK.

/usr/src/postgresql/postgresql-8.0.0cvs/src/backend/commands
$ gcc -E -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes
-Wmissing-declarations -I../../../src/include -DBUILDING_DLL -c
tablespace.c | grep HAVE_SYMLINK

<none>

--
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/

Reini Urban schrieb:
> no HAVE_SYMLINK defined, though CYGWIN should added -DHAVE_SYMLINK.

oops, sorry for the noise. of course CYGWIN has it defined in pg_config.h.
CYGWIN can only do hardlinks (junctions) on directories of course.

maybe HAVE_SYMLINKS should be extended to HAVE_DIR_SYMLINKS when you
want to symlink a dir.

> /usr/src/postgresql/postgresql-8.0.0cvs/src/backend/commands
> $ gcc -E -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes
> -Wmissing-declarations -I../../../src/include -DBUILDING_DLL -c
> tablespace.c | grep HAVE_SYMLINK
>
> <none>
--
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/

On Mon, 4 Oct 2004, Reini Urban wrote:

> Gavin Sherry schrieb:
> > On Mon, 4 Oct 2004, Reini Urban wrote:
> >>>I cannot recreate on Linux. What platform, etc, are you on?
> >>
> >>hmm, I'll investigate then.
> >>
> >>postgresql latest CVS with 2 minor shlib building patches left
> >> (added -lpgport)
> >>cygwin-1.5.11
> >>gcc-3.4.1
> >
> > Hmm.. sounds like we're trying to support tablespaces on a system which
> > doesn't actually support symlinks (in the way we need them). Can any of
> > the windows guys help?
>
> Found the error:
> gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes
> -Wmissing-declarations -I../../../src/include -DBUILDING_DLL -c -o
> tablespace.o tablespace.c
>
> no HAVE_SYMLINK defined, though CYGWIN should added -DHAVE_SYMLINK.
>
> /usr/src/postgresql/postgresql-8.0.0cvs/src/backend/commands
> $ gcc -E -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes
> -Wmissing-declarations -I../../../src/include -DBUILDING_DLL -c
> tablespace.c | grep HAVE_SYMLINK
>
> <none>

Does it pass the regression tests when you define HAVE_SYMLINK ?

Gavin

On Mon, 4 Oct 2004, Reini Urban wrote:

> Reini Urban schrieb:
> > no HAVE_SYMLINK defined, though CYGWIN should added -DHAVE_SYMLINK.
>
> oops, sorry for the noise. of course CYGWIN has it defined in pg_config.h.
> CYGWIN can only do hardlinks (junctions) on directories of course.
>
> maybe HAVE_SYMLINKS should be extended to HAVE_DIR_SYMLINKS when you
> want to symlink a dir.
>
> > /usr/src/postgresql/postgresql-8.0.0cvs/src/backend/commands
> > $ gcc -E -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes
> > -Wmissing-declarations -I../../../src/include -DBUILDING_DLL -c
> > tablespace.c | grep HAVE_SYMLINK
> >
> > <none>

I though this may have been the problem. configure.in defines HAVE_SYMLINK
to 1 if we are win32. It seems that for Reini's case we are setting our
template (and PORTNAME) to win32 when I suspect it should be cygwin.
Anyone got any ideas?

Gavin

Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> I though this may have been the problem. configure.in defines HAVE_SYMLINK
> to 1 if we are win32. It seems that for Reini's case we are setting our
> template (and PORTNAME) to win32 when I suspect it should be cygwin.
> Anyone got any ideas?

What are the prospects of making the junction code work under cygwin?

regards, tom lane

Tom Lane schrieb:
> Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
>>I though this may have been the problem. configure.in defines HAVE_SYMLINK
>>to 1 if we are win32. It seems that for Reini's case we are setting our
>>template (and PORTNAME) to win32 when I suspect it should be cygwin.
>>Anyone got any ideas?
>
> What are the prospects of making the junction code work under cygwin?

Somethink like the attached patch is easier.
Just replace symlink() for dirs with link() #ifdef __CYGWIN__

just wait a sec until the tests run through...
(completely fresh build)
--
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/

I am confused. CVS has in port.h:

#if defined(WIN32) || defined(__CYGWIN__)
/*
* Win32 doesn't have reliable rename/unlink during concurrent access,
* and we need special code to do symlinks.
*/
extern int pgrename(const char *from, const char *to);
extern int pgunlink(const char *path);
extern int pgsymlink(const char *oldpath, const char *newpath);
...
#define rename(from, to) pgrename(from, to)
#define unlink(path) pgunlink(path)
#define symlink(oldpath, newpath) pgsymlink(oldpath, newpath)

so you should already be calling the junction code on Cygwin.

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

Reini Urban wrote:
> Tom Lane schrieb:
> > Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> >>I though this may have been the problem. configure.in defines HAVE_SYMLINK
> >>to 1 if we are win32. It seems that for Reini's case we are setting our
> >>template (and PORTNAME) to win32 when I suspect it should be cygwin.
> >>Anyone got any ideas?
> >
> > What are the prospects of making the junction code work under cygwin?
>
> Somethink like the attached patch is easier.
> Just replace symlink() for dirs with link() #ifdef __CYGWIN__
>
> just wait a sec until the tests run through...
> (completely fresh build)
> --
> Reini Urban
> http://xarch.tu-graz.ac.at/home/rurban/

> Index: tablespace.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/tablespace.c,v
> retrieving revision 1.11
> diff -u -b -r1.11 tablespace.c
> --- tablespace.c 30 Aug 2004 02:54:38 -0000 1.11
> +++ tablespace.c 4 Oct 2004 18:37:13 -0000
> @@ -349,7 +349,11 @@
> linkloc = (char *) palloc(strlen(DataDir) + 11 + 10 + 1);
> sprintf(linkloc, "%s/pg_tblspc/%u", DataDir, tablespaceoid);
>
> +#ifdef __CYGWIN__
> + if (link(location, linkloc) < 0)
> +#else
> if (symlink(location, linkloc) < 0)
> +#endif
> ereport(ERROR,
> (errcode_for_file_access(),
> errmsg("could not create symbolic link \"%s\": %m",
> @@ -976,7 +980,11 @@
> linkloc = (char *) palloc(strlen(DataDir) + 11 + 10 + 1);
> sprintf(linkloc, "%s/pg_tblspc/%u", DataDir, xlrec->ts_id);
>
> +#ifdef __CYGWIN__
> + if (link(location, linkloc) < 0)
> +#else
> if (symlink(location, linkloc) < 0)
> +#endif
> {
> if (errno != EEXIST)
> ereport(ERROR,

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Reini Urban schrieb:
> Tom Lane schrieb:
>> Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
>>
>>> I though this may have been the problem. configure.in defines
>>> HAVE_SYMLINK
>>> to 1 if we are win32. It seems that for Reini's case we are setting our
>>> template (and PORTNAME) to win32 when I suspect it should be cygwin.
>>> Anyone got any ideas?
>>
>> What are the prospects of making the junction code work under cygwin?
>
> Somethink like the attached patch is easier.
> Just replace symlink() for dirs with link() #ifdef __CYGWIN__
>
> just wait a sec until the tests run through...
> (completely fresh build)

Needed some time because contrib/earthdistance was missing,
so I removed it from the Makefile.

sorry,
bad: test tablespace ... FAILED
1 of 96 tests failed.

Reini Urban <rurban(at)x-ray(dot)at> writes:
> Somethink like the attached patch is easier.
> Just replace symlink() for dirs with link() #ifdef __CYGWIN__

Wouldn't it be cleaner to #define symlink as link?

regards, tom lane

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I am confused. CVS has in port.h:
> so you should already be calling the junction code on Cygwin.

Yeah, I'm sure he is, but it looks from the regression results like it
doesn't quite work on Cygwin. Is that fixable? If so, we'd have a
choice of whether to rely on junctions or on Cygwin's own emulation of
symlinks. I'd be inclined to think the former is a better idea, if only
because it'd give you some chance of migrating a data directory between
Cygwin and native ports.

regards, tom lane

Tom Lane schrieb:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>>I am confused. CVS has in port.h:
>>so you should already be calling the junction code on Cygwin.

true. didn't thought of that. very strange.

> Yeah, I'm sure he is, but it looks from the regression results like it
> doesn't quite work on Cygwin. Is that fixable?

I'll step that in the debugger.

> If so, we'd have a choice of whether to rely on junctions or on
> Cygwin's own emulation of symlinks. I'd be inclined to think the
> former is a better idea,
> if only because it'd give you some chance of migrating a data
> directory between Cygwin and native ports.

Cygwin can do symlinks for directories via the magic .lnk file.
But Cygwin can also do junctions via hardlinks in ln.exe.
I thought link() calls the junction code.
I'll investigate why the libc link() failed, and if ln.exe does some
sifferent magic, similar to pgsymlink.
--
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/

Reini Urban schrieb:
> Cygwin can do symlinks for directories via the magic .lnk file.
> But Cygwin can also do junctions via hardlinks in ln.exe.
> I thought link() calls the junction code.
> I'll investigate why the libc link() failed, and if ln.exe does some
> sifferent magic, similar to pgsymlink.

I thought a little bit over this.

hardlinks and junctions don't work across physical disks, only symlinks.
The whole deal about tablespace locations is to seperate it onto another
disc, similar to the mysql innodb secondary storage. (or better db's)

For cygwin it is very easy to support symlinks to other discs.
Just use the native cygwin symlink(), not using the
pgport/dirmode:pgsymlink() hook. Just some #define rename hackery at the
beginning of the file.

For mingw and the other native WIN32 platforms, you can only support
junctions (limited functionality, but fast) or go through the trouble of
some symlink emulation. But different to the current pgsymlink code.
The only advantage is that this symlink resolver can be held in memory,
just needs some dump/restore functions to a .conf file.
--
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/

Reini Urban wrote:
> Reini Urban schrieb:
> > Cygwin can do symlinks for directories via the magic .lnk file.
> > But Cygwin can also do junctions via hardlinks in ln.exe.
> > I thought link() calls the junction code.
> > I'll investigate why the libc link() failed, and if ln.exe does some
> > sifferent magic, similar to pgsymlink.
>
> I thought a little bit over this.
>
> hardlinks and junctions don't work across physical disks, only symlinks.

Where did you read this? I just looked and can see no such restriction.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 schrieb:
>>Reini Urban schrieb:
>>>Cygwin can do symlinks for directories via the magic .lnk file.
>>>But Cygwin can also do junctions via hardlinks in ln.exe.
>>>I thought link() calls the junction code.
>>>I'll investigate why the libc link() failed, and if ln.exe does some
>>>sifferent magic, similar to pgsymlink.
>>
>>I thought a little bit over this.
>>
>>hardlinks and junctions don't work across physical disks, only symlinks.
>
>
> Where did you read this? I just looked and can see no such restriction.

Sorry, obviously I just got old information.
So we have to update our old cygwin code for NTFS5.

You can use Volume Mount Points with DeviceIoControl now too, since
Win2000 NTFS 5. Sorry. I only knew about Directory Junction Points.

http://www.codeproject.com/w2k/junctionpoints.asp
--
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/

Reini Urban schrieb:
> Tom Lane schrieb:
>> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>>
>>> I am confused. CVS has in port.h:
>>> so you should already be calling the junction code on Cygwin.
>
> true. didn't thought of that. very strange.
>
>> Yeah, I'm sure he is, but it looks from the regression results like it
>> doesn't quite work on Cygwin. Is that fixable?
>
> I'll step that in the debugger.

not yet done.

>> If so, we'd have a choice of whether to rely on junctions or on
>> Cygwin's own emulation of symlinks. I'd be inclined to think the
>> former is a better idea,
>> if only because it'd give you some chance of migrating a data
>> directory between Cygwin and native ports.
>
> Cygwin can do symlinks for directories via the magic .lnk file.
> But Cygwin can also do junctions via hardlinks in ln.exe.
> I thought link() calls the junction code.
> I'll investigate why the libc link() failed, and if ln.exe does some
> sifferent magic, similar to pgsymlink.

For the records:

Using cygwin native slow symlinks - see attached patch - works fine.
Quite an overhead via the magic .lnk file.
tablespace tests pass.

Should I investigate what users want?

1. speed:
* junctions, can only be manipulated via junction.exe
(sysinternals.com e.g.)
* only w2k and above,
2. or compatibility:
* .lnk, can be manipulated with ln.exe
* all windows version. even win95 when we fix
our outstanding cygserver issues with cygserver

-----------------
But another problem arose. Doesn't look like a sideeffect caused by my
symlink switch. I switched to latest CVS in between.

parallel_schedule always fails after finishing create_misc, independent
of the order. If it's the first 2nd, 3rd, ...
so it's not create_aggregate or any other test there.

This is the tail of postmaster.log:
ERROR: aggregate nosuchagg(*) does not exist
ERROR: operator does not exist: integer ######
ERROR: syntax error at or near ")" at character 45
ERROR: syntax error at or near "IN" at character 43
ERROR: new row for relation "check_tbl" violates check constraint
"check_con"
ERROR: new row for relation "check_tbl" violates check constraint
"check_con"
ERROR: new row for relation "check_tbl" violates check constraint
"check_con"
ERROR: new row for relation "check2_tbl" violates check constraint
"sequence_con"
ERROR: new row for relation "check2_tbl" violates check constraint
"sequence_con"
ERROR: new row for relation "check2_tbl" violates check constraint
"sequence_con"
ERROR: new row for relation "check2_tbl" violates check constraint
"sequence_con"
--
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/

Added to open items list:

* remove non-portable TABLESPACE clause from CREATE TABLE using
a SET or ALTER command

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

Philip Warner wrote:
> At 06:31 PM 1/09/2004, Fabien COELHO wrote:
> >I've noticed that the item does not seem to appear in Bruce's list, thus
> >I'm afraid it might be lost for 8.0 where I think it belongs... hence this
> >little reminder.
>
> Sounds good; I've implemented using SET in pg_dump/restore, just waiting
> for the command to work. If it's not there by beta3, I'll just use ALTER
> commands.
>
>
>
> ----------------------------------------------------------------
> 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 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

OK, I have applied the following patch that uses Cygwin native symlink()
instead of the Win32 junctions. The reason for this is that Cygwin
symlinks work on Win95/98/ME where junction points do not and we have no
way to know what system will be running the Cygwin binaries so the
safest bet is to use the Cygwin versions. On Win32 native we only run
on systems that support junctions.

I assume you can make directory symlinks on Cygwin. Was there some
issue that symlinks

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

Reini Urban wrote:
> Reini Urban schrieb:
> > Tom Lane schrieb:
> >> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> >>
> >>> I am confused. CVS has in port.h:
> >>> so you should already be calling the junction code on Cygwin.
> >
> > true. didn't thought of that. very strange.
> >
> >> Yeah, I'm sure he is, but it looks from the regression results like it
> >> doesn't quite work on Cygwin. Is that fixable?
> >
> > I'll step that in the debugger.
>
> not yet done.
>
> >> If so, we'd have a choice of whether to rely on junctions or on
> >> Cygwin's own emulation of symlinks. I'd be inclined to think the
> >> former is a better idea,
> >> if only because it'd give you some chance of migrating a data
> >> directory between Cygwin and native ports.
> >
> > Cygwin can do symlinks for directories via the magic .lnk file.
> > But Cygwin can also do junctions via hardlinks in ln.exe.
> > I thought link() calls the junction code.
> > I'll investigate why the libc link() failed, and if ln.exe does some
> > sifferent magic, similar to pgsymlink.
>
> For the records:
>
> Using cygwin native slow symlinks - see attached patch - works fine.
> Quite an overhead via the magic .lnk file.
> tablespace tests pass.
>
> Should I investigate what users want?
>
> 1. speed:
> * junctions, can only be manipulated via junction.exe
> (sysinternals.com e.g.)
> * only w2k and above,
> 2. or compatibility:
> * .lnk, can be manipulated with ln.exe
> * all windows version. even win95 when we fix
> our outstanding cygserver issues with cygserver
>
> -----------------
> But another problem arose. Doesn't look like a sideeffect caused by my
> symlink switch. I switched to latest CVS in between.
>
> parallel_schedule always fails after finishing create_misc, independent
> of the order. If it's the first 2nd, 3rd, ...
> so it's not create_aggregate or any other test there.
>
> This is the tail of postmaster.log:
> ERROR: aggregate nosuchagg(*) does not exist
> ERROR: operator does not exist: integer ######
> ERROR: syntax error at or near ")" at character 45
> ERROR: syntax error at or near "IN" at character 43
> ERROR: new row for relation "check_tbl" violates check constraint
> "check_con"
> ERROR: new row for relation "check_tbl" violates check constraint
> "check_con"
> ERROR: new row for relation "check_tbl" violates check constraint
> "check_con"
> ERROR: new row for relation "check2_tbl" violates check constraint
> "sequence_con"
> ERROR: new row for relation "check2_tbl" violates check constraint
> "sequence_con"
> ERROR: new row for relation "check2_tbl" violates check constraint
> "sequence_con"
> ERROR: new row for relation "check2_tbl" violates check constraint
> "sequence_con"
> --
> Reini Urban
> http://xarch.tu-graz.ac.at/home/rurban/

> --- postgresql-8.0.0cvs/src/backend/commands/tablespace.c.orig 2004-08-30 04:54:38.000000000 +0200
> +++ postgresql-8.0.0cvs/src/backend/commands/tablespace.c 2004-10-07 14:24:11.731406400 +0200
> @@ -51,6 +51,10 @@
> */
> #include "postgres.h"
>
> +#ifdef __CYGWIN__
> +#undef symlink
> +#endif
> +
> #include <unistd.h>
> #include <dirent.h>
> #include <sys/types.h>

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us> writes:
> OK, I have applied the following patch that uses Cygwin native symlink()
> instead of the Win32 junctions. The reason for this is that Cygwin
> symlinks work on Win95/98/ME where junction points do not and we have no
> way to know what system will be running the Cygwin binaries so the
> safest bet is to use the Cygwin versions. On Win32 native we only run
> on systems that support junctions.

I think this is probably a net loss, because what it will mean is that
you cannot take a data directory built under a Cygwin postmaster and use
it under a native postmaster, nor vice versa. Given the number of other
ways in which we do not support pre-NT4 Windows systems, what is the
benefit of allowing this one?

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > OK, I have applied the following patch that uses Cygwin native symlink()
> > instead of the Win32 junctions. The reason for this is that Cygwin
> > symlinks work on Win95/98/ME where junction points do not and we have no
> > way to know what system will be running the Cygwin binaries so the
> > safest bet is to use the Cygwin versions. On Win32 native we only run
> > on systems that support junctions.
>
> I think this is probably a net loss, because what it will mean is that
> you cannot take a data directory built under a Cygwin postmaster and use
> it under a native postmaster, nor vice versa. Given the number of other
> ways in which we do not support pre-NT4 Windows systems, what is the
> benefit of allowing this one?

I assume Cygwin supports pre-NT4, and always has, and I see no reason to
change that. Moving a data directory from Cygwin to native Win32 seems
like a pretty rare usage to diable pre-NT4 on a platform the previously
supported it.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 schrieb:
> Tom Lane wrote:
>>Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>>
>>>OK, I have applied the following patch that uses Cygwin native symlink()
>>>instead of the Win32 junctions. The reason for this is that Cygwin
>>>symlinks work on Win95/98/ME where junction points do not and we have no
>>>way to know what system will be running the Cygwin binaries so the
>>>safest bet is to use the Cygwin versions. On Win32 native we only run
>>>on systems that support junctions.
>>
>>I think this is probably a net loss, because what it will mean is that
>>you cannot take a data directory built under a Cygwin postmaster and use
>>it under a native postmaster, nor vice versa. Given the number of other
>>ways in which we do not support pre-NT4 Windows systems, what is the
>>benefit of allowing this one?
>
> I assume Cygwin supports pre-NT4, and always has, and I see no reason to
> change that. Moving a data directory from Cygwin to native Win32 seems
> like a pretty rare usage to diable pre-NT4 on a platform the previously
> supported it.

ok, thanks. I'll communicate that.

It's a new feature, so people will not know what's going on, but they
already asked about tablespace. And maybe someone wants to test that on
his WinME laptop.
--
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:

> OK, I have applied the following patch that uses Cygwin native symlink()
> instead of the Win32 junctions. The reason for this is that Cygwin
> symlinks work on Win95/98/ME where junction points do not

Is this really a Win95/98/ME vs NT distinction or a FAT32 vs NTFS distinction?
In which case does an NT machine that happens to be using a FAT32 file system
have the same problem?

> and we have no way to know what system will be running the Cygwin binaries

Is there a reason to make this a compile-time decision? Can't it just try to
make a junction and if it fails then use the Cygwin symlink?

--
greg

Greg Stark wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>
> > OK, I have applied the following patch that uses Cygwin native symlink()
> > instead of the Win32 junctions. The reason for this is that Cygwin
> > symlinks work on Win95/98/ME where junction points do not
>
> Is this really a Win95/98/ME vs NT distinction or a FAT32 vs NTFS distinction?
> In which case does an NT machine that happens to be using a FAT32 file system
> have the same problem?

I believe it is OS, not file system.
>
> > and we have no way to know what system will be running the Cygwin binaries
>
> Is there a reason to make this a compile-time decision? Can't it just try to
> make a junction and if it fails then use the Cygwin symlink?

Yes, if we feel like probing for the Windows OS during runtime. I don't
think it is worth it.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 schrieb:
> Greg Stark wrote:
>>Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>>>OK, I have applied the following patch that uses Cygwin native symlink()
>>>instead of the Win32 junctions. The reason for this is that Cygwin
>>>symlinks work on Win95/98/ME where junction points do not
>>
>>Is this really a Win95/98/ME vs NT distinction or a FAT32 vs NTFS distinction?
>>In which case does an NT machine that happens to be using a FAT32 file system
>>have the same problem?
>
> I believe it is OS, not file system.

Both:
On Win95 family systems you cannot do junctions at all.
(must use cygwin instead)

Up to NT4 and NTFS4 you can junction across the same harddrive.
With FAT, FAT32, VFAT not. ("convert")
(directory mount points)

Since W2k and NTFS5 you can junction across all local volumes.
With W2k and NTFS4 or FAT32 not. ("convert")
(volume mount points. implemented by NTFS5 "reparse points")
This also works with the new EFS (encrypted filesystem).
Don't know how the new WinFS will handle that, but it should
not break it.

I'm not sure about network drives though.
Reparse points don't seem to support network drives. (for now).
They do work with simple cygwin symlinks. But Samba and novell shares
will need some security tweaks. Esp. when run as service.

>>Is there a reason to make this a compile-time decision? Can't it just try to
>>make a junction and if it fails then use the Cygwin symlink?
>
> Yes, if we feel like probing for the Windows OS during runtime. I don't
> think it is worth it.

Agreed. Speed is not a matter for cygwin.
--
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/