Re: probelm with alter table add constraint......

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
Cc: "roopasatish *EXTERN*" <roopabenzer(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: probelm with alter table add constraint......
Date: 2009-04-06 13:54:00
Message-ID: 27085.1239026040@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

"Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> writes:
> roopasatish wrote:
>> I have an issue with the add foreign key constraint which
>> goes for waiting and locks other queries as well.
>>
>> ALTER TABLE ONLY holding_positions ADD CONSTRAINT
>> holding_positions_stock_id_fkey FOREIGN KEY (stock_id)
>> REFERENCES stocks (stock_id) MATCH SIMPLE
>> ON UPDATE NO ACTION ON DELETE NO ACTION;
>>
>> The holding_positions table has no data in it as yet.

> Look in pg_catalog.pg_locks for a second transaction that
> holds a lock on the table holding_positions.

This statement also needs to get lock on the referenced table "stocks".
An open transaction that's referenced either table will block it.

regards, tom lane

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2009-04-06 13:58:05 Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance
Previous Message Matthew Wakeling 2009-04-06 12:52:42 Re: plpgsql arrays