Re: Database updates

[Chris Travers <..hidden..>]

On Fri, Jul 15, 2011 at 10:03 AM, John Locke <..hidden..> wrote:

>> Ok, so if we are dealing with a single database server, we could I
>> suppose do a load, and then dump schema dump
>> and then compare against a schema dump from production (filtering out
>> set commands and comments)
>
> Is that a built-in PG command?

Yeah.


>
> I'm not suggesting storing the schema in such a file, just a list of
> tables in an order they can be created that will load successfully
> (taking into account foreign keys, types, etc).

<snip>

> Yes, that's the whole point of having a separate file listing this stuff
> -- so the base schema can be generated automatically. And re-generated
> to easily compare against what it should be.
>
> Essentially that file replaces Pg-database.sql. But we need to keep the
> data out of it, because that data may get changed after it's already in
> the database, making the schema dump inconsistent/not-comparable.
>
> So I'm suggesting:
>
> 1. Schema index file, parsed by Perl/Bash/whatever
> 2. base schema sql file, auto-generated from a clean install by a script
> that parses #1
> 3. base sql data file, to populate menu tables, etc.

So how would this work exactly?  Are you thinking one DDL file per
table?  I guess I am wondering if this would create more maintenance
problems than it would solve.  I mean you have to store the DDL
somewhere, or you have to generate it from some other format which is
semantically reducible to the SQL DDL as supported by PostgreSQL.
Quite frankly if we are going that route, we should be using SQL DDL
instead.


>
> Hmm. Is there any way in Postgres to define column ordering via an alter
> statement? This is something MySQL supports ;-)

Nope:
http://wiki.postgresql.org/wiki/Alter_column_position

Apparently nobody wants this badly enough to actually do the work required.

>
> This might be an issue for developers, but for regular users, I think we
> just need to make sure we don't change the update scripts after creating
> them -- as long as people follow the upgrade procedures, wouldn't new
> columns get added to the end of the table? Or does this vary by Pg version?

Do we want to be limited to always adding new fields to the end of the
table?  I would rather have the base DDL files focused on clarity for
developers if given the choice because it means fewer errors and
omissions.

Another issue that we'd run into has to do with self-joins.  In
general the backup/restore processes from PostgreSQL are only
guaranteed to work in the event you dump both the schema and data
together in one file.  This is an issue for tables like menu_node.
It's certainly possible to get around this problem (by dropping the
fkey, loading the data, recreating it) but it means additional effort
and complexity and I think we'd have to weigh the benefit of doing so
against the benefit of using well tested tools to dump data in a
reloadable format rather than maintaining that by hand.

So I guess I think we should start from a few more basic questions:

Now that the menus should be pretty stable for the duration of 1.3
(absent bugs which you'd probably want flagged), what would you expect
such an output to look like that you can't get from a diff on various
versions of Pg-database?   I mean, what specifically is missing?
Table names?  Something else?  Or is it a matter of noise from
comparing dumps?

Best Wishes,
Chris Travers