Использование "при конфликте" с уникальным ограничением для таблицы, разделенной по дате

#postgresql #sql-update #sql-insert #database-partitioning #postgresql-11

#postgresql #sql-обновление #sql-вставка #разделение базы данных #postgresql-11

Вопрос:

Учитывая следующую таблицу:

 CREATE TABLE event_partitioned (
    customer_id varchar(50) NOT NULL,
    user_id varchar(50) NOT NULL,
    event_id varchar(50) NOT NULL,
    comment varchar(50) NOT NULL,
    event_timestamp timestamp with time zone DEFAULT NOW()
)
PARTITION BY RANGE (event_timestamp);

И разделение по календарной неделе [один пример]:

 CREATE TABLE event_partitioned_2020_51 PARTITION OF event_partitioned
FOR VALUES FROM ('2020-12-14') TO ('2020-12-20');

И уникальное ограничение [event_timestamp необходимо для ключа раздела]:

 ALTER TABLE event_partitioned
    ADD UNIQUE (customer_id, user_id, event_id, event_timestamp);

Я хотел бы обновить, существуют ли customer_id, user_id, event_id, в противном случае вставить:

 INSERT INTO event_partitioned (customer_id, user_id, event_id)
VALUES ('9', '99', '999')
ON CONFLICT (customer_id, user_id, event_id, event_timestamp) DO UPDATE
SET comment = 'I got updated';

Но я не могу добавить уникальное ограничение только для customer_id, user_id, event_id, следовательно, event_timestamp.

Таким образом, это приведет к вставке дубликатов customer_id, user_id, event_id . Даже при добавлении now () в качестве четвертого значения, если now () точно не соответствует тому, что уже есть в event_timestamp .

Есть ли способ, чтобы ON CONFLICT мог быть менее «детализированным» здесь и обновляться, если now () выпадает на неделю раздела, а не точно на ‘2020-12-14 09:13: 04.543256’, например?

В основном я пытаюсь избежать дублирования customer_id, user_id, event_id, по крайней мере, в течение недели, но все же извлекаю выгоду из разделения по неделям (чтобы поиск данных можно было сузить до диапазона дат и не сканировать всю разделенную таблицу).

Ответ №1:

Я не думаю, что вы можете сделать это on conflict в секционированной таблице. Однако вы можете выразить логику с помощью CTE:

 with 
    data as ( -- data
        select '9' as customer_id, '99' as user_id, '999' as event_id
    ),
    ins as (  -- insert if not exists
        insert into event_partitioned (customer_id, user_id, event_id)
        select * from data d
        where not exists (
            select 1 
            from event_partitioned ep
            where 
                ep.customer_id = d.customer_id
                and ep.user_id = d.user_id
                and ep.event_id = d.event_id
        )
        returning *
    )
update event_partitioned ep  -- update if insert did not happen
set comment = 'I got updated'
from data d
where 
    ep.customer_id = d.customer_id
    and ep.user_id = d.user_id
    and ep.event_id = d.event_id
    and not exists (select 1 from ins)

1. Спасибо @GMB — интересно, существует ли здесь вероятность возникновения состояния гонки с миллионами транзакций в день.

2. @sqldoug: это один запрос, поэтому он выполняется «атомарно», и нет риска возникновения состояния гонки: база данных управляет параллелизмом за вас. Конечно, производительность — это нечто другое: это, вероятно, будет менее эффективным, чем прямое on conflict утверждение.

Ответ №2:

ответ @GMB отличный и работает хорошо. Поскольку применение уникального ограничения к секционированной таблице (родительской таблице), разделенной по временному диапазону, обычно не так полезно, зачем теперь просто размещать уникальное ограничение / индекс на самом разделе?

В вашем случае event_partitioned_2020_51 может иметь уникальное ограничение:

 ALTER TABLE event_partitioned_2020_51
    ADD UNIQUE (customer_id, user_id, event_id, event_timestamp);

И последующий запрос может просто использовать

 INSERT ... INTO event_partitioned_2020_51 ON CONFLICT (customer_id, user_id, event_id, event_timestamp)

до тех пор, пока это предназначенный раздел, что обычно и происходит.