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

From: Tom Lane
Subject: Re: probelm with alter table add constraint......
Date: ,
Msg-id: 27085.1239026040@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: probelm with alter table add constraint......  ("Albe Laurenz")
Responses: Re: probelm with alter table add constraint......  (roopabenzer)
List: pgsql-performance

Tree view

probelm with alter table add constraint......  (roopasatish, )
 Re: probelm with alter table add constraint......  (Robert Haas, )
 Re: probelm with alter table add constraint......  ("Albe Laurenz", )
  Re: probelm with alter table add constraint......  (Tom Lane, )
   Re: probelm with alter table add constraint......  (roopabenzer, )

"Albe Laurenz" <> 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


pgsql-performance by date:

From: Scott Marlowe
Date:
Subject: Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance
From: Lists
Date:
Subject: Re: Best replication solution?