Postgres exclude using gist across different tables

Question

I have 2 tables like this

drop table if exists public.table_1;
drop table if exists public.table_2;

CREATE TABLE public.table_1 (
    id serial NOT NULL,
    user_id bigint not null,
    status varchar(255) not null,
    date_start date NOT NULL,
    date_end date NULL
);

CREATE TABLE public.table_2 (
    id serial NOT NULL,
    user_id bigint not null,
    status varchar(255) not null,
    date_start date NOT NULL,
    date_end date NULL
);


alter table public.table_1
add constraint my_constraint_1 
EXCLUDE USING gist (user_id with =, daterange(date_start, date_end, '[]') WITH &&)
where (status != 'deleted');

alter table public.table_2 
add constraint my_constraint_2 
EXCLUDE USING gist (user_id with =, daterange(date_start, date_end, '[]') WITH &&)
where (status != 'deleted');

Every table contains rows which are related to a user, and all the rows of the same user cannot overlap in range. In addition, some rows may be logically deleted, so I added a where condition. So far it's working w/o problems, but the 2 constraints work separately for each table.

I need to create a constraint which cover the 2 set of tables, so that a single daterange (of the same user and not deleted), may appaer only once across the 2 different tables.

Does the EXCLUDE notation be extended to work with different tables or do I need to check it with a trigger? If the trigger is the answer, which is the simplier way to do this? Create a temporary table with the union of the 2, add the constraint on it and check if fails?

score 3 · Answer 1 · answered Dec 11 '20 at 16:35

Starting from @Laurenz Albe suggestion, this is what I made

-- #################### SETUP SAMPLE TABLES ####################

drop table if exists public.table_1;
drop table if exists public.table_2;

CREATE TABLE public.table_1 (
    id serial NOT NULL,
    user_id bigint not null,
    status varchar(255) not null,
    date_start date NOT NULL,
    date_end date NULL
);

CREATE TABLE public.table_2 (
    id serial NOT NULL,
    user_id bigint not null,
    status varchar(255) not null,
    date_start date NOT NULL,
    date_end date NULL
);


alter table public.table_1
add constraint my_constraint_1 
EXCLUDE USING gist (user_id with =, daterange(date_start, date_end, '[]') WITH &&)
where (status != 'deleted');

alter table public.table_2 
add constraint my_constraint_2 
EXCLUDE USING gist (user_id with =, daterange(date_start, date_end, '[]') WITH &&)
where (status != 'deleted');

-- #################### SETUP TRIGGER ####################

create or REPLACE FUNCTION check_date_overlap_trigger_hook()
RETURNS trigger as
$body$
DECLARE
    l_table text;
    l_sql text;
    l_row record;
begin
    l_table := TG_ARGV[0];

    l_sql := format('
        select *
        from public.%s as t
        where
            t.user_id = %s -- Include only records of the same user
            and t.status != ''deleted'' -- Include only records that are active
    ', l_table, new.user_id);

    for l_row in execute l_sql       
    loop
        
        IF daterange(l_row.date_start, COALESCE(l_row.date_end, 'infinity'::date)) && daterange(new.date_start, COALESCE(new.date_end, 'infinity'::date))
        THEN
            RAISE EXCEPTION 'Date interval is overlapping with another one in table %', l_table
                USING HINT = 'You can''t have the same interval across table1 AND table2';
        END IF;
    
    end loop;
   
    RETURN NEW;
end
$body$
LANGUAGE plpgsql;

-- #################### INSTALL TRIGGER ####################

create trigger check_date_overlap
BEFORE insert or update
ON public.table_1 
FOR EACH row
EXECUTE PROCEDURE check_date_overlap_trigger_hook('table_2');

create trigger check_date_overlap
BEFORE insert or update
ON public.table_2
FOR EACH row
EXECUTE PROCEDURE check_date_overlap_trigger_hook('table_1');


-- #################### INSERT DEMO ROWS ####################

insert into public.table_1 (user_id, status, date_start, date_end) values (1, 'active', '2020-12-10', '2020-12-20');
insert into public.table_1 (user_id, status, date_start, date_end) values (1, 'deleted', '2020-12-15', '2020-12-25');
insert into public.table_1 (user_id, status, date_start, date_end) values (2, 'active', '2020-12-10', '2020-12-20');
insert into public.table_1 (user_id, status, date_start, date_end) values (2, 'deleted', '2020-12-15', '2020-12-25');


-- This will fail for overlap on the same table
-- insert into public.table_1 (user_id, status, date_start, date_end) values (1, 'active', '2020-12-15', '2020-12-25');

-- This will fail as the user 1 already has an overlapping period on table 1
-- insert into public.table_2 (user_id, status, date_start, date_end) values (1, 'active', '2020-12-15', '2020-12-25');

-- This will fail as the user 1 already has an overlapping period on table 1
insert into public.table_2 (user_id, status, date_start, date_end) values (1, 'deleted', '2020-12-15', '2020-12-25');
update public.table_2 set status = 'active' where id = 1;


select 'table_1' as src_table, * from public.table_1
union
select 'table_2', * from public.table_2

score 1 · Answer 2 · answered Dec 09 '20 at 11:27

1

You can probably use a trigger, but triggers are always vulnerable to race conditions (unless you are using SERIALIZABLE isolation).

If your tables really have the same columns, why don't you use a single table (and perhaps add a type column to disambiguate)?

answered Dec 09 '20 at 11:27

Laurenz Albe

209,280
17
206
263

this is a simplified example of my actual case, the real tables have about 10-20 each which are different, this is why I'm having 2 different tables. The only common part is the date start/end which cannot overlap. In terms of triggers, besides the race condition problem, do you have any suggestion how to implement the exclude constraint? – Deviling Master Dec 09 '20 at 11:29
Well, whenever an `INSERT` or `UPDATE` takes place on either table, select the other table for overlapping entries and throw an error if there are any. – Laurenz Albe Dec 09 '20 at 11:33
Ok, I understand. Is there an already built in function that I can use to check for overlap of do I need to manually check row by row with the dates of the record I'm processing? – Deviling Master Dec 09 '20 at 11:57
What do you mean "row by row"? Yes, it has to be a trigger `FOR EACH ROW`. – Laurenz Albe Dec 09 '20 at 12:13
Yes of course, the trigger need to perform a loop for every row in the other table to check for overlap, my question is if I need to check the overlapping by "hand" (like if start date is over or before the current row counterpart, ....) or exists some function that ease this job – Deviling Master Dec 09 '20 at 13:36
1

Use the `&&` operator that you use in your exclusion constraint. – Laurenz Albe Dec 09 '20 at 13:58
Thank you for your help, I added my full implementation as answer, is that what you had in mind? – Deviling Master Dec 11 '20 at 16:35
1

Yes, but I would write two functions, with the table name hard coded, so I get better performance. – Laurenz Albe Dec 11 '20 at 16:42

Postgres exclude using gist across different tables

2 Answers2