Quick Links

AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)

From:	Bryce Nesbitt <bryce2(at)obviously(dot)com>
To:	pgsql-sql(at)postgresql(dot)org
Subject:	AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)
Date:	2009-09-22 16:55:52
Message-ID:	4AB90198.2010109@obviously.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-sql

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 been doing 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, and generally it all gets into a huge tangle:

# 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 contexts
JOIN 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 (SELECT DISTINCT
a1.context_key FROM virtual_ancestors a1, collections, virtual_ancestors a2
WHERE a1.ancestor_key = collections.context_key AND collections.owner_key = a2.context_key AND a2.ancestor_key = '82034')
AND articles.indexed
ORDER BY contexts.publication_date DESC;

Responses

Re: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better) at 2009-09-22 17:35:39 from Tom Lane
Re: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better) at 2009-10-07 05:25:44 from Erik Jones

Browse pgsql-sql by date

	From	Date	Subject
Next Message	Tom Lane	2009-09-22 17:35:39	Re: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)
Previous Message	Bryce Nesbitt	2009-09-22 16:41:47	AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks