Re: [HACKERS] Tablespaces

> > > For tablespaces on OS's that don't support it, I think we will have to
> > > store the path name in the file and read it via the backend. Somehow we
> > > should cache those lookups.
> >
> > My feeling is that we need not support tablespaces on OS's without
> > symlinks.

To create symlinked directories on Win2k NTFS see:
http://www.sysinternals.com/ntw2k/source/misc.shtml#junction

I think Win2000 or XP would be a reasonable restriction for Win32 PG
installations that want tablespaces.

Andreas

"Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at> writes:
>>> My feeling is that we need not support tablespaces on OS's without
>>> symlinks.

> To create symlinked directories on Win2k NTFS see:
> http://www.sysinternals.com/ntw2k/source/misc.shtml#junction
> I think Win2000 or XP would be a reasonable restriction for Win32 PG
> installations that want tablespaces.

Oh, good --- symlinks for directories are all that we need for this
design. I think that settles it then.

regards, tom lane

Zeugswetter Andreas SB SD wrote:

>To create symlinked directories on Win2k NTFS see:
> http://www.sysinternals.com/ntw2k/source/misc.shtml#junction
>
>
>

I don't think we could use this s/w though, unless the author is
prepared to relicense it. I'm sure implementing a clean room version of
the relevant parts wouldn't be too hard, though.

cheers

andrew

> "Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at> writes:
>>>> My feeling is that we need not support tablespaces on OS's without
>>>> symlinks.
>
>> To create symlinked directories on Win2k NTFS see:
>> http://www.sysinternals.com/ntw2k/source/misc.shtml#junction
>> I think Win2000 or XP would be a reasonable restriction for Win32 PG
>> installations that want tablespaces.
>
> Oh, good --- symlinks for directories are all that we need for this
> design. I think that settles it then.
>

What archival tools are there that would restore this to this back to the
filesystem: tar? zip? What would happen if a symlink were removed or
pointed to an invalid location while the postmaste was running?

I think the catalog approach would future proof yourself; think about the
possibility of new filesystems, table storage mechanisms, or devices.
There may be a raw disk system in the future. With that you could point
to a block device instead of a filesystem directory. Symlinks seem like a
simple solution but will it be complete enough? A meta file or catalog
entry could store more than just the location of the tablespace: max/min
space allocation, vacuum frequencies, storage type (maybe in-place
updates), locking schemes (in case someome had a wild hair to customize
NFS operations).

That meta/configuration file with a summary or one that is generated for a
summary could be useful.

I just can't help but state that "I've got a bad feeling about this."

tswan(at)idigx(dot)com wrote:
> > "Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at> writes:
> >>>> My feeling is that we need not support tablespaces on OS's without
> >>>> symlinks.
> >
> >> To create symlinked directories on Win2k NTFS see:
> >> http://www.sysinternals.com/ntw2k/source/misc.shtml#junction
> >> I think Win2000 or XP would be a reasonable restriction for Win32 PG
> >> installations that want tablespaces.
> >
> > Oh, good --- symlinks for directories are all that we need for this
> > design. I think that settles it then.
> >
>
> What archival tools are there that would restore this to this back to the
> filesystem: tar? zip? What would happen if a symlink were removed or
> pointed to an invalid location while the postmaste was running?

Well, for backup, just run tar or find on /data with a flag to follow
symlinks, and you are done. Can't get much easier than that.

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

> tswan(at)idigx(dot)com wrote:
>> > "Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at> writes:
>> >>>> My feeling is that we need not support tablespaces on OS's without
>> >>>> symlinks.
>> >
>> >> To create symlinked directories on Win2k NTFS see:
>> >> http://www.sysinternals.com/ntw2k/source/misc.shtml#junction
>> >> I think Win2000 or XP would be a reasonable restriction for Win32 PG
>> >> installations that want tablespaces.
>> >
>> > Oh, good --- symlinks for directories are all that we need for this
>> > design. I think that settles it then.
>> >
>>
>> What archival tools are there that would restore this to this back to
>> the
>> filesystem: tar? zip? What would happen if a symlink were removed or
>> pointed to an invalid location while the postmaste was running?
>
> Well, for backup, just run tar or find on /data with a flag to follow
> symlinks, and you are done. Can't get much easier than that.

I'm ruferring to NTFS and the win32 platforms. How does tar handle these
symlinks on the NTFS filesystem? What about if someone finds that FAT32
is significantly better for the database?

It seems a little insane to introduce an OS/filesystem dependency at the
onset of a porting effort especially if you hope to be OS agnostic for
feature sets. I think someone would be crying foul if a new feature only
worked on Linux and not on FreeBSD.

Additionally, another developer noted the advantage of a text file is that
it would be easy for someone to develop tools to help if it became
difficult to edit or parse. Additionally, there could be a change away
from a flat file format to an XML format to configure the tablespace area.

Another argument against the symlink approach is how they may change in
the future. A file location is universal, symlink behavoir may not be.
The symlink behavior on different ports may change. To rely on symlinks
introduces an unnecessary OS dependency. All that is needed is the file
location. This can be derived from a flat file, an XML file, a sysetm
catalog.

Also, to support some features on some platforms is a real poor prospect.
( This application requires the Linux port of PostgreSQL 7.5.1 ) seems
like a poor choice for advocating PostgreSQL. The extra effort insures
that all ports, current and future, can get the same set of features and
nhancements. I like Unix and Linux as much as the next guy, but I have
to be real and make the presumption that there are and will be other
operating systems and it would be wise to plan a little for that.

<tswan(at)idigx(dot)com> writes:

>> tswan(at)idigx(dot)com wrote:
>>> > "Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at> writes:
>>> >>>> My feeling is that we need not support tablespaces on OS's without
>>> >>>> symlinks.
>>> >
>>> >> To create symlinked directories on Win2k NTFS see:
>>> >>   http://www.sysinternals.com/ntw2k/source/misc.shtml#junction
>>> >> I think Win2000 or XP would be a reasonable restriction for Win32 PG
>>> >> installations that want tablespaces.
>>> >
>>> > Oh, good --- symlinks for directories are all that we need for this
>>> > design.  I think that settles it then.
>>> >
>>>
>>> What archival tools are there that would restore this to this back to
>>> the
>>> filesystem: tar? zip?  What would happen if a symlink were removed or
>>> pointed to an invalid location while the postmaste was running?
>>
>> Well, for backup, just run tar or find on /data with a flag to
>> follow symlinks, and you are done.  Can't get much easier than
>> that.
>
> I'm ruferring to NTFS and the win32 platforms.  How does tar handle
> these symlinks on the NTFS filesystem?  What about if someone finds
> that FAT32 is significantly better for the database?

tar doesn't know anything about PostgreSQL system catalogs. If we use
symlinks for tablespaces then it would be possible to backup downed
databases with a simple tar command on every platform *I* care about
(and probably Windows too). Using system catalogs for this stuff
would simply guarantee that I would have to read the system catalogs
and then back up each tablespace manually. In short, your idea would
trade off (maybe) having to backup tablespaces manually on a few
platforms for the certainty of having to backup tablespaces manually
on all platforms.

How is that a win?

> It seems a little insane to introduce an OS/filesystem dependency at
> the onset of a porting effort especially if you hope to be OS
> agnostic for feature sets.  I think someone would be crying foul if
> a new feature only worked on Linux and not on FreeBSD.

First of all, symlinks are a pretty popular "feature." Even Windows
supports what would be needed. Second of all, PostgreSQL will still
run on OSes without symlinks, tablespaces won't be available, but
PostgreSQL will still run. Since we are all using PostgreSQL without
tablespaces now, it can hardly be argued that tablespaces are a
critical feature.

We aren't talking about a "feature that work[s] on Linux on not on
FreeBSD." We are talking about a feature that works on every OS that
suports symlinks (which includes even operating systems like Windows
that PostgreSQL doesn't currently support).

> Additionally, another developer noted the advantage of a text file
> is that it would be easy for someone to develop tools to help if it
> became difficult to edit or parse.  Additionally, there could be a
> change away from a flat file format to an XML format to configure
> the tablespace area.

The advantage of symlinks is that no tools would have to be written
and 'ls -l' would show everything you would need to know about where
your tablespaces actually were.

XML files are relatively easy to parse, but they certainly aren't as
easy as simply letting PostgreSQL follow a symlink. Why reinvent the
wheel with what would essentially be PostgreSQL's own implementation
of a symlink?

To go back to your *tar* example, are we going to rewrite tar so that
it reads PostgreSQL's XML file and *does the right thing*?

> Another argument against the symlink approach is how they may change
> in the future.   A file location is universal, symlink behavoir may
> not be. The symlink behavior on different ports may change.  To
> rely on symlinks introduces an unnecessary OS dependency.  All that
> is needed is the file location.  This can be derived from a flat
> file, an XML file, a sysetm catalog.

Yes, and someone can whip out vi and edit a file or fire up psql and
change the system catalog. Heck, someone could issue a 'mv' or 'rm'
command on the actual files, for that matter. PostgreSQL can't
protect itself from careless sysadmins, and symlinks are no more
inherently dangerous than normal files. They don't just *disappear*.

> Also, to support some features on some platforms is a real poor
> prospect.  ( This application requires the Linux port of PostgreSQL
> 7.5.1 ) seems like a poor choice for advocating PostgreSQL.   The
> extra effort insures that all ports, current and future, can get the
> same set of features and nhancements.   I like Unix and Linux as
> much as the next guy, but I have to be real and make the presumption
> that there are and will be other operating systems and it would be
> wise to plan a little for that.

The fact of the matter is that PostgreSQL runs better on some
platforms than others, and it probably always will. Heck, as of
today, PostgreSQL is officially supported on the Gamecube. Does that
mean that the PostgreSQL developers should limit themselves to the
features offered by the Gamecube? What, precisely, is the point of
developing for the lowest common denominator?

Perhaps if you could give us an example of an actual case where some
actual PostgreSQL users (or potential users) might be affected?

jearl(at)bullysports(dot)com wrote:

><tswan(at)idigx(dot)com> writes:
>
>
>
>>>tswan(at)idigx(dot)com wrote:
>>>
>>>
>>>>>"Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at> writes:
>>>>>
>>>>>
>>>>>>>>My feeling is that we need not support tablespaces on OS's without
>>>>>>>>symlinks.
>>>>>>>>
>>>>>>>>
>>>>>>To create symlinked directories on Win2k NTFS see:
>>>>>> http://www.sysinternals.com/ntw2k/source/misc.shtml#junction
>>>>>>I think Win2000 or XP would be a reasonable restriction for Win32 PG
>>>>>>installations that want tablespaces.
>>>>>>
>>>>>>
>>>>>Oh, good --- symlinks for directories are all that we need for this
>>>>>design. I think that settles it then.
>>>>>
>>>>>
>>>>>
>>>>What archival tools are there that would restore this to this back to
>>>>the
>>>>filesystem: tar? zip? What would happen if a symlink were removed or
>>>>pointed to an invalid location while the postmaste was running?
>>>>
>>>>
>>>Well, for backup, just run tar or find on /data with a flag to
>>>follow symlinks, and you are done. Can't get much easier than
>>>that.
>>>
>>>
>>I'm ruferring to NTFS and the win32 platforms. How does tar handle
>>these symlinks on the NTFS filesystem? What about if someone finds
>>that FAT32 is significantly better for the database?
>>
>>
>
>tar doesn't know anything about PostgreSQL system catalogs. If we use
>symlinks for tablespaces then it would be possible to backup downed
>databases with a simple tar command on every platform *I* care about
>(and probably Windows too). Using system catalogs for this stuff
>would simply guarantee that I would have to read the system catalogs
>and then back up each tablespace manually. In short, your idea would
>trade off (maybe) having to backup tablespaces manually on a few
>platforms for the certainty of having to backup tablespaces manually
>on all platforms.
>
>How is that a win?
>
>
>
Apparently, I have failed tremendously in addressing a concern. The
question is does PostgreSQL need to rely on symlinks and will that
dependency introduce problems?

There is an active win32 port underway (see this mailing list). One
proposal was to try to use an OS specific filesystem feature to perform
a symlink on NTFS. Can the special symlink that NTFS allegedly supports
be archived the same way symlinks are archived on Unix? If so, is there
a utility that can do this (zip, tar, etc). The backup operator would
still need to know what directories needed to be archived in addtion to
the pgdata directory. Is this symlink structure a normal/special file
that can be archived by normal means (tar,zip, etc)?

Example:

PGDATA is C:\pgdata
I have a tablespace in Z:\1\ and Z:\2\
There exists an alleged symlink in
C:\pgdata\data\base\tablespaces\schmoo -> Z:\1

Can I archive [ C:\pgdata, Z:\1, Z:\2 ], restore them, and have
postgresql working just as before?

>>It seems a little insane to introduce an OS/filesystem dependency at
>>the onset of a porting effort especially if you hope to be OS
>>agnostic for feature sets. I think someone would be crying foul if
>>a new feature only worked on Linux and not on FreeBSD.
>>
>>
>
>First of all, symlinks are a pretty popular "feature." Even Windows
>supports what would be needed. Second of all, PostgreSQL will still
>run on OSes without symlinks, tablespaces won't be available, but
>PostgreSQL will still run. Since we are all using PostgreSQL without
>tablespaces now, it can hardly be argued that tablespaces are a
>critical feature.
>
>We aren't talking about a "feature that work[s] on Linux on not on
>FreeBSD." We are talking about a feature that works on every OS that
>suports symlinks (which includes even operating systems like Windows
>that PostgreSQL doesn't currently support).
>
>
>

Hello? What was this response from Tom Lane? "My feeling is that we
need not support tablespaces on OS's without symlinks." That seems to
be indicative of a feature set restriction base on platform.

>>Additionally, another developer noted the advantage of a text file
>>is that it would be easy for someone to develop tools to help if it
>>became difficult to edit or parse. Additionally, there could be a
>>change away from a flat file format to an XML format to configure
>>the tablespace area.
>>
>>
>
>The advantage of symlinks is that no tools would have to be written
>and 'ls -l' would show everything you would need to know about where
>your tablespaces actually were.
>
>
>
Where is 'ls -l' on a win32 box? If you will follow the discussion of
symlinks under MinGW you will see that they don't work as commanded.
And, postgresql is supposed to be compiled under MinGW, but not require
it to run.

From Windows 2000, 'ls' is not recognized as an internal or external
command,
operable program or batch file.

>XML files are relatively easy to parse, but they certainly aren't as
>easy as simply letting PostgreSQL follow a symlink. Why reinvent the
>wheel with what would essentially be PostgreSQL's own implementation
>of a symlink?
>
>

Is opening a file recreating a symlink? If you are opening file
descriptors why rely on symlinks. If you know the location either from
the system catalog, a or configuration file, is it any terribly more
complicated? Basically, if a tablespace needed to be renamed, or
moved, or changed, your going to have to do file management anyway.
The symlink saves you just a lookup as to what files go where? If you
kept this small hash in memory, it's not a continuous lookup because you
have the redirection internally. And, it's more portable.

>To go back to your *tar* example, are we going to rewrite tar so that
>it reads PostgreSQL's XML file and *does the right thing*?
>
>

I'm not talking about integrating tar with PostgreSQL or uniting the
universal string theory with how to cook apple pie. Why would think we
would have to rewrite tar?

>
>
>>Another argument against the symlink approach is how they may change
>>in the future. A file location is universal, symlink behavoir may
>>not be. The symlink behavior on different ports may change. To
>>rely on symlinks introduces an unnecessary OS dependency. All that
>>is needed is the file location. This can be derived from a flat
>>file, an XML file, a sysetm catalog.
>>
>>
>
>Yes, and someone can whip out vi and edit a file or fire up psql and
>change the system catalog. Heck, someone could issue a 'mv' or 'rm'
>command on the actual files, for that matter. PostgreSQL can't
>protect itself from careless sysadmins, and symlinks are no more
>inherently dangerous than normal files. They don't just *disappear*.
>
>
>
>>Also, to support some features on some platforms is a real poor
>>prospect. ( This application requires the Linux port of PostgreSQL
>>7.5.1 ) seems like a poor choice for advocating PostgreSQL. The
>>extra effort insures that all ports, current and future, can get the
>>same set of features and nhancements. I like Unix and Linux as
>>much as the next guy, but I have to be real and make the presumption
>>that there are and will be other operating systems and it would be
>>wise to plan a little for that.
>>
>>
>
>The fact of the matter is that PostgreSQL runs better on some
>platforms than others, and it probably always will. Heck, as of
>today, PostgreSQL is officially supported on the Gamecube. Does that
>mean that the PostgreSQL developers should limit themselves to the
>features offered by the Gamecube? What, precisely, is the point of
>developing for the lowest common denominator?
>
>

The other option proposed was to give win32 a subset of features that
would be available to other platforms. In this case, that would be that
the win32 port could support tablespaces. This is strikingly different
than a performance issue. It would be one thing for tablespaces to
perform poorly, it's another for them to fail or not exist altogether.

>Perhaps if you could give us an example of an actual case where some
>actual PostgreSQL users (or potential users) might be affected?
>
>

See the comment from Tom Lane on limiting features. Look at the
potential Win32 market which outnumbers the unix market in number of
computers and developers by a large margin.

On Friday 05 March 2004 07:51, Thomas Swan wrote:
>
> Apparently, I have failed tremendously in addressing a concern. The
> question is does PostgreSQL need to rely on symlinks and will that
> dependency introduce problems?
>
> There is an active win32 port underway (see this mailing list). One
> proposal was to try to use an OS specific filesystem feature to perform
> a symlink on NTFS. Can the special symlink that NTFS allegedly supports
> be archived the same way symlinks are archived on Unix? If so, is there
> a utility that can do this (zip, tar, etc).

Why not try and find out?
http://www.nedprod.com/programs/Win32/SymLink/
This will apparently create symlinks on NTFS5 volumes via a simple dialogue
box.

You'd probably want to test using pkzip/winzip, Symantec/Norton - anyone think
of any others?

--
Richard Huxton
Archonet Ltd

Thomas Swan wrote:
> >The fact of the matter is that PostgreSQL runs better on some
> >platforms than others, and it probably always will. Heck, as of
> >today, PostgreSQL is officially supported on the Gamecube. Does that
> >mean that the PostgreSQL developers should limit themselves to the
> >features offered by the Gamecube? What, precisely, is the point of
> >developing for the lowest common denominator?
> >
> >
>
> The other option proposed was to give win32 a subset of features that
> would be available to other platforms. In this case, that would be that
> the win32 port could support tablespaces. This is strikingly different
> than a performance issue. It would be one thing for tablespaces to
> perform poorly, it's another for them to fail or not exist altogether.
>
> >Perhaps if you could give us an example of an actual case where some
> >actual PostgreSQL users (or potential users) might be affected?
> >
> >
>
> See the comment from Tom Lane on limiting features. Look at the
> potential Win32 market which outnumbers the unix market in number of
> computers and developers by a large margin.

Let me put it this way. Unix tools like tar can already use symlinks,
as can administrators from the command line, so symlinks are best on
that platform, period.

If we have a platform that doesn't have symlinks (and I think Win32
might), we will have to implement a different way to do tablespace
lookups _only_ for those platforms.

The use of symlinks on Unix has just too many advantages to use a
another sub-optimal solution on that platform.

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

On Fri, 5 Mar 2004, Thomas Swan wrote:

> jearl(at)bullysports(dot)com wrote:
>
> ><tswan(at)idigx(dot)com> writes:
> >
> >
> >
> >>>tswan(at)idigx(dot)com wrote:
> >>>
> >>>
> >>>>>"Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at> writes:
> >>>>>
> >>>>>
> >>>>>>>>My feeling is that we need not support tablespaces on OS's without
> >>>>>>>>symlinks.
> >>>>>>>>
> >>>>>>>>
> >>>>>>To create symlinked directories on Win2k NTFS see:
> >>>>>> http://www.sysinternals.com/ntw2k/source/misc.shtml#junction
> >>>>>>I think Win2000 or XP would be a reasonable restriction for Win32 PG
> >>>>>>installations that want tablespaces.
> >>>>>>
> >>>>>>
> >>>>>Oh, good --- symlinks for directories are all that we need for this
> >>>>>design. I think that settles it then.
> >>>>>
> >>>>>
> >>>>>
> >>>>What archival tools are there that would restore this to this back to
> >>>>the
> >>>>filesystem: tar? zip? What would happen if a symlink were removed or
> >>>>pointed to an invalid location while the postmaste was running?
> >>>>
> >>>>
> >>>Well, for backup, just run tar or find on /data with a flag to
> >>>follow symlinks, and you are done. Can't get much easier than
> >>>that.
> >>>
> >>>
> >>I'm ruferring to NTFS and the win32 platforms. How does tar handle
> >>these symlinks on the NTFS filesystem? What about if someone finds
> >>that FAT32 is significantly better for the database?
> >>
> >>
> >
> >tar doesn't know anything about PostgreSQL system catalogs. If we use
> >symlinks for tablespaces then it would be possible to backup downed
> >databases with a simple tar command on every platform *I* care about
> >(and probably Windows too). Using system catalogs for this stuff
> >would simply guarantee that I would have to read the system catalogs
> >and then back up each tablespace manually. In short, your idea would
> >trade off (maybe) having to backup tablespaces manually on a few
> >platforms for the certainty of having to backup tablespaces manually
> >on all platforms.
> >
> >How is that a win?
> >
> >
> >
> Apparently, I have failed tremendously in addressing a concern. The
> question is does PostgreSQL need to rely on symlinks and will that
> dependency introduce problems?
>
> There is an active win32 port underway (see this mailing list). One
> proposal was to try to use an OS specific filesystem feature to perform
> a symlink on NTFS. Can the special symlink that NTFS allegedly supports
> be archived the same way symlinks are archived on Unix? If so, is there
> a utility that can do this (zip, tar, etc). The backup operator would
> still need to know what directories needed to be archived in addtion to
> the pgdata directory. Is this symlink structure a normal/special file
> that can be archived by normal means (tar,zip, etc)?

According to this page:

http://www.linuxinfor.com/en/man1/ln.1.html

from the linux man pages,
"On existing implementations, if it is at all possible to make a hard link
to a directory, this may be done by the superuser only. POSIX forbids the
system call link(2) and the utility ln to make hard links to directories
(but does not forbid hard links to cross filesystem boundaries)."

and states that the command ln is "POSIX 1003.2. However, POSIX 1003.2
(1996) does not discuss soft links. Soft links were introduced by BSD, and
do not occur in System V release 3 (and older) systems."

I fear the more useful of the two would be soft links, but if soft links
are not a part of the POSIX standard, then postgresql probably shouldn't
base key features on them unless said features would be onerous to
implement without soft links.

Oddly enough though, Microsoft has now released their unix services
package for free, and it comes with the commands to create a symbolic
link, and runs on Windows NT 4.0. Windows 2000. Windows XP. and Windows
Server 2003.

So, soft links would appear to not be a real non-starter, as long as the
ability to make softlinks on those systems won't rely on having some
strange package installed (like MS's Unix services package.)

I imagine there's a standard OS call in modern MS OSes that will let you
create a symbolic link with no special libs installed, and if that's the
case, the the argument that maybe FAT would be a better file system comes
under the same heading as running your database on NFS. Neither is a good
idea, and PGSQL can't guarantee normal, reliable operation.

>
> Example:
>
> PGDATA is C:\pgdata
> I have a tablespace in Z:\1\ and Z:\2\
> There exists an alleged symlink in
> C:\pgdata\data\base\tablespaces\schmoo -> Z:\1
>
> Can I archive [ C:\pgdata, Z:\1, Z:\2 ], restore them, and have
> postgresql working just as before?

Have you tried building said structure and backing up and restoring it to
see?

> >We aren't talking about a "feature that work[s] on Linux on not on
> >FreeBSD." We are talking about a feature that works on every OS that
> >suports symlinks (which includes even operating systems like Windows
> >that PostgreSQL doesn't currently support).
>
> Hello? What was this response from Tom Lane? "My feeling is that we
> need not support tablespaces on OS's without symlinks." That seems to
> be indicative of a feature set restriction base on platform.

If every OS we run on supports sym links is that such a restriction?

Which OSes (I'm asking because I really don't know) that are currently
supported don't support symbolic links?

> >>Additionally, another developer noted the advantage of a text file
> >>is that it would be easy for someone to develop tools to help if it
> >>became difficult to edit or parse. Additionally, there could be a
> >>change away from a flat file format to an XML format to configure
> >>the tablespace area.
> >>
> >>
> >
> >The advantage of symlinks is that no tools would have to be written
> >and 'ls -l' would show everything you would need to know about where
> >your tablespaces actually were.
> >
> >
> >
> Where is 'ls -l' on a win32 box? If you will follow the discussion of
> symlinks under MinGW you will see that they don't work as commanded.
> And, postgresql is supposed to be compiled under MinGW, but not require
> it to run.

It's free from Microsoft. Postgresql wouldn't require it. It's a
standard file system level call on Windows, and has been since NT 4.0 was
a baby. That was 1996-1997 when I built an NT4.0 server, downloaded the
free gnu utils and found out that they worked.

Plain and simple, Windows has supported symbolic links, soft and hard, for
some time, and every flavor of unix known does as well. While MVS on a
mainframe or VMS might present some problems, those operating systems all
have LVMs and you could just mount and unmount space under your database.

"Thomas Swan" <tswan(at)idigx(dot)com> writes:

> jearl(at)bullysports(dot)com wrote:

[snip]

> Apparently, I have failed tremendously in addressing a concern. The
> question is does PostgreSQL need to rely on symlinks and will that
> dependency introduce problems?
>
> There is an active win32 port underway (see this mailing list).  
> One proposal was to try to use an OS specific filesystem feature to
> perform a symlink on NTFS.  Can the special symlink that NTFS
> allegedly supports be archived the same way symlinks are archived on
> Unix?  If so, is there a utility that can do this (zip, tar, etc). 
> The backup operator would still need to know what directories needed
> to be archived in addtion to the pgdata directory.    Is this
> symlink structure a normal/special file that can be archived by
> normal means (tar,zip, etc)?
>
> Example:
>
> PGDATA is C:\pgdata
> I have a tablespace in Z:\1\ and Z:\2\
> There exists an alleged symlink in
> C:\pgdata\data\base\tablespaces\schmoo -> Z:\1
>
> Can I archive [ C:\pgdata, Z:\1, Z:\2 ], restore them, and have
> postgresql working just as before?

My point is that if you are using some sort of sytem table, catalog
file, or whatever else (besides symlinks), you are still going to have
to backup up c:\pgdata z:\1 and z:\2 manually anyhow. Unless, of
course, you have some magical version of tar or zip that reads the
PostgreSQL specific configuration file and somehow "does the right
thing." At least with symlinks you have a fighting chance on some
platforms of being able to do:

tar zcvf /tmp/backup.tgz /usr/local/postgres/pgdata/

and have it do the right thing. Using PostgreSQL specific catalogs
you would force UNIX users (the majority of PostgreSQL users right
now) to back up their tablespaces manually. Forcing platforms with
symlinks to use your wacky symlink replacement just guarantees that
all platforms work equally poorly. It doesn't make the Win32 port any
better. You would still have to backup c:\pgdata z:\1\ and z:\2\
separately on Win32. The only difference is that now your misery
would have the company of all of the rest of us.

>>>It seems a little insane to introduce an OS/filesystem dependency
>>>at the onset of a porting effort especially if you hope to be OS
>>>agnostic for feature sets.  I think someone would be crying foul if
>>>a new feature only worked on Linux and not on FreeBSD.    
>>>
>>
>>First of all, symlinks are a pretty popular "feature."  Even Windows
>>supports what would be needed.  Second of all, PostgreSQL will still
>>run on OSes without symlinks, tablespaces won't be available, but
>>PostgreSQL will still run.  Since we are all using PostgreSQL
>>without tablespaces now, it can hardly be argued that tablespaces
>>are a critical feature.
>>
>>We aren't talking about a "feature that work[s] on Linux on not on
>>FreeBSD."  We are talking about a feature that works on every OS
>>that suports symlinks (which includes even operating systems like
>>Windows that PostgreSQL doesn't currently support).
>
> Hello?  What was this response from Tom Lane? "My feeling is that we
> need not support tablespaces on OS's without symlinks."  That seems to
> be indicative of a feature set restriction base on platform.

Tom Lane works for Red Hat. You can't hardly expect him to spend all
of his time working around the limitations of the competition's
operating system.

>>>Additionally, another developer noted the advantage of a text file
>>>is that it would be easy for someone to develop tools to help if it
>>>became difficult to edit or parse.  Additionally, there could be a
>>>change away from a flat file format to an XML format to configure
>>>the tablespace area.
>>
>>The advantage of symlinks is that no tools would have to be written
>>and 'ls -l' would show everything you would need to know about where
>>your tablespaces actually were.
>
> Where is 'ls -l' on a win32 box?  If you will follow the discussion
> of symlinks under MinGW you will see that they don't work as
> commanded. And, postgresql is supposed to be compiled under MinGW,
> but not require it to run.
>
> From Windows 2000, 'ls' is not recognized as an internal or external
> command, operable program or batch file.

Yes, Windows lacks 'ls', but it has similar tools.

>>XML files are relatively easy to parse, but they certainly aren't as
>>easy as simply letting PostgreSQL follow a symlink.  Why reinvent the
>>wheel with what would essentially be PostgreSQL's own implementation
>>of a symlink?
>> 
>>
>
> Is opening a file recreating a symlink?  If you are opening file
> descriptors why rely on symlinks.  If you know the location either
> from the system catalog, a or configuration file, is it any terribly
> more complicated?   Basically, if a tablespace needed to be renamed,
> or moved, or changed, your going to have to do file management
> anyway.  The symlink saves you just a lookup as to what files go
> where?  If you kept this small hash in memory, it's not a continuous
> lookup because you have the redirection internally.  And, it's more
> portable.

Yes, and the implementation of tablespaces apparently keeps track of
all of that in the system catalogs. In other words, the new table
space mechanism will be fancier than what we currently have (which is
just the ability to move the files themselves somewhere else and
create symlinks).

What the symlink does is allow the core part of PostgreSQL to pretend
that the files are still in one directory just like they have always
been. It also allows folks using OSes that support symlinks to be
able to simply tar up the directory :).

>>To go back to your *tar* example, are we going to rewrite tar so that
>>it reads PostgreSQL's XML file and *does the right thing*?
>
> I'm not talking about integrating tar with PostgreSQL or uniting the
> universal string theory with how to cook apple pie.  Why would think
> we would have to rewrite tar?

Your tar example was the only argument that you made against symlinks
that made any sense at all. Here's an example that you wanted to see
work.

Example:

PGDATA is C:\pgdata
I have a tablespace in Z:\1\ and Z:\2\
There exists an alleged symlink in
C:\pgdata\data\base\tablespaces\schmoo -> Z:\1

Can I archive [ C:\pgdata, Z:\1, Z:\2 ], restore them, and have
postgresql working just as before?

Now, using some sort of XML catalog can you archive [ C:\pgdata, Z:\1,
Z:\2 ] and have PostgreSQL working just as before?

The answer to that question is *no*, unless of course you read the XML
file yourself, archive each tablespace manually, and then restore it
all piece by piece. After all, zip or tar doesn't know anything about
your XML file, and so it doesn't have a clue about your various
tablespaces outside of c:\pgdata. Since this is *precisely* what you
would have to do with the symlink implementation (the difference being
that you would have to read the system catalogs instead of an XML
file) what's the point in handcapping systems with symlinks that work?

[snip]

> The other option proposed was to give win32 a subset of features that
> would be available to other platforms.  In this case, that would be that
> the win32 port could support tablespaces.  This is strikingly different
> than a performance issue.   It would be one thing for tablespaces to
> perform poorly, it's another for them to fail or not exist altogether.
>
>>Perhaps if you could give us an example of an actual case where some
>>actual PostgreSQL users (or potential users) might be affected?
>
> See the comment from Tom Lane on limiting features.  Look at the
> potential Win32 market which outnumbers the unix market in number of
> computers and developers by a large margin.

I *agree* with Tom Lane. Personally I am more concerned about
PostgreSQL running well on Linux (the platform I use) then I am about
being able to possibly win potential Windows installations. As far as
I am concerned my use of PostgreSQL on Linux gives me a competitive
advantage :). I see no reason to dumb down the Linux version of
PostgreSQL simply so that I can share the misery that a Windows user
has to face on a daily basis.

Besides which, directory symlinks actually do exist on Windows. I
have spent a bit of time playing with sysinternals 'junction' program,
and while not quite as cool as symlinks, it would certainly work.

Jason

[ lots of opinions about depending on symlinks for tablespaces ]

One thing that I think hasn't been noted in this thread is that our
initial implementation won't bind us forever. If it becomes clear that
a symlink-based implementation has real problems, we can change it.
But if we spend extra effort to avoid symlinks from the start, we will
never know whether that effort was justified or wasted.

We have enough work to do to make tablespaces happen that I feel we
should take the path of least resistance to start with. I do not think
it would cost us much wasted effort if the decision proves out wrong.

regards, tom lane