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:
[EMAIL PROTECTED]> 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
[EMAIL PROTECTED]> 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:
[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
