Optimization of FK creation

Поиск
Список
Период
Сортировка
От Robert Ayrapetyan
Тема Optimization of FK creation
Дата
Msg-id AANLkTikcUfX4+wja_Guo+UtVCMzjwEbSEhU6gpQTBoEK@mail.gmail.com
обсуждение исходный текст
Ответы Re: Optimization of FK creation  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Hello.

It's often neccessary to create empty table and FK to some existing
table from this newly created table.
However, even newly created (empty) table requires lock on parent
table it wants refer to.
On large production DBs with high load on parent table it's almost
neverending operation.

For example,

CREATE TABLE "table_foo" (
  ...
);

ALTER TABLE "table_foo"
  ADD CONSTRAINT "FK_table_foo_REF_table_parent"
  FOREIGN KEY ("ColName") REFERENCES "table_parent" ("ColName");

hangs forever because requires lock on "ColName" of table_parent with
billions of records,
which for empty table seems senseless.

I think it's very easy to check if "table_foo" is empty and therefore
not requires any locks on
"parent_table" and generate FK immediately without acquiring the lock
on "parent" table.

Similar problem raised here:
http://postgresql.1045698.n5.nabble.com/probelm-with-alter-table-add-constraint-td2072411.html

Please fix this issue asap.

regards,
--
Ayrapetyan Robert

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

Предыдущее
От: tv@fuzzy.cz
Дата:
Сообщение: Re: LOG: unexpected EOF on client connection
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Optimization of FK creation