Re: adding foreign key constraint locks up table

From: kakarukeys <kakarukeys(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: adding foreign key constraint locks up table
Date: 2011-01-13 15:33:46
Message-ID: 9a80caa4-71a1-4029-a87e-298027251fec@v12g2000vbx.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Jan 9, 11:34 am, robertmh(dot)(dot)(dot)(at)gmail(dot)com (Robert Haas) wrote:
> On Wed, Jan 5, 2011 at 2:09 AM, kakarukeys <kakaruk(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> > As requested, here are some output of the investigative queries, run
> > when the problem occurred. I could see some locks there, but I don't
> > know why the alter table addconstrainttakes so long of time.
>
> It's pretty clear from the output you posted that it's waiting for a
> lock, but you didn't include the full contents of pg_stat_activity and
> pg_locks, so we can't see who has the lock it's waiting for.  Tom's
> guess upthread is a good bet, though.
>
> --
> Robert Haas
> EnterpriseDB:http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-performance mailing list (pgsql-performa(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance

Yes. Lately, I have learned quite abit of pgsql process to interpret
the log. There was always an AccessShareLock granted on
monitor_monitortopic by some process idle in transaction. This blocks
AccessExclusiveLock that the alter table statement tried to acquire.

The correct solution will be to have that transaction rolled back and
the lock released (or simply kill the process) before running alter
table.

Thank you all for the help.

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Jon Nelson 2011-01-13 15:51:29 queries with lots of UNIONed relations
Previous Message Cédric Villemain 2011-01-12 20:09:47 Re: Slow query + why bitmap index scan??