Re: Mapping/DB Migration tool

On Wed, 2006-07-26 at 15:18 +0200, Karsten Hilbert wrote:
> > For some kinds of changes, and especially those that make
> destructive
> > in-place changes that might require debugging, I've written views
> which
> > generate the SQL statements to execute.
> Would you mind giving a small example ? That sounds really
> useful.

I don't have any examples of the destructive kind available, but here's
a non-destructive one.

I once discovered that deleting a primary key was taking forever. I
finally tracked this down to the lack of an index on one of the many
tables which contained FK references to that PK. The pg_* views contain
all of the necessary data to identify these cases. I wrote such views
to select all FK-PK pairs with index status, and another to show those
without indexes on the FK. For example:

rkh(at)csb-dev=> select * from pgtools.foreign_keys;
fk_namespace | fk_relation | fk_column | fk_indexed | pk_namespace | pk_relation | pk_column | pk_indexed | ud | c_namespace | c_name
--------------+---------------+---------------+------------+--------------+-------------+---------------+------------+----+-------------+-------------------------
unison | p2gblatalnhsp | p2gblathsp_id | t | unison | p2gblathsp | p2gblathsp_id | t | cc | unison | p2gblathsp_id_exists
unison | p2gblatalnhsp | p2gblataln_id | t | unison | p2gblataln | p2gblataln_id | t | cc | unison | p2gblataln_id_exists
unison | p2gblathsp | pseq_id | t | unison | pseq | pseq_id | t | cc | unison | pseq_id_exists

rkh(at)csb-dev=> select * from pgtools.foreign_keys_missing_indexes limit 5;
fk_namespace | fk_relation | fk_column | fk_indexed | pk_namespace | pk_relation | pk_column | pk_indexed | ud | c_namespace | c_name
--------------+-------------+-------------+------------+--------------+-------------+-------------+------------+----+-------------+-----------------
gong | node | alias_id | f | gong | alias | alias_id | t | cn | gong | alias_id_exists
taxonomy | node | division_id | f | taxonomy | division | division_id | t | cc | taxonomy | $1
mukhyala | pao | tax_id | f | mukhyala | mytax | tax_id | t | cr | mukhyala | pao_tax_id_fkey

Then. something like this:
rkh(at)csb-dev=> select 'create index '||fk_relation||'_'||fk_column||'_idx on '||fk_relation||'('||fk_column||');' from pgtools.foreign_keys_missing_indexes ;
?column?
-----------------------------------------------------------------------------
create index node_alias_id_idx on node(alias_id);
create index node_division_id_idx on node(division_id);
create index pao_tax_id_idx on pao(tax_id);

Finally, I used psql to generate the script and execute it:
$ psql -Atc 'select <as above>' | psql -Xa
(I'm skipping the quoting hassle, which you could circumvent by creating
a view to build the script.)

In case your interested in these "pgtools" views, I've uploaded them to
http://harts.net/reece/pgtools/ .

(Note: I created these views a long time ago with the intent to release
them, but I never did so. I think there's now a pgtools or pg_tools
package on sourceforge, but that's unrelated.)

-Reece
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0