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

From: Albe Laurenz
Subject: Re: probelm with alter table add constraint......
Date: ,
Msg-id: D960CB61B694CF459DCFB4B0128514C202FF6545@exadv11.host.magwien.gv.at
(view: Whole thread, Raw)
In response to: probelm with alter table add constraint......  (roopasatish)
Responses: Re: probelm with alter table add constraint......  (Tom Lane)
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, )

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.

How many backends do you see in pg_stat_activity that
are running or in a transaction?

Any other backend that is in a transaction that has e.g.
selected from the table will block the ALTER TABLE.

Yours,
Laurenz Albe


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?