Re: adding foreign key constraint locks up table

Список
Период
Сортировка
От Lew
Тема Re: adding foreign key constraint locks up table
Дата
Msg-id ifcp05$odt$1@news.albasani.net
обсуждение исходный текст
Ответ на adding foreign key constraint locks up table  (kakarukeys)
Список pgsql-performance
Дерево обсуждения
adding foreign key constraint locks up table  (kakarukeys, )
 Re: adding foreign key constraint locks up table  (Gurjeet Singh, )
  Re: adding foreign key constraint locks up table  ("Kevin Grittner", )
   Re: adding foreign key constraint locks up table  (Gurjeet Singh, )
    Re: adding foreign key constraint locks up table  ("Kevin Grittner", )
 Re: adding foreign key constraint locks up table  (Lew, )
 Re: adding foreign key constraint locks up table  (kakarukeys, )
  Re: adding foreign key constraint locks up table  (Gurjeet Singh, )
 Re: adding foreign key constraint locks up table  (Florian Weimer, )
  Re: adding foreign key constraint locks up table  (Tom Lane, )
   Re: adding foreign key constraint locks up table  (Florian Weimer, )
    Re: adding foreign key constraint locks up table  (Tom Lane, )
 Re: adding foreign key constraint locks up table  (kakarukeys, )
  Re: adding foreign key constraint locks up table  (Robert Haas, )
 Re: adding foreign key constraint locks up table  (kakarukeys, )
On 12/28/2010 02:08 AM, kakarukeys wrote:
> I have a table "aaa" which is not very big. It has less than 10'000
> rows. However read operations on this table is very frequent.
>
> Whenever I try to create a new table "bbb" with foreign key pointing
> to "aaa". The operation locks, and reading "aaa" is not possible. The
> query also never seems to finish.

How long did you wait?

> ALTER TABLE "bbb" ADD CONSTRAINT "topic_id_refs_id_3942a46c6ab2c0b4"
> FOREIGN KEY ("topic_id") REFERENCES "aaa" ("id") DEFERRABLE INITIALLY
> DEFERRED;
>
> The current workaround is to create any new table at off-peak hours,
> e.g. [sic] midnight after restarting the db.
>
> I would like to know if there's any proper solution of this. Is this
> an issue affecting all relational databases? My db is PostgreSQL 8.3.

Naturally the system has to lock the table to alter it.  It also has to check
that all records already in "bbb" satisfy the new constraint.

What's the longest you've waited for ALTER TABLE to release its lock?

--
Lew
Ceci n'est pas une pipe.

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: adding foreign key constraint locks up table
Следующее
От: Greg Smith
Дата:
Сообщение: Re: Regression: 8.3 2 seconds -> 8.4 100+ seconds