Re: [HACKERS] tablespace and sequences?
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Has anyone tested inheritance with tablespaces? ie. child in different tablespace to parent, select query that goes over both...? They're at completely different levels of the system ... I'd be as surprised to hear of a bug here as to hear that integer addition fails if the operands are from different tablespaces. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] tablespace and sequences?
Dear Philip, Actually I was thinking of a little more than a setting to ignore errors; we would need to: - modify pg_dump to store the tablespace name as a separate part of the TOC entry, NOT as part of the CREATE TABLE. - modify pg_restore to issue 'set default tablespace ' before restoring a table OR, per Fabiens suggestion, issue an ALTER TABLE after the create. This prior SET option looks much better and cleaner. Maybe the TOC entry update is not really necessary if the SET is separate? If the SET fails, what tablespace is expected to be chose? pg_global? I guess the SET would be mandatory, that is it would supercede other defaults such as chose the tablespace of the SCHEMA? Also, should there be provision for unsetting? I can give a hand about the implementation over the week-end, esp. as I'm the one taking a stand on this issue. However I do not know much about pg_dump format and issues, so I'm not sure I'm the best person for a quick and clean implementation. Have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] tablespace and sequences?
At 06:14 PM 20/08/2004, Fabien COELHO wrote: This prior SET option looks much better and cleaner. Maybe the TOC entry update is not really necessary if the SET is separate? I'd prefer if it was separate since we want to minimize the number of multi-statement TOC entries...I think. A new TOC entry is close to zero cost. Reformatting the TOC to include the tablespace name is more expensive, but there are a few things I'd like to add, so it's worth it. If the SET fails, what tablespace is expected to be chose? Good question. Is there a name for the normal/default/whatever tablespace? Tom may need to implement: SET DEFAULT TABLESPACE AS FRED SET DEFAULT TABLESPACE DEFAULT or something less tacky, but allowing for the default to be derived from the schema database rather than the last SET command. The pg_dump will need to check the result of the SET command and reset the tablespace if it fails...and probably die if that fails. I can give a hand about the implementation over the week-end, esp. as I'm the one taking a stand on this issue. However I do not know much about pg_dump format and issues, so I'm not sure I'm the best person for a quick and clean implementation. I'm happy to do the pg_dump changes, assuming Tom gets the SET stuff sorted out. But would appreciate it if you could do some testing. 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] tablespace and sequences?
Dear Philip, I can give a hand about the implementation over the week-end, [...] I'm happy to do the pg_dump changes, assuming Tom gets the SET stuff sorted out. But would appreciate it if you could do some testing. Ok. Just tell me. As European/American/Asian timezones are involved, it can go around the clock. Good night, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tablespace and sequences?
At 03:14 PM 20/08/2004, Tom Lane wrote: If we attempt to reload this mess with a different default tablespace for the parent object, what happens to the child in each case? ISTM that for a table create with CREATE TABLE...TABLESPACE we should try to preserve the tablespace when doing a dump/restore. If the table 'inherited' it's tablespace, then a dump/restore should do nothing (ie. not issue a SET TABLESPACE). 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] tablespace and sequences?
If the location doesn't exist will postgresql try to create it? istm it could do this and if it fails then you are no worse off, but if it were to succeed you're that much better off. Yea, I assume if you can't create the tablespace you put everything for that tablespace in the default tablespace. If your talking about a restoration, the answer is NO. It just fails, because on CREATE TABLE foo ... TABLESPACE bla the table will not be created if tablespace bla does not exists, and so the restoration will fail. This is the current situation, and that's why I'm arguing in the void;-) -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tablespace and sequences?
Dear Bruce, Bruce - pg_dump TODO for --no-tablespace or something? Uh, TODO already has: * 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. Sure. I was advocating for this TODO item to be moved to the beta TODO for coming 8.0, so it would be for the other list... -- Fabien Coelho - [EMAIL PROTECTED] ---(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] tablespace and sequences?
In point of fact I think this discussion is much ado about nothing, as there is already a workaround Just call that a kludge as it means that the admin is expected to create as many dummy and unknown (if you have a custom dump file) tablespaces as necessary to please pg_restore. These useless tablespaces just create a mess in the database, that I will have to clean afterwards... if I can! Then bad news, ISTM that altering the tablespace of an index, a sequence or a schema is not implemented. So I'll have to move the files and links around, and update manually the catalog entries, or possibly drop and recreate all indexes... I hope I won't have large objects around, because it might look really bad then. What a nice piece of restoration;-) So basically I'll have created stupid directories and tablespaces and there is no way to fix them afterwards even if they are meaningless:-( that is about as simple as anything that we would likely be able to substitute. I really think a better job can and should be done, at least from the user perspective. -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] tablespace and sequences?
Fabien COELHO [EMAIL PROTECTED] writes: Just call that a kludge as it means that the admin is expected to create as many dummy and unknown (if you have a custom dump file) tablespaces There are any number of ways to find it out --- read the output of pg_restore -s, or just try the restore and observe the errors. Besides which, we are talking here about the output of pg_dumpall, which is currently always text. Then bad news, ISTM that altering the tablespace of an index, a sequence or a schema is not implemented. Wrong, unnecessary, and trivial respectively. I see this request as being exactly on a par with requests to make pg_dumpall output restore into a different set of databases, or into a different set of schemas than what was dumped from. Sure, it would be convenient sometimes. But it's not *necessary* and it's not something to be starting in on when we're already well into beta. Could we have less straw-man-bashing and more discussion of the minimum necessary solution for this problem? It's long past time to be gilding the lily for 8.0. You can give it a new paint job in 8.1, if you like. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] tablespace and sequences?
At 12:21 AM 20/08/2004, Tom Lane wrote: You can give it a new paint job in 8.1, if you like. To side-step the issue, is there a tablespace equivalent of a default schema? Could we 'set default tablespace xxx', then have pg_dump/restore use a 'create table' that does not refer to the tablespace? 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] tablespace and sequences?
Tom Lane wrote: Fabien COELHO [EMAIL PROTECTED] writes: Just call that a kludge as it means that the admin is expected to create as many dummy and unknown (if you have a custom dump file) tablespaces There are any number of ways to find it out --- read the output of pg_restore -s, or just try the restore and observe the errors. Besides which, we are talking here about the output of pg_dumpall, which is currently always text. Then bad news, ISTM that altering the tablespace of an index, a sequence or a schema is not implemented. Wrong, unnecessary, and trivial respectively. I see this request as being exactly on a par with requests to make pg_dumpall output restore into a different set of databases, or into a different set of schemas than what was dumped from. Sure, it would be convenient sometimes. But it's not *necessary* and it's not something to be starting in on when we're already well into beta. I don't think it is the same because a dump can be restored on any system. This is a case where the operating system has to be set up for the restore to work completely. Could we have less straw-man-bashing and more discussion of the minimum necessary solution for this problem? It's long past time to be gilding the lily for 8.0. You can give it a new paint job in 8.1, if you like. You certainly can argue that it is too late to be doing this during beta. I always felt this was a feature we needed for 8.0 personally but the urgency among the group is coming pretty late. -- 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] tablespace and sequences?
Philip Warner wrote: At 12:21 AM 20/08/2004, Tom Lane wrote: You can give it a new paint job in 8.1, if you like. To side-step the issue, is there a tablespace equivalent of a default schema? Could we 'set default tablespace xxx', then have pg_dump/restore use a 'create table' that does not refer to the tablespace? That is what I was assuming. You can't retroactively change the dump file during restore so we would have some SET varaiable you would set before doing the restore that said to handle create tablespace errors. -- 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] tablespace and sequences?
At 02:33 AM 20/08/2004, Bruce Momjian wrote: Could we 'set default tablespace xxx', then have pg_dump/restore use a 'create table' that does not refer to the tablespace? That is what I was assuming. You can't retroactively change the dump file during restore so we would have some SET varaiable you would set before doing the restore that said to handle create tablespace errors. Actually I was thinking of a little more than a setting to ignore errors; we would need to: - modify pg_dump to store the tablespace name as a separate part of the TOC entry, NOT as part of the CREATE TABLE. - modify pg_restore to issue 'set default tablespace ' before restoring a table OR, per Fabiens suggestion, issue an ALTER TABLE after the create. Then table-space related errors will not stop a table being created. 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] tablespace and sequences?
Philip Warner wrote: At 02:33 AM 20/08/2004, Bruce Momjian wrote: Could we 'set default tablespace xxx', then have pg_dump/restore use a 'create table' that does not refer to the tablespace? That is what I was assuming. You can't retroactively change the dump file during restore so we would have some SET varaiable you would set before doing the restore that said to handle create tablespace errors. Actually I was thinking of a little more than a setting to ignore errors; we would need to: - modify pg_dump to store the tablespace name as a separate part of the TOC entry, NOT as part of the CREATE TABLE. - modify pg_restore to issue 'set default tablespace ' before restoring a table OR, per Fabiens suggestion, issue an ALTER TABLE after the create. Then table-space related errors will not stop a table being created. But that doesn't fix ascii dumps loaded via psql. -- 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] tablespace and sequences?
At 12:37 PM 20/08/2004, Bruce Momjian wrote: But that doesn't fix ascii dumps loaded via psql. It does; the ascii dump file is generated by exactly the same technique as pg_restore. Internally, pg_dump builds a TOC, then calls RestoreArchive to dump the text. It was designed this way for a bunch of reasons, and one was to avoid too much difference between the output of each format. Which is why it is very unlikely that pg_dump -Fc | pg_restore would produce output substantially different from that of pg_dump. So, as long as pg_dump puts the CREATE TABLE and ALTER TABLE in different commands, they will appear as such in the text file. 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] tablespace and sequences?
Philip Warner [EMAIL PROTECTED] writes: At 12:37 PM 20/08/2004, Bruce Momjian wrote: But that doesn't fix ascii dumps loaded via psql. It does; the ascii dump file is generated by exactly the same technique as pg_restore. Right. Philip's suggestion would essentially use the same technique that we previously adopted for portability of WITH/WITHOUT OIDS --- if the SET fails, it won't stop the table from being created. (Note we have to be careful that the semantics of the SET actually cause the error to occur on the SET and not later on the CREATE. But that's doable.) It seemed like a reasonable idea to me... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] tablespace and sequences?
At 01:09 PM 20/08/2004, Tom Lane wrote: It seemed like a reasonable idea to me... Do we have a SET DEFAULT TABLESPACE? Can we add one for this release? If not, we probably need to go with the ALTER TABLE. Although a SET DEFAULT TABLESPACE would be convenent in general. 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] tablespace and sequences?
On Fri, Aug 20, 2004 at 01:26:39PM +1000, Philip Warner wrote: At 01:09 PM 20/08/2004, Tom Lane wrote: It seemed like a reasonable idea to me... Do we have a SET DEFAULT TABLESPACE? Can we add one for this release? If not, we probably need to go with the ALTER TABLE. Although a SET DEFAULT TABLESPACE would be convenent in general. The problem with ALTER TABLE is that it can be hugely expensive, I think. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Thou shalt check the array bounds of all strings (indeed, all arrays), for surely where thou typest foo someone someday shall type supercalifragilisticexpialidocious (5th Commandment for C programmers) ---(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] tablespace and sequences?
Alvaro Herrera wrote: On Fri, Aug 20, 2004 at 01:26:39PM +1000, Philip Warner wrote: At 01:09 PM 20/08/2004, Tom Lane wrote: It seemed like a reasonable idea to me... Do we have a SET DEFAULT TABLESPACE? Can we add one for this release? If not, we probably need to go with the ALTER TABLE. Although a SET DEFAULT TABLESPACE would be convenent in general. The problem with ALTER TABLE is that it can be hugely expensive, I think. I was thinking that too, but I assume they are creating the table empty, moving it to another tablespace, then loading it. -- 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] tablespace and sequences?
Alvaro Herrera [EMAIL PROTECTED] writes: The problem with ALTER TABLE is that it can be hugely expensive, I think. As long as you did it before loading any data, it wouldn't be too bad. But certainly a preceding SET would be cheaper than pushing even zero-size files around. I don't have any problem with adding a SET variable at this stage of the game, if everyone agrees it's an appropriate solution. One point here is the handling of index tablespaces. I added TABLESPACE as part of pg_get_indexdef output, but we'd need a different solution if we want to go down this path. Maybe it's not a problem given this idea about where pg_dump is going to specify tablespace. But someone needs to take a close look at pg_dump's logic to see if this can work. 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] tablespace and sequences?
At 01:47 PM 20/08/2004, Tom Lane wrote: But someone needs to take a close look at pg_dump's logic to see if this can work. Not sure where the issues lie, but anything that can reside in a tablespace (table, index,...anything else?), needs to dump it's definition without reference to a tablespace, and pg_dump needs to be modified to dump the tablespace name in the TOC entry, and pg_restore needs to maintain 'current' tablespace the same way it does schemas. Backend then needs to obey the variable setting. What have I missed? I can do the pg_dump stuff if noone else wants to. 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] tablespace and sequences?
One point here is the handling of index tablespaces. I added TABLESPACE as part of pg_get_indexdef output, but we'd need a different solution if we want to go down this path. Maybe it's not a problem given this idea about where pg_dump is going to specify tablespace. But someone needs to take a close look at pg_dump's logic to see if this can work. Another parameter to pg_get_indexdef() :( Chris ---(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] tablespace and sequences?
What have I missed? I can do the pg_dump stuff if noone else wants to. I'm all of a sudden really busy :( Extra karate at nights + new responsibilities at work, so my plan on doing the stuff listed for pg_dump under TODO (specifically comments on index and composite type columns) is rather lagging. If you feel like doing those, let me know and I won't bother. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tablespace and sequences?
Christopher Kings-Lynne [EMAIL PROTECTED] writes: One point here is the handling of index tablespaces. I added TABLESPACE as part of pg_get_indexdef output, but we'd need a different solution if we want to go down this path. Another parameter to pg_get_indexdef() :( Actually I think we'd just revert the ruleutils.c change that showed TABLESPACE in pg_get_indexdef. The real question is to be sure that pg_dump could get along without it. If Philip wants to fix pg_dump, I'm content to just stay out of his way ;-) 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] tablespace and sequences?
At 02:27 PM 20/08/2004, Tom Lane wrote: Actually I think we'd just revert the ruleutils.c Just to confirm; it's only tables and indexes that have tablespaces, and I can issue some kind of SET command. Any idea of the syntax? As an aside: should a database be allowed to have a default tablespace? 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] tablespace and sequences?
Philip Warner [EMAIL PROTECTED] writes: Just to confirm; it's only tables and indexes that have tablespaces, and I can issue some kind of SET command. Any idea of the syntax? As an aside: should a database be allowed to have a default tablespace? Well, tables and indexes definitely have tablespaces. Schemas have default tablespaces that their child objects inherit, though there is no storage associated with the schema itself. Databases have default tablespaces that (a) their child objects inherit, and (b) the system catalogs of that database live in. We already have some TODO items about sorting out exactly how the defaulting behavior works here. In particular, what if anything is the difference between a child object inheriting a default tablespace TS, and explicitly saying TABLESPACE TS in its definition? If we attempt to reload this mess with a different default tablespace for the parent object, what happens to the child in each case? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tablespace and sequences?
Actually I think we'd just revert the ruleutils.c change that showed TABLESPACE in pg_get_indexdef. The real question is to be sure that pg_dump could get along without it. If Philip wants to fix pg_dump, I'm content to just stay out of his way ;-) Well my original patch did without it, someone can copy that code. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] tablespace and sequences?
We already have some TODO items about sorting out exactly how the defaulting behavior works here. In particular, what if anything is the difference between a child object inheriting a default tablespace TS, and explicitly saying TABLESPACE TS in its definition? If we attempt to reload this mess with a different default tablespace for the parent object, what happens to the child in each case? Has anyone tested inheritance with tablespaces? ie. child in different tablespace to parent, select query that goes over both...? Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] tablespace and sequences?
Dear Christopher, Allow database recovery where tablespaces can't be created How is that at all a problem? It is enough a small problem to be put in the todo list. It's no different to the requirement to have installed all your contrib .so's before running your restore, what's so hard about making a few dirs? It's also no different to the old database locations support. Personally, I think it's a non-issue. Well, maybe. I think the .so comparison is not fully appropriate, as the installation is quite generic an issue, possibly addressed by packaging or some scripting. As for pg_dump/pg_restore, they are performed at the database level. In the previous situation with LOCATION, one had to handle the issue of creating the database before a restoration. Now with tablespace the issue is more specific, and it is possibly embedded at the SQL level output by pg_dump/pg_restore, on which one has much less control. Well, maybe you suggest I can do some | sed 's/TABLESPACE \w+//g' | as a kludge somewhere, or create dummy tablespaces even if I have only one disk. That does not look really good, and I won't know what is needed by looking at a pg_dump compressed generated file. Maybe the right answer is that disks are now large and cheap, so who will need tablespace anyway? So indeed there is no problem;-) It's also impossible to do as you suggest and have a --ignore-tablespace flag. I was not arguing about implementation, but about a desirable feature for a basic database admin. Anyway, I think it could be implemented, possibly with some twicking in the format, or with some setting on the server side. Now I agree that any other feature which provide the ability to handle this non issue would be welcome, I won't stick on this particular option. All it could do is at dump time to dump NO tablespace, which is NOT what you want. Wow! you know what I may want although I even don't know;-) If I want to move a database from one server to another, I'm not sure the disk layout and tablespace issues will have been handled the same way on both machines. So some help to handle these issues would be welcome. At restore time it doesn't do anything since pg_dumpall is a text format only. I'm not thinking about pg_dumpall but pg_dump/pg_restore. Have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] tablespace and sequences?
At restore time it doesn't do anything since pg_dumpall is a text format only. I'm not thinking about pg_dumpall but pg_dump/pg_restore. Tablespaces are dumped by pg_dumpall, not pg_dump. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] tablespace and sequences?
I'm not thinking about pg_dumpall but pg_dump/pg_restore. Tablespaces are dumped by pg_dumpall, not pg_dump. If so, indeed it would be a non-issue. However, shell pg_dump coelho | grep TABLESPACE CREATE SCHEMA test AUTHORIZATION coelho TABLESPACE test; TABLESPACE appears in a basic pg_dump SQL output. If the test tablespace does not exist, the command will fail, and so my whole restoration. Thus I still stick to my opinion;-) Have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(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] tablespace and sequences?
shell pg_dump coelho | grep TABLESPACE CREATE SCHEMA test AUTHORIZATION coelho TABLESPACE test; TABLESPACE appears in a basic pg_dump SQL output. If the test tablespace does not exist, the command will fail, and so my whole restoration. Thus I still stick to my opinion;-) Your complaint was that you need a way of continuing a restore if the _tablespace_ cannot be created. ie. If the directory does not exist. If you have objects in a tablespace, then too bad. It's no different to if the schema the object in doesn't exist. Or the table the data is in doesn't exist. Or the functin the view references doesn't exist. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tablespace and sequences?
Dear Christopher, TABLESPACE appears in a basic pg_dump SQL output. If the test tablespace does not exist, the command will fail, and so my whole restoration. Thus I still stick to my opinion;-) Your complaint was that you need a way of continuing a restore if the _tablespace_ cannot be created. ie. If the directory does not exist. Indeed it is possible that I was not clear enough! The issue I feel should be addressed is the ability to restore a database while ignoring tablespace issues, not only their creation but also their uses. If you have objects in a tablespace, then too bad. Well, ISTM that it is the problem I'm discussing... If I cannot restore a base I see that as a problem, which is indeed a lack of humour from my side. It's no different to if the schema the object in doesn't exist. Or the table the data is in doesn't exist. Or the functin the view references doesn't exist. It is a little bit different because a schema, a table or a function are database application issues and are normally addressed by pg_dump and pg_restore, although tablespaces are more an administration issue wrt disk layout and the like, which are likely to be different from one machine to another (compare with I obviously want the same schema/table/function for my application). So the notion of dump/restore of a tablespace need some careful thinking. But maybe I'm just stupid to dream that I could restore or transfer my data even if I used a tablespace somewhere? ;-) It looks that we don't have the same perspective about database administration. Anyway, have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(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] tablespace and sequences?
On Wednesday 18 August 2004 04:39, Christopher Kings-Lynne wrote:
shell pg_dump coelho | grep TABLESPACE
CREATE SCHEMA test AUTHORIZATION coelho TABLESPACE test;
TABLESPACE appears in a basic pg_dump SQL output. If the test
tablespace does not exist, the command will fail, and so my whole
restoration.
Thus I still stick to my opinion;-)
Your complaint was that you need a way of continuing a restore if the
_tablespace_ cannot be created. ie. If the directory does not exist.
If you have objects in a tablespace, then too bad. It's no different to
if the schema the object in doesn't exist. Or the table the data is in
doesn't exist. Or the functin the view references doesn't exist.
Chris, help me understand this will you? On my production system I have a few
very large tables I want to move into their own tablespace so I can but them
a a very large disk, and a couple frequently updated tables I would like to
move into their own tablespace so i can put them on their own (small, raid
oriented) disk. I need to do all this from a physical side of things for
performance and administration in production, but when I create test
databases for developers/testing, I don't want to have to recreate the same
physical layout on every system it sounds like you are saying that is the
case... or maybe I am misreading you?
--
Robert Treat
Build A Better Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] tablespace and sequences?
Dear Robert, Chris, help me understand this will you? I'm not Chris, but it looks like Robert may eventually share my concerns, so I'm happy not to be alone on this one ;-) On my production system I have a few very large tables I want to move into their own tablespace so I can but them a a very large disk, and a couple frequently updated tables I would like to move into their own tablespace so i can put them on their own (small, raid oriented) disk. I need to do all this from a physical side of things for performance and administration in production, but when I create test databases for developers/testing, I don't want to have to recreate the same physical layout on every system What you describe is basically the reason why I'm advocating, quite unsuccessfully at the time, that pg_dump/pg_restore should deal with tablespace in some careful and appropriate manner even in coming 8.0. it sounds like you are saying that is the case... It is indeed the case and the reason for my query about the todo item. The current status is that you cannot restore a dump if tablespaces where used if the same tablespaces do not exist in the target system. So it is fine if you want to restore on the same system, but not on another one. You would have to create them artificially or to edit them out of the script if you want a transfer on a different system. Have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] tablespace and sequences?
It is a little bit different because a schema, a table or a function are database application issues and are normally addressed by pg_dump and pg_restore, although tablespaces are more an administration issue wrt disk layout and the like, which are likely to be different from one machine to another (compare with I obviously want the same schema/table/function for my application). So the notion of dump/restore of a tablespace need some careful thinking. But maybe I'm just stupid to dream that I could restore or transfer my data even if I used a tablespace somewhere? ;-) OK, perhaps. It it not easy to implement however, since the tablespace clause on indexes comes from the pg_get_indexdef() function and isn't added by pg_dump. Bruce - pg_dump TODO for --no-tablespace or something? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] tablespace and sequences?
On Wednesday 18 August 2004 21:39, you wrote:
Christopher Kings-Lynne wrote:
It is a little bit different because a schema, a table or a function
are database application issues and are normally addressed by pg_dump
and pg_restore, although tablespaces are more an administration issue
wrt disk layout and the like, which are likely to be different from one
machine to another (compare with I obviously want the same
schema/table/function for my application). So the notion of
dump/restore of a tablespace need some careful thinking.
But maybe I'm just stupid to dream that I could restore or transfer my
data even if I used a tablespace somewhere? ;-)
OK, perhaps. It it not easy to implement however, since the tablespace
clause on indexes comes from the pg_get_indexdef() function and isn't
added by pg_dump.
Bruce - pg_dump TODO for --no-tablespace or something?
Uh, TODO already has:
* 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.
If the location doesn't exist will postgresql try to create it? istm it could
do this and if it fails then you are no worse off, but if it were to succeed
you're that much better off.
--
Robert Treat
Build A Better Lamp :: Linux Apache {middleware} PostgreSQL
---(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] tablespace and sequences?
Robert Treat [EMAIL PROTECTED] writes: If the location doesn't exist will postgresql try to create it? istm it could do this and if it fails then you are no worse off, but if it were to succeed you're that much better off. I think this would be fairly pointless. In most of the practical tablespace scenarios I can think of, the tablespace directory probably lives within a root-owned directory (eg, a filesystem root directory). That's why CREATE TABLESPACE expects the directory to have been made already. In point of fact I think this discussion is much ado about nothing, as there is already a workaround that is about as simple as anything that we would likely be able to substitute. Suppose the dump contains CREATE TABLESPACE t1 LOCATION '/foo/bar' and for some reason /foo/bar is no longer an appropriate location. All the DBA need do is select a location that *is* suitable and create tablespace t1 at that location. Then run the restore. The create tablespace command will fail on duplicate name, but the tablespace is there and all the subsequent operations will be just fine. Of course we need to document this procedure, but we'd have to document any other approach as well... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] tablespace and sequences?
Robert Treat wrote: On Wednesday 18 August 2004 21:39, you wrote: Christopher Kings-Lynne wrote: It is a little bit different because a schema, a table or a function are database application issues and are normally addressed by pg_dump and pg_restore, although tablespaces are more an administration issue wrt disk layout and the like, which are likely to be different from one machine to another (compare with I obviously want the same schema/table/function for my application). So the notion of dump/restore of a tablespace need some careful thinking. But maybe I'm just stupid to dream that I could restore or transfer my data even if I used a tablespace somewhere? ;-) OK, perhaps. It it not easy to implement however, since the tablespace clause on indexes comes from the pg_get_indexdef() function and isn't added by pg_dump. Bruce - pg_dump TODO for --no-tablespace or something? Uh, TODO already has: * 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. If the location doesn't exist will postgresql try to create it? istm it could do this and if it fails then you are no worse off, but if it were to succeed you're that much better off. Yea, I assume if you can't create the tablespace you put everything for that tablespace in the default 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tablespace and sequences?
Fabien COELHO wrote: (3) psql auto completion does not have CREATE/DROP TABLESPACE in its list. I have already posted a patch for this(http://candle.pha.pa.us/mhonarc/patches/msg0.html) and afaik it is on Bruce's Beta-TODO list too. Stefan ---(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] tablespace and sequences?
(3) psql auto completion does not have CREATE/DROP TABLESPACE in its list. I have already posted a patch for this(http://candle.pha.pa.us/mhonarc/patches/msg0.html) Good. I should have checked the pending patch queue. and afaik it is on Bruce's Beta-TODO list too. Argh, I missed this one! Is it somewhere on line? -- Fabien Coelho - [EMAIL PROTECTED] ---(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] tablespace and sequences?
Fabien COELHO wrote: Dear hackers, Some minor comments about the new tablespace feature in 8.0beta1: It seems to me that tablespaces and sequences are not yet prefectly integrated. (1) the CREATE SEQUENCE foo TABLESPACE disk2 syntax does not seem to be implemented. (2) when creating an implicit sequence with SERIAL, the sequence is created in the tablespace of the schema/database, not the one of the table, although indexes are added to the tablespace of the table. It would seem more logical to put it in the same table space as the table by default? We decided it didn't make much sense to allow the on-row sequences to be anywhere but the default tablespace. (3) psql auto completion does not have CREATE/DROP TABLESPACE in its list. Maybe these non-important issues could be added to the TODO list. I've noticed some todos about tablespaces, but not these. Yep, in patch queue. -- 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] tablespace and sequences?
Fabien COELHO wrote: (3) psql auto completion does not have CREATE/DROP TABLESPACE in its list. I have already posted a patch for this(http://candle.pha.pa.us/mhonarc/patches/msg0.html) Good. I should have checked the pending patch queue. and afaik it is on Bruce's Beta-TODO list too. Argh, I missed this one! Is it somewhere on line? Yep, URL at the top: --- P O S T G R E S Q L 8 . 0 O P E NI T E M S Current version at ftp://momjian.postgresql.org/pub/postgresql/open_items. Changes --- * Win32 o add binary version stamps? o fix signal-safe socket handler for SSL o fix query cancel in psql (?) o report correct errno codes from native Windows system calls o shorten timezone for %t log_line_prefix o start pg_autovacuum easily o fix users who's timezones are not recognized o allow installed locales rather than hardcoded one o update encoding list to include win1250 o synchonize supported encodings and docs * fix oid2name for tablespaces * allow libpq to check parameterized data types * make pgxs install the default * add xid to log_line_prefix for PITR * add psql tab completion for tablespaces * cleanup FRONTEND use in /port, malloc, elog * fix recovery of DROP TABLESPACE after checkpoint * fix ambiguity for objects using default tablespaces * fix case where template db already uses target tablespace * determine proper crash recovery/logging for pg_subtrans * remove to_char(interval) if we initdb * have plpython reject pseudotype arguments because it crashes * add i386 solaris spinlock code -- 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] tablespace and sequences?
Dear Bruce, (1) the CREATE SEQUENCE foo TABLESPACE disk2 syntax does not seem to be implemented. (2) when creating an implicit sequence with SERIAL, the sequence is created in the tablespace of the schema/database, not the one of the table, although indexes are added to the tablespace of the table. It would seem more logical to put it in the same table space as the table by default? We decided it didn't make much sense to allow the on-row sequences to be anywhere but the default tablespace. Hmmm... I can understand the performance/utility rationale, but I don't like the lack of orthogonality on principle. I like elegance;-) As a sequence looks a lot like a table, I guess it should not be that hard to have it anyway. Well, just my little opinion, and not a big issue. Thanks for your answer. -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] tablespace and sequences?
Fabien COELHO wrote: Dear Bruce, (1) the CREATE SEQUENCE foo TABLESPACE disk2 syntax does not seem to be implemented. (2) when creating an implicit sequence with SERIAL, the sequence is created in the tablespace of the schema/database, not the one of the table, although indexes are added to the tablespace of the table. It would seem more logical to put it in the same table space as the table by default? We decided it didn't make much sense to allow the on-row sequences to be anywhere but the default tablespace. Hmmm... I can understand the performance/utility rationale, but I don't like the lack of orthogonality on principle. I like elegance;-) As a sequence looks a lot like a table, I guess it should not be that hard to have it anyway. Well, just my little opinion, and not a big issue. I can't remember why we didn't just make it orthoginal. -- 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] tablespace and sequences?
and afaik it is on Bruce's Beta-TODO list too. Argh, I missed this one! Is it somewhere on line? Yep, URL at the top: Quite an unexpected location! thanks for the pointer. Current version at ftp://momjian.postgresql.org/pub/postgresql/open_items. IMVHO, I think the following todo item should make it for 8.0: 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. Indeed, if someone step to 8.0, make some use of tablespace, and connot move its databases because of this issue, I guess she will not going to be happy at all... I guess something like --ignore-tablespace at the restoration phase would be good. At the dump phase it would be a minimum. -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] tablespace and sequences?
(1) the CREATE SEQUENCE foo TABLESPACE disk2 syntax does not seem to be implemented. This is intentional. Sequences are not large enough to need to be pushed around among multiple tablespaces. Also, if we did allow sequences to be associated with tablespaces, we'd be precluding other implementation changes that are on the wish-list (such as storing all sequences in a single system table, instead of needing a separate disk file for each one). That is a point. As for the semantics, sequences have a tablespace anyway, which is the default tablespace of the schema as it seems, and it appears in pg_class, so it is already implemented somewhere, no doubt about that. As for the syntax, you could decide to ignore the tablespace part of the syntax if such evolution would require it, maybe with some warning for the user that part of its query is no more up to date... It would no more a big deal than dropping LOCATION from CREATE DATABASE, which is not a upward compatible change and was performed remorselessly anyway. The original patch actually had support for specifying a tablespace for a sequence. That was deliberately removed, and it's not going to go back in later. Too bad for elegance and orthogonality. Thanks for your clear answer anyway;-) Have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] tablespace and sequences?
We decided it didn't make much sense to allow the on-row sequences to be anywhere but the default tablespace. Hmmm... I can understand the performance/utility rationale, but I don't like the lack of orthogonality on principle. I like elegance;-) As a sequence looks a lot like a table, I guess it should not be that hard to have it anyway. Well, just my little opinion, and not a big issue. Thanks for your answer. Well then, should you be able to move composite types to other tablespaces as well?? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] tablespace and sequences?
IMVHO, I think the following todo item should make it for 8.0: 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. Indeed, if someone step to 8.0, make some use of tablespace, and connot move its databases because of this issue, I guess she will not going to be happy at all... I guess something like --ignore-tablespace at the restoration phase would be good. At the dump phase it would be a minimum. How is that at all a problem? It's no different to the requirement to have installed all your contrib .so's before running your restore, what's so hard about making a few dirs? It's also no different to the old database locations support. Personally, I think it's a non-issue. It's also impossible to do as you suggest and have a --ignore-tablespace flag. All it could do is at dump time to dump NO tablespace, which is NOT what you want. At restore time it doesn't do anything since pg_dumpall is a text format only. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
