Postgresql v12 ссылается на многораздельную таблицу на многораздельную таблицу, но не с ключом раздела

Я пытался сослаться на секционированную таблицу в postgresql v12, но возникла проблема. Таблицы разделены по range в столбце даты, и значения для каждого раздела являются разделителями месяцев (например: FOR VALUES FROM ('2020-04-01T00: 00: 00') TO ('2020-05-01T00: 00: 00 '))

У меня 2 таблицы:

Table A
     Column      |           Type           | Collation | Nullable | Default 
-----------------+--------------------------+-----------+----------+---------
 uuid            | character varying(36)    |           | not null | 
 start_date      | timestamp with time zone |           | not null | 
Partition key: RANGE (start_date)
Indexes:
    "table_a_pkey" PRIMARY KEY, btree (uuid, start_date)

Table B
     Column      |           Type           | Collation | Nullable | Default 
-----------------+--------------------------+-----------+----------+---------
 uuid            | character varying(36)    |           | not null | 
 start_date      | timestamp with time zone |           | not null | 
 ref_a_id        | character varying(36)    |           | not null | 
Partition key: RANGE (start_date)
Indexes:
    "table_b_pkey" PRIMARY KEY, btree (uuid, start_date)

При добавлении первичного ключа в таблицу разделов я знаю, что ключ раздела должен быть включен. Однако table_a.start_date и table_b.start_date не совсем совпадают, только месяц обеих дат должен совпадать в целом и, конечно, от ref_a_id до uuid.

Пока что я пробовал вот что:

ALTER TABLE table_a ADD CONSTRAINT ref_fk FOREIGN KEY (ref_a_id) REFERENCES table_b (uuid) DEFERRABLE INITIALLY DEFERRED;
-> ERROR:  there is no unique constraint matching given keys for referenced table "table_b"

ALTER TABLE table_a ADD CONSTRAINT ref_fk FOREIGN KEY (ref_a_id) REFERENCES table_b DEFERRABLE INITIALLY DEFERRED;
-> ERROR:  number of referencing and referenced columns for foreign key disagree

ALTER TABLE table_a ADD CONSTRAINT ref_fk FOREIGN KEY (ref_a_id, start_date) REFERENCES table_b (uuid, start_date) DEFERRABLE INITIALLY DEFERRED;

Последний работает, но во время импорта я получил

-> ERROR:  insert or update on table "table_b" violates foreign key constraint "ref_fk"
DETAIL:  Key (ref_a_id, start_date)=(XXXXX-XXXXX-XXXXX-XXXXX, 2020-05-27 01:32:13+00) is not present in table "table_a".

Единственное решение, которое я вижу прямо сейчас, - это добавить новый столбец year_month со значениями типа 2020_04 и выполнить разбиение по list в этом новом столбце. Поэтому ссылка должна совпадать.

Есть другое решение? Можно ли указать только месяц из start_date столбцов вместо добавления нового столбца? Или может быть ссылка только на table_a.uuid?


postgresql reference partitioning foreign-keys postgresql-12
person Coodie_d    schedule 26.05.2020    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Вы должны разделить столбец на две части: year_month типа date, который содержит дату, округленную до месяцев, и offset типа interval, в котором хранится разница в start_date. Затем вы можете использовать первый столбец в своем ограничении.

person Laurenz Albe    schedule 26.05.2020
comment
Есть ли разница в производительности между вашим предложением и разделением list на столбец varchar? - person Coodie_d; 26.05.2020
comment
Никогда, никогда не храните даты в строковом столбце. Вы упускаете из виду проверку значений, арифметику даты и ненужное пространство для загрузки. Так что да, это может иметь значение для производительности, но это не главная проблема. - person Laurenz Albe; 26.05.2020
comment
Значит, не было бы другого решения, кроме добавления нового столбца (или разделения start_date)? Неважно, как отформатирован этот новый столбец. - person Coodie_d; 26.05.2020
comment
Я не могу придумать лучшего решения. Это не означает, что такого решения не существует. - person Laurenz Albe; 26.05.2020