Re: Schema version control - Mailing list pgsql-general

What about upgrades that can't be derived directly from an inspection of the schema?  Some examples:

- Adding a NOT NULL constraint (without adding a DEFAULT).  You often want to precede this with filling in any existing NULL values, so the new constraint doesn't fail.
- Updating triggers, functions and their effects.  For example, when I have an FTS index with a trigger to update an index column, and I change the underlying trigger, I often do something like "UPDATE table SET column = column", to cause all of the update triggers to fire and recalculate the index columns.
- Creating a new column based on an old one, and removing the old one; eg. add a column "n", run "UPDATE ... SET n = i*j * 2", and then drop the old columns "i" and "j".
- Updating data from an external source, such as ORM model code; for example, if you have a table representing external files, an update may want to calculate and update the SHA-1 of each file.
- For efficiency, dropping a specific index while making a large update, and then recreating the index.

In my experience, while generating schema updates automatically is handy, it tends to make nontrivial database updates more complicated.  These sorts of things happen often and are an integral part of a database update, so I'm just curious how/if you deal with them.

I've used Ruby's migrations, and for my Django databases I use my own migration system which is based in principle off of it: create scripts to migrate the database from version X to X+1 and X-1, and upgrade or downgrade by running the appropriate scripts in sequence.

It's not ideal, since it can't generate a database at a specific version directly; it always has to run through the entire sequence of migrations to the version you want, and the migrations accumulate.  However, it can handle whatever arbitrary steps are needed to update a database, and I don't need to test updates from every version to every other version.