On Jan 9, 11:34 am, robertmh...@xxxxxxxxx (Robert Haas) wrote: > On Wed, Jan 5, 2011 at 2:09 AM, kakarukeys <kakaruk...@xxxxxxxxx> 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...@xxxxxxxxxxxxxx) > 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
