Thread: In-place upgrade: catalog side
Since this whole in-place upgrade thing is going nowhere until there's also a good solution for ye olde "The database cluster was initialized with CATALOG_VERSION_NO ..." error, I spent some time today touring through what everybody else has done there and have some initial review commentary and questions. Zdenek provided the following code: http://src.opensolaris.org/source/xref/sfw/usr/src/cmd/postgres/postgresql-upgrade/ The main thing you'll find there is a ksh script that handles most of the upgrade, presuming there's no page format changes. It looks like it was originally aimed at 8.1->8.2 upgrades (easy as long as you don't use INET/CIDR in your database) and still has some hard-coded stuff from that in there to clean up. I spent some time reading the code to figure out exactly what it's doing, but come to find there's an easier way to ramp up. It looks like this script is a fairly straight shell port (presumably to make things more flexible) of EDB's pg_migrator: http://pgfoundry.org/projects/pg-migrator/ If you want to understand the basics of what happens in either program, the pg_migrator download has a nice outline in doc/intro.txt The basic concept seems workable, albeit a bit more brute-force than I was expecting: don't bother trying to figure out what actually changed in the catalog, just start with a new cluster, restore schema, then slam renumbered data pages into it. At a high level it works like this: -Run pg_dumpall against the old database to grab its schema -Extract some catalog information from the old database -Export the pg_control information -Create a new cluster -Copy the old pg_clog over -With the help of pg_resetxlog, move over the timeline and other ids -Restore the schema dump -Update TOAST info -Join the new database relid information against the old set so you can easily map old and new relids for each relation -Move the underlying database files from their old location (the original relid) to the new one -Adjust tablespace information I'd like to start doing something constructive to help move this part forward, so here's an initial set of questions I've got for mapping that work out: -A ksh script for something this critical is a non-starter on Windows in particular, so a good plan would be to use Zdenek's script as a malleable prototype, confirm it works, then update pg_migrator with anything it's missing, right? -Are there already any specific tasks done by Zdenek's script that are already known to be unique to only its implementation? Eventually I expect I'll figure that out for sure myself just by comparing the code, was curious what the already known divergences were. -There are 10 TODO items listed for the pg_migrator project, most or all of which look like should be squashed before this is really complete. Any chance somebody (Korry?) has an improved version of this floating around beyond what's in the pgfoundry CVS already? -Am I really the only person who is frantic that this program isn't being worked on actively? -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Greg Smith napsal(a):
>
> The main thing you'll find there is a ksh script that handles most of
> the upgrade, presuming there's no page format changes. It looks like it
> was originally aimed at 8.1->8.2 upgrades (easy as long as you don't use
> INET/CIDR in your database) and still has some hard-coded stuff from
> that in there to clean up.
Yes, It is correct. It is only for 8.1->8.2. It works fine for 8.3->8.4 too, but
I'm working on cleanup and fixing bugs. I hope that I will send updated version
to community today.
<snip>
> I'd like to start doing something constructive to help move this part
> forward, so here's an initial set of questions I've got for mapping that
> work out:
>
> -A ksh script for something this critical is a non-starter on Windows in
> particular, so a good plan would be to use Zdenek's script as a
> malleable prototype, confirm it works, then update pg_migrator with
> anything it's missing, right?
It is more workaround or temporary solution. This approach is easy but it has
lot of limitation. Problem with toast tables is one, but biggest problem is with
dropped columns. And maybe there will be more issues. Problem with dump is that
you lost a internal data.
I personally prefer to have special mode (like boostrap) which converts data
from old catalog to new format.
I think pg_upgrade.sh is good starter, before we will implement direct catalog
upgrade.
> -Are there already any specific tasks done by Zdenek's script that are
> already known to be unique to only its implementation? Eventually I
> expect I'll figure that out for sure myself just by comparing the code,
> was curious what the already known divergences were.
If you compare with pg_migrator, there is better handling of locale and I think
vacuum freeze is used correctly. Also shuffling with tablespaces is little bit
different (it should avoid to move data outside of mountpoint). But in principal
the idea is same.
> -There are 10 TODO items listed for the pg_migrator project, most or all
> of which look like should be squashed before this is really complete.
> Any chance somebody (Korry?) has an improved version of this floating
> around beyond what's in the pgfoundry CVS already?
As I mentioned before pg_migrator and pg_upgrade.sh is not good way. It is
workaround. It does not make sense to continue in this way.
Zdenek
Zdenek Kotala wrote: > Greg Smith napsal(a): >> -There are 10 TODO items listed for the pg_migrator project, most or >> all of which look like should be squashed before this is really >> complete. Any chance somebody (Korry?) has an improved version of this >> floating around beyond what's in the pgfoundry CVS already? > > As I mentioned before pg_migrator and pg_upgrade.sh is not good way. It > is workaround. It does not make sense to continue in this way. Why not? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Zdenek Kotala wrote: > If you compare with pg_migrator, there is better handling of locale and I think > vacuum freeze is used correctly. Also shuffling with tablespaces is little bit > different (it should avoid to move data outside of mountpoint). But in principal > the idea is same. > > > -There are 10 TODO items listed for the pg_migrator project, most or all > > of which look like should be squashed before this is really complete. > > Any chance somebody (Korry?) has an improved version of this floating > > around beyond what's in the pgfoundry CVS already? > > As I mentioned before pg_migrator and pg_upgrade.sh is not good way. It is > workaround. It does not make sense to continue in this way. As the author of the original shell script, which was in /contrib/pg_upgrade, I think the code has grown to the point where it should be reimplemented in something like Perl. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Heikki Linnakangas napsal(a):
> Zdenek Kotala wrote:
>> Greg Smith napsal(a):
>>> -There are 10 TODO items listed for the pg_migrator project, most or
>>> all of which look like should be squashed before this is really
>>> complete. Any chance somebody (Korry?) has an improved version of
>>> this floating around beyond what's in the pgfoundry CVS already?
>>
>> As I mentioned before pg_migrator and pg_upgrade.sh is not good way.
>> It is workaround. It does not make sense to continue in this way.
>
> Why not?
>
Problem is the pg_dump does not export all important data for upgrade. For
example relfileid and so on. However, biggest problem here are dropped columns
(thanks to point me on this issue). Dropped column does not have information
about type. It could be possible to fake it somehow during a dump and drop them
again after restore, but I'm not convinced that it is what we want.
The solution is good now as a starter, but it is far from final solution.
Zdenek
On Wed, 3 Dec 2008, Zdenek Kotala wrote: > It works fine for 8.3->8.4 too, but I'm working on cleanup and fixing > bugs. I hope that I will send updated version to community today. That would be great. It didn't feel like you were quite done with it yet. I'll be glad to help test it out, just didn't want to jump into that if it was known to still have issues that were being worked on. Please let us know what the remaining bugs you know about are at that point, I really don't want this part of things to get ignored just because the page format stuff is the harder part. > It is more workaround or temporary solution. This approach is easy but it has > lot of limitation. Problem with toast tables is one, but biggest problem is > with dropped columns. And maybe there will be more issues. Problem with dump > is that you lost a internal data. Can you be a bit more specific about what the problems with TOAST and dropped columns are? If those are covered in your presentation or came up already and I missed it, just point me that way; I'm still working my way through parts of this and don't expect to ever have it all in my head like you do at this point. Obviously this approach is going to be somewhat traumatic even if perfectly executed because of things like losing table statistics. As we move closer to final crunch time here, what I am trying to keep clear in my head is which bits are absolutely required to do any type of in-place upgrade, whether or not the page format changes in 8.4. What's nice is that those parts I can be testing right now just by trying to upgrade from 8.3 to 8.4. Barring things like the TOAST problem you mention getting in the way, the fundamental approach taken by these upgrade scripts seems workable for the job even it's not optimal, and that's a whole lot better than nothing at all. > I personally prefer to have special mode (like boostrap) which converts data > from old catalog to new format. That's a perfectly fine idea I would like to see too. But if we have to write such a thing from scratch right now, I'm afraid that may be too late to implement and still ship the next release on schedule. And if such bootstrap code is needed, we sure need to make sure the prototype it's going to be built on is solid ASAP. That's what I want to help you look into if you can catch me up a bit here. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Greg Smith napsal(a):
> On Wed, 3 Dec 2008, Zdenek Kotala wrote:
>
>> It works fine for 8.3->8.4 too, but I'm working on cleanup and fixing
>> bugs. I hope that I will send updated version to community today.
>
> That would be great. It didn't feel like you were quite done with it
> yet. I'll be glad to help test it out, just didn't want to jump into
> that if it was known to still have issues that were being worked on.
> Please let us know what the remaining bugs you know about are at that
> point, I really don't want this part of things to get ignored just
> because the page format stuff is the harder part.
>
>> It is more workaround or temporary solution. This approach is easy but
>> it has lot of limitation. Problem with toast tables is one, but
>> biggest problem is with dropped columns. And maybe there will be more
>> issues. Problem with dump is that you lost a internal data.
>
> Can you be a bit more specific about what the problems with TOAST and
> dropped columns are? If those are covered in your presentation or came
> up already and I missed it, just point me that way; I'm still working my
> way through parts of this and don't expect to ever have it all in my
> head like you do at this point. Obviously this approach is going to be
> somewhat traumatic even if perfectly executed because of things like
> losing table statistics.
The TOAST problem is already addressed and script should handle it correctly.
But I don't like it much, because it is kind of magic.
Dropped column is another story. Heikki pointed me this issue in Prato and
current published version of script does not handle it. Problem is that dropped
columns are only mark as a deleted and data are still stored in tuples. Catalog
contains related information about position and length, but when you perform
dump and restore, this information is lost and columns are shifted ...
I already added to check for dropped column and now I'm going to test how
upgrade works with visibility map. I'll send this version when I finish tests.
<snip>
>
>> I personally prefer to have special mode (like boostrap) which
>> converts data from old catalog to new format.
>
> That's a perfectly fine idea I would like to see too. But if we have to
> write such a thing from scratch right now, I'm afraid that may be too
> late to implement and still ship the next release on schedule. And if
> such bootstrap code is needed, we sure need to make sure the prototype
> it's going to be built on is solid ASAP. That's what I want to help you
> look into if you can catch me up a bit here.
I agree. This is a starter for 8.3 -> 8.4, but we need more robust solution in
the future.
Zdenek
On Thu, 4 Dec 2008, Zdenek Kotala wrote: > The TOAST problem is already addressed and script should handle it correctly. > But I don't like it much, because it is kind of magic. I just read through the whole "toast by chunk-end" thread again and it does seem pretty complicated. What is the magic part you're still not happy with? > I'll send this version when I finish tests. You really should feel free to forward these things over as soon as you've got something working, even if you're still running your own tests. With all due respect to how much you've done here, the sooner we can get more people working with and on something closer to candidate code the better. I've have started a couple of days ago but couldn't find anything but the old script. If some parts have comments like "this is an awful check for dropped columns that probably doesn't even work yet", that's OK. We need to get other people helping out with this besides you. > Problem is that dropped columns are only mark as a deleted and data are > still stored in tuples. Catalog contains related information about > position and length, but when you perform dump and restore, this > information is lost and columns are shifted ... Here's a good example; that seems a perfect problem for somebody else to work on. I understand it now well enough to float ideas without even needing to see your code. Stop worring about it, I'll grab responsibility for making sure it gets done by someone. So, for everyone else who isn't Zdenek: when columns are dropped, pg_attribute.attisdropped turns true and atttypid goes to 0. pg_dump skips over them, and even if it didn't pg_restore doesn't know how to put them back. I can think of a couple of hacks to work around this, and one of them might even work: 1) Create a dummy type that exists only to flag these during conversion. Re-add all the deleted columns by turning off attisdropped and flag them with that type. Dump. Restore. Re-delete the columns. My first pass through poking holes in this idea wonders how the dump will go crazy if it finds rows that were created after the column was dropped, that therefore have no value for it. 2) Query the database to find all these deleted columns and store the information we need about them, save that into some text files (similary to how relids are handled by the script right now). After the schema restore, read that list in, iterating over the missing ones. For each column that was gone, increment attnum for everything above that position to renumber a place for it. Put a dummy column entry back in that's already marked as deleted. 3) Wander back into pre-upgrade land by putting together something that wanders through every table updating any row that contains data for a dropped column. Since dropping columns isn't really common in giant data warehouses, something that had to wander over all the tuples related to a table that has lost a column should only need to consider a pretty small subset of the database. You might even make it off-line without getting too many yelps from the giant DW crowd, seems like it would be easy to write something to estimate the amount of work needed in advance of doing it even (before you take the system down, run a check utility that says "The server currently has 65213 rows of data for tables with deleted columns"). Who wants to show off how much more they know about this than me by saying what's right or wrong with these various ideas? If we care about the fact that columns never go away and are using (1) or (2), could also consider adding some additional meta-data to 8.4 such that something like vacuum can flag when a column no longer exists in any part of the data. All deleted columns move from 8.3 to 8.4, but one day the 8.5 upgrade could finally blow them away. There's already plenty of per-table catalog data being proposed to push into 8.4 for making future upgrades easier, this seems like a possible candidate for something to make space for there. As I just came to appreciate the problem I'm not sure about that. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Greg Smith <gsmith@gregsmith.com> writes: > Here's a good example; that seems a perfect problem for somebody else to work > on. I understand it now well enough to float ideas without even needing to see > your code. Stop worring about it, I'll grab responsibility for making sure it > gets done by someone. > > So, for everyone else who isn't Zdenek: when columns are dropped, > pg_attribute.attisdropped turns true and atttypid goes to 0. pg_dump skips > over them, and even if it didn't pg_restore doesn't know how to put them back. > I can think of a couple of hacks to work around this, and one of them might > even work: > > 1) Create a dummy type that exists only to flag these during conversion. Re-add > all the deleted columns by turning off attisdropped and flag them with that > type. Dump. Restore. Re-delete the columns. My first pass through poking > holes in this idea wonders how the dump will go crazy if it finds rows that > were created after the column was dropped, that therefore have no value for it. No, those records would work fine, they will have the column set NULL. But in any case it doesn't matter, you don't need to dump out the data at all -- that's kind of the whole point of the exercise after all :) > Who wants to show off how much more they know about this than me by saying > what's right or wrong with these various ideas? *blush* :) They all seem functional ideas. But it seems to me they're all ideas that would be appropriate if this was a pgfoundry add-on for existing releases. But if this is an integrated feature targeting future releases we have more flexibility and there are more integrated approaches possible. How about adding a special syntax for CREATE TABLE which indicates to include a dropped column in that position? Then pg_dump could have a -X option to include those columns as placeholders. Something like: CREATE TABLE foo (col1 integer,NULL COLUMN,col2 integer ); > If we care about the fact that columns never go away and are using (1) or (2), > could also consider adding some additional meta-data to 8.4 such that something > like vacuum can flag when a column no longer exists in any part of the data. > All deleted columns move from 8.3 to 8.4, but one day the 8.5 upgrade could > finally blow them away. There's already plenty of per-table catalog data being > proposed to push into 8.4 for making future upgrades easier, this seems like a > possible candidate for something to make space for there. As I just came to > appreciate the problem I'm not sure about that. Hm, that's an interesting idea but I think it would work differently. If the column is dropped but there are tuples where the column is present then vacuum could squeeze the column out and set the null bit on each tuple instead. But that would involve a lot of I/O so it wouldn't be something we would want to do on a regular vacuum. Actually removing the attribute is downright hard. You would have to have the table locked, and squeeze the null bitmap -- and if you crash in the middle your data will be toast. I don't see much reason to worry about dropping the attribute though. The only cases where it matters are if you're near MaxAttrNum (1600 columns IIRC) or if it's the only null column (and in a table with more than 8 columns). -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!
Gregory Stark napsal(a):
> How about adding a special syntax for CREATE TABLE which indicates to include
> a dropped column in that position? Then pg_dump could have a -X option to
> include those columns as placeholders. Something like:
>
> CREATE TABLE foo (
> col1 integer,
> NULL COLUMN,
> col2 integer
> );
You need to know a size of the attribute for fetchattr function.
Zdenke
Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes: > Gregory Stark napsal(a): > >> How about adding a special syntax for CREATE TABLE which indicates to include >> a dropped column in that position? Then pg_dump could have a -X option to >> include those columns as placeholders. Something like: >> >> CREATE TABLE foo ( >> col1 integer, >> NULL COLUMN, >> col2 integer >> ); > > You need to know a size of the attribute for fetchattr function. True. and the alignment. I guess those would have to be in the syntax as well since we don't even know what type those columns were previously. The type might not even exist any more. This is an internal syntax so I don't see any reason to bother making new keywords just to pretty up the syntax. I don't see a problem with just doing something like "NULL COLUMN (2,1)" The only constraint it seems to me is that it should be an unlikely string for someone to come up with accidentally from a simple syntax error. That's why I originally suggested two reserved keywords. But even NULL(2,1) seems like it would be fine. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!
On Thu, 4 Dec 2008, Gregory Stark wrote: > They all seem functional ideas. But it seems to me they're all ideas that > would be appropriate if this was a pgfoundry add-on for existing releases. I was mainly trying to target things that would be achievable within the context of the existing shell script. I think that we need such a script that does 100% of the job and can be tested ASAP. If it's possible to slice the worst of the warts off later, great, but don't drop focus from getting a potential candidate release done first. > How about adding a special syntax for CREATE TABLE which indicates to > include a dropped column in that position? Then pg_dump could have a -X > option to include those columns as placeholders...This is an internal > syntax so I don't see any reason to bother making new keywords just to > pretty up the syntax. I don't see a problem with just doing something > like "NULL COLUMN (2,1)" It's a little bit ugly, but given the important of in-place upgrade I would think this is a reasonable hack to consider; two questions: -Is there anyone whose clean code sensibilities are really opposed to adding such a syntax into the 8.4 codebase? -If nobody has a beef about it, is this something you could draft a patch for? I'm going to be busy with the upgrade script stuff and don't know much about extending in this area anyway. > Actually removing the attribute is downright hard. You would have to have the > table locked, and squeeze the null bitmap -- and if you crash in the middle > your data will be toast. Not being familiar with the code, my assumption was that it would be possible to push all the tuples involved off to another page as if they'd been updated, with WAL logging and everything, similarly to the ideas that keep getting kicked around for creating extra space for header expansion. Almost the same code really, just with the target of moving everything that references the dead column rather than moving just enough to create the space needed. Actually doing the upgrade on the page itself does seem quite perilous. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
> Not being familiar with the code, my assumption was that it would be > possible to push all the tuples involved off to another page as if they'd > been updated, with WAL logging and everything, similarly to the ideas that > keep getting kicked around for creating extra space for header expansion. > Almost the same code really, just with the target of moving everything that > references the dead column rather than moving just enough to create the > space needed. Actually doing the upgrade on the page itself does seem quite > perilous. For in-place upgrade, you can tell which pages have been converted and which have not by looking at the page header, so you can put a switch into the code to handle each version appropriately. I don't think that would be possible in this case without purpose-built infrastructure. It might be possible to lock out writers only and rewrite the table in a new file though, disk space permitting. ...Robert
Greg Smith <gsmith@gregsmith.com> writes: > On Thu, 4 Dec 2008, Gregory Stark wrote: > >> They all seem functional ideas. But it seems to me they're all ideas that >> would be appropriate if this was a pgfoundry add-on for existing releases. > > I was mainly trying to target things that would be achievable within the > context of the existing shell script. I think that we need such a script that > does 100% of the job and can be tested ASAP. If it's possible to slice the > worst of the warts off later, great, but don't drop focus from getting a > potential candidate release done first. I suggested it because I thought it would be easier and less messy though. >> How about adding a special syntax for CREATE TABLE which indicates to include >> a dropped column in that position? Then pg_dump could have a -X >> option to include those columns as placeholders...This is an internal syntax >> so I don't see any reason to bother making new keywords just to pretty up the >> syntax. I don't see a problem with just doing something like "NULL COLUMN >> (2,1)" > > It's a little bit ugly, but given the important of in-place upgrade I would > think this is a reasonable hack to consider; two questions: Well it's ugly but it seems to me that it would be a whole lot more ugly to have a whole pile of code which tries to adjust the table definitions to insert "dropped" columns after the fact. > -Is there anyone whose clean code sensibilities are really opposed to adding > such a syntax into the 8.4 codebase? Incidentally I got this wrong in my previous email. If we're aiming at 8.4->8.5 as the first in-place update then we actually don't need this in 8.4 at all. The recommended path is always to use the *new* pg_dump to dump the old database even for regular updates and there would be no problem making that mandatory for an in-place update. So as long as the 8.5 pg_dump knows how to dump this syntax and the 8.5 create parser knows what to do with it then that would be sufficient. > -If nobody has a beef about it, is this something you could draft a patch for? > I'm going to be busy with the upgrade script stuff and don't know much about > extending in this area anyway. It doesn't sound hard off the top of my head. CREATE TABLE is a bit tricky though, I'll check to make sure it works. >> Actually removing the attribute is downright hard. You would have to have the >> table locked, and squeeze the null bitmap -- and if you crash in the middle >> your data will be toast. > > Not being familiar with the code, my assumption was that it would be possible > to push all the tuples involved off to another page as if they'd been updated, > with WAL logging and everything, similarly to the ideas that keep getting > kicked around for creating extra space for header expansion. Almost the same > code really, just with the target of moving everything that references the dead > column rather than moving just enough to create the space needed. Actually > doing the upgrade on the page itself does seem quite perilous. I'm sorry, I think I misunderstood the original idea, what you're talking about makes a lot more sense now. You want to save the space of the dead column by replacing it with NULL, not remove it from the table definition. That should be possible to do in vacuum or some other operation that has the vacuum lock without locking the table or introducing new tuples. Whatever does it does need the tuple descriptor which Vacuum normally doesn't need though. The page conversion time might be a good time since it'll need to deform all the tuples and re-form them anyways. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support!
On Wed, 3 Dec 2008, Bruce Momjian wrote: > As the author of the original shell script, which was in > /contrib/pg_upgrade, I think the code has grown to the point where it > should be reimplemented in something like Perl. Ah, there's the common ancestor I couldn't find. Sheesh, you learn Perl last month, and already you're a zealot. That was fast. As I see it the priorities for this part are: 1) Finish the shell-based pg_upgrade. The latest one we just got from Zdenek looks almost there, just have to sort out the dropped column bits. Start testing that in parallel with the below to figure out if there's anything that was missed. 2) Re-evaluate what's in there vs. what's already implemented in the C-based pg_migrator to determine the effort needed to upgrade that to fully functional. 3) Figure out who is available to do the expected work on schedule. 4) Determine what language they're going to do it in and whether the task is: a) Re-implementing the script in a more portable and powerful scripting language like Perl or Python b) Adding the additional features needed to complete pg_migrator c) Writing an implementationinto core via some bootstrap process You suggested (a), I was the latest in a series of people to suggest (b), and Zdenek suggested (c). They all have different trade-offs in terms of development time and expected quality of the resulting tool. At this point we'll be lucky to get anything done, and I think we may have to settle for whichever of the above options seems most likely to finish based on the skills of the person(s) doing the job. I think (c) may be out just because there will be increasing pressure for a final code freeze on anything in core, so that beta testing can begin on Jan 1. Whereas something that's going to end up in contrib like either the final pg_upgrade or an improved pg_migrator might get cut a bit more slack for starting beta less polished than the core code. (Insert retort from Tom about how that's not the way it's done here) Additionally, it may be the case that putting the appropriate hooks in to support 8.4->8.5 upgrades that have been suggested (dedicated free space management, catalog support, etc.) is the only thing that ships on time, and that the 8.4 announcement just mentions "a community tool for in-place upgrades from 8.3->8.4 is in currently in beta and can be downloaded from pgforge". That retreat position goes away if you've commited to putting the whole thing in core. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Thu, 4 Dec 2008, Gregory Stark wrote: > Incidentally I got this wrong in my previous email. If we're aiming at > 8.4->8.5 as the first in-place update then we actually don't need this in 8.4 > at all. I don't know about everybody else, but I haven't give up on putting together something that works for 8.3. That's why I'm still trying to chase down an approach, even if it's not as elegant as would be possible for 8.4->8.5. > It doesn't sound hard off the top of my head. CREATE TABLE is a bit tricky > though, I'll check to make sure it works. Support for NULL bits in CREATE TABLE might be a helpful trick to have available for this at some point. I'm not sure if it's actually worth doing after the rest of your comments though; see below. > I'm sorry, I think I misunderstood the original idea, what you're talking > about makes a lot more sense now. You want to save the space of the dead > column by replacing it with NULL, not remove it from the table definition. Not so much to save the space, it's more about breaking its need for the soon to be removed pg_attribute that used to lead to the dropped column. If you think it's possible to slip that change into the page conversion task, that's probably the ideal way to deal with this. If 8.4 has the appropriate catalog support to finally execute the full page upgrade vision Zdenek and everybody else has mapped out, it would make me feel better about something that just hacked around this problem crudely for 8.3->8.4. Knowing that a future 8.5 update could finally blow away the bogus dropped columns makes leaving them in there for this round not as bad, and it would avoid needing to mess with the whole pg_dump/CREATE TABLE with NULL bit. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Greg Smith <gsmith@gregsmith.com> writes: >> I'm sorry, I think I misunderstood the original idea, what you're talking >> about makes a lot more sense now. You want to save the space of the dead >> column by replacing it with NULL, not remove it from the table definition. > > Not so much to save the space, it's more about breaking its need for the soon > to be removed pg_attribute that used to lead to the dropped column. If you > think it's possible to slip that change into the page conversion task, that's > probably the ideal way to deal with this. Removing the dropped column attribute is what I think we cannot do. If you crashed while during that it would leave you with half the table with the columns in the old attribute position and half the columns in the new attribute position. There would be no way to tell which was which. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support!
Greg Smith wrote: > On Wed, 3 Dec 2008, Bruce Momjian wrote: > > > As the author of the original shell script, which was in > > /contrib/pg_upgrade, I think the code has grown to the point where it > > should be reimplemented in something like Perl. > > Ah, there's the common ancestor I couldn't find. Sheesh, you learn Perl > last month, and already you're a zealot. That was fast. Yes, I think that is accurate. ;-) I think the conversion to a more portable and powerful language is a long-term goal, rather than something we have to do now. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +