Имею задачу которую в общем виде можно описать так:
- Клиенты выгребают из таблицы строки которые изменились с определенного момента
- Признак что строка изменилась является поле типа timestamp (time_change)
- Есть отдельная таблица которая хранит время до какого момента клиент уже получил измененные данные
- Строки изменяются в транзакции, которая может длится достаточно долго (пусть 5 минут на изменение многих таблиц)
- В транзакции срабатывает триггер на установку момента изменения строки
- Возникают ситуации когда транзакция изменяющая данные уже идет, клиент подключился, посмотрел, что для него данных нет, записал в таблицу время когда проверял наличие новых данных и отключился. В это время закончилась транзакция которая обновляла данные и закоммитилось время раньше чем клиент проверял.
- При таком раскладе гарантированно клиент не получает часть обновленных данных
Начал делать примерно следующее
pg_dump
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
--
-- PostgreSQL database dump
--
CREATE FUNCTION public.return_time_change() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE xtime AS t
SET time_change = clock_timestamp(), updated = false
WHERE t.id = NEW.id;
return null;
END;
$$;
CREATE FUNCTION public.return_time_create() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE xtime AS t
SET time_create = clock_timestamp(), time_change = clock_timestamp()
WHERE t.id = NEW.id;
return null;
END;
$$;
CREATE TABLE public.xtime (
id bigint NOT NULL,
a text,
time_create timestamp with time zone,
time_change timestamp with time zone,
updated boolean DEFAULT true
);
CREATE SEQUENCE public.xtime_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER SEQUENCE public.xtime_id_seq OWNED BY public.xtime.id;
ALTER TABLE ONLY public.xtime ALTER COLUMN id SET DEFAULT nextval('public.xtime_id_seq'::regclass);
ALTER TABLE ONLY public.xtime ADD CONSTRAINT xtime_pkey PRIMARY KEY (id);
CREATE CONSTRAINT TRIGGER time_change AFTER UPDATE ON public.xtime DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (new.updated) EXECUTE PROCEDURE public.return_time_change();
CREATE CONSTRAINT TRIGGER time_create AFTER INSERT ON public.xtime DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE public.return_time_create();
Тестирую так:
1.
2.
3.
begin;
insert into xtime (a) SELECT generate_series(1,400000)::text;
commit;
Да, триггер выполняется один раз (в том числе благодаря костылю updated, но с этим можно смириться).
Только time_change измененных строк после коммита имеет разницу в несколько секунд, что есть проблема.
Есть ли способ для версий ниже 10 получить в процедуре готовый список строк и установить им time_change одним запросом чтобы время у всех строк было одинаковое и равнялось моменту коммита?
Возможно есть какой-то более правильный способ делать то, что я хочу не изобретая велосипед?
