Re: [PATCHES] Tablespace patch review
-Original Message- From: [EMAIL PROTECTED] on behalf of Bruce Momjian Sent: Sat 6/19/2004 1:05 AM To: Andreas Pflug Cc: Tom Lane; Gavin Sherry; PostgreSQL-patches Subject: Re: [PATCHES] Tablespace patch review We can build a gui on top of the command-line tool, no? No, we can't. Don't forget, everything we do in pgAdmin is via libpq. Regards, Dave ---(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: [PATCHES] Tablespace patch review
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: As for the authentication-is-expensive issue, what of it? You *should* have to authenticate yourself in order to look inside another person's database. The sort of cross-database inspection being proposed here would be a big security hole in many people's view. Accessing pg_class et al using the current sysuseid with acl checking should be ok and satisfy security demands, no? No. If the other user has you locked out from connecting to his database at all, he's probably not going to feel that he should have to disable your access to individual objects inside it. Well he's using my tablespace, so I'd like to know at least the object name. This has some connections to the discussions we periodically have about preventing Joe User from looking at the system catalogs. If we make any changes in this area at all, I would expect them to be in the direction of narrowing access, not widening it to include being able to see other databases' catalogs. Superuser/tablespace owner isn't quite Joe User, I believe. Actually, there seem quite some other cross database/shared table issues (schema default tablespace, dropping user who owns objects) which make it desirable to have superuser readonly access to pg_catalog tables. Maybe a todo for 7.6... Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Tablespace patch review
Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] on behalf of Bruce Momjian Sent: Sat 6/19/2004 1:05 AM To: Andreas Pflug Cc: Tom Lane; Gavin Sherry; PostgreSQL-patches Subject: Re: [PATCHES] Tablespace patch review We can build a gui on top of the command-line tool, no? No, we can't. Don't forget, everything we do in pgAdmin is via libpq. Yeah, gui on top of cmd line is nasty and a pain in regarding portability. It's not exactly challenging to implement it directly either, that's what I'll do in absence of a serverside solution. I'll redirect all pgadmin user speed complaints about this to Toms personal mailbox ;-) Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Tablespace patch review
Bruce Momjian wrote: I don't see why an admin tool can't connect to each database and get a listing of what is in each tablespace. I don't think connecting to 100 databases to get that information will be slow. Well, whatever you call slow or not slow. I checked it; connecting 10 databases, retrieving tablespace dependencies (pg_class union pg_schema) and closing takes about one second over an ssl connection, 0.2 seconds with non-ssl. This was a trusted connection, can't check what will happen with md5, krb or so. Regards, Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] Tablespace patch review
-Original Message- From: Andreas Pflug [mailto:[EMAIL PROTECTED] Sent: Sat 6/19/2004 6:40 PM To: Bruce Momjian Cc: Dave Page; Tom Lane; PostgreSQL-patches Subject: Re: [PATCHES] Tablespace patch review Well, whatever you call slow or not slow. I checked it; connecting 10 databases, retrieving tablespace dependencies (pg_class union pg_schema) and closing takes about one second over an ssl connection, 0.2 seconds with non-ssl. This was a trusted connection, can't check what will happen with md5, krb or so. Don't suppose you happened to try it on Win32 did you? Regards, Dave ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Tablespace patch review
Dave Page wrote: -Original Message- From: Andreas Pflug [mailto:[EMAIL PROTECTED] Sent: Sat 6/19/2004 6:40 PM To: Bruce Momjian Cc: Dave Page; Tom Lane; PostgreSQL-patches Subject: Re: [PATCHES] Tablespace patch review Well, whatever you call slow or not slow. I checked it; connecting 10 databases, retrieving tablespace dependencies (pg_class union pg_schema) and closing takes about one second over an ssl connection, 0.2 seconds with non-ssl. This was a trusted connection, can't check what will happen with md5, krb or so. Don't suppose you happened to try it on Win32 did you? This was from a win32 workstation (pgadmin3) to a Linux server. 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: [PATCHES] Tablespace patch review
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Well, I didn't use tablespaces here so the pg_tablespaces directory is empty, so I can't think of what the tablespace is. You look in the pg_tablespace catalog for the row with that OID. Also, are we calling it pg_tablespaces (plural) rather than pg_tablespace? I didn't have any particular opinion about that till just now ... but now I see that it's a good idea for the pg_tablespaces directory (the one that holds all the symlinks) to have a different name from the pg_tablespace catalog, especially since the latter has a couple of rows that do not correspond to any entries in the former. If you want something distinct, which I understand, perhaps pg_tblspc. I'm not wedded to pg_tablespaces as the name in particular, but it should not be pg_tablespace, or we'll suffer the same confusion over and over that you just did. OK. -- 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: [PATCHES] Tablespace patch review
Andreas Pflug wrote: Bruce Momjian wrote: I don't see why an admin tool can't connect to each database and get a listing of what is in each tablespace. I don't think connecting to 100 databases to get that information will be slow. Well, whatever you call slow or not slow. I checked it; connecting 10 databases, retrieving tablespace dependencies (pg_class union pg_schema) and closing takes about one second over an ssl connection, 0.2 seconds with non-ssl. This was a trusted connection, can't check what will happen with md5, krb or so. Well, we could use something like dbsize to report how much disk space is used by each database in the tablespace (it does an 'ls' in the directory as a server-side function), and connect to the database to get actual table names. I can't think of any clean system that would allow access to the table names in other databases without connecting to them. -- 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: [PATCHES] Tablespace patch review
Bruce Momjian [EMAIL PROTECTED] writes: OK, I have reviewed the patch. I think Tom is doing the same, but I want to report the things I found. I just came up for air after about two solid days of working on this patch ... had not seen your message before committing it. The good news is that I think I did see all the stuff you found. What facility is there for moving objects between tablespaces? None, as yet. Seems we should be consistent in having WIN32 defs or not. Probably. I removed #ifdefs whereever possible --- there are just a few left in tablespace.c and dbcommands.c now. I was contemplating replacing HAVE_SYMLINKS with a HAVE_TABLESPACES flag, but with the occurrences isolated to one file I'm not sure it's worth the trouble. Your code in tablespc.c calls realpath(). Do all OS's have that? It doesn't anymore --- I was concerned about the portability question too. The only point of that code AFAICS was to prevent creation of two pg_tablespace entries pointing at the same directory. I felt that the better way to handle this was to write a PG_VERSION file in the tablespace directory during CREATE TABLESPACE. A subsequent CREATE TABLESPACE on the same directory will fail because the directory isn't empty anymore. And the version file might come in handy someday... Does all object creation code put a lock in the tablespace row to prevent DROP TABLESPACE from removing a tablespace in use? I hacked up some logic to deal with this, based on taking out an ExclusiveLock on pg_tablespace when adding a per-database subdirectory to a tablespace directory or doing DROP TABLESPACE. It works but it'd be nice to reduce the strength of the lock ... There is interesting code that checks to see of the objects existing in a tablespace are about to be dropped by the transaction. s/interesting/doesn't work/ ... I didn't commit this stuff. Nor the smgr changes either; those were far from ready for prime time. Are we ripping out our initlocation code at the same time? Not done yet, but it's dead and should be removed as soon as a decent respect for the deceased permits ;-) Where do we need to add mention of tablespaces in the main non-reference-page docs? Clearly at least in the section on managing disk space. Yeah. The patch as committed covers the reference pages, but we desperately need a higher-level discussion of tablespaces for the administrator's guide. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Tablespace patch review
Bruce Momjian [EMAIL PROTECTED] writes: I have a few other questions: What is the procedure for moving tablespace directories? There is none. However, pg_tablespace still has the old location. Yup. The *only* thing that pg_tablespace.spclocation is used for is for pg_dumpall to dump appropriate CREATE TABLESPACE commands, so it's not like you couldn't hack it after the fact. Do we need ALTER TABLESPACE to move tablespaces, and ALTER clauses to move objects to other tablespaces? Are these TODO items for later? TODO. You sound like a man who's expecting a several-generations-polished facility when we only just committed the first version today. I do not feel a need to have any of these features in 7.5 ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Tablespace patch review
On Fri, 18 Jun 2004, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: OK, I have reviewed the patch. I think Tom is doing the same, but I want to report the things I found. I just came up for air after about two solid days of working on this patch ... had not seen your message before committing it. The good news is that I think I did see all the stuff you found. Awesome. What facility is there for moving objects between tablespaces? None, as yet. Seems we should be consistent in having WIN32 defs or not. Probably. I removed #ifdefs whereever possible --- there are just a few left in tablespace.c and dbcommands.c now. I was contemplating replacing HAVE_SYMLINKS with a HAVE_TABLESPACES flag, but with the occurrences isolated to one file I'm not sure it's worth the trouble. Your code in tablespc.c calls realpath(). Do all OS's have that? It doesn't anymore --- I was concerned about the portability question too. The only point of that code AFAICS was to prevent creation of two pg_tablespace entries pointing at the same directory. I felt that the better way to handle this was to write a PG_VERSION file in the tablespace directory during CREATE TABLESPACE. A subsequent CREATE TABLESPACE on the same directory will fail because the directory isn't empty anymore. And the version file might come in handy someday... Yes. That's a better idea. [snip] Where do we need to add mention of tablespaces in the main non-reference-page docs? Clearly at least in the section on managing disk space. Yeah. The patch as committed covers the reference pages, but we desperately need a higher-level discussion of tablespaces for the administrator's guide. I'll look at this tomorrow. Thanks for your assistance. regards, tom lane Gavin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Tablespace patch review
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I have a few other questions: What is the procedure for moving tablespace directories? There is none. However, pg_tablespace still has the old location. Yup. The *only* thing that pg_tablespace.spclocation is used for is for pg_dumpall to dump appropriate CREATE TABLESPACE commands, so it's not like you couldn't hack it after the fact. Do we need ALTER TABLESPACE to move tablespaces, and ALTER clauses to move objects to other tablespaces? Are these TODO items for later? TODO. You sound like a man who's expecting a several-generations-polished facility when we only just committed the first version today. I do not feel a need to have any of these features in 7.5 ... I just need to know what to add to the TODO list, and so we can answer people who are going to ask for this functionality. Added to TODO: * Allow reporting of which objects are in which tablespaces * Allow database recovery where tablespaces can't be created o Add ALTER TABLESPACE to change location, name, owner o Allow objects to be moved between tablespaces I think this all the items still needed. -- 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 8: explain analyze is your friend
Re: [PATCHES] Tablespace patch review
Tom Lane wrote: Are we ripping out our initlocation code at the same time? Not done yet, but it's dead and should be removed as soon as a decent respect for the deceased permits ;-) You want me to do the honors? -- 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 7: don't forget to increase your free space map settings
Re: [PATCHES] Tablespace patch review
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Are we ripping out our initlocation code at the same time? Not done yet, but it's dead and should be removed as soon as a decent respect for the deceased permits ;-) You want me to do the honors? Nah, I'll get it. I want to do some other small cleanup on that patch, too. (But Gavin, you're on the hook for a rewrite of the admin guide section about alternate locations into something about tablespaces...) Somebody's got to fix oid2name and dbsize though. Bruce, you want to catch those? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Tablespace patch review
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Somebody's got to fix oid2name and dbsize though. Bruce, you want to catch those? Uh, how do they have to be fixed? Isn't the relfilenode unchanged? Do we just need to add tablespace lookups? How useful will oid2name be if it doesn't understand about tablespaces? I dunno how it ought to be changed, but surely it needs some thought. dbsize doesn't even compile right now, because it's using GetDatabasePath which now has another argument. I did not patch it because it needs more thought: should it report the total of all tablespaces for the database, or should its API be extended so you can ask about individual tablespaces, or what? In any case it's not a one-liner fix... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] Tablespace patch review
On Fri, 18 Jun 2004, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Are we ripping out our initlocation code at the same time? Not done yet, but it's dead and should be removed as soon as a decent respect for the deceased permits ;-) You want me to do the honors? Nah, I'll get it. I want to do some other small cleanup on that patch, too. (But Gavin, you're on the hook for a rewrite of the admin guide section about alternate locations into something about tablespaces...) I can either replace the Alternative Locations section or make a higher level reference to tablespaces under Server Administration in the main index. What do people think? Gavin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] Tablespace patch review
On Fri, 18 Jun 2004, Bruce Momjian wrote: [snip] TODO. You sound like a man who's expecting a several-generations-polished facility when we only just committed the first version today. I do not feel a need to have any of these features in 7.5 ... I just need to know what to add to the TODO list, and so we can answer people who are going to ask for this functionality. Added to TODO: * Allow reporting of which objects are in which tablespaces Do we need an information_schema.tablespaces view as well as an update to information_schema.{tables|indexes|...} ? Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] Tablespace patch review
Gavin Sherry wrote: On Fri, 18 Jun 2004, Andreas Pflug wrote: Gavin Sherry wrote: On Fri, 18 Jun 2004, Bruce Momjian wrote: [snip] TODO. You sound like a man who's expecting a several-generations-polished facility when we only just committed the first version today. I do not feel a need to have any of these features in 7.5 ... I just need to know what to add to the TODO list, and so we can answer people who are going to ask for this functionality. Added to TODO: * Allow reporting of which objects are in which tablespaces Do we need an information_schema.tablespaces view as well as an update to information_schema.{tables|indexes|...} ? I checked this to implement it, and found it being less then trivial when *all* objects of a tablespace should be displayed, not just the ones in the current database. I don't think we should try and show all objects for a tablespace in information_schema. Agreed, information_schema is database specific. I was thinking about a pg_tablespace_contents(..) function anyway. Being able to list all objects in a tablespace, including which databases they are in, is clearly useful, however (eg: hunting down use of a give tablespace that you want dropped). Sounds like a script in contrib (or the main source tree?) to me. You're suggesting the dblink way using a shell script. Imagine 20, 200, ... databases. This would be a costly thing (and has to be implemented differently in win32). I'd like to see an implementation that enables gui interfaces to show objects that depend on a tablespace, so you'd need to be aware of a user clicking on show what's in that tablespace and he probably wouldn't expect to wait an extended period of time for all databases to be scanned, or impose a 200-connection load on the server. IMHO checking objects in a tablespace is a routine administrative task, so it should be supported natively by the server without need of contribs. And for win user acceptance, a command line tool won't be sufficient either. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Tablespace patch review
On Sat, 19 Jun 2004, Andreas Pflug wrote: [snip] I don't think we should try and show all objects for a tablespace in information_schema. Agreed, information_schema is database specific. I was thinking about a pg_tablespace_contents(..) function anyway. Being able to list all objects in a tablespace, including which databases they are in, is clearly useful, however (eg: hunting down use of a give tablespace that you want dropped). Sounds like a script in contrib (or the main source tree?) to me. You're suggesting the dblink way using a shell script. Imagine 20, 200, ... databases. This would be a costly thing (and has to be implemented differently in win32). I'd like to see an implementation that enables gui interfaces to show objects that depend on a tablespace, so you'd need to be aware of a user clicking on show what's in that tablespace and he probably wouldn't expect to wait an extended period of time for all databases to be scanned, or impose a 200-connection load on the server. I see this more as a script like Tom described in another email. We'd have a list of tablespacecs and databases and scan each database (on connection at a time) and get the information the user wants. IMHO checking objects in a tablespace is a routine administrative task, so it should be supported natively by the server without need of contribs. And for win user acceptance, a command line tool won't be sufficient either. I would debate that. Firstly, tablespaces aren't supported on windows yet. Secondly, I'd think that Unix users would be fine with a command line tool, especially one that can connect to a remote host. For those not used to command line tools, I can imagine extensions to pgadmin or phppgadmin. Gavin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Tablespace patch review
Gavin Sherry wrote: I would debate that. Firstly, tablespaces aren't supported on windows yet. Just a matter of time. And I'm talking of win32 workstations connecting to *ix servers too. Secondly, I'd think that Unix users would be fine with a command line tool, especially one that can connect to a remote host. For those not used to command line tools, I can imagine extensions to pgadmin or phppgadmin. :-) :-) :-) Unfortunately, us admin tool programmers can't practice witchcraft, so we need a pgsql function for that... Certainly, we could iterate all known databases making a one-time connection (if allowed to connect, what about template0?), retrieving tablespace dependencies, and close again. As debated above, that's quite costly, especially if more sophisticated authentication mechanisms are used. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Tablespace patch review
Andreas Pflug [EMAIL PROTECTED] writes: IMHO checking objects in a tablespace is a routine administrative task, so it should be supported natively by the server without need of contribs. I strongly disagree. Dropping a tablespace is not a routine activity, and we don't have to have submillisecond response to operations that are only needed when your first attempt to drop one fails. As for the authentication-is-expensive issue, what of it? You *should* have to authenticate yourself in order to look inside another person's database. The sort of cross-database inspection being proposed here would be a big security hole in many people's view. And for win user acceptance, a command line tool won't be sufficient either. This does not deserve a response. 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: [PATCHES] Tablespace patch review
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: IMHO checking objects in a tablespace is a routine administrative task, so it should be supported natively by the server without need of contribs. I strongly disagree. Dropping a tablespace is not a routine activity, Dropping certainly not. But inspecting? If implemented in a gui, it's just a click away. As for the authentication-is-expensive issue, what of it? You *should* have to authenticate yourself in order to look inside another person's database. The sort of cross-database inspection being proposed here would be a big security hole in many people's view. Accessing pg_class et al using the current sysuseid with acl checking should be ok and satisfy security demands, no? Since it's the same cluster, we can be sure that it 's the same user in that cross db too. If the user has no access, the result won't have a meaning either. The auth-is-expensive issue is about creating the db connection itself again and again, when we only want to change a database. And for win user acceptance, a command line tool won't be sufficient either. This does not deserve a response. Well, that's not quite appropriate. A 'command line is enough for server maintenance' attitude won't attract win people; they're blind without a mouse. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Tablespace patch review
Andreas Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: As for the authentication-is-expensive issue, what of it? You *should* have to authenticate yourself in order to look inside another person's database. The sort of cross-database inspection being proposed here would be a big security hole in many people's view. Accessing pg_class et al using the current sysuseid with acl checking should be ok and satisfy security demands, no? No. If the other user has you locked out from connecting to his database at all, he's probably not going to feel that he should have to disable your access to individual objects inside it. This has some connections to the discussions we periodically have about preventing Joe User from looking at the system catalogs. If we make any changes in this area at all, I would expect them to be in the direction of narrowing access, not widening it to include being able to see other databases' catalogs. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Tablespace patch review
Andreas Pflug wrote: And for win user acceptance, a command line tool won't be sufficient either. This does not deserve a response. Well, that's not quite appropriate. A 'command line is enough for server maintenance' attitude won't attract win people; they're blind without a mouse. We can build a gui on top of the command-line tool, no? -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Tablespace patch review
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: How useful will oid2name be if it doesn't understand about tablespaces? I dunno how it ought to be changed, but surely it needs some thought. I assume we just need to add a tablespace display when run with no args, and a -s option to display _with_ -d to display only objects in that database. We could go fancy and spin through all the databases and list the datbase name and objects in that tablespace. I should think that the table-level display ought to show both the relfilenode and tablespace OIDs for each table. Given the number of open items for 7.5, I am thinking of keeping this for post-feature freeze. Both are contrib. Right, I doubt Marc will object to fixing contrib stuff after feature freeze ... regards, tom lane ---(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: [PATCHES] Tablespace patch review
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: How useful will oid2name be if it doesn't understand about tablespaces? I dunno how it ought to be changed, but surely it needs some thought. I assume we just need to add a tablespace display when run with no args, and a -s option to display _with_ -d to display only objects in that database. We could go fancy and spin through all the databases and list the datbase name and objects in that tablespace. I should think that the table-level display ought to show both the relfilenode and tablespace OIDs for each table. This is the existing display: (3) aspg oid2name -d test All tables from database test: - 17147 = sql_features 17152 = sql_implementation_info 17157 = sql_languages 17162 = sql_packages 17167 = sql_sizing 17172 = sql_sizing_profiles 17220 = x For objects in the default tablespace, they don't show a tablespace oid, right? Where do we put it? A column that will be empty if they don't use tablespaces? Given the number of open items for 7.5, I am thinking of keeping this for post-feature freeze. Both are contrib. Right, I doubt Marc will object to fixing contrib stuff after feature freeze ... Also, remember I am only online fulltime for another two days, then I am leaving for Europe, return on July 3, after feature freeze. -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Tablespace patch review
Are we ripping out our initlocation code at the same time? Not done yet, but it's dead and should be removed as soon as a decent respect for the deceased permits ;-) You want me to do the honors? What about people upgrading from 7.4 databases that used database locations? Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Tablespace patch review
Christopher Kings-Lynne [EMAIL PROTECTED] writes: What about people upgrading from 7.4 databases that used database locations? They'll get a nice warning: regression=# create database foo location 'bar'; WARNING: LOCATION is not supported anymore HINT: Consider using tablespaces instead. CREATE DATABASE and everything will go into the default tablespace. I don't really see how to do much better than that ... 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: [PATCHES] Tablespace patch review
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: I should think that the table-level display ought to show both the relfilenode and tablespace OIDs for each table. For objects in the default tablespace, they don't show a tablespace oid, right? Where do we put it? A column that will be empty if they don't use tablespaces? pg_class will show a zero for objects in the default tablespace, but I think oid2name should pull the actual tablespace ID from pg_database.dattablespace and show that. The convention about zero is just to make life simple for CREATE DATABASE --- users of oid2name should not have to think about it. 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: [PATCHES] Tablespace patch review
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: I should think that the table-level display ought to show both the relfilenode and tablespace OIDs for each table. For objects in the default tablespace, they don't show a tablespace oid, right? Where do we put it? A column that will be empty if they don't use tablespaces? pg_class will show a zero for objects in the default tablespace, but I think oid2name should pull the actual tablespace ID from pg_database.dattablespace and show that. The convention about zero is just to make life simple for CREATE DATABASE --- users of oid2name should not have to think about it. Well, I didn't use tablespaces here so the pg_tablespaces directory is empty, so I can't think of what the tablespace is. Is it the database oid? Also, are we calling it pg_tablespaces (plural) rather than pg_tablespace? -- 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 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: [PATCHES] Tablespace patch review
Bruce Momjian [EMAIL PROTECTED] writes: Well, I didn't use tablespaces here so the pg_tablespaces directory is empty, so I can't think of what the tablespace is. You look in the pg_tablespace catalog for the row with that OID. Also, are we calling it pg_tablespaces (plural) rather than pg_tablespace? I didn't have any particular opinion about that till just now ... but now I see that it's a good idea for the pg_tablespaces directory (the one that holds all the symlinks) to have a different name from the pg_tablespace catalog, especially since the latter has a couple of rows that do not correspond to any entries in the former. I'm not wedded to pg_tablespaces as the name in particular, but it should not be pg_tablespace, or we'll suffer the same confusion over and over that you just did. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] Tablespace patch review
Bruce Momjian wrote: Gavin Sherry wrote: Attached is an updated patch, fixing a compile error which my compiler didn't seem to detect/suffer from and incorporating fixes to problems raised by Neil. Thanks, Gavin OK, I have reviewed the patch. I think Tom is doing the same, but I want to report the things I found. I have a few other questions: What is the procedure for moving tablespace directories? I assume with the postmaster down the directory can be moved and the symlink changed. However, pg_tablespace still has the old location. Should we use lstat so pg_tablespace gets updated automatically or as part of pg_dump, or throw a server message if the symlink doesn't match pg_tablespace. We need to add instructions that pg_tablespace needs to be updated if the symlink is changed. What bothers me is that someone updating just the symlink might run fine but would not be able to restore a dump to the same machine. And about restore, particularly to another machine, what do we do if the tablespace can't be created in the location specified in the dump? The tablespace creation fails, and all objects specified in that tablespace also fail? Seems bad, particularly if you are restoring after a hardware failure. Do we need a GUC that says if the tablespace doesn't exist, create the object in the default location? Do we need a pg_dump option that ignores tablespaces completely for portability and for restoring to another server? Is pg_dump smart enough not to emit the tablespace if the object would already be created in the right tablespace, perhaps because of its schema? The new tablespace clause adds a non-standard clause to CREATE TABLE, something we were hoping to avoid, but I doubt it is possible. Do we need ALTER TABLESPACE to move tablespaces, and ALTER clauses to move objects to other tablespaces? Are these TODO items for later? -- 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: [PATCHES] Tablespace patch review
I wrote the pg_dump bits, so I guess I can answer these... And about restore, particularly to another machine, what do we do if the tablespace can't be created in the location specified in the dump? The tablespace creation fails, and all objects specified in that tablespace also fail? Seems bad, particularly if you are restoring after a hardware failure. Do we need a GUC that says if the tablespace doesn't exist, create the object in the default location? Do we need a pg_dump option that ignores tablespaces completely for portability and for restoring to another server? Wll. There's a lot of stuff that can already fail in a restore - you always must watch your restore output to ensure things have worked. When I restore and it can't create a tsearch function because I forgot to install the tsearch.so, it errors and then keeps going with tables and triggers and all sorts of stuff failing to restore because they depended on that function. I don't see the failure to create a tablespace as being any different. Is pg_dump smart enough not to emit the tablespace if the object would already be created in the right tablespace, perhaps because of its schema? Yes it is, I was very careful about that. If you never use a tablespace in your life, you will never see tablespace commands in your dumps. The changes to pg_dump work just fine against a pre-7.5 backend and again no tablespace commands will be output. The new tablespace clause adds a non-standard clause to CREATE TABLE, something we were hoping to avoid, but I doubt it is possible. Well, if you don't use them, you won't see them, so I don't have a problem with that :) Do we need ALTER TABLESPACE to move tablespaces, and ALTER clauses to move objects to other tablespaces? Are these TODO items for later? Would be nice :) I'm also in favour of ADD [PRIMARY KEY | UNIQUE ](blah) TABLESPACE asdf which isn't in the current patch. Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend