tablespace and sequences?

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@cri.ensmp.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fabien COELHO 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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Dear 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@cri.ensmp.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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@cri.ensmp.fr

Fabien COELHO <coelho@cri.ensmp.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@cri.ensmp.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@cri.ensmp.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@cri.ensmp.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@cri.ensmp.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@cri.ensmp.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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@users.sourceforge.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tom Lane wrote:

Robert Treat <xzilla@users.sourceforge.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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@cri.ensmp.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@cri.ensmp.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@cri.ensmp.fr

Fabien COELHO <coelho@cri.ensmp.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@cri.ensmp.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Philip Warner 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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Dear Tom,

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@cri.ensmp.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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@rhyme.com.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Alvaro Herrera <alvherre@dcc.uchile.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@familyhealth.com.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@rhyme.com.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@familyhealth.com.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@cri.ensmp.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@cri.ensmp.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@cri.ensmp.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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@yahoo.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@yahoo.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@bigfoot.com> wrote:

Christopher Browne wrote:

Centuries ago, Nostradamus foresaw when systemguards@yahoo.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@yahoo.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@yahoo.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

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?

There was special code added to handle symlinks on windows using NTFS
junction points to pgport. Not sure if it's a) enabled, or b) working,
under cygwin. Dunno if anyone has even tried it. I'd go looking around
that area :-)

//Magnus

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@linuxworld.com.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@linuxworld.com.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@linuxworld.com.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@postgresql.org so that your
message can get through to the mailing list cleanly

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

Reini Urban schrieb:

Tom Lane schrieb:

Gavin Sherry <swm@linuxworld.com.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@x-ray.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@candle.pha.pa.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@candle.pha.pa.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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.

Pardon me for saying "Huh"? Junctions work just fine across disks. I
just created a tablespace here, and got the following from the
sysinternals too:
D:\pgdata\pg_tblspc>junction 164705

Junction v1.03 - Win2K junction creator and reparse point viewer
Copyright (C) 2000-2002 Mark Russinovich
Systems Internals - http://www.sysinternals.com

D:\pgdata\pg_tblspc\164705: JUNCTION
Substitute Name: c:\temp\pgtest

And yes, tables created there go on C:. Tables in the default tablespace
go on D:.

(IIRC, junctions are even used for the Remote Storage facility, so the
disk theoreticallky doesn't even have to be in your machine at the time)

I don't think hardlinks work across disks, though, but I don't think we
use them.

//Magnus

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/

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.

There is no such restriction for junctions, I just tried it to be safe.

Andreas

Zeugswetter Andreas SB SD schrieb:

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.

There is no such restriction for junctions, I just tried it to be safe.

Yes, sorry. I had old NTFS4 information.
NTFS5 supports volume mount points now too.

But shouldn't we check in configure :) for this filesystem then?
(Ha! ntfs5.m4 for MSVC folks)
--
Reini Urban

Reini Urban said:

Zeugswetter Andreas SB SD schrieb:

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.

There is no such restriction for junctions, I just tried it to be
safe.

Yes, sorry. I had old NTFS4 information.
NTFS5 supports volume mount points now too.

But shouldn't we check in configure :) for this filesystem then?
(Ha! ntfs5.m4 for MSVC folks)

No, of course not. That would only check the machine where you compile, not
where you install/run.

cheers

andrew

Andrew Dunstan wrote:

Reini Urban said:

Zeugswetter Andreas SB SD schrieb:

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.

There is no such restriction for junctions, I just tried it to be
safe.

Yes, sorry. I had old NTFS4 information.
NTFS5 supports volume mount points now too.

But shouldn't we check in configure :) for this filesystem then?
(Ha! ntfs5.m4 for MSVC folks)

No, of course not. That would only check the machine where you compile, not
where you install/run.

Tablespaces are not supported on NT4. They throw an error.

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

Bruce Momjian schrieb:

Andrew Dunstan wrote:

Reini Urban said:

Zeugswetter Andreas SB SD schrieb:

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.

There is no such restriction for junctions, I just tried it to be
safe.

Yes, sorry. I had old NTFS4 information.
NTFS5 supports volume mount points now too.

But shouldn't we check in configure :) for this filesystem then?
(Ha! ntfs5.m4 for MSVC folks)

No, of course not. That would only check the machine where you compile, not
where you install/run.

Tablespaces are not supported on NT4. They throw an error.

So just describe in the docs that only NTFS5 (i.e. W2K and up) supports
th new tablespace feature.
I could find my cygwin niche then to support it by our native and slow
symlink implementation :)
--
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/

Reini Urban wrote:

Bruce Momjian schrieb:

Andrew Dunstan wrote:

Reini Urban said:

Zeugswetter Andreas SB SD schrieb:

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.

There is no such restriction for junctions, I just tried it to be
safe.

Yes, sorry. I had old NTFS4 information.
NTFS5 supports volume mount points now too.

But shouldn't we check in configure :) for this filesystem then?
(Ha! ntfs5.m4 for MSVC folks)

No, of course not. That would only check the machine where you compile, not
where you install/run.

Tablespaces are not supported on NT4. They throw an error.

So just describe in the docs that only NTFS5 (i.e. W2K and up) supports
th new tablespace feature.
I could find my cygwin niche then to support it by our native and slow
symlink implementation :)

I think we are fine. We have already discussed not even supporting NT4
because tablespaces don't work, but now it will throw a not supported
error, which seems fine.

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

Reini Urban schrieb:

Tom Lane schrieb:

Bruce Momjian <pgman@candle.pha.pa.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@postgresql.org)

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

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@candle.pha.pa.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

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@candle.pha.pa.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian schrieb:

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.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@candle.pha.pa.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@candle.pha.pa.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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.

No, it's file system.
Junctions will not work on NT OS:es with FAT32.
Directory junctions require NTFSv5, which is only available on Windows
2000 and newer.

//Magnus

"Magnus Hagander" <mha@sollentuna.net> writes:

No, it's file system.
Junctions will not work on NT OS:es with FAT32.
Directory junctions require NTFSv5, which is only available on Windows
2000 and newer.

So then there really has to be a run-time check for this. Either at initdb
time, or at the time the database tries to create the link. You can't know at
compile-time whether the filesystem the database will be on will support
junctions.

--
greg

Bruce Momjian schrieb:

Greg Stark wrote:

Bruce Momjian <pgman@candle.pha.pa.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/

Magnus Hagander wrote:

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.

No, it's file system.
Junctions will not work on NT OS:es with FAT32.
Directory junctions require NTFSv5, which is only available on Windows
2000 and newer.

My point was that junctions don't exist on Win98 no matter what file
system you use. I don't think the API even exists on those OS's, never
mind what file system you have.

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

Hi,

There are certainly cygwin-users trying out PostgreSQL on cygwin on WinXX. If the newest cygwin-version will suddenly stop working under WinXX, they will not be happy.

I've given consideration to the argument that you can no longer take data-directories from the cygwin-version to the native-version... And I think that there's not a *huge* loss there. For me, as an observer and occiasional user/developer, I think the loss of not running on cygwin+winXX is larger.

After all, the data can still be dumped / reloaded. And what gives me the certainty that the two versions of PostgreSQL, the cygwin and the native version, are not already compiled in such way that they're not binary compatible? (remember, I'm an outsider on this, with no knowledge of the binary formats, and I'm trying to remain in that perspective for this discussion)

I don't know what the failure will be when you now try to move a data-directory from the cygwin version to the native version, when cygwin uses a .lnk hack and native uses a junction. Did anyone try? What do the results look like? Is there an acceptable way to stop ppl from trying / give sensible errors without introducing too much crap in the code and without harming ppls data?

regards,

--Tim

-----Original Message-----
From: pgsql-cygwin-owner@postgresql.org [mailto:pgsql-cygwin-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Tuesday, October 12, 2004 1:02 AM
To: Bruce Momjian
Cc: Reini Urban; PostgreSQL Developers; pgsql-cygwin@postgresql.org
Subject: Re: [CYGWIN] [HACKERS] open item: tablespace handing in pg_dump/pg_restore

Bruce Momjian <pgman@candle.pha.pa.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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Leeuw van der, Tim schrieb:

There are certainly cygwin-users trying out PostgreSQL on cygwin on
WinXX. If the newest cygwin-version will suddenly stop working under
WinXX, they will not be happy.

That's why we use cygwin symlinks, not junctions.

I've given consideration to the argument that you can no longer take
data-directories from the cygwin-version to the native-version... And I
think that there's not a *huge* loss there. For me, as an observer and
occiasional user/developer, I think the loss of not running on
cygwin+winXX is larger.

After all, the data can still be dumped / reloaded. And what gives me
the certainty that the two versions of PostgreSQL, the cygwin and the
native version, are not already compiled in such way that they're not
binary compatible? (remember, I'm an outsider on this, with no knowledge
of the binary formats, and I'm trying to remain in that perspective for
this discussion)

See below. Conflicting --enable-integer-datetimes and --enable-multibyte
would be an issue. I don't know if and how our converters handle
multibyte/non-multibyte, when the backend changes.

I don't know what the failure will be when you now try to move a
data-directory from the cygwin version to the native version, when
cygwin uses a .lnk hack and native uses a junction. Did anyone try? What
do the results look like? Is there an acceptable way to stop ppl from
trying / give sensible errors without introducing too much crap in the
code and without harming ppls data?

That's a non-critical issue. You can always replace the cygwin .lnk dir
with an actual junction on cygwin also. You'd need to be superuser and
use "ln -d" or get "junction" from sysinternals.com.
But than you must have NTFS4 (same drive) or NTFS5 (other local drive).

You can also replace the junction with a cygwin .lnk if you switch to
FAT, but then you MUST use the cygwin binaries on the data.
Or don't use tablespace at all. It's a pretty esoteric feature at all.

But it will get problematic on big/little endian machine changes, and
different integer sizes. Don't know if the data is converted on the fly
then. I only know of AutoCAD's DWG: they designed its data format and
accessors to be machine and CPU independent. And you usually don't copy
machine dependent /usr/share/postgresql trees to other machines.

-----Original Message-----
From: pgsql-cygwin-owner@postgresql.org [mailto:pgsql-cygwin-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Tuesday, October 12, 2004 1:02 AM
To: Bruce Momjian
Cc: Reini Urban; PostgreSQL Developers; pgsql-cygwin@postgresql.org
Subject: Re: [CYGWIN] [HACKERS] open item: tablespace handing in pg_dump/pg_restore

Bruce Momjian <pgman@candle.pha.pa.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?

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

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.

No, it's file system.
Junctions will not work on NT OS:es with FAT32.
Directory junctions require NTFSv5, which is only available

on Windows

2000 and newer.

My point was that junctions don't exist on Win98 no matter what file
system you use. I don't think the API even exists on those OS's, never
mind what file system you have.

The API exists - it's just a call to DeviceIOControl(). That one exists
on all win32 platforms. It will return a "not supported" error value,
but the API is there. (Unlike some other APIs where the program won't
even start because the entrypoint is not present in the system DLLs)

The only reason they are not in 9x is that 9x does not support NTFS. The
function is implemented only in the filesystem, not in the OS itself.

//Magnus