On Wed, Feb 2, 2011 at 12:31 PM, David E. Wheeler <da...@kineticode.com> wrote: > They are identical except for the extra line in the second one. If I had, say > 15 different versions of an extension, then I'd have 15 upgrade scripts. > That's fine. But in your plan, the script to upgrade from version 1 to > version 15 would have all the same code as the v14 script, plus any > additional. The v14 script would have everything in v13. v13 would have > everything in v12. With no support for the equivalent of psql's \i, that's > extremely redundant and a huge PITA to maintain. Hence my hate.
Stepping back from the implementation details and file naming conventions a bit, it seems to me that when you do schema upgrades, there are basically three possible things you might want to put in the upgrade script: 1. SQL statements that you want to execute unconditionally, such as (1a) CREATE OR REPLACE FUNCTION on something that has a compatible signature in every prior release in which it exists, or (1b) CREATE TABLE on a table that was added in the most recent release. 2. SQL statements that you want to execute if the version we're upgrading *from* is older than X. For example, CREATE TABLE on a table that was added in version 6 should be executed if we're coming from a version less than 6, and skipped otherwise. 3. SQL statements that you want to execute if the version we're upgrading *from* is between X and Y. This is less common, but you sometimes need it. For example, in version 6 you added a table, but by version 13 it wasn't needed any more so you removed it. The upgrade script for version 17 should drop the table if we're coming from a version between 6 and 12 (if we're coming from pre-6, it was never created to begin with, and we don't want to drop an unrelated table with the same name, and if we're coming from 13-16, it either never existed or, depending on the history, some previous upgrade dropped it). So how could we provide this functionality? Dimitri's approach is simple in concept, but it potentially requires a LOT of bookkeeping when an extension has been around for a while, to make sure that all of the upgrade files contain exactly the right combinations of stuff. I've managed schema upgrades that went through dozens of versions, and making sure that you can correctly upgrade from every previous version to v48 is definitely going to be a challenge. David's approach makes that a little simpler in some ways, but I think it falls down pretty badly on point #3. I'd actually be inclined to just have ONE upgrade file and invent some kind of meta-language for controlling which statements get executed. Just to pick a syntax that everyone will probably hate: [..] -- unconditional stuff [..6] -- stuff to do if coming from pre-7 [..] -- some more unconditional stuff [6..12] -- stuff to do if coming from between 6 and 12 [..] -- a few more unconditional things You might all be either scoffing right now or laughing so hard there are tears running down your face, but in my not insignificant experience that's what real schema upgrade scripts need to cope with in real-world situations, so I hereby pre-reject any comments of the form "that should never be necessary in real life because..." and/or "for that to be necessary you'd have to have done the following bat-shit stupid thing". -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
