AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)

Dear Postgres Gurus;

I've just diagnosed a PostgreSQL 8.3.4 server which, about once a month, would deadlock shortly after 11pm.  It had
beendoing this for years,
 
and the prior response was simply to reboot everything.  The culprit boils down to:

# create table cache_table_20090921 (
site_key      int NOT NULL REFERENCES contexts(context_key),
blah_blah TEXT NULL,
PRIMARY KEY (site_key)
);

Without the REFERENCES this is a very fast operation and always works.

Adding "DEFERRABLE INITIALLY DEFERRED" or "ON DELETE CASCADE" (as the original did) makes no difference.  With any
REFERENCES,the create table may block a long time (up to 45 minutes and counting), all while attempting to get an
AccessExclusiveLock. The server quickly backs up,
 
simple select statements block, the server maxes out the number of incoming apache connections, things start swapping,
andgenerally it all gets into a huge tangle:
 

# SELECT l.locktype,c.relname,l.pid,l.mode,granted from pg_locks l,pg_class c where l.relation=c.oid order by
relname,granted;locktype|               relname               |  pid  |        mode         | grantedrelation |
article_key_idx                    | 18891 | AccessShareLock     | trelation | articles                            |
18891| AccessShareLock     | trelation | articles_editorid_idx               | 18891 | AccessShareLock     | trelation
|articles_pkey                       | 18891 | AccessShareLock     | trelation | articles_response_to_key_idx        |
18891| AccessShareLock     | trelation | articles_state_idx                  | 18891 | AccessShareLock     | trelation
|article_words                       | 18891 | AccessShareLock     | trelation | article_words_cw                    |
18891| AccessShareLock     | trelation | article_words_wc                    | 18891 | AccessShareLock     | trelation
|collection_context_key_idx          | 18891 | AccessShareLock     | trelation | collection_owner_key_idx            |
18891| AccessShareLock     | trelation | collections                         | 18891 | AccessShareLock     | trelation
|context_publication_key_idx         | 18891 | AccessShareLock     | trelation | contexts                            |
18891| AccessShareLock     | trelation | contexts                            |  3879 | AccessExclusiveLock | frelation
|contexts                            |  5477 | AccessShareLock     | frelation | contexts                            |
5484| AccessShareLock     | frelation | contexts                            |  5485 | AccessShareLock     | frelation |
contexts                           |  5486 | AccessShareLock     | frelation | contexts                            |
5487| AccessShareLock     | frelation | contexts                            |  5489 | AccessShareLock     | frelation |
contexts                           |  5493 | AccessShareLock     | frelation | contexts                            |
5494| AccessShareLock     | frelation | contexts                            |  5496 | AccessShareLock     | frelation |
contexts                           |  5497 | AccessShareLock     | frelation | contexts                            |
5498| AccessShareLock     | frelation | contexts                            |  5499 | AccessShareLock     | frelation |
contexts                           |  5500 | AccessShareLock     | frelation | contexts                            |
5502| AccessShareLock     | frelation | contexts                            |  5503 | AccessShareLock     | frelation |
contexts                           |  5504 | AccessShareLock     | frelation | contexts                            |
5505| AccessShareLock     | frelation | contexts                            |  5506 | AccessShareLock     | frelation |
contexts                           |  5507 | AccessShareLock     | frelation | contexts                            |
5508| AccessShareLock     | frelation | contexts                            |  5509 | AccessShareLock     | frelation |
contexts                           |  5510 | AccessShareLock     | frelation | contexts                            |
5511| AccessShareLock     | frelation | contexts                            |  5512 | AccessShareLock     | frelation |
contexts                           |  5515 | AccessShareLock     | frelation | contexts                            |
5516| AccessShareLock     | frelation | contexts                            |  5517 | AccessShareLock     | frelation |
contexts                           |  5518 | AccessShareLock     | frelation | contexts                            |
5519| AccessShareLock     | frelation | contexts                            |  5520 | AccessShareLock     | frelation |
contexts                           |  5521 | AccessShareLock     | frelation | contexts                            |
5523| AccessShareLock     | frelation | contexts                            |  5524 | AccessShareLock     | frelation |
contexts_id_key                    | 18891 | AccessShareLock     | trelation | contexts_pkey                       |
18891| AccessShareLock     | trelation | contexts_publication_date_idx       | 18891 | AccessShareLock     | trelation
|contexts_site_key_ct_id_journal_key | 18891 | AccessShareLock     | trelation | contexts_site_key_ct_type_idx       |
18891| AccessShareLock     | trelation | group_key_idx                       | 18891 | AccessShareLock     | trelation
|parent_key_idx                      | 18891 | AccessShareLock     | trelation | pg_class                            |
3911| AccessShareLock     | trelation | pg_class_oid_index                  |  3911 | AccessShareLock     | trelation |
pg_class_relname_nsp_index         |  3911 | AccessShareLock     | trelation | pg_locks                            |
3911| AccessShareLock     | trelation | site_key_idx                        | 18891 | AccessShareLock     | trelation |
virtual_ancestor_key_idx           | 18891 | AccessShareLock     | trelation | virtual_ancestors                   |
18891| AccessShareLock     | trelation | virtual_ancestors_pkey              | 18891 | AccessShareLock     | trelation
|virtual_context_key_idx             | 18891 | AccessShareLock     | trelation | words                               |
18891| AccessShareLock     | trelation | words_pkey                          | 18891 | AccessShareLock     | trelation
|words_word                          | 18891 | AccessShareLock     | t
 

# select pg_size_pretty(pg_total_relation_size('contexts_pkey'));
35MB
# select pg_size_pretty(pg_total_relation_size('contexts'));
787MB
# select pg_cancel_backend(3879)
t

And all is well again.

-----------------------------------------------------------------------------------------------------------------
So my questions are various
1) Why the AccessExclusiveLock on create table?
2) Why is the foreign key check a heavy operation, since a new table
will have zero foreign keys, it can't possibly violate the constraint yet.
3) Other than eliminating dynamic table creation, how can this operation
be altered?
4) Is there more data I should collect about the lock (e.g. do I have a
good lock summary select statement)?



Note that process 18891 in the example is doing a select:
--------------
SELECT contexts.context_key FROM contextsJOIN articles ON
(articles.context_key=contexts.context_key)WHERE contexts.context_key IN
(SELECT context_key FROM article_words JOIN words using (word_key) WHERE word = 'four')AND contexts.context_key IN
(SELECT context_key FROM article_words JOIN words using (word_key) WHERE word = 'step')AND contexts.context_key IN
(SELECT context_key FROM article_words JOIN words using (word_key) WHERE word = 'control')AND contexts.context_key IN
(SELECT context_key FROM article_words JOIN words using (word_key) WHERE word = 'process')AND contexts.context_key IN
(SELECT context_key FROM article_words JOIN words using (word_key) WHERE word = 'business')AND contexts.context_key IN
(SELECTDISTINCT
 
a1.context_key FROM virtual_ancestors a1, collections, virtual_ancestors a2WHERE a1.ancestor_key =
collections.context_keyAND collections.owner_key = a2.context_key AND a2.ancestor_key = '82034')AND
articles.indexedORDERBY contexts.publication_date DESC;