Re: [HACKERS] tablespaces inside $PGDATA considered harmful
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
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
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 br...@momjian.ushttp://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
On Thu, Apr 23, 2015 at 4:30 PM, Andres Freund and...@anarazel.de 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
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 br...@momjian.ushttp://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
On Thu, Apr 23, 2015 at 09:13:52AM -0400, Robert Haas wrote: On Wed, Apr 22, 2015 at 10:41 PM, Bruce Momjian br...@momjian.us 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 br...@momjian.ushttp://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
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 br...@momjian.ushttp://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
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
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
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
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
On Thu, Apr 23, 2015 at 11:00 AM, Bruce Momjian br...@momjian.us 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
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
On 4/23/15 11:01 AM, Andres Freund wrote: On April 23, 2015 6:12:05 PM GMT+03:00, Jim Nasby jim.na...@bluetreble.com 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
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
On Thu, Apr 23, 2015 at 3:57 PM, Andres Freund and...@anarazel.de 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
On Wed, Apr 22, 2015 at 10:41 PM, Bruce Momjian br...@momjian.us 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
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 br...@momjian.ushttp://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
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
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 br...@momjian.ushttp://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
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
* 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
[HACKERS] tablespaces inside $PGDATA considered harmful
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 inside $PGDATA considered harmful
On Fri, Jan 30, 2015 at 11:43 AM, Joshua D. Drake j...@commandprompt.com 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
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, 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
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
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 in the data directory
On Mon, Dec 3, 2012 at 10:06 PM, Bruce Momjian br...@momjian.us 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
On Tue, Dec 4, 2012 at 09:37:46AM -0500, Robert Haas wrote: On Mon, Dec 3, 2012 at 10:06 PM, Bruce Momjian br...@momjian.us 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 br...@momjian.ushttp://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
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
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
On Dec 3, 2012 2:55 AM, Andrew Dunstan and...@dunslane.net wrote: On 12/02/2012 07:50 PM, Magnus Hagander wrote: On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander 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. /Magnus
Re: [HACKERS] Tablespaces in the data directory
On 12/03/2012 12:33 PM, Magnus Hagander wrote: On Dec 3, 2012 2:55 AM, Andrew Dunstan and...@dunslane.net mailto:and...@dunslane.net wrote: On 12/02/2012 07:50 PM, Magnus Hagander wrote: On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane t...@sss.pgh.pa.us mailto:t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net 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
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 br...@momjian.ushttp://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
On Dec 3, 2012, at 12:33, Magnus Hagander wrote: On Dec 3, 2012 2:55 AM, Andrew Dunstan and...@dunslane.net wrote: On 12/02/2012 07:50 PM, Magnus Hagander wrote: On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander 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 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
On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander 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. -- 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
On 12/02/2012 07:50 PM, Magnus Hagander wrote: On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander 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? 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
-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
[HACKERS] Tablespaces in the data directory
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 in the data directory
On 1 December 2012 13:45, Magnus Hagander mag...@hagander.net 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
Re: [HACKERS] Tablespaces in the data directory
Magnus Hagander 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? 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
[HACKERS] Tablespaces for temporary objects
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 for temporary objects
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
Re: [HACKERS] Tablespaces oddity?
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?
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 [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
Re: [HACKERS] Tablespaces oddity?
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
[HACKERS] Tablespaces oddity?
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
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
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
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 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
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 [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
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
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
[HACKERS] tablespaces and non-empty directories
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 and non-empty directories
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
Re: [HACKERS] tablespaces and non-empty directories
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
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
Re: [HACKERS] Tablespaces
On Fri, 2005-06-03 at 08:41 +0200, Hans-Jrgen Schnig 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
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
[HACKERS] Tablespaces
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 [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
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
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
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
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
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]
Re: [HACKERS] tablespaces for temporary files
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]
[HACKERS] tablespaces for temporary files
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
-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
-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
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: [HACKERS] Tablespaces
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: [HACKERS] Tablespaces
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
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: [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. 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: [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. 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
-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 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). FACT: Microsoft dominates because they break the law. 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
Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
-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. 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. Religious arguments are hard to fight when one person is unable to listen. As for best choice for their company, I
Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
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
-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
[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
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
[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
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
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
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
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: [pgsql-hackers-win32] [HACKERS] Tablespaces
-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
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: [HACKERS] Tablespaces
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
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
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
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 and DB administration
On Fri, 2004-05-28 at 08:15, [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: You are absolutely wrong on all accounts here. A RAID5 system is slower than a single spindle as it is only as fast as the slowest disk in the stripe and the overhead of the RAID. Huh, what kind of controller do you use... Sounds like some value IDE one. I'd never suggest IDE raid5 for DBMS purposes anyway. Actually, my RAID system, currently on my test system, is fully UWLVD SCSI with fast spindles. Here is a logical factual question for you to answer: how can a set of disks, lets say 7, 6 data drives with one parity, deliver results faster than the slowest drive in the stripe? If you say predictive and intelligent caching, yea, maybe, but *all* disks today have caching, but the initial request still has to wait for the longest seek time across all spindles and the slowest spindle position. I've been dealing with RAID systems for almost a decade now, and they are not a magic bullet. RAID systems are always slower than their compnent disks. This is the drawback to using them and a fundimental limitation. A single disk will average 1/2 spindle seek, assuming its initial head placement is random, and average 1/2 spindle revolution to track, assuming no out of order sector access. A RAID system has to wait for the slowest disk, thus while a single disk can average 1/2 seek and rotation, two disks will not. So, your raid disk access will ALWAYS be slower or as slow as a single disk access not including the additional RAID processing. Some high end SCSI drives comes with an option for using an external source for spindle syncronization. These drives will thus not have to wait for rotation, as head positions are aligned. The advantage to a RAID is that a number of smaller disks can look like a big disk with some redundency. The advantage to a RAID controller is that the RAID processing and parity generation overhead is done on an external device. Using a RAID controller that presents a SCSI LUN is great because you don't need to trust third party drivers. All in all, RAID is a good idea, but it isn't faster. As for IDE RAID, IDE RAID is an awesome idea. SCSI disks are just too expensive. Infortrend has a cool IDE to SCSI or Fibre RAID system that rocks. Addonics has these too, I've been using them with great results. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] tablespaces and DB administration
[EMAIL PROTECTED] wrote: As for IDE RAID, IDE RAID is an awesome idea. SCSI disks are just too expensive. Infortrend has a cool IDE to SCSI or Fibre RAID system that rocks. Obviously, you're caught by those marketing geeks. You're taking bandwidth (MB/s)as performance index, which is irrelevant for database access. Limiting factor is average access time, and there's still no 3ms seek time ide disk. This is not a problem of the interface, it's just a fact that (for marketing reasons?) all server grade disks are not equipped with ide. A good raid system will be able to have independend seeks issued on all disks in parallel, thus scaling by spindle number (only for parallel accessing processes of course, not for serialized access). What you're proposing is that the app should parallelize it, instead of leaving this to the instance that can (should) do this better. Regards, Andreas ---(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 and DB administration
[EMAIL PROTECTED] wrote: As for IDE RAID, IDE RAID is an awesome idea. SCSI disks are just too expensive. Infortrend has a cool IDE to SCSI or Fibre RAID system that rocks. Obviously, you're caught by those marketing geeks. You're taking bandwidth (MB/s)as performance index, which is irrelevant for database access. Limiting factor is average access time, and there's still no 3ms seek time ide disk. This is not a problem of the interface, it's just a fact that (for marketing reasons?) all server grade disks are not equipped with ide. Depending on your application, IDE RAID is a very cost effective system. Sometimes speed is not important. A good raid system will be able to have independend seeks issued on all disks in parallel, thus scaling by spindle number (only for parallel accessing processes of course, not for serialized access). What you're proposing is that the app should parallelize it, instead of leaving this to the instance that can (should) do this better. I'm not suggesting this at all, and clearly you have not read what I wrote. It is physically impossible for RAID to be faster than its component disks. Period. To argue that a single RAID system is faster than separate (comparable) disks managed independently is just not true. I have even explained why. Yes, RAID systems do scale by spindle, and seeks are issued in parallel, but you STILL need to wait for all spindles to complete the operation. Operations on a RAID system are at least as slow as the slowest disk. What you are missing is that the RAID is dealing with the multiple drives as one drive. Two operations have to happen serially, one after the other, where as with separate disks, the two can happen simultaneously. ---(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
