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

От: Albe Laurenz
Тема: Re: probelm with alter table add constraint......
Дата: ,
Msg-id: D960CB61B694CF459DCFB4B0128514C202FF6545@exadv11.host.magwien.gv.at
(см: обсуждение, исходный текст)
Ответ на: probelm with alter table add constraint......  (roopasatish)
Ответы: Re: probelm with alter table add constraint......  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

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 по дате сообщения:

От: Scott Marlowe
Дата:
Сообщение: Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance
От: Lists
Дата:
Сообщение: Re: Best replication solution?