Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 02:37 PM 6/11/2004, Tom Lane wrote: If you have a preliminary patch, you could pass it along and I'll finish it up. Attached. It has some trivial-looking rejects on current CVS. Let me know if you would prefer me to do the work, or want some testing done. It was tested (in terms of output validity) with 8.0b1. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ pg_dump_040820_patch_with_funcs.gz Description: Binary data ---(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] Using ALTER TABLESPACE in pg_dump
Philip Warner [EMAIL PROTECTED] writes: Attached. It has some trivial-looking rejects on current CVS. Let me know if you would prefer me to do the work, or want some testing done. It was tested (in terms of output validity) with 8.0b1. Applied with minor cleanups. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Philip, I've just committed the backend changes involved in setting up a default_tablespace GUC variable for pg_dump to use, but I didn't do anything to convert pg_dump to doing so instead of using explicit TABLESPACE clauses. You had muttered something about wanting to add a TOC entry field for this --- do you still want to do the work? You can probably get it done faster than I could, but I dunno if you have time at the moment. I'd like to get it in over the weekend so that we can put out a new beta next week. BTW, part of the backend changes was to stop emitting TABLESPACE clauses in pg_get_indexdef() and pg_get_constraintdef() output, so as of CVS tip pg_dump will in fact fail to restore index tablespaces accurately. I assume this is the backend behavior you want, but holler if not. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 06:19 AM 6/11/2004, Tom Lane wrote: You had muttered something about wanting to add a TOC entry field for this --- do you still want to do the work? You can probably get it done faster than I could, but I dunno if you have time at the moment. I'd like to get it in over the weekend so that we can put out a new beta next week. Time is at a serious premium for me at the moment (I have several projects all due about now); but I wrote a patch for this a few weeks back, so it should not be a lot of work (unless pg_dump has changed in the last couple of months). I will *try* to get it done by Monday morning your time, and will let you know if I am going to miss this deadline as soon as I know. BTW, part of the backend changes was to stop emitting TABLESPACE clauses in pg_get_indexdef() and pg_get_constraintdef() output, so as of CVS tip pg_dump will in fact fail to restore index tablespaces accurately. I assume this is the backend behavior you want, but holler if not. Excellent. I assume that anything that can have a tablespace (database, schema(?), table and index -- anything else?) should emit a 'set default_tablespace=ts' before creation (and that this will affect auto-created indexes as appropriate, whatever that means). Thanks for all the work. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Philip Warner [EMAIL PROTECTED] writes: Time is at a serious premium for me at the moment (I have several projects all due about now); but I wrote a patch for this a few weeks back, so it should not be a lot of work (unless pg_dump has changed in the last couple of months). If you have a preliminary patch, you could pass it along and I'll finish it up. Excellent. I assume that anything that can have a tablespace (database, schema(?), table and index -- anything else?) should emit a 'set default_tablespace=ts' before creation (and that this will affect auto-created indexes as appropriate, whatever that means). default_tablespace will affect both tables and auto-created indexes. But I was under the impression that pg_dump deliberately avoids auto-creation of indexes... isn't each one split out as an ADD CONSTRAINT operation? Schemas don't have tablespaces anymore. 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] Using ALTER TABLESPACE in pg_dump
TODO item removed: * Allow database recovery where tablespaces can't be created When a pg_dump is restored, all tablespaces will attempt to be created in their original locations. If this fails, the user must be able to adjust the restore process. Not done yet, but it will be with SET default_tablespace. I don't think we need adjust but rather default to the default tablespace is just fine, and they can pre-create tablespaces in different locations to adjust the restore anyway. Great! --- Philip Warner wrote: At 06:19 AM 6/11/2004, Tom Lane wrote: You had muttered something about wanting to add a TOC entry field for this --- do you still want to do the work? You can probably get it done faster than I could, but I dunno if you have time at the moment. I'd like to get it in over the weekend so that we can put out a new beta next week. Time is at a serious premium for me at the moment (I have several projects all due about now); but I wrote a patch for this a few weeks back, so it should not be a lot of work (unless pg_dump has changed in the last couple of months). I will *try* to get it done by Monday morning your time, and will let you know if I am going to miss this deadline as soon as I know. BTW, part of the backend changes was to stop emitting TABLESPACE clauses in pg_get_indexdef() and pg_get_constraintdef() output, so as of CVS tip pg_dump will in fact fail to restore index tablespaces accurately. I assume this is the backend behavior you want, but holler if not. Excellent. I assume that anything that can have a tablespace (database, schema(?), table and index -- anything else?) should emit a 'set default_tablespace=ts' before creation (and that this will affect auto-created indexes as appropriate, whatever that means). Thanks for all the work. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 8: explain analyze is your friend -- 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: [HACKERS] Using ALTER TABLESPACE in pg_dump
FYI, we need tablespace_default to control this pg_dump output for a primary key: ALTER TABLE ONLY test2 ADD CONSTRAINT test2_pkey PRIMARY KEY (x); --- Tom Lane wrote: Philip Warner [EMAIL PROTECTED] writes: Time is at a serious premium for me at the moment (I have several projects all due about now); but I wrote a patch for this a few weeks back, so it should not be a lot of work (unless pg_dump has changed in the last couple of months). If you have a preliminary patch, you could pass it along and I'll finish it up. Excellent. I assume that anything that can have a tablespace (database, schema(?), table and index -- anything else?) should emit a 'set default_tablespace=ts' before creation (and that this will affect auto-created indexes as appropriate, whatever that means). default_tablespace will affect both tables and auto-created indexes. But I was under the impression that pg_dump deliberately avoids auto-creation of indexes... isn't each one split out as an ADD CONSTRAINT operation? Schemas don't have tablespaces anymore. 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]) -- 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: [HACKERS] Using ALTER TABLESPACE in pg_dump
I wrote: I'd be willing to jump this way if we can work out the default-tablespace inconsistencies that Bruce has on the open items list. After further thought it seems to me that using a default_tablespace GUC variable doesn't eliminate all the open issues. In particular it is no help for the problem of merging two different tablespaces during CREATE DATABASE, ie, creating a new DB with a dattablespace that is different from the template DB's default when the template DB already has some tables explicitly placed into that tablespace. In this situation we have the problem that the cloned DB would have pg_class rows with different references to the same tablespace (either zero for the database default, or the explicit OID of the tablespace). Among other things this would make it impossible to use the cloned DB again as a template for CREATE DATABASE. AFAICS this problem stems ultimately from the choice to have a special representation (zero) in pg_class for the database's default tablespace. The only way to really get rid of it would be to eliminate that provision and say that pg_class.reltablespace is always the correct explicit OID. What that would mean in turn is that we could not copy a database and move its tables into a different tablespace, at least not without very major work on CREATE DATABASE to make it alter pg_class on-the-fly while copying. We might want to think about doing that eventually, but for now I'd say that the restriction on merging tablespaces is just something we have to live with. It's less annoying than not being able to relocate a database, for sure. Despite this, the default_tablespace GUC variable seems more attractive than what we have now. Last call for objections ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Tom Lane wrote: I wrote: I'd be willing to jump this way if we can work out the default-tablespace inconsistencies that Bruce has on the open items list. After further thought it seems to me that using a default_tablespace GUC variable doesn't eliminate all the open issues. In particular it is no help for the problem of merging two different tablespaces during CREATE DATABASE, ie, creating a new DB with a dattablespace that is different from the template DB's default when the template DB already has some tables explicitly placed into that tablespace. In this situation we have the problem that the cloned DB would have pg_class rows with different references to the same tablespace (either zero for the database default, or the explicit OID of the tablespace). Among other things this would make it impossible to use the cloned DB again as a template for CREATE DATABASE. Right. I would say 99% of people are using template1 as the template for new databases, and if we clearly give an error message when they use a database not in the default tablespace (which we do now), it seems just fine. Let's see how many people complain and make adjustments in 8.1 if needed. AFAICS this problem stems ultimately from the choice to have a special representation (zero) in pg_class for the database's default tablespace. The only way to really get rid of it would be to eliminate that provision and say that pg_class.reltablespace is always the correct explicit OID. What that would mean in turn is that we could not copy a database and move its tables into a different tablespace, at least not without very major work on CREATE DATABASE to make it alter pg_class on-the-fly while copying. Agreed. That is just too much work for so little gain. We might want to think about doing that eventually, but for now I'd say that the restriction on merging tablespaces is just something we have to live with. It's less annoying than not being able to relocate a database, for sure. One downside that came up yesterday in a discussion is that once shemas don't have default tablespaces we can't easily have default tablespaces for toast and temporary table system schemas. Now we can't actually do that now anyway because they are created by the system but it might limit how we can control these in the future. I am just throwing this out as a point. Despite this, the default_tablespace GUC variable seems more attractive than what we have now. Last call for objections ... Sounds good. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
On Sun, 31 Oct 2004, Bruce Momjian wrote: Tom Lane wrote: I wrote: I'd be willing to jump this way if we can work out the default-tablespace inconsistencies that Bruce has on the open items list. After further thought it seems to me that using a default_tablespace GUC variable doesn't eliminate all the open issues. In particular it is no help for the problem of merging two different tablespaces during CREATE DATABASE, ie, creating a new DB with a dattablespace that is different from the template DB's default when the template DB already has some tables explicitly placed into that tablespace. In this situation we have the problem that the cloned DB would have pg_class rows with different references to the same tablespace (either zero for the database default, or the explicit OID of the tablespace). Among other things this would make it impossible to use the cloned DB again as a template for CREATE DATABASE. Right. I would say 99% of people are using template1 as the template for new databases, and if we clearly give an error message when they use a database not in the default tablespace (which we do now), it seems just fine. Let's see how many people complain and make adjustments in 8.1 if needed. I agree. AFAICS this problem stems ultimately from the choice to have a special representation (zero) in pg_class for the database's default tablespace. The only way to really get rid of it would be to eliminate that provision and say that pg_class.reltablespace is always the correct explicit OID. What that would mean in turn is that we could not copy a database and move its tables into a different tablespace, at least not without very major work on CREATE DATABASE to make it alter pg_class on-the-fly while copying. Agreed. That is just too much work for so little gain. I agree. Although, I think having a createdb() with transaction semantics and the ability to modify data on the fly would be useful -- not just for tablespace handling. As you say, it is a fair bit of work, however. We might want to think about doing that eventually, but for now I'd say that the restriction on merging tablespaces is just something we have to live with. It's less annoying than not being able to relocate a database, for sure. One downside that came up yesterday in a discussion is that once shemas don't have default tablespaces we can't easily have default tablespaces for toast and temporary table system schemas. Now we can't actually do that now anyway because they are created by the system but it might limit how we can control these in the future. I am just throwing this out as a point. Neil has been talking to me about being able to set a tablespace for temporary tables at or after create database time. I'm not sure about TOAST however. I considered the idea of adding something to CREATE TABLE like TOASTSPACE tablespace, such that all TOAST tables would be put in the 'toastspace'. But I think the syntax is ugly and would confuse many users who do not know what toast is. Thanks, Gavin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Gavin Sherry wrote: One downside that came up yesterday in a discussion is that once shemas don't have default tablespaces we can't easily have default tablespaces for toast and temporary table system schemas. Now we can't actually do that now anyway because they are created by the system but it might limit how we can control these in the future. I am just throwing this out as a point. Neil has been talking to me about being able to set a tablespace for temporary tables at or after create database time. I'm not sure about TOAST however. I considered the idea of adding something to CREATE TABLE like TOASTSPACE tablespace, such that all TOAST tables would be put in the 'toastspace'. But I think the syntax is ugly and would confuse many users who do not know what toast is. I think we need to add temp_tablespace and toast_tablespace GUC variables to deal with this, perhaps for 8.1. -- 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: [HACKERS] Using ALTER TABLESPACE in pg_dump
Added to open items: * Add a GUC variable to control temporary and TOAST tablespace usage --- Gavin Sherry wrote: On Sun, 31 Oct 2004, Bruce Momjian wrote: Tom Lane wrote: I wrote: I'd be willing to jump this way if we can work out the default-tablespace inconsistencies that Bruce has on the open items list. After further thought it seems to me that using a default_tablespace GUC variable doesn't eliminate all the open issues. In particular it is no help for the problem of merging two different tablespaces during CREATE DATABASE, ie, creating a new DB with a dattablespace that is different from the template DB's default when the template DB already has some tables explicitly placed into that tablespace. In this situation we have the problem that the cloned DB would have pg_class rows with different references to the same tablespace (either zero for the database default, or the explicit OID of the tablespace). Among other things this would make it impossible to use the cloned DB again as a template for CREATE DATABASE. Right. I would say 99% of people are using template1 as the template for new databases, and if we clearly give an error message when they use a database not in the default tablespace (which we do now), it seems just fine. Let's see how many people complain and make adjustments in 8.1 if needed. I agree. AFAICS this problem stems ultimately from the choice to have a special representation (zero) in pg_class for the database's default tablespace. The only way to really get rid of it would be to eliminate that provision and say that pg_class.reltablespace is always the correct explicit OID. What that would mean in turn is that we could not copy a database and move its tables into a different tablespace, at least not without very major work on CREATE DATABASE to make it alter pg_class on-the-fly while copying. Agreed. That is just too much work for so little gain. I agree. Although, I think having a createdb() with transaction semantics and the ability to modify data on the fly would be useful -- not just for tablespace handling. As you say, it is a fair bit of work, however. We might want to think about doing that eventually, but for now I'd say that the restriction on merging tablespaces is just something we have to live with. It's less annoying than not being able to relocate a database, for sure. One downside that came up yesterday in a discussion is that once shemas don't have default tablespaces we can't easily have default tablespaces for toast and temporary table system schemas. Now we can't actually do that now anyway because they are created by the system but it might limit how we can control these in the future. I am just throwing this out as a point. Neil has been talking to me about being able to set a tablespace for temporary tables at or after create database time. I'm not sure about TOAST however. I considered the idea of adding something to CREATE TABLE like TOASTSPACE tablespace, such that all TOAST tables would be put in the 'toastspace'. But I think the syntax is ugly and would confuse many users who do not know what toast is. Thanks, Gavin -- 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: [HACKERS] Using ALTER TABLESPACE in pg_dump
Bruce Momjian [EMAIL PROTECTED] writes: Gavin Sherry wrote: I'm not sure about TOAST however. I considered the idea of adding something to CREATE TABLE like TOASTSPACE tablespace, such that all TOAST tables would be put in the 'toastspace'. But I think the syntax is ugly and would confuse many users who do not know what toast is. I think we need to add temp_tablespace and toast_tablespace GUC variables to deal with this, perhaps for 8.1. A tablespace for temp tables is okay, but I'm fairly dubious about the idea of a toast tablespace. The current behavior is that a toast table is automatically placed into the same tablespace as its parent, and that seems exactly right to me. It's certainly the right thing from the point of view of users who do not understand TOAST and expect all of a table's data to get put where they said to put the table. 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] Using ALTER TABLESPACE in pg_dump
OK, TODO updated: * Add a GUC variable to control the tablespace for temporary objects --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Gavin Sherry wrote: I'm not sure about TOAST however. I considered the idea of adding something to CREATE TABLE like TOASTSPACE tablespace, such that all TOAST tables would be put in the 'toastspace'. But I think the syntax is ugly and would confuse many users who do not know what toast is. I think we need to add temp_tablespace and toast_tablespace GUC variables to deal with this, perhaps for 8.1. A tablespace for temp tables is okay, but I'm fairly dubious about the idea of a toast tablespace. The current behavior is that a toast table is automatically placed into the same tablespace as its parent, and that seems exactly right to me. It's certainly the right thing from the point of view of users who do not understand TOAST and expect all of a table's data to get put where they said to put the table. regards, tom lane -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Added to open items list: * Tablespace o add new GUC default_tablespace to control object creation when no explicit TABLESPACE clause exists Use it in pg_dump. o Remove tablespace default for databases and schemas Place objects as specified by the TABLESPACE clause or default_tablespace. The database tablespace controls only the system objects. --- Tom Lane wrote: Philip Warner [EMAIL PROTECTED] writes: At 08:00 AM 26/10/2004, Tom Lane wrote: I don't want a GUC variable that actively changes the default tablespace; at least not unless you want to abandon the current mechanisms for default tablespace choices entirely, and go over to making the GUC variable be the sole arbiter. Something consistent with Schemas does sound good to me; a tablespace search path (or just single default), and support for a TABLESPACE clause on table and INDEX definitions would be good. I can't see what a search path would be good for. For the three largest databases I work on, the namespace/schema that a table resides in is irrelevant to the tablespace that it should be stored in. So default tablespaces on the schema are a bit of a pointless feature. The ability to have the features of schemas: default tablespace for given users, a GUC variable, and ACLs on tablespaces would be far more valuable. Another nice thing is that not having default tablespaces associated with schemas eliminates that nasty issue about being able to drop such a tablespace while the schema is still there. It seems like we still need some notion of a database's schema, to put the system catalogs in, but perhaps that need not be the same as the default schema for user tables created in the database? I'd be willing to jump this way if we can work out the default-tablespace inconsistencies that Bruce has on the open items list. Does anyone want to draft a concrete proposal? It seems like the basic elements are: * A GUC variable named something like default_tablespace that controls which TS objects are created in when there's no explicit TABLESPACE clause. The factory default for this would of course be pg_default. Otherwise it's settable just like any other GUC var. * Get rid of TABLESPACE clause for CREATE SCHEMA, and pg_namespace.nsptablespace (ooops, another initdb). * Need to define exactly what TABLESPACE clause for a database controls; location of its catalogs of course, but anything else? * We could possibly say that a TABLESPACE clause attached to CREATE TABLE determines the default tablespace for indexes created by the same command; I'm not sure if this is a good idea, or if the indexes should go into default_tablespace absent a TABLESPACE clause attached directly to their defining constraints. We certainly want default_tablespace to control indexes created by separate commands, so there'd be some inconsistency if we do the former. regards, tom lane -- 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: [HACKERS] Using ALTER TABLESPACE in pg_dump
Dear Tom, ISTM that the core business of a database is to help organize and protect data, and it is plainly that. You just wish you won't need it, so it is somehow abstract, but when and if you need it, it is not second-order at all;-) and it is much too late to redo the dump. So you create some tablespaces by hand. Big deal. I agree that is is doable this way, although not really nice. This objection is not strong enough to justify an ugly, klugy definition for where tables get created. I do also agree about this. My real point is that while reading the thread quickly, I was afraid the problem would not be better addressed at all in the coming release. It seems that I was wrong as it does not look to be the case. Any fix instead of nothing is fair enough for me. Thanks for your answer, have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(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] Using ALTER TABLESPACE in pg_dump
Philip Warner wrote: If we can adopt the move-after-create solution, then we really only have two options: - virtual tablespaces (which do seem kind of useful, especially for development vs. production config where the local/personal dev version can use the same script as a production DB but not need half a dozen TSs) - magic-tablespace-var that behaves like the schema search path I was thinking we could have a var like schema search path that specifies where we try to create the object: SET tablespace_path = 'tblspc1, pg_default'; CREATE TABLE test(x int); This combines the idea of pulling the TABLESPACE specification out of the CREATE, and allows a fallback if the primary tablespace doesn't exist. -- 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: [HACKERS] Using ALTER TABLESPACE in pg_dump
Bruce Momjian [EMAIL PROTECTED] writes: I was thinking we could have a var like schema search path that specifies where we try to create the object: SET tablespace_path = 'tblspc1, pg_default'; CREATE TABLE test(x int); This combines the idea of pulling the TABLESPACE specification out of the CREATE, and allows a fallback if the primary tablespace doesn't exist. ... and takes us even further away from the notion that the default tablespace is determined by the parent object (database or schema). I think that we have a clean, understandable, easy-to-use tablespace behavior now, and we should not muck it up for abstract second-order goals like having portable dumps for databases that were created unportably in the first place. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Dear Tom, [...] This combines the idea of pulling the TABLESPACE specification out of the CREATE, and allows a fallback if the primary tablespace doesn't exist. ... and takes us even further away from the notion that the default tablespace is determined by the parent object (database or schema). I think that we have a clean, understandable, easy-to-use tablespace behavior now, and we should not muck it up for abstract second-order goals like having portable dumps for databases that were created unportably in the first place. I disagree on the view that being able to restore a database on another machine after a crash is an abstract second-order goal;-) ISTM that the core business of a database is to help organize and protect data, and it is plainly that. You just wish you won't need it, so it is somehow abstract, but when and if you need it, it is not second-order at all;-) and it is much too late to redo the dump. When a machine crashes, usually I did not foresee how it will crash, and whether I will or will not be able to restore on the same machine, with or without the same tablespaces... It depends on what went wrong. Thus ISTM that having the ability to fix that at restore time is simply what is needed, when it is needed. Now I do agree that having a straight behavior is a much better thing. The ALTER ... TABLESPACE ... generated by restore from some headers seems the right simple solution to me, but the alter syntax is not fully implemented AFAICR:-( Completing the implementation for the missing parts (ALTER DATABASE... and ALTER SCHEMA... ?), feature/beta freeze or not, would seem the reasonnable path to me. I'm sorry I don't have time to develop and submit a patch... Have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Fabien COELHO [EMAIL PROTECTED] writes: I disagree on the view that being able to restore a database on another machine after a crash is an abstract second-order goal;-) ISTM that the core business of a database is to help organize and protect data, and it is plainly that. You just wish you won't need it, so it is somehow abstract, but when and if you need it, it is not second-order at all;-) and it is much too late to redo the dump. So you create some tablespaces by hand. Big deal. This objection is not strong enough to justify an ugly, klugy definition for where tables get created. If tablespaces had to be associated with physically distinct devices then there would be merit in your concerns, but they are only directories and so there is no reason that you cannot create the same set of tablespace names on your new machine that you had on your old. 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] Using ALTER TABLESPACE in pg_dump
Tom Lane wrote: Fabien COELHO [EMAIL PROTECTED] writes: I disagree on the view that being able to restore a database on another machine after a crash is an abstract second-order goal;-) ISTM that the core business of a database is to help organize and protect data, and it is plainly that. You just wish you won't need it, so it is somehow abstract, but when and if you need it, it is not second-order at all;-) and it is much too late to redo the dump. So you create some tablespaces by hand. Big deal. This objection is not strong enough to justify an ugly, klugy definition for where tables get created. If tablespaces had to be associated with physically distinct devices then there would be merit in your concerns, but they are only directories and so there is no reason that you cannot create the same set of tablespace names on your new machine that you had on your old. I am confused. I thought Tom's argument was that we shouldn't add an overly complex tablespace SET variable just to prevent the non-standard TABLESPACE in CREATE, which I can understand. However, the text above seems to indicate we don't need an 'ignore tablespace specification if it does not exist' which I think we do need for cases where we want to restore on to a system that doesn't use tablespaces or for non-super-user restores. -- 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: [HACKERS] Using ALTER TABLESPACE in pg_dump
Bruce Momjian [EMAIL PROTECTED] writes: I am confused. I thought Tom's argument was that we shouldn't add an overly complex tablespace SET variable just to prevent the non-standard TABLESPACE in CREATE, which I can understand. However, the text above seems to indicate we don't need an 'ignore tablespace specification if it does not exist' which I think we do need for cases where we want to restore on to a system that doesn't use tablespaces or for non-super-user restores. I'm willing to live with a soft error type of GUC variable for those cases. I don't want a GUC variable that actively changes the default tablespace; at least not unless you want to abandon the current mechanisms for default tablespace choices entirely, and go over to making the GUC variable be the sole arbiter. (Which would be consistent with the way we handle selection of which schema to create in, so I'm not necessarily against it.) I guess what I'm trying to say is I don't want a hodgepodge design, because I think it'll be confusing and unusable. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I am confused. I thought Tom's argument was that we shouldn't add an overly complex tablespace SET variable just to prevent the non-standard TABLESPACE in CREATE, which I can understand. However, the text above seems to indicate we don't need an 'ignore tablespace specification if it does not exist' which I think we do need for cases where we want to restore on to a system that doesn't use tablespaces or for non-super-user restores. I'm willing to live with a soft error type of GUC variable for those cases. I don't want a GUC variable that actively changes the default tablespace; at least not unless you want to abandon the current mechanisms for default tablespace choices entirely, and go over to making the GUC variable be the sole arbiter. (Which would be consistent with the way we handle selection of which schema to create in, so I'm not necessarily against it.) I guess what I'm trying to say is I don't want a hodgepodge design, because I think it'll be confusing and unusable. Agreed. My tablespace path idea would be very hard to understand if combined with the existing db/schema/table default rules. I can't decide which is the best approach. Don't indexes default to the schema of the table rather than the schema path, so they aren't 100% controlled by the search path? -- 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: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 08:00 AM 26/10/2004, Tom Lane wrote: I don't want a GUC variable that actively changes the default tablespace; at least not unless you want to abandon the current mechanisms for default tablespace choices entirely, and go over to making the GUC variable be the sole arbiter. Something consistent with Schemas does sound good to me; a tablespace search path (or just single default), and support for a TABLESPACE clause on table and INDEX definitions would be good. For the three largest databases I work on, the namespace/schema that a table resides in is irrelevant to the tablespace that it should be stored in. So default tablespaces on the schema are a bit of a pointless feature. The ability to have the features of schemas: default tablespace for given users, a GUC variable, and ACLs on tablespaces would be far more valuable. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Philip Warner [EMAIL PROTECTED] writes: At 08:00 AM 26/10/2004, Tom Lane wrote: I don't want a GUC variable that actively changes the default tablespace; at least not unless you want to abandon the current mechanisms for default tablespace choices entirely, and go over to making the GUC variable be the sole arbiter. Something consistent with Schemas does sound good to me; a tablespace search path (or just single default), and support for a TABLESPACE clause on table and INDEX definitions would be good. I can't see what a search path would be good for. For the three largest databases I work on, the namespace/schema that a table resides in is irrelevant to the tablespace that it should be stored in. So default tablespaces on the schema are a bit of a pointless feature. The ability to have the features of schemas: default tablespace for given users, a GUC variable, and ACLs on tablespaces would be far more valuable. Another nice thing is that not having default tablespaces associated with schemas eliminates that nasty issue about being able to drop such a tablespace while the schema is still there. It seems like we still need some notion of a database's schema, to put the system catalogs in, but perhaps that need not be the same as the default schema for user tables created in the database? I'd be willing to jump this way if we can work out the default-tablespace inconsistencies that Bruce has on the open items list. Does anyone want to draft a concrete proposal? It seems like the basic elements are: * A GUC variable named something like default_tablespace that controls which TS objects are created in when there's no explicit TABLESPACE clause. The factory default for this would of course be pg_default. Otherwise it's settable just like any other GUC var. * Get rid of TABLESPACE clause for CREATE SCHEMA, and pg_namespace.nsptablespace (ooops, another initdb). * Need to define exactly what TABLESPACE clause for a database controls; location of its catalogs of course, but anything else? * We could possibly say that a TABLESPACE clause attached to CREATE TABLE determines the default tablespace for indexes created by the same command; I'm not sure if this is a good idea, or if the indexes should go into default_tablespace absent a TABLESPACE clause attached directly to their defining constraints. We certainly want default_tablespace to control indexes created by separate commands, so there'd be some inconsistency if we do the former. 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: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 09:28 AM 26/10/2004, Tom Lane wrote: I can't see what a search path would be good for. Nothing at this stage. It seems like we still need some notion of a database's schema, Yes. I'd be willing to jump this way if we can work out the default-tablespace inconsistencies that Bruce has on the open items list. I'll have a look in the next 18 hours... * A GUC variable named something like default_tablespace that controls which TS objects are created in when there's no explicit TABLESPACE clause. The factory default for this would of course be pg_default. Otherwise it's settable just like any other GUC var. Agree. * Get rid of TABLESPACE clause for CREATE SCHEMA, and pg_namespace.nsptablespace (ooops, another initdb). Agree. * Need to define exactly what TABLESPACE clause for a database controls; location of its catalogs of course, but anything else? Nothing else would be my call; make it like the tablespace on tables. * We could possibly say that a TABLESPACE clause attached to CREATE TABLE determines the default tablespace for indexes created by the same command; This is a hard one. We need ALTER INDEX or STORE INDEX or whatever if we can't nicely put each index in it's own tablespace. We're only talking PKs aren't we? I'll have to think about this. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
On Mon, 25 Oct 2004, Tom Lane wrote: Philip Warner [EMAIL PROTECTED] writes: At 08:00 AM 26/10/2004, Tom Lane wrote: I don't want a GUC variable that actively changes the default tablespace; at least not unless you want to abandon the current mechanisms for default tablespace choices entirely, and go over to making the GUC variable be the sole arbiter. Something consistent with Schemas does sound good to me; a tablespace search path (or just single default), and support for a TABLESPACE clause on table and INDEX definitions would be good. I can't see what a search path would be good for. I agree. For the three largest databases I work on, the namespace/schema that a table resides in is irrelevant to the tablespace that it should be stored in. So default tablespaces on the schema are a bit of a pointless feature. The ability to have the features of schemas: default tablespace for given users, a GUC variable, and ACLs on tablespaces would be far more valuable. Another nice thing is that not having default tablespaces associated with schemas eliminates that nasty issue about being able to drop such a tablespace while the schema is still there. Hmmm.. despite that problem, I was rather fond of schema default tablespaces because they allow DBAs to set a policy for a particular schema. The cases I've discussed with people so far are things like creating a schema for a (closed source) application and associating that with a tablespace. There by, all new objects created will be in that tablespace without the need for DBA intervention. Its not necessary, but its nice I think. It seems like we still need some notion of a database's schema, to put the system catalogs in, but perhaps that need not be the same as the default schema for user tables created in the database? By schema here, do you mean tablespace? I'd be willing to jump this way if we can work out the default-tablespace inconsistencies that Bruce has on the open items list. Does anyone want to draft a concrete proposal? It seems like the basic elements are: * A GUC variable named something like default_tablespace that controls which TS objects are created in when there's no explicit TABLESPACE clause. The factory default for this would of course be pg_default. Otherwise it's settable just like any other GUC var. * Get rid of TABLESPACE clause for CREATE SCHEMA, and pg_namespace.nsptablespace (ooops, another initdb). * Need to define exactly what TABLESPACE clause for a database controls; location of its catalogs of course, but anything else? This could be a bit messy (from a user's point of view). There are two meanings (according to your plan): 1) the tablespace clause is the default for the catalogs AND for newly created objects (we set default_tablespace in datconfig); OR, 2) it only sets the tablespace for the catalogs. (You could say that it just sets the default tablespace for new objects, but then how do you set the catalog tablespace). I guess (1) makes sense but it limits people. If we do (2), we have two options: a) User needs to ALTER DATABASE SET default_table.. b) we add a new key work. I think (b) is ugly. * We could possibly say that a TABLESPACE clause attached to CREATE TABLE determines the default tablespace for indexes created by the same command; I'm not sure if this is a good idea, or if the indexes should go into default_tablespace absent a TABLESPACE clause attached directly to their defining constraints. We certainly want default_tablespace to control indexes created by separate commands, so there'd be some inconsistency if we do the former. I think a viable solution is to go with the latter (ie, for CREATE TABLE foo(i int primary key) TABLESPACE ts; the index on i is created in default_tablespace). However, I might be nice to be able to specify the tablespace as part of the primary key clause. I say nice, but not necessary. regards, tom lane Thanks, Gavin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Gavin Sherry [EMAIL PROTECTED] writes: Hmmm.. despite that problem, I was rather fond of schema default tablespaces because they allow DBAs to set a policy for a particular schema. The cases I've discussed with people so far are things like creating a schema for a (closed source) application and associating that with a tablespace. There by, all new objects created will be in that tablespace without the need for DBA intervention. Its not necessary, but its nice I think. On the other hand, driving it from a GUC variable would allow you to easily set a per-user default, which might be at least as useful. It seems like we still need some notion of a database's schema, to put the system catalogs in, but perhaps that need not be the same as the default schema for user tables created in the database? By schema here, do you mean tablespace? Sorry, fingers faster than brain obviously. Time to take a break... I think a viable solution is to go with the latter (ie, for CREATE TABLE foo(i int primary key) TABLESPACE ts; the index on i is created in default_tablespace). However, I might be nice to be able to specify the tablespace as part of the primary key clause. I say nice, but not necessary. We already have that don't we? create table foo (f1 int, primary key (f1) using index tablespace its) tablespace tts; The question is where to put foo_pkey when using index tablespace isn't there but tablespace is. (BTW, since we stole that syntax from Oracle, maybe we should check what they do...) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
On Mon, 25 Oct 2004, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: Hmmm.. despite that problem, I was rather fond of schema default tablespaces because they allow DBAs to set a policy for a particular schema. The cases I've discussed with people so far are things like creating a schema for a (closed source) application and associating that with a tablespace. There by, all new objects created will be in that tablespace without the need for DBA intervention. Its not necessary, but its nice I think. On the other hand, driving it from a GUC variable would allow you to easily set a per-user default, which might be at least as useful. It seems like we still need some notion of a database's schema, to put the system catalogs in, but perhaps that need not be the same as the default schema for user tables created in the database? By schema here, do you mean tablespace? Sorry, fingers faster than brain obviously. Time to take a break... I think a viable solution is to go with the latter (ie, for CREATE TABLE foo(i int primary key) TABLESPACE ts; the index on i is created in default_tablespace). However, I might be nice to be able to specify the tablespace as part of the primary key clause. I say nice, but not necessary. We already have that don't we? create table foo (f1 int, primary key (f1) using index tablespace its) tablespace tts; The question is where to put foo_pkey when using index tablespace isn't there but tablespace is. Hah. I wasn't sure if that ever got in -- guess I should have checked. (BTW, since we stole that syntax from Oracle, maybe we should check what they do...) As an aside -- I'm not quite sure we stole the syntax from Oracle. Oracle has *a lot* more functionality and nothing like the parent's tablespace system. Basically, more than one database object can be stored in a single data file in oracle. A tablespace is a group of such files. You can have two files in a tablespace in diferent locations. That is, tablespace foo might consist of /data1/a.dat and /data2/b.dat. So, when you create a new database, you can determine where the 'system catalogs' are by setting the datafile location for the system catalog tablespaces. You can *also* set a default tablespace for the database -- default in the sense that all newly created objects with no explicit tablespace clause are created in the tablespace. With an exception as follows: Oracle relies heavily on the concept of a user's default tablespace. Interestingly, this is what you just mentioned above :-). 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: [HACKERS] Using ALTER TABLESPACE in pg_dump
Gavin Sherry [EMAIL PROTECTED] writes: [ ... in Oracle: ] So, when you create a new database, you can determine where the 'system catalogs' are by setting the datafile location for the system catalog tablespaces. You can *also* set a default tablespace for the database -- default in the sense that all newly created objects with no explicit tablespace clause are created in the tablespace. With an exception as follows: Oracle relies heavily on the concept of a user's default tablespace. Interestingly, this is what you just mentioned above :-). So if we went with a GUC-driven approach, we could emulate both of those things easily, because people could set the default_tablespace GUC variable in either ALTER DATABASE or ALTER USER. This is starting to sound like a win. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Philip Warner wrote: At 09:28 AM 26/10/2004, Tom Lane wrote: I can't see what a search path would be good for. Nothing at this stage. The idea of a tablespace search path was that restores could specify a fallback if the tablespace doesn't exist, but it seems easier for the SET to just fail because the tablespace doesn't exist and the object goes into the default location. -- 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: [HACKERS] Using ALTER TABLESPACE in pg_dump
Tom Lane wrote: I think a viable solution is to go with the latter (ie, for CREATE TABLE foo(i int primary key) TABLESPACE ts; the index on i is created in default_tablespace). However, I might be nice to be able to specify the tablespace as part of the primary key clause. I say nice, but not necessary. We already have that don't we? create table foo (f1 int, primary key (f1) using index tablespace its) tablespace tts; The question is where to put foo_pkey when using index tablespace isn't there but tablespace is. I think that lacking a tablespace clause in the index section the behavior of least surprise would be to use the outer tablespace specification if it exists, and if not use the GUC variable for the tablespace (basically the tablespace of the table for the index). We already name the tablespace using our own name if we create it as part of CREATE TABLE so it seems natural to also use the tablespace of the table. The idea that a non-specified value defaults to the outer level's default (tablespace) is a natural behavior people expect. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
I have read through this thread hoping that a solution would be found but I see we are still poking. My ideas: o Anything that works only for pg_restore and hence doesn't work for ASCII dumps isn't an acceptable solution o Creating the tablespaces before the dump is restored is a good solution for moving tablespaces, but as Tom pointed out, it doesn't work well for non-super-user restores o Moving the indexes can't be dont easily after they are created because they are not zero-length files o The soft-failure GUC option for non-existant tablespaces is a hack just for use by pg_dump. It doesn't fix the problem that the tablespace clause makes the SQL nonstandard. And the best quote from the thread: Philip Warner wrote: soapbox A fact I positively loath! Relying on the 'bluder-on-regardless' approach is not something I'd like to enshrine. /soapbox The 'bluder-on-regardless' phrase is very funny. --- Philip Warner wrote: At 05:41 AM 21/10/2004, Robert Treat wrote: I've been thinking of an alternative solution that sounds very similar to this. The idea is to output the CREATE TABLESPACE commands inside pg_dump ... 1) tablespace already exists to fail, but since we no longer stop on error during restore, soapbox A fact I positively loath! Relying on the 'bluder-on-regardless' approach is not something I'd like to enshrine. /soapbox 2) if you have to restore on a machine with a different disk layout, give pg_restore a --override-tablespace command, which would substitute pg_default tablespace into the creation command of any tablespaces that get passed in. The bonus is that we would only have to parse on one specific command rather than worry about parsing several different commands. ... I'm still inclined to avoid any parsing if at all possible. We should hit the code that generates the definitions (90% in pg_dump) and turn the definitions into more intelligent templates. ... I was thinking that you could create two logical tablespaces on the same physical directory. This is basically the virtual/fake tablespace idea. Sometimes I think it is worth stepping back from a problem and ask what would we do if we had a clean slate, then use that to inform our current set of decisions. I'd be very interested in other people's ideas, but my thinking is: - we might not have a tablespace clause inside object definitions; we could add a STORE TABLE xxx IN TABLESPACE ts statement. Not really practical now; but worth bearing in mind as an approach for future non-standard syntax. - we'd have a full set of ALTER xxx SET TABLESPACE commands which moved relevant data etc. We have most of them. - pg_dump would issue the alter commands after creating the object; OK, it moves files, not very efficient, but using a 'SET MAGIC_TABLESPACE_VAR' has previously been discounted as a solution. *If* you accept this as a good approach in an ideal world, then I think we need to ask ourselves if we should implement the remaining ALTER commands in 8.0 and be done with it. The other solutions: magic-tablespace-var, virtual-tablespaces...all seem to add clunky functionality that will only be used in pg_dump. If we're going to add something, I'd prefer not to add clutter. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 12:38 PM 25/10/2004, Bruce Momjian wrote: o Anything that works only for pg_restore and hence doesn't work for ASCII dumps isn't an acceptable solution Agree; but don't forget that an ascii dump is implemented almost identically to pg_dump | pg_restore, so when I refer to using pg_restore in this thread it almost certainly applies to ascii dumps as well. Eg. extra stuff in the TOC, and using the definition as a template *will* produce the requested output in ascii dumps. o Creating the tablespaces before the dump is restored is a good solution for moving tablespaces, but as Tom pointed out, it doesn't work well for non-super-user restores And for users who want to create a single database with no extra tablespaces (eg. development version vs. production instance). o Moving the indexes can't be dont easily after they are created because they are not zero-length files Pity. o The soft-failure GUC option for non-existant tablespaces is a hack just for use by pg_dump. It doesn't fix the problem that the tablespace clause makes the SQL nonstandard. If we can adopt the move-after-create solution, then we really only have two options: - virtual tablespaces (which do seem kind of useful, especially for development vs. production config where the local/personal dev version can use the same script as a production DB but not need half a dozen TSs) - magic-tablespace-var that behaves like the schema search path Are there any others? And the best quote from the thread: Philip Warner wrote: soapbox A fact I positively loath! Relying on the 'bluder-on-regardless' approach is not something I'd like to enshrine. /soapbox The 'bluder-on-regardless' phrase is very funny. Fame at last! Even with the typo. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
On Tue, 2004-10-19 at 21:06, Philip Warner wrote:
At 04:20 AM 20/10/2004, Tom Lane wrote:
Nope. I can break that trivially, eg:
Thats why in my first message I mentioned escaping and unescaping all '%'
in the deinition.
There's also the nontrivial matter of how pg_dump would decide where to
insert the %%tablespace%% string into the CREATE INDEX command in the
first place.
I'd vote against parsing, and add a parameter to get_indexdef.
If we're going to add code to parse CREATE INDEX and
insert the tablespace in the correct place, meseems it'd be better to
insert it on the pg_restore side.
But if we have to parse, I'd add it in pg_dump so all items that are
relevant can be dumped with '%%tablespace%%'. pg_dump still constructs
CREATE TABLE statements, so that is the natural place to add the tablespace
marker and avoid parsing for tables.
I've been thinking of an alternative solution that sounds very similar
to this. The idea is to output the CREATE TABLESPACE commands inside
pg_dump to guarantee that all tablespaces used by schema objects would
exist. A couple trouble scenarios would be
1) tablespace already exists - this causes the CREATE TABLESPACE command
to fail, but since we no longer stop on error during restore, the
restore can continue and subsequent object creation should be fine.
2) if you have to restore on a machine with a different disk layout,
give pg_restore a --override-tablespace command, which would substitute
pg_default tablespace into the creation command of any tablespaces that
get passed in. The bonus is that we would only have to parse on one
specific command rather than worry about parsing several different
commands. This would allow the tablespace to exist, so any subsequent
commands referring to it would not fail.
** update **
While writing up this email I tried to explain the idea to Kris Jurka on
irc, and may have found fatal flaw... I was thinking that you could
create two logical tablespaces on the same physical directory. So that
tablespace fred and wilma could both be at the same location as
pg_default, but according to the docs I'm not sure this is true:
The directory that will be used for the tablespace. The directory must
be empty and must be owned by the PostgreSQL system user. The directory
must be specified by an absolute path name.
OTOH looking at a copy of an 8.0 database I see the following:
template1=# select * from pg_tablespace;
spcname | spcowner | spclocation | spcacl
+--+-+
pg_default |1 | |
pg_global |1 | |
(2 rows)
so istm there is nothing preventing pg_tablespace from having multiple
spcname using the same spclocation, however the create tablespace
command certainly wont allow it as it stands now. I'm not entirely sure
why we couldn't allow CREATE TABLESPACE to accept a variable of
pg_default which mean to just create the tablespace logically but use
the same physical location of pg_default... or possibly some other
workable solution... istm the idea is 90% of the way there, perhaps
someone can see the last few parts?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 05:41 AM 21/10/2004, Robert Treat wrote: I've been thinking of an alternative solution that sounds very similar to this. The idea is to output the CREATE TABLESPACE commands inside pg_dump ... 1) tablespace already exists to fail, but since we no longer stop on error during restore, soapbox A fact I positively loath! Relying on the 'bluder-on-regardless' approach is not something I'd like to enshrine. /soapbox 2) if you have to restore on a machine with a different disk layout, give pg_restore a --override-tablespace command, which would substitute pg_default tablespace into the creation command of any tablespaces that get passed in. The bonus is that we would only have to parse on one specific command rather than worry about parsing several different commands. ... I'm still inclined to avoid any parsing if at all possible. We should hit the code that generates the definitions (90% in pg_dump) and turn the definitions into more intelligent templates. ... I was thinking that you could create two logical tablespaces on the same physical directory. This is basically the virtual/fake tablespace idea. Sometimes I think it is worth stepping back from a problem and ask what would we do if we had a clean slate, then use that to inform our current set of decisions. I'd be very interested in other people's ideas, but my thinking is: - we might not have a tablespace clause inside object definitions; we could add a STORE TABLE xxx IN TABLESPACE ts statement. Not really practical now; but worth bearing in mind as an approach for future non-standard syntax. - we'd have a full set of ALTER xxx SET TABLESPACE commands which moved relevant data etc. We have most of them. - pg_dump would issue the alter commands after creating the object; OK, it moves files, not very efficient, but using a 'SET MAGIC_TABLESPACE_VAR' has previously been discounted as a solution. *If* you accept this as a good approach in an ideal world, then I think we need to ask ourselves if we should implement the remaining ALTER commands in 8.0 and be done with it. The other solutions: magic-tablespace-var, virtual-tablespaces...all seem to add clunky functionality that will only be used in pg_dump. If we're going to add something, I'd prefer not to add clutter. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Philip Warner [EMAIL PROTECTED] writes: To solve this, we should dump the table definition as a format string and dump the tablespace clause (ie. ' TABLESPACE ts') as a separate part of the table definition TOC entry. If the user wants the tablespace to be dumped, then we substitute the tablespace clause, otherwise a blank string. This could be a useful general approach in the future. I think the tricky part of that would be inserting the tablespace clause in the right place; for CREATE INDEX this seems to require nontrivial parsing. (Both the index column definitions and the WHERE clause could be arbitrarily complicated expressions.) If we can get around that part then this wouldn't be too hard. Also, I like the option of a soft-tablespace option, but also liked the idea of the fake/logical/virtual tablespaces someone suggested earlier; if restoring into a database without a required tablespace, then create a virtual tablespace that points to pg_default. Given that tablespaces are fundamentally only directories, there isn't any particularly strong reason to not just make a real tablespace. You aren't going to constrain space allocation or anything by having another directory in/alongside $PGDATA. So I think the virtual tablespace idea is basically pointless. The real crux of all this, I think, is what if I want to restore as a non-superuser, and so I don't have privilege to create tablespaces to match what the dump wants? The soft-failure option provides an answer here, but creating either real or virtual tablespaces wouldn't fly. A --notablespace option in pg_restore would solve it too, but only if you'd done an -Fc or -Ft dump; with a plain text dump you still got trouble. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 03:06 AM 20/10/2004, Tom Lane wrote: I think the tricky part of that would be inserting the tablespace clause in the right place; for CREATE INDEX this seems to require nontrivial parsing. (Both the index column definitions and the WHERE clause could be arbitrarily complicated expressions.) If we can get around that part then this wouldn't be too hard. I may be missing something here; I was assuming that pg_dump would dump would build the CREATE INDEX/TABLE/etc commands with the %%tablespace%% already embedded. pg_restore would not need to do any parsing. Or is there something I don't understand? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Philip Warner [EMAIL PROTECTED] writes: At 03:06 AM 20/10/2004, Tom Lane wrote: I think the tricky part of that would be inserting the tablespace clause in the right place; for CREATE INDEX this seems to require nontrivial parsing. (Both the index column definitions and the WHERE clause could be arbitrarily complicated expressions.) If we can get around that part then this wouldn't be too hard. I may be missing something here; I was assuming that pg_dump would dump would build the CREATE INDEX/TABLE/etc commands with the %%tablespace%% already embedded. pg_restore would not need to do any parsing. Or is there something I don't understand? Maybe there's something I don't understand. How are you expecting pg_restore to control whether it outputs the command with a TABLESPACE clause embedded or not, if pg_dump has already built the command string that way? I thought you were envisioning that pg_restore would insert, or not insert, a TABLESPACE clause into a command that didn't initially have one. 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] Using ALTER TABLESPACE in pg_dump
At 03:25 AM 20/10/2004, Tom Lane wrote: Maybe there's something I don't understand. How are you expecting pg_restore to control whether it outputs the command with a TABLESPACE clause embedded or not, if pg_dump has already built the command string that way? This will only work if we modify the dump format (a new version) of dump/restore; the TOC entry for a table would have: DEFINITION: CREATE TABLE fred ... %%tablespace%% ... TABLESPACE: ' TABLESPACE t' pg_restore would read these, and use the settings from the command line to either substitute an empty string or the TABLESPACE text for %%tablespace%% in the DEFINTION. Same would apply for indexes etc. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Philip Warner [EMAIL PROTECTED] writes: DEFINITION: CREATE TABLE fred ... %%tablespace%% ... TABLESPACE: ' TABLESPACE t' pg_restore would read these, and use the settings from the command line to either substitute an empty string or the TABLESPACE text for %%tablespace%% in the DEFINTION. Nope. I can break that trivially, eg: CREATE INDEX fooi ON foo (f1) WHERE upper(f1) ' %%tablespace%%'; Not very probable, maybe, but you can't just do a blind sed-style substitution. There's also the nontrivial matter of how pg_dump would decide where to insert the %%tablespace%% string into the CREATE INDEX command in the first place. If we're going to add code to parse CREATE INDEX and insert the tablespace in the correct place, meseems it'd be better to insert it on the pg_restore side. 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] Using ALTER TABLESPACE in pg_dump
At 04:20 AM 20/10/2004, Tom Lane wrote: Nope. I can break that trivially, eg: Thats why in my first message I mentioned escaping and unescaping all '%' in the deinition. There's also the nontrivial matter of how pg_dump would decide where to insert the %%tablespace%% string into the CREATE INDEX command in the first place. I'd vote against parsing, and add a parameter to get_indexdef. If we're going to add code to parse CREATE INDEX and insert the tablespace in the correct place, meseems it'd be better to insert it on the pg_restore side. But if we have to parse, I'd add it in pg_dump so all items that are relevant can be dumped with '%%tablespace%%'. pg_dump still constructs CREATE TABLE statements, so that is the natural place to add the tablespace marker and avoid parsing for tables. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Using ALTER TABLESPACE in pg_dump
One additional idea for this item is to use CREATE to first create the object, then move it using ALTER, and the ALTER might fail if the tablespace doesn't exist. The only problem with that is this TODO item: o Allow databases and schemas to be moved to different tablespaces One complexity is whether moving a schema should move all existing schema objects or just define the location for future object creation. If we add a new SET variable and use it in pg_dump we will have to support it forever even if there is no practical use for it. (ALTER was originally part of the open item but I removed it thinking we might not have space to load the table in the default location, but I forgot we create it empty and could move it before we load it.) One interesting side-affect of allowing tablespace specification to fail is that it might give users enough control that we can mark this item as done: * Allow database recovery where tablespaces can't be created When a pg_dump is restored, all tablespaces will attempt to be created in their original locations. If this fails, the user must be able to adjust the restore process. One idea would be that users could create any tablespaces or objects they want to change before they do the restore and the restore would use their new configuration and just error/skip the items they already created. (This would also behave well if you load the dump and say stop on any errors.) For example, if you want to eliminate a tablespace, you just don't create the directory and load your dump. Tablespace create will fail, and all objects that use that tablespace will fail their ALTER and will remain in their default locations. This actually seems less error-prone than the idea of them manually changing things in the dump file. --- Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: o remove non-portable TABLESPACE clause from CREATE TABLE and use a new default_tablespace SET variable I'm coming around to the conclusion that this is simply a bad idea. The problem with having such a SET variable is that it plays hob with the existing definition about where schemas and tables get a default tablespace from. Which source wins (the database or schema default tablespace, or the SET variable)? And why? The only really clean way to have a SET variable for this is to forget about schema- or table-based defaults. Do we want to do that? (Hey, it'd solve the problem with schema tablespaces being droppable, because there wouldn't *be* any such thing as a schema's tablespace anymore. But on the whole this seems like a step backward in usability.) Agreed, a step backwards, but see below. What we might want to do is invent a --notablespace option for pg_dump, comparable to --noowner, to let someone make a dump that contains no TABLESPACE clauses. Yea, that would work, but we went through so much work to allow SQL standard DDL statements, and it seems a shame to break it just for tablespaces. And, having it be a separate SET would also allow the tablespace creation to fail and still get the objects created. (If the explicit_tablespace doesn't exist during CREATE, we throw a warning. This would contrast with a create _failure_ when the tablespace doesn't exist and you say 'TABLESPACE t1' in CREATE.) So there were actually two uses for this, one for standards compliance, and the other was for flexibility in restoring to a system where the tablespaces can't be created. The SET could give us different behavior (warning vs. error) which would be useful for pg_dump. Could we call it explicit_tablespace and when it is , it is the default, but when it isn't it is just like using 'TABLESPACE t1' in the CREATE, but throws a warning instead of an error if the tablespace doesn't exist? My assumption is that it would not be like the default_with_oids variable usage by pg_dump because it would be reset to '' (default) by pg_dump after each time it is used. I assume explicit_tablespace would always override the schema or database tablespace because it is explicit. In fact this would partially fix the TODO we have: * Allow database recovery where tablespaces can't be created When a pg_dump is restored, all tablespaces will attempt to be created in their original locations. If this fails, the user must be able to adjust the restore process. -- Bruce Momjian| http://candle.pha.pa.us [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 1:
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Bruce Momjian [EMAIL PROTECTED] writes: One additional idea for this item is to use CREATE to first create the object, then move it using ALTER, and the ALTER might fail if the tablespace doesn't exist. This seems fairly impractical, at least for indexes where there is no way to do the ALTER before the object is filled with data. If we add a new SET variable and use it in pg_dump we will have to support it forever even if there is no practical use for it. Yeah, that's one thing that bothers me. One interesting side-affect of allowing tablespace specification to fail is that it might give users enough control that we can mark this item as done: Hmm, here's a variant idea: how about a GUC variable named something like soft_tablespace_specs which when TRUE would mean that a nonexistent tablespace name in a TABLESPACE clause is ignored (maybe with a WARNING) rather than being an error, and so the object is created in whatever the default tablespace for it would be. You wouldn't even necessarily want to have pg_dump set this true for itself, but people could turn it on when they needed to load a dump with wrong tablespace names in it. (If we didn't have pg_dump turn it on automatically, then we'd not be beholden to support it forever.) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: One additional idea for this item is to use CREATE to first create the object, then move it using ALTER, and the ALTER might fail if the tablespace doesn't exist. This seems fairly impractical, at least for indexes where there is no way to do the ALTER before the object is filled with data. If we add a new SET variable and use it in pg_dump we will have to support it forever even if there is no practical use for it. Yeah, that's one thing that bothers me. One interesting side-affect of allowing tablespace specification to fail is that it might give users enough control that we can mark this item as done: Hmm, here's a variant idea: how about a GUC variable named something like soft_tablespace_specs which when TRUE would mean that a nonexistent tablespace name in a TABLESPACE clause is ignored (maybe with a WARNING) rather than being an error, and so the object is created in whatever the default tablespace for it would be. You wouldn't even necessarily want to have pg_dump set this true for itself, but people could turn it on when they needed to load a dump with wrong tablespace names in it. (If we didn't have pg_dump turn it on automatically, then we'd not be beholden to support it forever.) That's a nice idea in that it doesn't require a SET for every object that uses tablespace, and allows user control over tablespace failure. The only downside is that it prevents SQL-compliant CREATE syntax in dumps. I was thinking you could use ALTER just for tables but then the tablespace failure rules would be different for tables and other objects, which is unacceptable. -- 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: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 03:15 AM 19/10/2004, Bruce Momjian wrote: The only downside is that it prevents SQL-compliant CREATE syntax in dumps. One idea that may be worth considering: we currently dump a complete SQL statement including a TABLESPACE clause, which makes it hard to allow pg_restore to have a 'no tablespaces' option (it is fixed by pg_dump). To solve this, we should dump the table definition as a format string and dump the tablespace clause (ie. ' TABLESPACE ts') as a separate part of the table definition TOC entry. If the user wants the tablespace to be dumped, then we substitute the tablespace clause, otherwise a blank string. This could be a useful general approach in the future. The main issue will be quoting the clause identifiers (if we use '%%tablespace%%' then we have to handle columns or tables whose names contain '%%tablespace%%'. Simplest would be to backslash quote all '%' and '\' then do subs. Also, I like the option of a soft-tablespace option, but also liked the idea of the fake/logical/virtual tablespaces someone suggested earlier; if restoring into a database without a required tablespace, then create a virtual tablespace that points to pg_default. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
