Re: Mapping/DB Migration tool - Mailing list pgsql-general

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@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@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@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