Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Mark Kirkwood]

On 26/09/17 20:44, Mark Kirkwood wrote:




$ pg_basebackup -D .
WARNING:  could not read symbolic link "pg_tblspc/space1": Invalid 
argument
pg_basebackup: directory "/data0/pgdata/11/pg_tblspc/space1" exists 
but is not empty

pg_basebackup: removing contents of data directory "."



Err - actually this example is wrong - sorry. In fact pg_basebackup is 
complaining because it does not want to overwrite the contents of the 
tablespace (need to use the -T option as I'm on the same host)!


A correct example of pg_basebackup failing due to tablespaces inside 
$PGDATA/pg_tblspc can be easily demonstrated by trying to set up 
streaming replication on another host:


$ pg_basebackup -h 10.0.119.100 -P -D .
WARNING:  could not read symbolic link "pg_tblspc/space1": Invalid argument
pg_basebackup: could not create directory "./pg_tblspc": File exists

Fortunately this can be worked around by changing to tar format:

$ pg_basebackup -h 10.0.119.100 -Ft -P -D .
WARNING:  could not read symbolic link "pg_tblspc/space1": Invalid argument
1560632/1560632 kB (100%), 2/2 tablespaces

...however, not that great that the plain mode is busted.

regards

Mark




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Mark Kirkwood]

On 29/04/15 09:35, Bruce Momjian wrote:


On Fri, Apr 24, 2015 at 01:05:03PM -0400, Bruce Momjian wrote:

This way, both pg_dump and pg_upgrade will issue warnings, though, of
course, those warnings can be ignored.  I am hopeful these two warnings
will be sufficient and we will not need make these errors, with the
possible inconvenience it will cause.  I am still afraid that someone
will ignore the new errors pg_dump would generate and lose data.  I just
don't remember enough cases where we threw new errors on _data_ restore.

Frankly, those using pg_upgrade already will have to move the old
tablespaces out of the old cluster if they ever want to delete those
clusters, so I am hopeful these additional warnings will help eliminate
this practice, which is already cumbersome and useless.  I am not
planning to revisit this for 9.6.




(resurrecting an old thread) I encountered this the other day, a 
customer had created tablespaces with directories inside 
$PGDATA/pg_tblspc. This is just pathalogical - e.g (v11 checkout with 
PGDATA=/data0/pgdata/11):


bench=# CREATE TABLESPACE space1 LOCATION 
'/data0/pgdata/11/pg_tblspc/space1';

WARNING:  tablespace location should not be inside the data directory
CREATE TABLESPACE
bench=# ALTER TABLE pgbench_accounts SET  TABLESPACE space1;
ALTER TABLE

Ok, so I've been warned:

$ pg_basebackup -D .
WARNING:  could not read symbolic link "pg_tblspc/space1": Invalid argument
pg_basebackup: directory "/data0/pgdata/11/pg_tblspc/space1" exists but 
is not empty

pg_basebackup: removing contents of data directory "."

So pg_basebackup is completely broken by this construction - should we 
not prohibit the creation of tablespace directories under $PGDATA (or at 
least $PGDATA/pg_tblspc) at this point?


regards

Mark




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Bruce Momjian]

On Fri, Apr 24, 2015 at 01:05:03PM -0400, Bruce Momjian wrote:
> This way, both pg_dump and pg_upgrade will issue warnings, though, of
> course, those warnings can be ignored.  I am hopeful these two warnings
> will be sufficient and we will not need make these errors, with the
> possible inconvenience it will cause.  I am still afraid that someone
> will ignore the new errors pg_dump would generate and lose data.  I just
> don't remember enough cases where we threw new errors on _data_ restore.
> 
> Frankly, those using pg_upgrade already will have to move the old
> tablespaces out of the old cluster if they ever want to delete those
> clusters, so I am hopeful these additional warnings will help eliminate
> this practice, which is already cumbersome and useless.  I am not
> planning to revisit this for 9.6.

Patch applied.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Bruce Momjian]

On Wed, Apr 22, 2015 at 10:41:02PM -0400, Bruce Momjian wrote:
> > josh=# create tablespace tbl2 location '/home/josh/pg94/data/pg_xlog/';
> > CREATE TABLESPACE
> > 
> > It really seems like we ought to block *THAT*.  Of course, if we block
> > tablespace creation in PGDATA generally, then that's covered.
> 
> I have developed the attached patch to warn about creating tablespaces
> inside the data directory.  The case this doesn't catch is referencing a
> symbolic link that points to the same directory.  We can't make it an
> error so people can use pg_upgrade these setups.  This would be for 9.5
> only.

OK, based on later discussions, I have updated my 9.5 patch to have
pg_upgrade also display a warning (the warning will also appear in the
pg_upgrade logs, but I doubt the user will see it), e.g.:

Setting next OID for new clusterok
Sync data directory to disk ok
Creating script to analyze new cluster  ok

WARNING:  user-defined tablespace locations should not be inside the 
data directory, e.g. /u/pgsql.old/data/pg_tblspc

Upgrade Complete

Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh

Could not create a script to delete the old cluster's data
files because user-defined tablespaces exist in the old cluster
directory.  The old cluster's contents must be deleted manually.

This way, both pg_dump and pg_upgrade will issue warnings, though, of
course, those warnings can be ignored.  I am hopeful these two warnings
will be sufficient and we will not need make these errors, with the
possible inconvenience it will cause.  I am still afraid that someone
will ignore the new errors pg_dump would generate and lose data.  I just
don't remember enough cases where we threw new errors on _data_ restore.

Frankly, those using pg_upgrade already will have to move the old
tablespaces out of the old cluster if they ever want to delete those
clusters, so I am hopeful these additional warnings will help eliminate
this practice, which is already cumbersome and useless.  I am not
planning to revisit this for 9.6.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Robert Haas]

On Thu, Apr 23, 2015 at 4:30 PM, Andres Freund  wrote:
> On 2015-04-23 16:26:09 -0400, Robert Haas wrote:
>> But pg_upgrade automates all that, so you can't use pg_upgrade in that
>> case.  If we add a GUC as I suggested, you can still use pg_upgrade.
>
> But we also have to live with data directories being in a shit state
> forever onward. We won't really be able to remove the option
> realistically.
>
> It's not that hard to just move the tablespace out of the data directory
> while the server. As long as you move it on the same partition, it's
> even fast.

OK, fair point.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[David Steele]

On 4/23/15 4:30 PM, Andres Freund wrote:
> On 2015-04-23 16:26:09 -0400, Robert Haas wrote:
>> But pg_upgrade automates all that, so you can't use pg_upgrade in that
>> case.  If we add a GUC as I suggested, you can still use pg_upgrade.
> 
> But we also have to live with data directories being in a shit state
> forever onward. We won't really be able to remove the option
> realistically.
> 
> It's not that hard to just move the tablespace out of the data directory
> while the server. As long as you move it on the same partition, it's
> even fast.

I agree.  It wouldn't be that hard to do a bit of directory manipulation
before upgrading - and that's only for the people who have put
tablespaces in $PGDATA.  I've never seen it before, but I have no doubt
that it happens.  I can see how it might make a weird sort of sense
depending on the level of experience.

-- 
- David Steele
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Andres Freund]

On 2015-04-23 16:26:09 -0400, Robert Haas wrote:
> But pg_upgrade automates all that, so you can't use pg_upgrade in that
> case.  If we add a GUC as I suggested, you can still use pg_upgrade.

But we also have to live with data directories being in a shit state
forever onward. We won't really be able to remove the option
realistically.

It's not that hard to just move the tablespace out of the data directory
while the server. As long as you move it on the same partition, it's
even fast.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Robert Haas]

On Thu, Apr 23, 2015 at 3:57 PM, Andres Freund  wrote:
> On 2015-04-23 15:46:20 -0400, Robert Haas wrote:
>> Well, we've made backward-incompatible changes before.  Not to this
>> specific thing, but in general.  I don't think there's anything
>> preventing us from doing so here, except that we don't want to annoy
>> too many users.
>
> I think the number of users that have done this, and haven't yet
> (knowing or unknowningly) been bitten by it is pretty low. In that
> scenario it seems much better to break compatibility given that it's
> pretty easy to fix during restore (just precreate the tablespace).  It's
> not something you have to retest a whole application for.
>
> If you want to avoid that one error you can still do pg_dumpall
> --globals, edit and run that script, and only then restore the the
> actual databases.

But pg_upgrade automates all that, so you can't use pg_upgrade in that
case.  If we add a GUC as I suggested, you can still use pg_upgrade.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Andres Freund]

On 2015-04-23 15:17:55 -0500, Jim Nasby wrote:
> Yes, but only after creating a brand new cluster from scratch, which would
> then disallow them from putting tablespaces in $PGDATA.

pg_dumpall output includes tablespaces.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Jim Nasby]

On 4/23/15 11:01 AM, Andres Freund wrote:

On April 23, 2015 6:12:05 PM GMT+03:00, Jim Nasby  
wrote:

On 4/22/15 9:41 PM, Bruce Momjian wrote:

The case this doesn't catch is referencing a
symbolic link that points to the same directory.  We can't make it an
error so people can use pg_upgrade these setups.


Couldn't we make it an ERROR unless IsBinaryUpgrade?


People still upgrade without pg upgrade.


Yes, but only after creating a brand new cluster from scratch, which 
would then disallow them from putting tablespaces in $PGDATA.


Or are you saying people do binary upgrades without pg_upgrade? I don't 
think we have any obligation to support that...

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Andres Freund]

On 2015-04-23 15:46:20 -0400, Robert Haas wrote:
> Well, we've made backward-incompatible changes before.  Not to this
> specific thing, but in general.  I don't think there's anything
> preventing us from doing so here, except that we don't want to annoy
> too many users.

I think the number of users that have done this, and haven't yet
(knowing or unknowningly) been bitten by it is pretty low. In that
scenario it seems much better to break compatibility given that it's
pretty easy to fix during restore (just precreate the tablespace).  It's
not something you have to retest a whole application for.

If you want to avoid that one error you can still do pg_dumpall
--globals, edit and run that script, and only then restore the the
actual databases.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Robert Haas]

On Thu, Apr 23, 2015 at 11:00 AM, Bruce Momjian  wrote:
>> > I have developed the attached patch to warn about creating tablespaces
>> > inside the data directory.  The case this doesn't catch is referencing a
>> > symbolic link that points to the same directory.  We can't make it an
>> > error so people can use pg_upgrade these setups.  This would be for 9.5
>> > only.
>>
>> I think this is a good thing to do, but I sure wish we could go
>> further and block it completely.  That may require more thought than
>> we have time to put in at this stage of the release cycle, though, so
>> +1 for doing at least this much.
>
> OK, good.  Thinking to 9.6, I am not sure how we could throw an error
> because we have allowed this in the past and pg_dump is going to be
> restored with a raw SQL CREATE TABLESPACE command.
>
> We have had this type of problem before, but never resolved it.  We
> almost need pg_dump to set a GUC variable telling the backend it is
> restoring a dump and issue a warning, but throw an error if the same
> command was issued outside of a pg_dump restore.  FYI, pg_upgrade
> already throws a warning related to the non-creation of a delete script.

Well, we've made backward-incompatible changes before.  Not to this
specific thing, but in general.  I don't think there's anything
preventing us from doing so here, except that we don't want to annoy
too many users.

I don't think the right solution is to add a GUC so that pg_dump
ignores this, and otherwise deny it.  It's bad if you do it as part of
a restore, and it's bad if you do it some other time, too.

What I'd recommend is that we add a GUC stupid_tablespaces=off.  If
you have done this in the past, and you want to upgrade (whether via
pg_dump or pg_upgrade) to a new release, you'll have to configure the
new cluster for stupid_tablespaces=on.  If you don't, you'll get an
error.  If you do, you'll get a warning.  That way, people can still
upgrade, but they have to set the GUC to make it work, so they'll be
clearly aware that they're doing something that is not recommended.

(Of course we might want to call the GUC something like other than
stupid_tablespaces, like allow_tablespaces_in_data_directory, but you
get the idea.)

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Andres Freund]

On April 23, 2015 6:12:05 PM GMT+03:00, Jim Nasby  
wrote:
>On 4/22/15 9:41 PM, Bruce Momjian wrote:
>> The case this doesn't catch is referencing a
>> symbolic link that points to the same directory.  We can't make it an
>> error so people can use pg_upgrade these setups.
>
>Couldn't we make it an ERROR unless IsBinaryUpgrade?

People still upgrade without pg upgrade.

Andres

--- 
Please excuse brevity and formatting - I am writing this on my mobile phone.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Bruce Momjian]

On Thu, Apr 23, 2015 at 05:05:14PM +0200, Andres Freund wrote:
> On 2015-04-23 11:00:43 -0400, Bruce Momjian wrote:
> > On Thu, Apr 23, 2015 at 09:13:52AM -0400, Robert Haas wrote:
> > > I think this is a good thing to do, but I sure wish we could go
> > > further and block it completely.  That may require more thought than
> > > we have time to put in at this stage of the release cycle, though, so
> > > +1 for doing at least this much.
> > 
> > OK, good.  Thinking to 9.6, I am not sure how we could throw an error
> > because we have allowed this in the past and pg_dump is going to be
> > restored with a raw SQL CREATE TABLESPACE command.  
> 
> We could just document that you need to pre-create the tablespace and
> ignore the resulting error. This isn't going to affect too many people.

This approach is going to cause any object in that tablespace to not
restore --- are we sure that enough people check for restore errors that
we will not have people losing data on a restore?

Also, the error is going to cause pg_upgrade to fail.  We could have
pg_upgrade --check detect these cases and force people to fix their
setups before they run pg_upgrade --- at least that would be consistent
with the pg_dump behavior.  Jim Nasby suggested throwing an error unless
IsBinaryUpgrade is set, and that would work, but it means we are
allowing such tablespaces to be upgraded using pg_upgrade only, which
seems kind of odd.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Jim Nasby]

On 4/22/15 9:41 PM, Bruce Momjian wrote:

The case this doesn't catch is referencing a
symbolic link that points to the same directory.  We can't make it an
error so people can use pg_upgrade these setups.


Couldn't we make it an ERROR unless IsBinaryUpgrade?
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Andres Freund]

On 2015-04-23 11:00:43 -0400, Bruce Momjian wrote:
> On Thu, Apr 23, 2015 at 09:13:52AM -0400, Robert Haas wrote:
> > I think this is a good thing to do, but I sure wish we could go
> > further and block it completely.  That may require more thought than
> > we have time to put in at this stage of the release cycle, though, so
> > +1 for doing at least this much.
> 
> OK, good.  Thinking to 9.6, I am not sure how we could throw an error
> because we have allowed this in the past and pg_dump is going to be
> restored with a raw SQL CREATE TABLESPACE command.  

We could just document that you need to pre-create the tablespace and
ignore the resulting error. This isn't going to affect too many people.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Bruce Momjian]

On Thu, Apr 23, 2015 at 09:13:52AM -0400, Robert Haas wrote:
> On Wed, Apr 22, 2015 at 10:41 PM, Bruce Momjian  wrote:
> >> What is a real problem is that we don't block creating tablespaces
> >> anywhere at all, including in obviously problematic places like the
> >> transaction log directory:
> >>
> >> josh=# create tablespace tbl2 location '/home/josh/pg94/data/pg_xlog/';
> >> CREATE TABLESPACE
> >>
> >> It really seems like we ought to block *THAT*.  Of course, if we block
> >> tablespace creation in PGDATA generally, then that's covered.
> >
> > I have developed the attached patch to warn about creating tablespaces
> > inside the data directory.  The case this doesn't catch is referencing a
> > symbolic link that points to the same directory.  We can't make it an
> > error so people can use pg_upgrade these setups.  This would be for 9.5
> > only.
> 
> I think this is a good thing to do, but I sure wish we could go
> further and block it completely.  That may require more thought than
> we have time to put in at this stage of the release cycle, though, so
> +1 for doing at least this much.

OK, good.  Thinking to 9.6, I am not sure how we could throw an error
because we have allowed this in the past and pg_dump is going to be
restored with a raw SQL CREATE TABLESPACE command.  

We have had this type of problem before, but never resolved it.  We
almost need pg_dump to set a GUC variable telling the backend it is
restoring a dump and issue a warning, but throw an error if the same
command was issued outside of a pg_dump restore.  FYI, pg_upgrade
already throws a warning related to the non-creation of a delete script.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Robert Haas]

On Wed, Apr 22, 2015 at 10:41 PM, Bruce Momjian  wrote:
>> What is a real problem is that we don't block creating tablespaces
>> anywhere at all, including in obviously problematic places like the
>> transaction log directory:
>>
>> josh=# create tablespace tbl2 location '/home/josh/pg94/data/pg_xlog/';
>> CREATE TABLESPACE
>>
>> It really seems like we ought to block *THAT*.  Of course, if we block
>> tablespace creation in PGDATA generally, then that's covered.
>
> I have developed the attached patch to warn about creating tablespaces
> inside the data directory.  The case this doesn't catch is referencing a
> symbolic link that points to the same directory.  We can't make it an
> error so people can use pg_upgrade these setups.  This would be for 9.5
> only.

I think this is a good thing to do, but I sure wish we could go
further and block it completely.  That may require more thought than
we have time to put in at this stage of the release cycle, though, so
+1 for doing at least this much.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Bruce Momjian]

On Fri, Jan 30, 2015 at 01:26:22PM -0800, Josh Berkus wrote:
> Robert, Stephen, etc.:
> 
> Apparently you can create a tablespace in the tablespace directory:
> 
> josh=# create tablespace tbl location '/home/josh/pg94/data/pg_tblspc/';
> CREATE TABLESPACE
> josh=# create table test_tbl ( test text ) tablespace tbl;
> CREATE TABLE
> josh=# \q
> josh@Radegast:~/pg94/data/pg_tblspc$ ls
> 17656  PG_9.4_201409291
> josh@Radegast:~/pg94/data/pg_tblspc$ ls -l
> total 4
> lrwxrwxrwx 1 josh josh   30 Jan 30 13:02 17656 ->
> /home/josh/pg94/data/pg_tblspc
> drwx-- 3 josh josh 4096 Jan 30 13:02 PG_9.4_201409291
> josh@Radegast:~/pg94/data/pg_tblspc$
> 
> In theory if I could guess the next OID, I could cause a failure there,
> but that appears to be obscure enough to be not worth bothering about.
> 
> What is a real problem is that we don't block creating tablespaces
> anywhere at all, including in obviously problematic places like the
> transaction log directory:
> 
> josh=# create tablespace tbl2 location '/home/josh/pg94/data/pg_xlog/';
> CREATE TABLESPACE
> 
> It really seems like we ought to block *THAT*.  Of course, if we block
> tablespace creation in PGDATA generally, then that's covered.

I have developed the attached patch to warn about creating tablespaces
inside the data directory.  The case this doesn't catch is referencing a
symbolic link that points to the same directory.  We can't make it an
error so people can use pg_upgrade these setups.  This would be for 9.5
only.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +
diff --git a/src/backend/commands/tablespace.c b/src/backend/commands/tablespace.c
new file mode 100644
index fd22612..4ec1aff
*** a/src/backend/commands/tablespace.c
--- b/src/backend/commands/tablespace.c
*** CreateTableSpace(CreateTableSpaceStmt *s
*** 288,293 
--- 288,299 
   errmsg("tablespace location \"%s\" is too long",
  		location)));
  
+ 	/* Warn if the tablespace is in the data directory. */
+ 	if (path_is_prefix_of_path(DataDir, location))
+ 		ereport(WARNING,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+  errmsg("tablespace location should not be inside the data directory")));
+ 
  	/*
  	 * Disallow creation of tablespaces named "pg_xxx"; we reserve this
  	 * namespace for system purposes.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Marc Mamin]

> it is just as likely they simply are not aware
> of the downsides and the only reason they put it is $PGDATA is that
> it seemed like a logical place to put a directory that is intended to hold
> database data.

Yes, this is the reason why we got in this issue. The name PGDATA is misleading.

> The creators of tablespaces seem to have envisioned their usage as a means
> of pulling in disparate file systems and not simply for namespaces within the 
> main
> filesystem that $PGDATA exists on.

true too. We have a lot of tablespaces. I'd probably won't go that way by now, 
but it still has the advantage to help quickly move parts of the data to  
manage filesystem usage.

> Given all this, it seems like a good idea to at least give a warning
> if somebody tries to create a tablespace instead the data directory.

IMHO the first place to put a warning is within the documentation:
 http://www.postgresql.org/docs/9.4/interactive/manage-ag-tablespaces.html
 and possibly a crosslink in 
http://www.postgresql.org/docs/9.4/interactive/sql-createtablespace.html
 
>If this is intended to be back-patched then I'd go with just a warning. If
>this is strictly 9.5 material then I'd say that since our own tools behave
>badly in the current situation we should simply outright disallow it. 

We have a lot of maintenance scripts that rely on our architecture
($PGDADAT -> symlinks -> tablespace locations). 
We already made a quick evaluation on how to fix this, but gave it up 
for now due to the work amount.
So please be cautious about disallowing it too abruptly. 
Back-patching a change that disallow our current architecture could prevent us 
to apply minor releases for a while...

regards,

Marc Mamin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Josh Berkus]

Robert, Stephen, etc.:

Apparently you can create a tablespace in the tablespace directory:

josh=# create tablespace tbl location '/home/josh/pg94/data/pg_tblspc/';
CREATE TABLESPACE
josh=# create table test_tbl ( test text ) tablespace tbl;
CREATE TABLE
josh=# \q
josh@Radegast:~/pg94/data/pg_tblspc$ ls
17656  PG_9.4_201409291
josh@Radegast:~/pg94/data/pg_tblspc$ ls -l
total 4
lrwxrwxrwx 1 josh josh   30 Jan 30 13:02 17656 ->
/home/josh/pg94/data/pg_tblspc
drwx-- 3 josh josh 4096 Jan 30 13:02 PG_9.4_201409291
josh@Radegast:~/pg94/data/pg_tblspc$

In theory if I could guess the next OID, I could cause a failure there,
but that appears to be obscure enough to be not worth bothering about.

What is a real problem is that we don't block creating tablespaces
anywhere at all, including in obviously problematic places like the
transaction log directory:

josh=# create tablespace tbl2 location '/home/josh/pg94/data/pg_xlog/';
CREATE TABLESPACE

It really seems like we ought to block *THAT*.  Of course, if we block
tablespace creation in PGDATA generally, then that's covered.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Josh Berkus]

On 01/30/2015 09:19 AM, Stephen Frost wrote:
> * Robert Haas (robertmh...@gmail.com) wrote:
>> Given all this, it seems like a good idea to at least give a warning
>> if somebody tries to create a tablespace instead the data directory.
> 
> A warning seems like a good idea.  I actually thought we *did* prevent
> it..
> 
>> Arguably, we should prohibit it altogether, but there are obviously
>> people that want to do it, and there could even be somewhat valid
>> reasons for that, like wanting to set per-tablespace settings
>> differently for different tablespaces.  Possibly we should prohibit it
>> anyway, or maybe there should be an option to create a tablespace
>> whose directory is a real directory, not a symlink.  So then:
>>
>> CREATE TABLESPACE foo LOCATION '/home/rhaas/pgdata/pg_tblspc/foo';
>>
>> ...would fail, but if you really want a separate tablespace inside the
>> data directory, we could allow:
>>
>> CREATE TABLESPACE foo NO LOCATION;
>>
>> ...which would just create a bare directory where the symlink would normally 
>> go.
> 
> I actually really like this 'NO LOCATION' idea.  Are there reasons why
> that would be difficult or ill-advised to do?
> 
> I could see the NO LOCATION approach being useful for migrating between
> systems, in particular, or a way to have pg_basebackup work that doesn't
> involve having to actually map all the tablespaces...

I like this idea too.  And it would make tablespaces more manageable for
people who are using them for reasons other than putting them on
different disks.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[David G Johnston]

Robert Haas wrote
> Arguably, we should prohibit it altogether, but there are obviously
> people that want to do it, and there could even be somewhat valid
> reasons for that, 

Lots of hand-waving here and it is just as likely they simply are not aware
of the downsides and the only reason they put it is $PGDATA is that
it seemed like a logical place to put a directory that is intended to hold
database data.


> like wanting to set per-tablespace settings differently for different
> tablespaces.

I do not follow where this has anything to do with the location of the
physical tablespace directory?


> Possibly we should prohibit it
> anyway, or maybe there should be an option to create a tablespace
> whose directory is a real directory, not a symlink.  So then:
> 
> CREATE TABLESPACE foo LOCATION '/home/rhaas/pgdata/pg_tblspc/foo';
> 
> ...would fail, but if you really want a separate tablespace inside the
> data directory, we could allow:
> 
> CREATE TABLESPACE foo NO LOCATION;
> 
> ...which would just create a bare directory where the symlink would
> normally go.

CREATE TABLE foo LOCATION INTERNAL

The creators of tablespaces seem to have envisioned their usage as a means
of pulling in
disparate file systems and not simply for namespaces within the main
filesystem
that $PGDATA exists on.

This seems arbitrary and while the internal location specification likely
doesn't buy one much in terms of real options it doesn't seem like it has
any serious downsides either.


> In the short term, I favor just adding a warning, so that people get
> some clue that they are doing something that might be a bad idea.  In
> the long term, we might want to do more.  Thoughts?

If this is intended to be back-patched then I'd go with just a warning.  If
this is strictly 9.5 material then I'd say that since our own tools behave
badly in the current situation we should simply outright disallow it.  In
either case we should consider what tools we can provide to detect the
now-illegal configuration and, during pg_upgrade, configure the new cluster
to adhere to the correct configuration or help the user migrate their
internalized tablespaces to a different part of their filesystem.

Writing this I ponder the situation that someone would mount a different
file system directly under $PGDATA so that they get both benefits - single
parent and the different properties of the filesystems they are using.  If
we force "Internal" to be in the same location as the default tablespace we
only accomplish half of their goals.

David J.



--
View this message in context: 
http://postgresql.nabble.com/tablespaces-inside-PGDATA-considered-harmful-tp5836161p5836180.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Stephen Frost]

* Robert Haas (robertmh...@gmail.com) wrote:
> Given all this, it seems like a good idea to at least give a warning
> if somebody tries to create a tablespace instead the data directory.

A warning seems like a good idea.  I actually thought we *did* prevent
it..

> Arguably, we should prohibit it altogether, but there are obviously
> people that want to do it, and there could even be somewhat valid
> reasons for that, like wanting to set per-tablespace settings
> differently for different tablespaces.  Possibly we should prohibit it
> anyway, or maybe there should be an option to create a tablespace
> whose directory is a real directory, not a symlink.  So then:
> 
> CREATE TABLESPACE foo LOCATION '/home/rhaas/pgdata/pg_tblspc/foo';
> 
> ...would fail, but if you really want a separate tablespace inside the
> data directory, we could allow:
> 
> CREATE TABLESPACE foo NO LOCATION;
> 
> ...which would just create a bare directory where the symlink would normally 
> go.

I actually really like this 'NO LOCATION' idea.  Are there reasons why
that would be difficult or ill-advised to do?

I could see the NO LOCATION approach being useful for migrating between
systems, in particular, or a way to have pg_basebackup work that doesn't
involve having to actually map all the tablespaces...

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[David Steele]

On 1/30/15 11:43 AM, Joshua D. Drake wrote:
> On 01/30/2015 08:19 AM, Bruce Momjian wrote:
>>
>> On Fri, Jan 30, 2015 at 11:12:43AM -0500, Robert Haas wrote:
>>> I think everyone who has read this mailing list for a while is
>>> probably already aware of this problem.  When you create a tablespace
>>> somewhere inside the data directory, weird things happen. If you
>>> pg_upgrade and then incautiously run the delete_old_cluster.sh script
>>> thus created, you will blow away large chunks of your data.[1]  If you
>>
>> pg_upgrade doesn't create the deletion script in this case, and warns
>> the user:
>>
>>  Could not create a script to delete the old cluster's data
>>  files because user-defined tablespaces exist in the old cluster
>>  directory.  The old cluster's contents must be deleted
>> manually.
>>
>>> In the short term, I favor just adding a warning, so that people get
>>> some clue that they are doing something that might be a bad idea.  In
>>> the long term, we might want to do more.  Thoughts?
>>
>> Yes, good idea.
>
> Uhm, wouldn't it be a rather simple patch to say:
>
> if tablespace_create() in $PGDATA:
>   ERROR!
>
> ?
>
> I mean yes a warning is good but it is after the fact, the tablespace
> is already created. We know that tablespaces in $PGDATA are a bad
> idea, why not protect the user?

I would be in favor of an error.  It would then be OK for basebackup,
pg_upgrade, and friends to error when a tablespace lives in $PGDATA,
rather than trying to deal with the situation in strange ways.

If the user really wants tablespaces in $PGDATA they can always change
the links manually in the filesystem and deal with any consequences on
their own. 

-- 
- David Steele
da...@pgmasters.net




signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Robert Haas]

On Fri, Jan 30, 2015 at 11:43 AM, Joshua D. Drake  
wrote:
> I mean yes a warning is good but it is after the fact, the tablespace is
> already created. We know that tablespaces in $PGDATA are a bad idea, why not
> protect the user?

Please go back and read the discussion of that option in the OP.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Joshua D. Drake]

On 01/30/2015 08:19 AM, Bruce Momjian wrote:


On Fri, Jan 30, 2015 at 11:12:43AM -0500, Robert Haas wrote:

I think everyone who has read this mailing list for a while is
probably already aware of this problem.  When you create a tablespace
somewhere inside the data directory, weird things happen. If you
pg_upgrade and then incautiously run the delete_old_cluster.sh script
thus created, you will blow away large chunks of your data.[1]  If you


pg_upgrade doesn't create the deletion script in this case, and warns
the user:

 Could not create a script to delete the old cluster's data
 files because user-defined tablespaces exist in the old cluster
 directory.  The old cluster's contents must be deleted manually.


In the short term, I favor just adding a warning, so that people get
some clue that they are doing something that might be a bad idea.  In
the long term, we might want to do more.  Thoughts?


Yes, good idea.


Uhm, wouldn't it be a rather simple patch to say:

if tablespace_create() in $PGDATA:
  ERROR!

?

I mean yes a warning is good but it is after the fact, the tablespace is 
already created. We know that tablespaces in $PGDATA are a bad idea, why 
not protect the user?


JD







--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should
 not be surprised when they come back as Romans."


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

[Bruce Momjian]

On Fri, Jan 30, 2015 at 11:12:43AM -0500, Robert Haas wrote:
> I think everyone who has read this mailing list for a while is
> probably already aware of this problem.  When you create a tablespace
> somewhere inside the data directory, weird things happen. If you
> pg_upgrade and then incautiously run the delete_old_cluster.sh script
> thus created, you will blow away large chunks of your data.[1]  If you

pg_upgrade doesn't create the deletion script in this case, and warns
the user:

Could not create a script to delete the old cluster's data
files because user-defined tablespaces exist in the old cluster
directory.  The old cluster's contents must be deleted manually.

> In the short term, I favor just adding a warning, so that people get
> some clue that they are doing something that might be a bad idea.  In
> the long term, we might want to do more.  Thoughts?

Yes, good idea.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] tablespaces inside $PGDATA considered harmful

[Robert Haas]

I think everyone who has read this mailing list for a while is
probably already aware of this problem.  When you create a tablespace
somewhere inside the data directory, weird things happen. If you
pg_upgrade and then incautiously run the delete_old_cluster.sh script
thus created, you will blow away large chunks of your data.[1]  If you
try to use pg_basebackup, it will back up your data twice and maybe
throw some warnings.[2]  You can also induce pg_database_size() to
give wrong results --- it'll count pg_tblspace/$TABLESPACE_OID as well
as pg_tblspace/some-stupid-tablespace-name, the former being a symlink
to the latter.

Given all this, it seems like a good idea to at least give a warning
if somebody tries to create a tablespace instead the data directory.
Arguably, we should prohibit it altogether, but there are obviously
people that want to do it, and there could even be somewhat valid
reasons for that, like wanting to set per-tablespace settings
differently for different tablespaces.  Possibly we should prohibit it
anyway, or maybe there should be an option to create a tablespace
whose directory is a real directory, not a symlink.  So then:

CREATE TABLESPACE foo LOCATION '/home/rhaas/pgdata/pg_tblspc/foo';

...would fail, but if you really want a separate tablespace inside the
data directory, we could allow:

CREATE TABLESPACE foo NO LOCATION;

...which would just create a bare directory where the symlink would normally go.

In the short term, I favor just adding a warning, so that people get
some clue that they are doing something that might be a bad idea.  In
the long term, we might want to do more.  Thoughts?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[1] 
http://www.postgresql.org/message-id/b6f6fd62f2624c4c9916ac0175d56d880ce46...@jenmbs01.ad.intershop.net
[2] 
http://www.postgresql.org/message-id/cabuevexkhe+kcqa+flueaizp5i5qvcnnjz2j0zzqcamjfhe...@mail.gmail.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Tablespaces in the data directory

[Noah Misch]

On Mon, Dec 03, 2012 at 01:14:30PM -0500, Andrew Dunstan wrote:
> I think it would be reasonable for it to complain if it came across a  
> PG_VERSION file in an unexpected location.

That sounds like a reliable approach to detecting the hazard.  Pseudocode:

chdir(proposed_tablespace_path)
do {
if (stat("PG_VERSION"))
ereport(WARNING, ...)
} while (chdir(".."))


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Tablespaces in the data directory

[Andreas Karlsson]

On 2012-12-01 14:45, Magnus Hagander wrote:

Someone just reported a problem when they had created a new tablespace
inside the old data directory. I'm sure there can be other issues
caused by this as well, but this is mainly a confusing scenario for
people now.

As there isn't (as far as I know at least) any actual *point* in
creating a tablespace inside the main data directory, should we
perhaps disallow this in CREATE TABLESPACE? Or at least throw a
WARNING if one does it?


Does this apply when creating a tablespace in another tablespace too? If 
the problem is that pg_basebackup copies that data twice it sounds like 
it should.


--
Andreas Karlsson


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Tablespaces in the data directory

[Bruce Momjian]

On Tue, Dec  4, 2012 at 09:37:46AM -0500, Robert Haas wrote:
> On Mon, Dec 3, 2012 at 10:06 PM, Bruce Momjian  wrote:
> > FYI, someone put their new cluster inside an existing old tablespace,
> > and when they ran the script to delete their old install, their new
> > install was deleted too.  My answer was, "Don't do that".
> 
> Uh, wow.  I feel bad for that person, but it does seem like a bit of a
> self-inflicted injury.

They wanted pg_upgrade to guard against it, and I said that was
possible, but it would require pg_upgrade to know which files to remove,
and that would make pg_upgrade more fragile.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Tablespaces in the data directory

[Robert Haas]

On Mon, Dec 3, 2012 at 10:06 PM, Bruce Momjian  wrote:
> FYI, someone put their new cluster inside an existing old tablespace,
> and when they ran the script to delete their old install, their new
> install was deleted too.  My answer was, "Don't do that".

Uh, wow.  I feel bad for that person, but it does seem like a bit of a
self-inflicted injury.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Tablespaces in the data directory

[Michael Glaesemann]

On Dec 3, 2012, at 12:33, Magnus Hagander wrote:

> On Dec 3, 2012 2:55 AM, "Andrew Dunstan"  wrote:
>> 
>> 
>> On 12/02/2012 07:50 PM, Magnus Hagander wrote:
>>> 
>>> On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane  wrote:
 
 Magnus Hagander  writes:
> 
> Someone just reported a problem when they had created a new tablespace
> inside the old data directory. I'm sure there can be other issues
> caused by this as well, but this is mainly a confusing scenario for
> people now.
> As there isn't (as far as I know at least) any actual *point* in
> creating a tablespace inside the main data directory, should we
> perhaps disallow this in CREATE TABLESPACE? Or at least throw a
> WARNING if one does it?
 
 It could be pretty hard to detect that in general (think symlinks
 and such).  I guess if we're just trying to print a helpful warning,
 we don't have to worry about extreme corner cases.  But what exactly
 do you have in mind --- complain about any relative path?  Complain
 about absolute paths that have a prefix matching the DataDir?
>>> 
>>> Oh, I hadn't thought quite so far as the implementation :) Was looking
>>> to see if there were going to be some major objections before I even
>>> started thinking about that.
>>> 
>>> But for the implementation, I'd say any absolute path that have a
>>> prefix matching DataDir. Tablespaces cannot be created using relative
>>> paths, so we don't have to deal with that.
>>> 
>> 
>> I have been known to symlink a tablespace on a replica back to a
> directory in the datadir, while on the primary it points elsewhere. What
> exactly is the problem?
> 
> That wouldn't be affected by this though, since it would only warn at
> create tablespace.
> 
> I'd still consider it a bad idea in general to do that, since you're
> basically messing with the internal structure of the data directory. Why
> not just link it to some place outside the data directory?

One reason is that subsequent copies of the data directory then also includes 
the tablespace data. Saves one step when setting up a standby.

Michael Glaesemann
grzm seespotcode net





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Tablespaces in the data directory

[Bruce Momjian]

On Mon, Dec  3, 2012 at 02:38:20AM -, Greg Sabino Mullane wrote:
> 
> -BEGIN PGP SIGNED MESSAGE-
> Hash: RIPEMD160
> 
> 
> > As there isn't (as far as I know at least) any actual *point* in
> > creating a tablespace inside the main data directory, should we
> > perhaps disallow this in CREATE TABLESPACE? Or at least throw a
> > WARNING if one does it?
> 
> Sure there is a point - emulating some other system. Could be 
> replication, QA box, disaster recovery, etc. I'd be 
> cool with a warning, but do not think we should disallow it.

FYI, someone put their new cluster inside an existing old tablespace,
and when they ran the script to delete their old install, their new
install was deleted too.  My answer was, "Don't do that".

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Tablespaces in the data directory

[Andrew Dunstan]

On 12/03/2012 12:33 PM, Magnus Hagander wrote:



On Dec 3, 2012 2:55 AM, "Andrew Dunstan" > wrote:

>
>
> On 12/02/2012 07:50 PM, Magnus Hagander wrote:
>>
>> On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane > wrote:

>>>
>>> Magnus Hagander mailto:mag...@hagander.net>> 
writes:


 Someone just reported a problem when they had created a new 
tablespace

 inside the old data directory. I'm sure there can be other issues
 caused by this as well, but this is mainly a confusing scenario for
 people now.
 As there isn't (as far as I know at least) any actual *point* in
 creating a tablespace inside the main data directory, should we
 perhaps disallow this in CREATE TABLESPACE? Or at least throw a
 WARNING if one does it?
>>>
>>> It could be pretty hard to detect that in general (think symlinks
>>> and such).  I guess if we're just trying to print a helpful warning,
>>> we don't have to worry about extreme corner cases.  But what exactly
>>> do you have in mind --- complain about any relative path?  Complain
>>> about absolute paths that have a prefix matching the DataDir?
>>
>> Oh, I hadn't thought quite so far as the implementation :) Was looking
>> to see if there were going to be some major objections before I even
>> started thinking about that.
>>
>> But for the implementation, I'd say any absolute path that have a
>> prefix matching DataDir. Tablespaces cannot be created using relative
>> paths, so we don't have to deal with that.
>>
>
> I have been known to symlink a tablespace on a replica back to a 
directory in the datadir, while on the primary it points elsewhere. 
What exactly is the problem?


That wouldn't be affected by this though, since it would only warn at 
create tablespace.


I'd still consider it a bad idea in general to do that, since you're 
basically messing with the internal structure of the data directory. 
Why not just link it to some place outside the data directory?


One obvious problem with it atm is that pg_basebackup breaks, in that 
it backs up your data twice, and throws warnings about things that 
aren't links if you actually out it inside pg_tblspc.





Well, when I last did it I don't think there was such a thing as 
pg_basebackup :-)


I think it would be reasonable for it to complain if it came across a 
PG_VERSION file in an unexpected location.


cheers

andrew



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Tablespaces in the data directory

[Magnus Hagander]

On Dec 3, 2012 2:55 AM, "Andrew Dunstan"  wrote:
>
>
> On 12/02/2012 07:50 PM, Magnus Hagander wrote:
>>
>> On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane  wrote:
>>>
>>> Magnus Hagander  writes:

 Someone just reported a problem when they had created a new tablespace
 inside the old data directory. I'm sure there can be other issues
 caused by this as well, but this is mainly a confusing scenario for
 people now.
 As there isn't (as far as I know at least) any actual *point* in
 creating a tablespace inside the main data directory, should we
 perhaps disallow this in CREATE TABLESPACE? Or at least throw a
 WARNING if one does it?
>>>
>>> It could be pretty hard to detect that in general (think symlinks
>>> and such).  I guess if we're just trying to print a helpful warning,
>>> we don't have to worry about extreme corner cases.  But what exactly
>>> do you have in mind --- complain about any relative path?  Complain
>>> about absolute paths that have a prefix matching the DataDir?
>>
>> Oh, I hadn't thought quite so far as the implementation :) Was looking
>> to see if there were going to be some major objections before I even
>> started thinking about that.
>>
>> But for the implementation, I'd say any absolute path that have a
>> prefix matching DataDir. Tablespaces cannot be created using relative
>> paths, so we don't have to deal with that.
>>
>
> I have been known to symlink a tablespace on a replica back to a
directory in the datadir, while on the primary it points elsewhere. What
exactly is the problem?

That wouldn't be affected by this though, since it would only warn at
create tablespace.

I'd still consider it a bad idea in general to do that, since you're
basically messing with the internal structure of the data directory. Why
not just link it to some place outside the data directory?

One obvious problem with it atm is that pg_basebackup breaks, in that it
backs up your data twice, and throws warnings about things that aren't
links if you actually out it inside pg_tblspc.

/Magnus

Re: [HACKERS] Tablespaces in the data directory

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


> As there isn't (as far as I know at least) any actual *point* in
> creating a tablespace inside the main data directory, should we
> perhaps disallow this in CREATE TABLESPACE? Or at least throw a
> WARNING if one does it?

Sure there is a point - emulating some other system. Could be 
replication, QA box, disaster recovery, etc. I'd be 
cool with a warning, but do not think we should disallow it.

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201212022133
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAlC8D7kACgkQvJuQZxSWSsj+5gCgsmi6NXue+Hp0gycVOL/JEGUT
anYAoIqwo24JeLfliRHLvwPbdK4F4TXa
=EwgC
-END PGP SIGNATURE-




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Tablespaces in the data directory

[Andrew Dunstan]

On 12/02/2012 07:50 PM, Magnus Hagander wrote:

On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane  wrote:

Magnus Hagander  writes:

Someone just reported a problem when they had created a new tablespace
inside the old data directory. I'm sure there can be other issues
caused by this as well, but this is mainly a confusing scenario for
people now.
As there isn't (as far as I know at least) any actual *point* in
creating a tablespace inside the main data directory, should we
perhaps disallow this in CREATE TABLESPACE? Or at least throw a
WARNING if one does it?

It could be pretty hard to detect that in general (think symlinks
and such).  I guess if we're just trying to print a helpful warning,
we don't have to worry about extreme corner cases.  But what exactly
do you have in mind --- complain about any relative path?  Complain
about absolute paths that have a prefix matching the DataDir?

Oh, I hadn't thought quite so far as the implementation :) Was looking
to see if there were going to be some major objections before I even
started thinking about that.

But for the implementation, I'd say any absolute path that have a
prefix matching DataDir. Tablespaces cannot be created using relative
paths, so we don't have to deal with that.



I have been known to symlink a tablespace on a replica back to a 
directory in the datadir, while on the primary it points elsewhere. What 
exactly is the problem?


cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Tablespaces in the data directory

[Magnus Hagander]

On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane  wrote:
> Magnus Hagander  writes:
>> Someone just reported a problem when they had created a new tablespace
>> inside the old data directory. I'm sure there can be other issues
>> caused by this as well, but this is mainly a confusing scenario for
>> people now.
>
>> As there isn't (as far as I know at least) any actual *point* in
>> creating a tablespace inside the main data directory, should we
>> perhaps disallow this in CREATE TABLESPACE? Or at least throw a
>> WARNING if one does it?
>
> It could be pretty hard to detect that in general (think symlinks
> and such).  I guess if we're just trying to print a helpful warning,
> we don't have to worry about extreme corner cases.  But what exactly
> do you have in mind --- complain about any relative path?  Complain
> about absolute paths that have a prefix matching the DataDir?

Oh, I hadn't thought quite so far as the implementation :) Was looking
to see if there were going to be some major objections before I even
started thinking about that.

But for the implementation, I'd say any absolute path that have a
prefix matching DataDir. Tablespaces cannot be created using relative
paths, so we don't have to deal with that.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Tablespaces in the data directory

[Tom Lane]

Magnus Hagander  writes:
> Someone just reported a problem when they had created a new tablespace
> inside the old data directory. I'm sure there can be other issues
> caused by this as well, but this is mainly a confusing scenario for
> people now.

> As there isn't (as far as I know at least) any actual *point* in
> creating a tablespace inside the main data directory, should we
> perhaps disallow this in CREATE TABLESPACE? Or at least throw a
> WARNING if one does it?

It could be pretty hard to detect that in general (think symlinks
and such).  I guess if we're just trying to print a helpful warning,
we don't have to worry about extreme corner cases.  But what exactly
do you have in mind --- complain about any relative path?  Complain
about absolute paths that have a prefix matching the DataDir?

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Tablespaces in the data directory

[Simon Riggs]

On 1 December 2012 13:45, Magnus Hagander  wrote:
> Someone just reported a problem when they had created a new tablespace
> inside the old data directory. I'm sure there can be other issues
> caused by this as well, but this is mainly a confusing scenario for
> people now.
>
> As there isn't (as far as I know at least) any actual *point* in
> creating a tablespace inside the main data directory, should we
> perhaps disallow this in CREATE TABLESPACE? Or at least throw a
> WARNING if one does it?

+1

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Tablespaces in the data directory

[Magnus Hagander]

Someone just reported a problem when they had created a new tablespace
inside the old data directory. I'm sure there can be other issues
caused by this as well, but this is mainly a confusing scenario for
people now.

As there isn't (as far as I know at least) any actual *point* in
creating a tablespace inside the main data directory, should we
perhaps disallow this in CREATE TABLESPACE? Or at least throw a
WARNING if one does it?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Tablespaces for temporary objects

[Jim C. Nasby]

On Tue, Sep 19, 2006 at 01:10:48AM +0200, Albert Cervera Areny wrote:
> Hi,
> I've decided to start hacking on PostgreSQL, and I've looked at the 
> easier jobs in the TODO list. I'm interested in implementing:
> 
> % Add a GUC variable to control the tablespace for temporary objects and sort 
> files. It could start with a random tablespace from a supplied list and cycle 
> through the list.
> 
> I wanted to know if there are any comments on how I should implement 
> this. I've already started and temporary tables already honour 
> the 'temp_tablespaces' GUC variable I created. However, I'm not very happy 
> with the name, and probably someone will have other comments on this.

If you've already got a patch for this, you should submit it to
pgsql-patches and request feedback there.
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

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

[HACKERS] Tablespaces for temporary objects

[Albert Cervera Areny]

Hi,
I've decided to start hacking on PostgreSQL, and I've looked at the 
easier jobs in the TODO list. I'm interested in implementing:

% Add a GUC variable to control the tablespace for temporary objects and sort 
files. It could start with a random tablespace from a supplied list and cycle 
through the list.

I wanted to know if there are any comments on how I should implement 
this. I've already started and temporary tables already honour 
the 'temp_tablespaces' GUC variable I created. However, I'm not very happy 
with the name, and probably someone will have other comments on this.

Thanks.


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] Tablespaces oddity?

[Philip Yarra]

On Wed, 29 Mar 2006 08:46 am, Philip Yarra wrote:
> OK, how about on \d+, if the object is not on pg_default or pg_global,
> print the tablespace that this object is on? That way, people not using
> tablespaces won't ever see it.

Tom, does this answer your objection? If so, I'll produce a patch for it.

Regards, Philip.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Tablespaces oddity?

[Philip Yarra]

On Wed, 29 Mar 2006 08:46 am, Philip Yarra wrote:
> OK, how about on \d+, if the object is not on pg_default or pg_global,
> print the tablespace that this object is on? That way, people not using
> tablespaces won't ever see it.

Tom, does this answer your objection? If so, I'll produce a patch for it.

Regards, Philip.

PS: sorry about duplicate post, accidentally sent from other email account.

-- 

"Debugging is twice as hard as writing the code in the first place.
Therefore, if you write the code as cleverly as possible, you are,
by definition, not smart enough to debug it." - Brian W. Kernighan

-
Utiba Pty Ltd 
This message has been scanned for viruses and
dangerous content by Utiba mail server and is 
believed to be clean.


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] Tablespaces oddity?

[Philip Yarra]

On Wed, 29 Mar 2006 01:36 am, Tom Lane wrote:
> Philip Yarra <[EMAIL PROTECTED]> writes:
> > Someone else might be able to see a better way to write this query, but I
> > think it would be good if \d could show this information, when you really
> > want to know which tablespace an object is on.
>
> If \d doesn't say anything then the table is in the database's default
> tablespace.  I see nothing wrong with that, and I do object to
> cluttering \d output with information that will be of no interest to
> people not using tablespaces.

OK, how about on \d+, if the object is not on pg_default or pg_global, print 
the tablespace that this object is on? That way, people not using tablespaces 
won't ever see it.

> > Note also that \l won't show you the tablespace for a DB, so you need
> > to query pg_database to even know which is the default tablespace for
> > a DB.
>
> I wouldn't object to adding default tablespace to \l output, or maybe \l+.

OK, not fussed which one it's on, so long as it's there - this should do it 
for \l+

SELECT d.datname as "Name",
r.rolname as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
pg_catalog.obj_description(d.oid, 'pg_database') as "Description",
t.spcname as "Tablespace"
FROM pg_catalog.pg_database d
LEFT JOIN pg_catalog.pg_roles r ON d.datdba = r.oid
LEFT JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid;

On a related note: is there a simple way to show all objects on a given 
tablespace? If not, would other people also see this as useful?

Regards, Philip.

-- 

"Debugging is twice as hard as writing the code in the first place.
Therefore, if you write the code as cleverly as possible, you are,
by definition, not smart enough to debug it." - Brian W. Kernighan

-
Utiba Pty Ltd 
This message has been scanned for viruses and
dangerous content by Utiba mail server and is 
believed to be clean.


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] Tablespaces oddity?

[Tom Lane]

Philip Yarra <[EMAIL PROTECTED]> writes:
> Someone else might be able to see a better way to write this query, but I 
> think it would be good if \d could show this information, when you really 
> want to know which tablespace an object is on. 

If \d doesn't say anything then the table is in the database's default
tablespace.  I see nothing wrong with that, and I do object to
cluttering \d output with information that will be of no interest to
people not using tablespaces.

> Note also that \l won't show you the tablespace for a DB, so you need
> to query pg_database to even know which is the default tablespace for
> a DB.

I wouldn't object to adding default tablespace to \l output, or maybe \l+.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[HACKERS] Tablespaces oddity?

[Philip Yarra]

Hi folks after discussing this on IRC today (thanks G_SabinoMullane!), I'm 
still surprised by this behaviour on 8.1.3:

pyarra=# create TABLESPACE spctables location '/mnt/pg_tables/data';
CREATE TABLESPACE
pyarra=# create table foo(id int) tablespace spctables;
CREATE TABLE
pyarra=# \d foo
  Table "public.foo"
 Column |  Type   | Modifiers
+-+---
 id | integer |
Tablespace: "spctables"

So far, so good...

pyarra=# CREATE DATABASE spctest TABLESPACE spctables;
CREATE DATABASE
pyarra=# \c spctest;
You are now connected to database "spctest".
spctest=# create table foo(id int) tablespace spctables;
CREATE TABLE
spctest=# create table bar(id int);
CREATE TABLE
spctest=# \d foo
  Table "public.foo"
 Column |  Type   | Modifiers
+-+---
 id | integer |

spctest=# \d bar
  Table "public.bar"
 Column |  Type   | Modifiers
+-+---
 id | integer |

I hoped that these last two tables would also be listed as being on spctables.

I think the issue is that pg_class.reltablespace = 0 where these objects are 
created on the default tablespace for this database. I can find out which 
tablespace the objects are really on with:

select relname, COALESCE(t.spcname,(select spcname from pg_tablespace where 
oid = (select dattablespace from pg_database where datname 
=current_database( as tablespace from pg_class c left join pg_tablespace 
t on (t.oid = c.reltablespace)

Someone else might be able to see a better way to write this query, but I 
think it would be good if \d could show this information, when you really 
want to know which tablespace an object is on. 

Note also that \l won't show you the tablespace for a DB, so you need to query 
pg_database to even know which is the default tablespace for a DB. It's not 
impossible, just harder than it needs to be, I reckon.

Any thoughts?

Regards, Philip.

-- 

"Debugging is twice as hard as writing the code in the first place.
Therefore, if you write the code as cleverly as possible, you are,
by definition, not smart enough to debug it." - Brian W. Kernighan

-
Utiba Pty Ltd 
This message has been scanned for viruses and
dangerous content by Utiba mail server and is 
believed to be clean.


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

Re: [HACKERS] tablespaces and non-empty directories

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Jim C. Nasby wrote:
>> Along those lines, is there anything else that would benefit from being
>> moved? pg_clog and pg_subtrans come to mind; but maybe pg_multixact and
>> pg_twophase are candidates as well?

> Hmm, I doubt moving any of the SLRU files (clog, subtrans, multixact)
> will have much of an impact.

Certainly pushing them onto the WAL spindle would be a serious misstep.
There is a good case for giving WAL its own dedicated disk --- there is
no case that I've seen for giving any of these their own disk.

> If there's too much I/O on those, a better
> solution would be to increase the number of buffers allocated to them.
> Currently we use 8 for all of them which is probably not appropiate for
> everyone.

I've just been looking at a test case provided by Rob Creager that
causes some pretty severe contention on SubtransControlLock.  There
are a number of possible answers to this, but increasing the number of
pg_subtrans buffers is definitely one of them.  I think it's probably
time we got rid of the assumption that all the uses of slru.c should
have the same number of buffers ...

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] tablespaces and non-empty directories

[Philip Yarra]

On Wed, 23 Nov 2005 11:23 am, Gavin Sherry wrote:
> > Along those lines, is there anything else that would benefit from being
> > moved? pg_clog and pg_subtrans come to mind; but maybe pg_multixact and
> > pg_twophase are candidates as well?
>
> pgsql_tmp

Does anyone have any recommendations about which of these would contend with 
each other for disk IO? I'm looking to put together a doco addition about 
multi-disk setup, so far I have something like:

/mnt/pg_base
/mnt/pg_xlog
/mnt/pg_tab1
/mnt/pg_idx1

...but is there significant gain in moving other bits from pg_base to a 
different spindle? If so, what can be safely combined, and what would 
definitely cause contention?

I know that the answer would vary for different types of DB activity, but any 
"rough guides" would be a handy place to start. 

Regards, Philip.
-- 

"Debugging is twice as hard as writing the code in the first place.
Therefore, if you write the code as cleverly as possible, you are,
by definition, not smart enough to debug it." - Brian W. Kernighan

-
Utiba Pty Ltd 
This message has been scanned for viruses and
dangerous content by Utiba mail server and is 
believed to be clean.


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] tablespaces and non-empty directories

[Alvaro Herrera]

Jim C. Nasby wrote:
> On Tue, Nov 22, 2005 at 01:38:34PM -0500, Bruce Momjian wrote:
> > 
> > * Allow the pg_xlog directory location to be specified during initdb
> >   with a symlink back to the /data location
> > 
> > I think the only reason it is not done yet is because it is so easy to
> > do for admins, and it is impossible to do while the server is running.
> 
> Along those lines, is there anything else that would benefit from being
> moved? pg_clog and pg_subtrans come to mind; but maybe pg_multixact and
> pg_twophase are candidates as well?

Hmm, I doubt moving any of the SLRU files (clog, subtrans, multixact)
will have much of an impact.  If there's too much I/O on those, a better
solution would be to increase the number of buffers allocated to them.
Currently we use 8 for all of them which is probably not appropiate for
everyone.

Not sure about pg_twophase, but I doubt it's used on a too much
performance critical path (after all, there an awful lot of other work
to do to "prepare" a transaction.)

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [HACKERS] tablespaces and non-empty directories

[Gavin Sherry]

On Tue, 22 Nov 2005, Jim C. Nasby wrote:

> On Tue, Nov 22, 2005 at 01:38:34PM -0500, Bruce Momjian wrote:
> > Gavin Sherry wrote:
> > > > Related question: are there plans afoot to allow specifying an alternate
> > > > location for pg_xlog (or pg_delete-me-not) to save doing the 
> > > > shutdown-DB, mv
> > > > directory to other disk, symlink, start-DB dance?
> > >
> > > People have discussed it but I don't know of anyone working on it.
> >
> > TODO has:
> >
> > * Allow the pg_xlog directory location to be specified during initdb
> >   with a symlink back to the /data location
> >
> > I think the only reason it is not done yet is because it is so easy to
> > do for admins, and it is impossible to do while the server is running.
>
> Along those lines, is there anything else that would benefit from being
> moved? pg_clog and pg_subtrans come to mind; but maybe pg_multixact and
> pg_twophase are candidates as well?

pgsql_tmp

Gavin

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] tablespaces and non-empty directories

[Jim C. Nasby]

On Tue, Nov 22, 2005 at 01:38:34PM -0500, Bruce Momjian wrote:
> Gavin Sherry wrote:
> > > Related question: are there plans afoot to allow specifying an alternate
> > > location for pg_xlog (or pg_delete-me-not) to save doing the shutdown-DB, 
> > > mv
> > > directory to other disk, symlink, start-DB dance?
> > 
> > People have discussed it but I don't know of anyone working on it.
> 
> TODO has:
> 
>   * Allow the pg_xlog directory location to be specified during initdb
> with a symlink back to the /data location
> 
> I think the only reason it is not done yet is because it is so easy to
> do for admins, and it is impossible to do while the server is running.

Along those lines, is there anything else that would benefit from being
moved? pg_clog and pg_subtrans come to mind; but maybe pg_multixact and
pg_twophase are candidates as well?
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] tablespaces and non-empty directories

[Bruce Momjian]

Gavin Sherry wrote:
> > Related question: are there plans afoot to allow specifying an alternate
> > location for pg_xlog (or pg_delete-me-not) to save doing the shutdown-DB, mv
> > directory to other disk, symlink, start-DB dance?
> 
> People have discussed it but I don't know of anyone working on it.

TODO has:

* Allow the pg_xlog directory location to be specified during initdb
  with a symlink back to the /data location

I think the only reason it is not done yet is because it is so easy to
do for admins, and it is impossible to do while the server is running.

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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] tablespaces and non-empty directories

[Tom Lane]

"Zeugswetter Andreas DCP SD" <[EMAIL PROTECTED]> writes:
>> No.  There is no reason to use a volume's root directory as a
>> tablespace;
>> especially so since the root directory ought to be owned by root

> That is not so on AIX. Only the moint point (the dir in the parent) is
> root.
> Once mounted it can have (and preserves) any permission you want.

Yeah, you *can* make it not-root-owned on most Unixen.  That doesn't
mean it's a good idea to do so.  For instance, if the root directory
is owned by Joe Luser, what's to stop him from blowing away lost+found
and thereby screwing up future fscks?  You should basically never have
more-privileged objects (such as lost+found) inside directories owned by
less-privileged users --- it's just asking for trouble.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] tablespaces and non-empty directories

[Zeugswetter Andreas DCP SD]

> > This is because lost+found exists. Since lost+found would be a 
> > reasonably common directory to find at a mount-point on Unix-like 
> > OSs*, would it make sense for CREATE TABLESPACE to ignore it if
present?

> No.  There is no reason to use a volume's root directory as a
tablespace;
> especially so since the root directory ought to be owned by root

That is not so on AIX. Only the moint point (the dir in the parent) is
root.
Once mounted it can have (and preserves) any permission you want.
But on AIX the workaround is to remove the directory after mounting and
before
creating the tablespace.

Andreas

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] tablespaces and non-empty directories

[Tom Lane]

Philip Yarra <[EMAIL PROTECTED]> writes:
> This is because lost+found exists. Since lost+found would be a reasonably 
> common directory to find at a mount-point on Unix-like OSs*, would it make 
> sense for CREATE TABLESPACE to ignore it if present?

No.  There is no reason to use a volume's root directory as a
tablespace; especially so since the root directory ought to be owned
by root and so you'd have a permissions problem anyhow.  Make a
subdirectory.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] tablespaces and non-empty directories

[Gavin Sherry]

On Thu, 17 Nov 2005, Philip Yarra wrote:

> I assume CREATE TABLESPACE refuses to use a non-empty directory because of the
> risk of trashing existing files. Makes sense, but consider the following:

Right, that was the reasoning.

>
> # mkfs -t ext2 /dev/sdc1
> # mount -t ext2 /dev/sdc1 /mnt/pg_tables
> # chown postgres /mnt/pg_tables
> # su -c psql pyarra
> pyarra=# CREATE TABLESPACE spc_tables LOCATION '/mnt/pg_tables/';
> ERROR:  directory "/mnt/pg_tables" is not empty
>
> This is because lost+found exists. Since lost+found would be a reasonably
> common directory to find at a mount-point on Unix-like OSs*, would it make
> sense for CREATE TABLESPACE to ignore it if present?

This came up when tablespaces were being developed.

>
> Of course this isn't hard to get around:
> # mkdir /mnt/pg_tables/data
> # chown postgres /mnt/pg_tables/data
> CREATE TABLESPACE spc_tables LOCATION '/mnt/pg_tables/data/';

Right. We decided that this was easy for admins to do and also makes
things a little clearer: if /mnt/pg_tables was the data directory, you'd
have something like:

lost+found123413212223132[etc]

It might not be immediately obvious what the numeric named directories are
for.

>
> If consensus is that it is a bad idea to treat lost+found as a special case,
> would it be worth putting an explicit mention in the doco about the preferred
> way to set up a database with multiple disks?

Sounds like a good idea.

>
> Related question: are there plans afoot to allow specifying an alternate
> location for pg_xlog (or pg_delete-me-not) to save doing the shutdown-DB, mv
> directory to other disk, symlink, start-DB dance?

People have discussed it but I don't know of anyone working on it.

Gavin

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

[HACKERS] tablespaces and non-empty directories

[Philip Yarra]

I assume CREATE TABLESPACE refuses to use a non-empty directory because of the 
risk of trashing existing files. Makes sense, but consider the following:

# mkfs -t ext2 /dev/sdc1
# mount -t ext2 /dev/sdc1 /mnt/pg_tables
# chown postgres /mnt/pg_tables
# su -c psql pyarra
pyarra=# CREATE TABLESPACE spc_tables LOCATION '/mnt/pg_tables/';
ERROR:  directory "/mnt/pg_tables" is not empty

This is because lost+found exists. Since lost+found would be a reasonably 
common directory to find at a mount-point on Unix-like OSs*, would it make 
sense for CREATE TABLESPACE to ignore it if present?

Of course this isn't hard to get around:
# mkdir /mnt/pg_tables/data
# chown postgres /mnt/pg_tables/data
CREATE TABLESPACE spc_tables LOCATION '/mnt/pg_tables/data/';

If consensus is that it is a bad idea to treat lost+found as a special case, 
would it be worth putting an explicit mention in the doco about the preferred 
way to set up a database with multiple disks?

Related question: are there plans afoot to allow specifying an alternate 
location for pg_xlog (or pg_delete-me-not) to save doing the shutdown-DB, mv 
directory to other disk, symlink, start-DB dance?

Regards, Philip.

* Solaris 9 and Linux both use lost+found, Tru64 v4.0f does not seem to (and 
has extra guff for quota management too). I doubt we could cater to every 
possible Unix OS and the administrative files it creates at mount points, 
however since lost+found is so common, if it's there, we could ignore it.

-- 

"Debugging is twice as hard as writing the code in the first place.
Therefore, if you write the code as cleverly as possible, you are,
by definition, not smart enough to debug it." - Brian W. Kernighan

-
Utiba Pty Ltd 
This message has been scanned for viruses and
dangerous content by Utiba mail server and is 
believed to be clean.


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] Tablespaces

[Simon Riggs]

On Fri, 2005-06-03 at 11:17 +0800, Christopher Kings-Lynne wrote:
> Maybe the next postgresql.org survey could be on tablespace usage?

Could we plan a more comprehensive survey, with more than one question?

Judging by the number of people who fill out surveys, we would still get
thousands of replies if we asked them 10 questions instead of 1. That
would allow us to cross-correlate the answers to gain an even better
picture of what is happening and what is wanted.

Best Regards, Simon Riggs


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Tablespaces

[Simon Riggs]

On Fri, 2005-06-03 at 08:41 +0200, Hans-Jürgen Schönig wrote:
> Christopher Kings-Lynne wrote:
> > I'm interested if anyone is using tablespaces?  Do we have any actual 
> > reports of people actually using them, to advantage, in the field??
> > 
> > Maybe the next postgresql.org survey could be on tablespace usage?
> > 
> 
> I have seen that tablespaces are widely used and highly appreciated.
> I have not seen people complaining about the current implementation.
> 

My recent experience is that it is mostly the new Windows users who are
using 8.0. Yes, there are people using Tablespaces on those. The only
complaint is why can't you move pg_xlog easily also?

The migration to 8.0 for a many users appears very slow, with many
PostgreSQL users still planning to enter production on 7.3 and 7.4. This
has much to do with supported versions of integrated products, rather
than any lack of interest in 8.0. 

Best Regards, Simon Riggs


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] Tablespaces

[Hans-Jürgen Schönig]

Christopher Kings-Lynne wrote:
I'm interested if anyone is using tablespaces?  Do we have any actual 
reports of people actually using them, to advantage, in the field??


Maybe the next postgresql.org survey could be on tablespace usage?

Chris




I have seen that tablespaces are widely used and highly appreciated.
I have not seen people complaining about the current implementation.

best regards,

hans


--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/664/393 39 74
www.cybertec.at, www.postgresql.at


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

[HACKERS] Tablespaces

[Christopher Kings-Lynne]

I'm interested if anyone is using tablespaces?  Do we have any actual 
reports of people actually using them, to advantage, in the field??


Maybe the next postgresql.org survey could be on tablespace usage?

Chris




---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [HACKERS] tablespaces for temporary files

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Greg Stark wrote:
> >> Actually the sort algorithm postgres uses would be much more efficient if it
> >> could get access to two or three locations guaranteed to be on different
> >> spindles.
> 
> > Agreed, and I was going to mention the idea of a round-robin allocation
> > setup where the system cycles through a list of possible locations for
> > both sort files and temporary tables.
> 
> Greg's point was that sort would want to *know* that it created three
> temp files on three different devices.  Throwing random effects of other
> sessions into the mix wouldn't make it better.

OK, let's say the sort starts on a random tablespace and then goes
sequentially through the list:

* Add a GUC variable to control the tablespace for temporary objects and
  sort files

  It could start with a random tablespace from a supplied list and cycle
  through the list.

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

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

Re: [HACKERS] tablespaces for temporary files

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Greg Stark wrote:
>> Actually the sort algorithm postgres uses would be much more efficient if it
>> could get access to two or three locations guaranteed to be on different
>> spindles.

> Agreed, and I was going to mention the idea of a round-robin allocation
> setup where the system cycles through a list of possible locations for
> both sort files and temporary tables.

Greg's point was that sort would want to *know* that it created three
temp files on three different devices.  Throwing random effects of other
sessions into the mix wouldn't make it better.

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [HACKERS] tablespaces for temporary files

[Bruce Momjian]

Greg Stark wrote:
> 
> Tom Lane <[EMAIL PROTECTED]> writes:
> 
> > On the whole I'm unconvinced that this is worth the trouble.  One of the
> > reasons for allowing people to move databases around is to determine
> > where their temp files go.  
> 
> The one scenario I would expect to see is having the temp files on filesystem
> all to themselves separate from the database. So using the database's location
> seems like it would never really satisfy that need.
> 
> Actually the sort algorithm postgres uses would be much more efficient if it
> could get access to two or three locations guaranteed to be on different
> spindles. Last I read the comments it talked about a three tape polyphase sort
> emulated on a single tape. It's a _lot_ less efficient emulated on a single
> tape than it would be on three separate tapes. And for large sorts drive
> really do behave like tapes.
> 
> Personally I am inclined to think that sorting and hash table spills really
> belong in a location specified completely separate from tablespaces.
> 
> Others may be thinking of this more in terms of enforcing resource quotas in
> which case the current regime makes more sense. But from a performance point
> of view the current system is pointless.

Agreed, and I was going to mention the idea of a round-robin allocation
setup where the system cycles through a list of possible locations for
both sort files and temporary tables.  One trick is that the cycle
pointer has to be global controlled so once one session uses an area the
next session uses the next location.

Updated TODO item:

* Add a GUC variable to control the tablespace for temporary objects and
  sort files

  This perhaps should use a round-robin allocation system where several
  tablespaces are used in a cycle.  The cycle pointer should be global.


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

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] tablespaces for temporary files

[Greg Stark]

Tom Lane <[EMAIL PROTECTED]> writes:

> On the whole I'm unconvinced that this is worth the trouble.  One of the
> reasons for allowing people to move databases around is to determine
> where their temp files go.  

The one scenario I would expect to see is having the temp files on filesystem
all to themselves separate from the database. So using the database's location
seems like it would never really satisfy that need.

Actually the sort algorithm postgres uses would be much more efficient if it
could get access to two or three locations guaranteed to be on different
spindles. Last I read the comments it talked about a three tape polyphase sort
emulated on a single tape. It's a _lot_ less efficient emulated on a single
tape than it would be on three separate tapes. And for large sorts drive
really do behave like tapes.

Personally I am inclined to think that sorting and hash table spills really
belong in a location specified completely separate from tablespaces.

Others may be thinking of this more in terms of enforcing resource quotas in
which case the current regime makes more sense. But from a performance point
of view the current system is pointless.

-- 
greg


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] tablespaces for temporary files

[Neil Conway]

On Sat, 2004-10-30 at 00:50, Tom Lane wrote:
> (1) What are the protection requirements for this variable?

I think it can be USERSET -- most commands let the user specify a
tablespace explicitly, and this is basically just another way of doing
that. The user executing the query will need CREATE privileges on the
tablespace they end up writing to.

> (2) I don't think that "undefined" is a particularly good concept for
> GUC variables.  Particularly not ones that you are envisioning setting
> from multiple places.

Hmm, ok. How about a token like "$database" that expands to the
tablespace of the current database?

> (3) I don't like the idea that a catalog lookup will be necessary before
> we can create or access temp files.  It would be quite unacceptable from
> a modularity standpoint to have the low-level routines that currently
> determine temp file paths do catalog accesses.

I don't agree it is "unacceptable", but it isn't ideal, granted.

> On the whole I'm unconvinced that this is worth the trouble.  One of the
> reasons for allowing people to move databases around is to determine
> where their temp files go.

I think this needlessly limits the flexibility of the system. Once
you've created a database and added a bunch of tables to it (in the DB's
tablespace), is there an easy way to change the tablespace used for
temporary files? What if the DBA has placed the database in a relatively
slow tablespace because that is suitable most of the time, but needs to
quickly execute a large OLAP query that consumes a lot of temporary
space? What if it makes sense at a particular installation for different
users to use different tablespaces for their temporary files?

I just think that always using the database's tablespace for temporary
files needlessly conflates two distinct concepts.

> Also, it's always been possible for people
> to change the pgsql_tmp subdirectory into a symlink.

This is a pain for the DBA, as you mention; it requires shutting down
the database; and it is fragile to begin with because the "pgsql_tmp"
directory is created on demand.

-Neil



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] tablespaces for temporary files

[Tom Lane]

Neil Conway <[EMAIL PROTECTED]> writes:
> So I'd like to add a GUC variable called something like
> "scratch_tablespace". If undefined (the default), temporary files for
> sorting/etc. will be created in the current database's tablespace.

(1) What are the protection requirements for this variable?

(2) I don't think that "undefined" is a particularly good concept for
GUC variables.  Particularly not ones that you are envisioning setting
from multiple places.

(3) I don't like the idea that a catalog lookup will be necessary before
we can create or access temp files.  It would be quite unacceptable from
a modularity standpoint to have the low-level routines that currently
determine temp file paths do catalog accesses.

On the whole I'm unconvinced that this is worth the trouble.  One of the
reasons for allowing people to move databases around is to determine
where their temp files go.  Also, it's always been possible for people
to change the pgsql_tmp subdirectory into a symlink.  While I know that
that isn't particularly DBA-friendly, it seems sufficient to me for what
I suspect is a third-order requirement.

Let's at least wait till we get some demand from the field before we
start inventing frammishes for tablespaces.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] tablespaces for temporary files

[Christopher Kings-Lynne]

So I'd like to add a GUC variable called something like
"scratch_tablespace". If undefined (the default), temporary files for
Should be called 'work_tablesapce' to match 'work_mem' :)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[HACKERS] tablespaces for temporary files

[Neil Conway]

I'd like to provide a way for DBAs to specify that the temporary files
needed to for sorting, holdable cursors and similar operations should be
created in a particular tablespace. (Right now these files are created
in the tablespace associated with the current database.)

Two ways to do this come to mind: via a GUC variable, or by setting a
property of CREATE DATABASE (that could be altered via ALTER DATABASE).
I think using a GUC variable is probably the better bet: it is more
flexible, since ALTER DATABASE ... SET and ALTER USER ... SET can be
used to define the GUC variable automatically for particular users and
databases.

So I'd like to add a GUC variable called something like
"scratch_tablespace". If undefined (the default), temporary files for
sorting/etc. will be created in the current database's tablespace. If
set to the name of an existent tablespace, that tablespace will be used
for temporary storage. If set to a nonexistent tablespace, a warning
will be printed and we'll fallback to using the current database's
tablespace.

Regarding naming, I considered calling the GUC variable
"temporary_tablespace" or something similar, but it seems to me that
this might cause confusion with temporary tables -- furthermore, it
might be an interesting feature to define a "temporary table tablespace"
in the future, leading to yet more confusion. I think "scratch
tablespace" is a pretty decent name for this concept, but I'm open to
suggestions.

Comments?

-Neil



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[pgsql]

>
>> > -Original Message-
>> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
>> >
>> > > I surely hope not. Especially not multi-gig databases. The folks
>> running
>> > > those should know better than to use Windows, and if they do not,
>> I'll
>> > > be happy to tell them so.
>
> You know, it makes you wonder. Tom must not have enough work to do if he's
> so
> bored that he wants to spice up the postgres mailing lists this way :)
>
> --
> greg

It is the creative mind. We all suffer from "Engineer's Tourettes
Syndrome" (The uncontrollable need to express contrarian and margenally
related opinions.) and at some point.

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[Greg Stark]

> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> >
> > > I surely hope not. Especially not multi-gig databases. The folks running
> > > those should know better than to use Windows, and if they do not, I'll
> > > be happy to tell them so.

You know, it makes you wonder. Tom must not have enough work to do if he's so
bored that he wants to spice up the postgres mailing lists this way :)

-- 
greg


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[pgsql]

>>
>> Having been a Windows developer since version 1.03, with DOS
>> and CP/M before that, I can say with complete authority that
>> most Windows developers are not "good." The worst I've seen
>> is Charles Petzold, and he sets the bar.
>
> Charles Petzold is a decent programmer.  I have read his books and he
> knows what he's talking about.  He no W. Richard Stevens or Donald
> Knuth, but I would hire him to do a job.
>

Funny story. In Windows 2.x days, a bug was found in Petzolds calculator
example having to do with the stupid way Win16 dealt with various aspects
of Window properties such as hMenu. When I read the book, I had been
programming in Windows 1.x and early 2.x, and thought to myself, "that's
not right."

Well, it turns out that it was a bug that broke a lot of Windows program
when Win 3.0 came out in standard mode.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[Andrew Dunstan]

[EMAIL PROTECTED] wrote:
Actually, I am not a wide eyed passionate Linux zealot. Like my support
for John Kerry, I gladly choose the better side of mediocrity over extream
evil, it is nothing more than pure practicality.
 


I don't like dubya either, but he isn't extreme evil. This sort of 
argument is over the top, and the analogy is out of place. You ought to 
know by now that there is almost no correlation between technological 
views and political views (e.g. many FOSS advocates have politics that 
are anaethema to me). So let's leave the politics out of it. In fact, 
let's get on with doing actual work.

cheers
andrew
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[Scott Marlowe]

On Fri, 2004-06-11 at 11:29, Dann Corbit wrote:
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> > Sent: Friday, June 11, 2004 9:39 AM
> > To: Tom Lane
> > Cc: Dann Corbit; Zeugswetter Andreas SB SD; 
> > [EMAIL PROTECTED]; [EMAIL PROTECTED]; Bruce Momjian; Greg 
> > Stark; [EMAIL PROTECTED]; PostgreSQL Win32 port list
> > Subject: Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
> > 
> > 
> > > "Dann Corbit" <[EMAIL PROTECTED]> writes:
> > >> I expect that one year after release, there will be ten 
> > times as many 
> > >> PostgreSQL systems on Win32 as all combined versions now on UNIX 
> > >> flavors
> > >
> > > I surely hope not.  Especially not multi-gig databases.  The folks 
> > > running those should know better than to use Windows, and 
> > if they do 
> > > not, I'll be happy to tell them so.
> 
> I know better than to tell people to change their operating system.
> Linux is a great OS, and people familiar with it will do exceedingly
> well.  But there are 40 million computers sold in a year, most of which
> have some flavor of Windows installed.  

I think the more important part of Tom's point isn't that Windows in
general sucks (even though it does) but that PostgreSQL ON Windows is a
brand new thing, and if you're willing to put a multi-gig ERP system on
it and bet the company, you shouldn't be in a data center, because right
now it simply hasn't been tested enough.

Now, setting up a unix box with postgresql for production and becoming a
part of the windows testing effort in your spare time, until Windows
proves itself ready and worthy, that makes sense.  

I'm no fan of microsoft or Bill Gates, for the reasons mentioned in
books like "The Microsoft Files".  But my main objection to putting a
PostgreSQL on Windows server online right now would be the same one I
would have against putting a MS SQL server on Windows online right now,
neither one has ever been proven reliable.  :-)


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

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[Bruce Momjian]

[EMAIL PROTECTED] wrote:
> 
> >>
> >> We should provide people with the right tools, true, but we
> >> are bound by our conscience to inform them about Windows' failures.
> >
> > It must be nice to be young and still see everything as black and white
> > with no shades of gray.
> 
> I wouldn't call 41 very young.
> 
> > For those who think that Windows should be
> > canned, Gates should be burned at the stake, and Linux should rule the
> > world, I have no problem with their opinions.  We all get to choose what
> > we like and dislike.  I think that the typical Linux fan is WAY over the
> > top both in seeing the advantages with rose colored glasses and turing
> > opposition molehills into mountains.  But passion is good, and I like to
> > see it.  If it were not for the passion of the Linux crowd, there would
> > be a far less interesting competitor for MS and a far less interesting
> > toolset to use with it.
> 
> Actually, I am not a wide eyed passionate Linux zealot. Like my support
> for John Kerry, I gladly choose the better side of mediocrity over extream
> evil, it is nothing more than pure practicality.

Well, call me extreme evil too.  Then I guess PostgreSQL is partly pure
evil, or partly extreme evil, or something like that.

Of course, if you meet me, I don't appear so.  We are taught to hide our
evil so effectively.

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

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[Dann Corbit]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Friday, June 11, 2004 2:41 PM
> To: Dann Corbit
> Cc: [EMAIL PROTECTED]; PostgreSQL Win32 port list
> Subject: RE: [pgsql-hackers-win32] [HACKERS] Tablespaces
[snip]
> Microsoft has harmed the computing industry more than any 
> single factor that I can remember. I've seen a lot of it, 
> from DEC to Wang, and microsoft has single handedly wiped out 
> more computing innovation in 20 years than any 10 other companies.
> 
> Stac, Go Computing, Netscape, BeOS, and the list grows for as 
> long as you think about it.

Netscape and BeOS are still around.  I think Linux is a bigger blow to
BeOS than MS.
Stak was unbelievable and MS got a tap on the wrist compared to the harm
caused.
I will have to look up Go Computing to see what all that is about.
 
> It isn't wide eyes passion, I've programed computers since jr 
> high school, on a PDP-8/e. It is what I love to do, and it is 
> what I make my living doing.
> 
> Some things are important in life. Spending a few extra 
> dollars *NOT* going to Walmart is one small thing you can do 
> to improve the world. Taking advantage of every LEGITIMATE 
> opportunity to move a person or project off Windows is one 
> small step one can do to improve our industry.

If they are moved to another platform for their benefit or for the right
reasons there is nothing wrong with it.  If it is because of your own
ideology and not for the benefit of the client then it is harm to them
and immoral.  IMO-YMMV

> > Maybe the thread should go to some advocacy channel at this point.
> 
> Yes.
> 
> >
> > My reason for jumping in was to show that:
> > 1.  PostgreSQL will have a exponential leap in possible 
> sites when it 
> > opens up to Win32 systems 2.  There will be huge installations on 
> > Win32 systems, like it or not.
> >
> > Some other things to keep in mind:
> > 1.  The average Windows user is far, far less computer saavy than a 
> > Linux (or other flavor of UNIX user) and hence, there will be a big 
> > load of "deer in the headlights" users coming on board.
> 
> Total koolaid induced delusion. Dumb users are dumb users, 
> "deer in the headlights" looks come from flashing VCR clocks. 
> Competent professionals can handle a few twists. The switch 
> from DOS Windows (3x,9x,ME) to XP was just as traumatic.

You are totally wrong about that.  'Dumb users' are people who don't
care to become computer saavy.  Often because they don't need to.
Someone who can't program their VCR may be able to do brain surgery on
you.  Like Will Rogers said, "Everyone is ignorant, only in different
areas."  If people don't want to become computer experts, we should not
try to force them to become so.  You and I enjoy computers but other
people just want what the computer can deliver and don't care to learn
how it got there.
 
> > 2.  On the plus side, there are millions of good developers 
> familiar 
> > with Windows.  Some of these may become involved with the 
> PostgreSQL 
> > project and give added value.
> 
> Having been a Windows developer since version 1.03, with DOS 
> and CP/M before that, I can say with complete authority that 
> most Windows developers are not "good." The worst I've seen 
> is Charles Petzold, and he sets the bar.

Charles Petzold is a decent programmer.  I have read his books and he
knows what he's talking about.  He no W. Richard Stevens or Donald
Knuth, but I would hire him to do a job.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[pgsql]

>>
>> We should provide people with the right tools, true, but we
>> are bound by our conscience to inform them about Windows' failures.
>
> It must be nice to be young and still see everything as black and white
> with no shades of gray.

I wouldn't call 41 very young.

> For those who think that Windows should be
> canned, Gates should be burned at the stake, and Linux should rule the
> world, I have no problem with their opinions.  We all get to choose what
> we like and dislike.  I think that the typical Linux fan is WAY over the
> top both in seeing the advantages with rose colored glasses and turing
> opposition molehills into mountains.  But passion is good, and I like to
> see it.  If it were not for the passion of the Linux crowd, there would
> be a far less interesting competitor for MS and a far less interesting
> toolset to use with it.

Actually, I am not a wide eyed passionate Linux zealot. Like my support
for John Kerry, I gladly choose the better side of mediocrity over extream
evil, it is nothing more than pure practicality.

A diversity of platforms in the market place creates jobs, increased
security (any particular exploit does not wipe out a vast majority of
targets.), and feeds innovation and competition.

Microsoft has harmed the computing industry more than any single factor
that I can remember. I've seen a lot of it, from DEC to Wang, and
microsoft has single handedly wiped out more computing innovation in 20
years than any 10 other companies.

Stac, Go Computing, Netscape, BeOS, and the list grows for as long as you
think about it.

It isn't wide eyes passion, I've programed computers since jr high school,
on a PDP-8/e. It is what I love to do, and it is what I make my living
doing.

Some things are important in life. Spending a few extra dollars *NOT*
going to Walmart is one small thing you can do to improve the world.
Taking advantage of every LEGITIMATE opportunity to move a person or
project off Windows is one small step one can do to improve our industry.

>
> Maybe the thread should go to some advocacy channel at this point.

Yes.

>
> My reason for jumping in was to show that:
> 1.  PostgreSQL will have a exponential leap in possible sites when it
> opens up to Win32 systems
> 2.  There will be huge installations on Win32 systems, like it or not.
>
> Some other things to keep in mind:
> 1.  The average Windows user is far, far less computer saavy than a
> Linux (or other flavor of UNIX user) and hence, there will be a big load
> of "deer in the headlights" users coming on board.

Total koolaid induced delusion. Dumb users are dumb users, "deer in the
headlights" looks come from flashing VCR clocks. Competent professionals
can handle a few twists. The switch from DOS Windows (3x,9x,ME) to XP was
just as traumatic.


> 2.  On the plus side, there are millions of good developers familiar
> with Windows.  Some of these may become involved with the PostgreSQL
> project and give added value.

Having been a Windows developer since version 1.03, with DOS and CP/M
before that, I can say with complete authority that most Windows
developers are not "good." The worst I've seen is Charles Petzold, and he
sets the bar.

>


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[Dann Corbit]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Friday, June 11, 2004 1:37 PM
> To: Dann Corbit
> Cc: Tom Lane; Zeugswetter Andreas SB SD; 
> [EMAIL PROTECTED]; [EMAIL PROTECTED]; Bruce Momjian; Greg 
> Stark; [EMAIL PROTECTED]; PostgreSQL Win32 port list
> Subject: RE: [pgsql-hackers-win32] [HACKERS] Tablespaces
> 
> 
> >> -Original Message-
> >> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> >> Sent: Friday, June 11, 2004 9:39 AM
> >> To: Tom Lane
> >> Cc: Dann Corbit; Zeugswetter Andreas SB SD; [EMAIL PROTECTED]; 
> >> [EMAIL PROTECTED]; Bruce Momjian; Greg Stark; 
> >> [EMAIL PROTECTED]; PostgreSQL Win32 port list
> >> Subject: Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
> >>
> >>
> >> > "Dann Corbit" <[EMAIL PROTECTED]> writes:
> >> >> I expect that one year after release, there will be ten
> >> times as many
> >> >> PostgreSQL systems on Win32 as all combined versions 
> now on UNIX 
> >> >> flavors
> >> >
> >> > I surely hope not.  Especially not multi-gig databases.  
> The folks 
> >> > running those should know better than to use Windows, and
> >> if they do
> >> > not, I'll be happy to tell them so.
> >
> > I know better than to tell people to change their operating system. 
> > Linux is a great OS, and people familiar with it will do 
> exceedingly 
> > well.  But there are 40 million computers sold in a year, most of 
> > which have some flavor of Windows installed.
> 
> How many billions of cigarettes are sold? How many Big Macs? 
> Popularity does  not imply quality or safety.

Right.  It implies volume.  That was the only point I was making.  If
everyone is wearing suede shoes, you will have trouble selling shoe
polish.
 
> > People know how to use and
> > administer them, and they have all their applications in Windows.  
> > They are not going to change for ideological reasons.
> 
> This is interesting, since when is ideology *not* the 
> american way? Have you looked at politics lately?

I am also politically neutral and have not voted since I was 18 as a
matter of conscience.

> > Also, it isn't just
> > DBAs that need to implement database systems.  Suppose, for 
> instance, 
> > that I want to write an accounting package.  I can use 
> PostgreSQL as a 
> > base and save my customers thousands of dollars.  If I tell them, 
> > "Now, you need to reformat your machine and install Linux" 
> that would 
> > not be very popular.  But they don't even need to know about the 
> > database.  And they should not have to care about the OS.  
> A database 
> > and an operating system are both things to help get work done.  
> > Believe it or not, lots of large companies depend on Windows OS.
> 
> I've been in the trenches for a while now, and I haven't met 
> a single CIO that is comfortable with Windows. They hate the 
> cost, they hate the viruses, they hate the instability. The 
> only thing they hate more is being isolated on an island. 
> Fortunately Linux is becoming less obscure.
> 
> >
> > Personally, I am technology neutral.  My position is "use 
> whatever you 
> > like."
> 
> I would call myself "neutral" to a point, but when I have to 
> give advice, I have to tell the truth. A little Linux goes a long way.

For me, if I was going to start a company, Linux is a technically
superior solution for a server in my view.  This is especially true due
to license reasons.  If I want a thousand users on a machine, the cost
for a Windows solution dwarfs any reasons I can think of not to switch
to Linux.  However, if a company does not have personnel trained to
administrate Linux machines and applications, then something else might
be a better choice for them.  ("Fire all your workers and hire new
ones." does not work)

> >> This is a prejudice that we should try to avoid. Yes, Windows is 
> >> lacking on so many levels, but that really isn't the point.
> >
> > Every OS has advantages and disadvantages.
> 
> Some more than other.

And yet each choice can have different weights depending upon who is
using it, for what reasons, and other business factors.
 
> > The applications for Windows
> > are many and mature.  The tool sets available for Linux are 
> extensive 
> > and usually free.  If you want real 24x7x365.25 then MVS cannot be 
> > beat. The file versioning and protections of OpenVMS are something 
> > that all operating systems should have modeled.
&

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[pgsql]

>> -Original Message-
>> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
>> Sent: Friday, June 11, 2004 9:39 AM
>> To: Tom Lane
>> Cc: Dann Corbit; Zeugswetter Andreas SB SD;
>> [EMAIL PROTECTED]; [EMAIL PROTECTED]; Bruce Momjian; Greg
>> Stark; [EMAIL PROTECTED]; PostgreSQL Win32 port list
>> Subject: Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
>>
>>
>> > "Dann Corbit" <[EMAIL PROTECTED]> writes:
>> >> I expect that one year after release, there will be ten
>> times as many
>> >> PostgreSQL systems on Win32 as all combined versions now on UNIX
>> >> flavors
>> >
>> > I surely hope not.  Especially not multi-gig databases.  The folks
>> > running those should know better than to use Windows, and
>> if they do
>> > not, I'll be happy to tell them so.
>
> I know better than to tell people to change their operating system.
> Linux is a great OS, and people familiar with it will do exceedingly
> well.  But there are 40 million computers sold in a year, most of which
> have some flavor of Windows installed.

How many billions of cigarettes are sold? How many Big Macs? Popularity
does  not imply quality or safety.

> People know how to use and
> administer them, and they have all their applications in Windows.  They
> are not going to change for ideological reasons.

This is interesting, since when is ideology *not* the american way? Have
you looked at politics lately?

> Also, it isn't just
> DBAs that need to implement database systems.  Suppose, for instance,
> that I want to write an accounting package.  I can use PostgreSQL as a
> base and save my customers thousands of dollars.  If I tell them, "Now,
> you need to reformat your machine and install Linux" that would not be
> very popular.  But they don't even need to know about the database.  And
> they should not have to care about the OS.  A database and an operating
> system are both things to help get work done.  Believe it or not, lots
> of large companies depend on Windows OS.

I've been in the trenches for a while now, and I haven't met a single CIO
that is comfortable with Windows. They hate the cost, they hate the
viruses, they hate the instability. The only thing they hate more is being
isolated on an island. Fortunately Linux is becoming less obscure.

>
> Personally, I am technology neutral.  My position is "use whatever you
> like."

I would call myself "neutral" to a point, but when I have to give advice,
I have to tell the truth. A little Linux goes a long way.

>
>> This is a prejudice that we should try to avoid. Yes, Windows
>> is lacking on so many levels, but that really isn't the point.
>
> Every OS has advantages and disadvantages.

Some more than other.

> The applications for Windows
> are many and mature.  The tool sets available for Linux are extensive
> and usually free.  If you want real 24x7x365.25 then MVS cannot be beat.
> The file versioning and protections of OpenVMS are something that all
> operating systems should have modeled.
>
>> A good box running Win2K or XP Server, with no internet
>> connectivity, and no user applications, can really perform
>> and be reliable. Would I choose this? Hell no, but there are
>> HUGE amount of people who either don't know any better or
>> have no real choice.
>
> And there are knowledgeable people who understand Windows, Linux and
> many other operating systems who choose Windows because it is the best
> choice for their company.

I seriously do not know anyone, including myself, that would choose
Windows on technical merrits alone. I know some need to choose it for
"killer" application requirements, but not on merrit.

As for best choice for their company, I can't even say that with a
straight face.

>
>> The REAL bonus here is getting PostgreSQL in their hands.
>> Right now, for the small to medium business running Windows,
>> Microsoft has a virtual lock with SQL Server. SQL Server is
>> expensive and a real PAIN.
>
> It is expensive and a multi-user system ramps the cost.  But it is
> easier to administer than PostgreSQL.  Hopefully, autovacuum will remove
> most of this discrepancy.

Having dealt with both, as well as MySQL, DB2, and Oracle, I not sure I
agree with that statement. As long as MSSQL is installed correctly the
first time, it may be OK.

>
>> Giving Windows users PostgreSQL with a good set of .NET,
>> ODBC, and JDBC drivers loosens the Microsoft stranglehold,
>> just a little bit. If they develop their application with
>> MSSQL, there is a good chance it will never use any open
>> source software

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[Dann Corbit]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Friday, June 11, 2004 9:39 AM
> To: Tom Lane
> Cc: Dann Corbit; Zeugswetter Andreas SB SD; 
> [EMAIL PROTECTED]; [EMAIL PROTECTED]; Bruce Momjian; Greg 
> Stark; [EMAIL PROTECTED]; PostgreSQL Win32 port list
> Subject: Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
> 
> 
> > "Dann Corbit" <[EMAIL PROTECTED]> writes:
> >> I expect that one year after release, there will be ten 
> times as many 
> >> PostgreSQL systems on Win32 as all combined versions now on UNIX 
> >> flavors
> >
> > I surely hope not.  Especially not multi-gig databases.  The folks 
> > running those should know better than to use Windows, and 
> if they do 
> > not, I'll be happy to tell them so.

I know better than to tell people to change their operating system.
Linux is a great OS, and people familiar with it will do exceedingly
well.  But there are 40 million computers sold in a year, most of which
have some flavor of Windows installed.  People know how to use and
administer them, and they have all their applications in Windows.  They
are not going to change for ideological reasons.  Also, it isn't just
DBAs that need to implement database systems.  Suppose, for instance,
that I want to write an accounting package.  I can use PostgreSQL as a
base and save my customers thousands of dollars.  If I tell them, "Now,
you need to reformat your machine and install Linux" that would not be
very popular.  But they don't even need to know about the database.  And
they should not have to care about the OS.  A database and an operating
system are both things to help get work done.  Believe it or not, lots
of large companies depend on Windows OS.

Personally, I am technology neutral.  My position is "use whatever you
like."
 
> This is a prejudice that we should try to avoid. Yes, Windows 
> is lacking on so many levels, but that really isn't the point.

Every OS has advantages and disadvantages.  The applications for Windows
are many and mature.  The tool sets available for Linux are extensive
and usually free.  If you want real 24x7x365.25 then MVS cannot be beat.
The file versioning and protections of OpenVMS are something that all
operating systems should have modeled.
 
> A good box running Win2K or XP Server, with no internet 
> connectivity, and no user applications, can really perform 
> and be reliable. Would I choose this? Hell no, but there are 
> HUGE amount of people who either don't know any better or 
> have no real choice.

And there are knowledgeable people who understand Windows, Linux and
many other operating systems who choose Windows because it is the best
choice for their company.
 
> The REAL bonus here is getting PostgreSQL in their hands. 
> Right now, for the small to medium business running Windows, 
> Microsoft has a virtual lock with SQL Server. SQL Server is 
> expensive and a real PAIN.

It is expensive and a multi-user system ramps the cost.  But it is
easier to administer than PostgreSQL.  Hopefully, autovacuum will remove
most of this discrepancy.
 
> Giving Windows users PostgreSQL with a good set of .NET, 
> ODBC, and JDBC drivers loosens the Microsoft stranglehold, 
> just a little bit. If they develop their application with 
> MSSQL, there is a good chance it will never use any open 
> source software and always run on Windows. If they develop 
> their application using PostgreSQL, there is a better 
> likelyhood that other open source projects will be used, AND 
> that should the requirement be to upgrade the system, a wider 
> range of OS and hardware options will present themselves.

Microsoft dominates because they offer real value (the world is not
completely full of idiot CEOs -- they make decisions based on profit).
The open source community is closing the gap, but it has a long way to
go.  I don't see Microsoft as the dark side of the force or anything.
Actually, the approach of PostgreSQL and ACE is (too me) the most
superior.  The GPL approach is far too confining, and getting a black
box that will be a terrible mystery if it breaks are not nearly so
pleasant.

Instead of telling people how to do their jobs, I suggest the approach
of providing the best possible tools and letting them decide how to use
them.



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[pgsql]

> "Dann Corbit" <[EMAIL PROTECTED]> writes:
>> I expect that one year after release, there will be ten times as many
>> PostgreSQL systems on Win32 as all combined versions now on UNIX flavors
>
> I surely hope not.  Especially not multi-gig databases.  The folks
> running those should know better than to use Windows, and if they
> do not, I'll be happy to tell them so.

This is a prejudice that we should try to avoid. Yes, Windows is lacking
on so many levels, but that really isn't the point.

A good box running Win2K or XP Server, with no internet connectivity, and
no user applications, can really perform and be reliable. Would I choose
this? Hell no, but there are HUGE amount of people who either don't know
any better or have no real choice.

The REAL bonus here is getting PostgreSQL in their hands. Right now, for
the small to medium business running Windows, Microsoft has a virtual lock
with SQL Server. SQL Server is expensive and a real PAIN.

Giving Windows users PostgreSQL with a good set of .NET, ODBC, and JDBC
drivers loosens the Microsoft stranglehold, just a little bit. If they
develop their application with MSSQL, there is a good chance it will never
use any open source software and always run on Windows. If they develop
their application using PostgreSQL, there is a better likelyhood that
other open source projects will be used, AND that should the requirement
be to upgrade the system, a wider range of OS and hardware options will
present themselves.



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] Tablespaces

[pgsql]

> I don't think we want features for their own sake, though, and I'm
> not convinced that raw filesystems are actually useful.  Course, it's
> not my itch, and PostgreSQL _is_ free software.
>

I agree that raw file systems are seldom useful with one caveat, more
advanced file systems are sometimes detrimental to database access.

Conceptually, a file system and a database are redundant, both are doing
their best to preserve data integrity. This is especially true with
journalling file systems. Not to mention technologies like reiserfs which
attempts to do sub-block allocation.

What I think would go a long way to improving database performance on
non-raw partitions would be a simplified file system -- SFS anyone? The
simplified file system would not track access time. It would not overly
try to manage disk space. The target applications are going to allocate
disk space on a block level, rather than quibble about 4K here or 8K here,
have a user defined standard allocation unit of 64K, 128K, or so on.
Reduction on allocation overhead also reduces meta-data updating I/O. I
can almost imagine 32BIT FAT with large clusers, only with real inodes.
The idea would be that a database, like PostgreSQL, would be managing the
data not the file system. The file systems job would only to be the most
minimalist interface to the OS.

The benefts would be awesome, near-raw partition access and standard OS
tools for maintainence.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Tablespaces

[Greg Stark]

"Zeugswetter Andreas SB SD" <[EMAIL PROTECTED]> writes:

> e.g if you have a constraint "acol integer, check acol < 5"
> and you have a query with a "where acol = 10" you could reduce that
> to "where false". 

I think part of the question is how much work do you put into checking this.
Checking constant known values like above is probably not too expensive.
Checking for ranges like "where acol between 5 and 10" is probably doable. And
that might be enough for partitioned tables. I think that's about all Oracle
bothers to check, for example. More complex where clauses and check
expressions might be hard to prove are true or false.

But then the work's still not done, you still have to add an optimization that
prunes members of a UNION ALL (or equivalent if it's done using inherited
tables or some other infrastructure) if they are known to provably produce
zero rows.

And then there are more subtle cases. Like if the query is "where acol = ?".
Then you know it only has to read one partition, but you don't know which one
at compile time. And it's important to handle that case because that might be
the only clause. So knowing that you only need one partition might be the
difference between a sequential scan of one partition, or an index scan of
many thousands of records because they're only a small percentage of the
entire table.

-- 
greg


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] Tablespaces

[Zeugswetter Andreas SB SD]

> > With the rule system and two underlying tables one could make it work by 
> > hand I think.
> 
> The rule system could be used to do this, but there was some discussion of
> using inherited tables to handle it. However neither handles the really hard
> part of detecting queries that use only a part of the table and taking that
> into account in generating the plan.

I think the consensus should be to add smarts to the planner to include 
static constraint information to reduce table access.

e.g if you have a constraint "acol integer, check acol < 5"
and you have a query with a "where acol = 10" you could reduce that
to "where false". This would help in all sorts of situations not only 
partitioned/inherited tables. I am not sure what the runtime cost of 
such an inclusion would be, so maybe it needs smarts to only try in certain 
cases ?

Andreas

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

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[Andreas Pflug]

Tom Lane wrote:
"Dann Corbit" <[EMAIL PROTECTED]> writes:
 

I expect that one year after release, there will be ten times as many
PostgreSQL systems on Win32 as all combined versions now on UNIX flavors
   

I surely hope not.  Especially not multi-gig databases.  The folks
running those should know better than to use Windows, and if they
do not, I'll be happy to tell them so.
 

Admins often don't have a choice, but a company strategy to use win 
only. Deciding on the platform before examining the app's requirements 
is always a bad idea, but that's what happens. Respecting this, 
suggesting "don't use win32 for high performance pgsql databasing" is 
equivalent to "don't use pgsql".

Regards,
Andreas


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[Tom Lane]

"Dann Corbit" <[EMAIL PROTECTED]> writes:
> I expect that one year after release, there will be ten times as many
> PostgreSQL systems on Win32 as all combined versions now on UNIX flavors

I surely hope not.  Especially not multi-gig databases.  The folks
running those should know better than to use Windows, and if they
do not, I'll be happy to tell them so.

regards, tom lane


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[Dann Corbit]

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of 
> Zeugswetter Andreas SB SD
> Sent: Friday, March 05, 2004 1:20 AM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Cc: Bruce Momjian; Tom Lane; Greg Stark; 
> [EMAIL PROTECTED]; PostgreSQL Win32 port list
> Subject: Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
> 
> 
> 
> > 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
> 
> My idea for platforms that don't support symlinks would be to 
> simply create a tblspaceoid directory inplace instead of the 
> symlink (maybe throw a warning). My feeling is, that using 
> the same syntax on such platforms is important, 
> but actual distribution is not (since they will most likely 
> be small systems).

I know of bot SQL*Server and Oracle database systems on Win32 with
hundreds of millions of rows and many hundreds of gigabytes of space.
These are production systems, run by fortune 500 companies.

I expect that PostgreSQL systems on Win32 will have multiple 64-bit CPU
systems, with 16 gigs or so of ram, and a terabyte of disk, not long
after 7.5 is released (unless problems with PostgreSQL on that platform
turn up).

Is that what you have in mind when you say "small systems"?

I expect that one year after release, there will be ten times as many
PostgreSQL systems on Win32 as all combined versions now on UNIX flavors
(of course, that is a SWAG, but I think a sound one)

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

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[Lawrence E. Smithmier, Jr.]

> "Zeugswetter Andreas SB SD" <[EMAIL PROTECTED]> 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.
>

Er, sorry to drop into the middle of this but do you want to cripple a port
before it is even complete?  Is there a compelling reason to use symlinks rather
than a flat file?  If the issue is just:

> Gavin Sherry <[EMAIL PROTECTED]> writes:
>how the low-level file access code finds a tablespace.

then what is wrong with using an XML file that is loaded and traversed at start
up?  I agree it would be a cool to use the file system as a database, but why
place a possible limiting factor for the sake of elegance?  Isn't XML a valid
and accepted way to store hierarchial data?

> Gavin Sherry <[EMAIL PROTECTED]> writes:
> I am expecting to hear some bleating about this from people whose
> preferred platforms don't support symlinks ;-).  However, if we don't

Well bleat I guess.  Although I wouldn't exactly say preferred.  I prefer to
think of myself as a realest getting paid to program on a platform.  A platform
with symlinks carrying quite a bit of baggage.  On NTFS they are called Junction
Points and are a special type of Reparse Point.  One thing I noticed on the
Microsoft site regarding these:

(http://www.microsoft.com/whdc/DDK/IFSkit/reparse.mspx)
>Reparse Points are a powerful feature of Windows 2000 (not available on Windows
>NT® 4.0), but developers should be aware that there can only be one reparse 
>point per file, and some new Windows 2000 mechanisms use reparse points (HSM, 
>Native Structured Storage). Developers need to have fallback strategies for 
>when the reparse point tag is already in use for a file.

makes me question their usefulness at this point.  I am currently exploring
another solution to the problem that caused me to investigate them.

Well, thanks for your time.  I guess I can go baaack to lurking now. ;-)


Lawrence E. Smithmier, Jr.
MCP, MCAD
(919) 522-9738
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Tablespaces

[Tom Lane]

Thomas Swan <[EMAIL PROTECTED]> writes:
> Bruce Momjian wrote:
>> The advantage of symlinks is that an administrator could see how things
>> are laid out from the command line.
>> 
> That's a poor reason to require symlinks.  The administrator can just as
> easily open up psql and query pg_tablespace to see that same
> information.

Something to keep in mind here is that one of the times you would most
likely need that information is when the database is broken and you
*can't* simply "open up psql" and inspect system catalogs.  I like the
fact that a symlink implementation can be inspected without depending on
a working database.

If we were going to build a non-symlink implementation, I'd want the
highlevel-to-lowlevel data transfer to take the form of a flat ASCII
file that could be inspected by hand, rather than some hidden in-memory
datastructure.  But given the previous discussion in this thread,
I cannot see any strong reason not to rely on symlinks for the purpose.
We are not in the business of building replacements for OS features.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [pgsql-hackers-win32] [HACKERS] Tablespaces

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> My feeling is that we need not support tablespaces on OS's without
>> symlinks.

> Agreed, but are we going to support non-tablespace installs?  I wasn't
> sure that was an option.

A setup containing only the default tablespace cannot use any symlinks.
That doesn't seem hard though.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Tablespaces

[Tom Lane]

Gavin Sherry <[EMAIL PROTECTED]> writes:
> I was trying to be paranoid about users who have multiple postmasters on
> the same machine and want to share a table space while both systems are
> live. There'd be no mechanism to test for that situation if we didn't have
> something like a postmaster.pid file. Is this being a little too paranoid?

Hm.  AFAICS there is no safe situation in which a tablespace directory
could be shared by two different installations (== toplevel $PGDATA
directories).  I don't think we need a dynamic postmaster.pid-type lock
to protect them.  What might make sense is some sort of marker file in a
tablespace directory that links back to the owning $PGDATA directory.
CREATE TABLESPACE should create this, or reject if it already exists.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Tablespaces

[Greg Stark]

Dennis Bjorklund <[EMAIL PROTECTED]> writes:

> On Thu, 26 Feb 2004, Gavin Sherry wrote:
> 
> > Comments? Questions? Suggestions?
> 
> Is that plan that in the future one can split a single table into 
> different table spaces? Like storing all rows with year < 1999 in one 
> tablespace and the rest in another?

That's a separate orthogonal feature called "partitioned tables". There's some
amount of resistance to the idea amongst postgres people, and there's nobody
who has spoken up interested in implementing it, but there's also lots of
interest from users. A good patch would probably go a long way to convincing
people :)

Table spaces are being able to store different tables in different physical
locations on disk. A first version of this has actually been implemented for
7.5 using symlinks.

Partitioned tables and tablespaces do indeed have a certain amount of synergy.
But even in a single tablespace your example makes sense.

> With the rule system and two underlying tables one could make it work by 
> hand I think.

The rule system could be used to do this, but there was some discussion of
using inherited tables to handle it. However neither handles the really hard
part of detecting queries that use only a part of the table and taking that
into account in generating the plan.

-- 
greg


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

Re: [HACKERS] Tablespaces

[Josh Berkus]

Gavin,

#1:  I really think that we should have a way to set a "default tablespace" 
for any database in a cluster.    This property would be vitally important 
for anyone wishing to use tablespaces to impose quotas.   First, the 
superuser would:
ALTER DATABASE db1 ALTER DEFAULT_TABLESPACE partition2;
then any regular users creating tables in that database would, by default, 
have TABLESPACE partition2 automatically appended to them by the parser 
unless overridden in the creation statement by specifying another, specific, 
tablespace.

Alternately, the default tablespace could be set through a GUC.   In my mind, 
this would be inferior on 2 counts:
1) It would require adding Yet Another Miscellaneos GUC Variable.
2) It would preclude large, multisuer installations from seamlessly using 
tablespaces for quotas, becuase there would be no way to transparently set 
the GUC differently for each user or database.


#2: Permissions:
I see the permissions issue as quite transparent.   First, I agree that only 
the superuser should have the right to create, alter, or drop tablespaces. 
'nuff said.
Second, as far as I can see, there is only one relevant permission for regular 
users:  USE.   Either the user is permitted to create objects in that 
tablespace, or he/she is not.  Other permissions, such as read access, should 
NOT be set by tablespace, as such permissions are already governed by 
database, table, and schema; to add a SELECT restriction to tablespaces would 
frequently result in paralytic snarls of conflicting permissions on complex 
installations.
Thus, by my proposal, the only GRANT for tablespaces (executed by a superuser) 
would be:
GRANT USE ON tablespace1 TO user;
This permission would ONLY be accessed for CREATE/ALTER TABLE, and CREATE 
INDEX statements.
Easy, neh?

#3: ALTER TABLE  CHANGE TABLESPACE:
This is strictly in the class of "would be a very nice & useful feature if 
it's not too difficult".   

Given how painful it is to drop & replace a table with multiple dependencies 
(on some databases, only possible by droping & re-loading the entire 
database) it would be nice to have an ALTER TABLE command that moved the 
table to another tablespace.    It doesn't *seem* to me that this would be a 
very challenging bit of programming, as the operation would be very similar 
to REINDEX in the manipulation of files.   (But what I know, really?)

Once tablespaces are a feature and some users start using them for quota 
management, there will quickly develop situations where the original 
tablespace for a db runs out of room and can't be resized.   Being able to 
move the table "in situ" then becomes vital, especially on very large 
databases ... and when someday combined with partitioned tables, will become 
essential.

Further, we will get an *immediate* flurry of requests from users who just 
upgraded to 7.5 and want to make use of the tablespaces feature on an 
existing production database.

ALTER INDEX ... CHANGE TABLESPACE is *not* needed, though, as there are no 
issues other than time which I know of with dropping & re-creating an index.

If ALTER TABLE CHANGE TABLESPACE has some major technical hurdles, then I 
think it's one of those things that could be put off until the next version 
of tablespaces, or even held until Partition Tables is developed for a 
combined solution.    But it would be nice to have.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Tablespaces

[Tom Lane]

"scott.marlowe" <[EMAIL PROTECTED]> writes:
> On Fri, 27 Feb 2004, Tom Lane wrote:
>> In my mind, one of the main benefits of this work will be that we'll be
>> able to get *rid* of the initlocation stuff.  It's a crock.

> OK, that's fine, but I keep thinking that a superuser should have to 
> create the tablespace itself, and then tables can be assigned by users 
> based on the rights assigned by the dba / superuser.

Yeah, we haven't yet gotten to the issue of permissions, but certainly
creating or deleting a tablespace has to be a superuser-only operation,
if only because you probably have also got some manual filesystem work
to do to set up the associated directory; and that has to be done as
root or postgres.

It might be a good idea to restrict connect/disconnect (if we use those
operations) to superusers as well.  

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] Tablespaces

[Dennis Bjorklund]

On Thu, 26 Feb 2004, Gavin Sherry wrote:

> Comments? Questions? Suggestions?

Is that plan that in the future one can split a single table into 
different table spaces? Like storing all rows with year < 1999 in one 
tablespace and the rest in another?

With the rule system and two underlying tables one could make it work by 
hand I think.

I've never used tablespaces in oracle so I don't know what it can offer. I 
though it could do things like the above. True? What is the syntax and 
for example, how does it effect indexes (not at all maybe).

If you don't want to discuss this now, I understand. It's not part of the
design as it is now. I'm just curious at what direction we are moving and
what is possible to do.

-- 
/Dennis Björklund


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] tablespaces and DB administration

[pgsql]

> [EMAIL PROTECTED] wrote:
>
>>
>>What you are missing is that the RAID is dealing with the multiple drives
>>as one drive. Two operations have to happen serially,
>>
>
> You're kidding or vastly underestimating raid controllers. The average
> db access is well served with a single block of data, stored on a single
> drive. Nicely parallelizable by a raid controller if it has a minimum of
> smartness.
>

The data contained on a RAID is spread across all the drives in the raid,
is this not true?

To access data on a drive, one must get the data off all of the drives at
the same time, is this not true? (yes there is flexibility with mirror and
ignoring parity on reads)

If you perform two different operations on the RAID, you must access each
RAID drive twice.

If you perform different operations on multiple different drives, you can
access the same amount of data as you would with the RAID, but have
parallelized operations.

This is a fact. It is *the* drawback to RAID system. If you do not
understand this, then you do not understand RAID systems.

Perform any benchmark you want. Take any RAID system you want. Or,
actually, I have a factual reason why RAID systems perform worse than
multiple single drives, I have written a quick program to show it. I have
even double checked on my own RAID system here. Now, your turn, show me
some facts that support your position, not just that "it should." Show me
how it can, show proof as to how a RAID system can be faster than its
component disks.

If you can't, I'm pretty sure I can safely ignore this part of the thread.

---(end of broadcast)---
TIP 8: explain analyze is your friend