Re: adding foreign key constraint locks up table

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.