[YSQL] DEFERRABLE constraint should be supported

[ramanans]

Jira Link: DB-2287
ALTER TABLE ONLY public.abc
ADD CONSTRAINT abc_xyz UNIQUE (a, b) DEFERRABLE INITIALLY DEFERRED;

[frozenspider]

An interesting test case for UNIQUE + DEFERRABLE that should be supported:

CREATE TABLE t2(i int UNIQUE DEFERRABLE, v text);

INSERT INTO t2 VALUES (1, 'v1'), (2, 'v2'), (3, 'v3'), (4, 'v4'), (5, 'v5');

SELECT *, (i % 5 + 1) AS new_id FROM t2 ORDER BY i;
--  i | v  | new_id
-- ---+----+--------
--  1 | v1 |      2
--  2 | v2 |      3
--  3 | v3 |      4
--  4 | v4 |      5
--  5 | v5 |      1
-- (5 rows)

UPDATE t2 SET i = (i % 5 + 1);
-- ERROR:  duplicate key value violates unique constraint "t2_i_key"

[frozenspider]

We should make sure we also properly support CREATE CONSTRAINT TRIGGER, including a case similar to this:

CREATE TABLE triggered(i int);

CREATE OR REPLACE FUNCTION my_trigger_func() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'Row: % -> %', OLD.i, NEW.i;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER trig_after_insert AFTER INSERT ON triggered DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION my_trigger_func();

BEGIN;
SET CONSTRAINTS trig_after_insert DEFERRED;
COPY triggered FROM '/path/to/my/file' WITH (FORMAT CSV);
COMMIT;

[EmiPhil]

CREATE CONSTRAINT TRIGGER is also helpful for creating a sequenced foreign key constraint between two bi-temporal tables. Without it, you need to ignore the trigger enforcing the constraint when updating the referenced table, even if by the end of the transaction it would have a valid state (since updating a bi-temporal table required both an UPDATE and INSERT).

There isn't exactly a short way to show the problem (TTST = transaction-time state table, VTST = valid-time state table, pov = period of validity, pop = period of presence):

-- create a new TTST period from now to infinity
create function pop_now()
returns tstzrange
as $$ select tstzrange(now() at time zone 'utc', null, '[)') $$
language sql;

-- check if the table is a ttst
create or replace function is_ttst(_tbl regclass, out result bool)
language plpgsql
as $$
begin
    select exists (
        select from pg_attribute
        where attrelid = _tbl
        and attname = 'pop'
        and not attisdropped
        and attnum > 0
        and atttypid = 3910::oid -- 3910 = type for tstzrange
    ) into result;
end;
$$;

-- check if the table is a vtst
create or replace function is_vtst(_tbl regclass, out result bool)
language plpgsql
as $$
begin
    select exists (
        select * from pg_attribute
        where attrelid = _tbl
        and attname = 'pov'
        and not attisdropped
        and attnum > 0
        and atttypid = 3910::oid -- 3910 = type for tstzrange
    ) into result;
end;
$$;

-- check if the table is a ttst and a vtst
create function is_bitemporal(_tbl regclass, out result bool)
language plpgsql
as $$
begin
    select is_ttst(_tbl) and is_vtst(_tbl) into result;
end;
$$;

create or replace function pk_fk_pair(pk text, fk text, out result text[])
language plpgsql
as $$
begin
    select array [pk,fk] into result;
end;
$$;

-- check to see if the sequenced key of _tbl1 contains all of the sequenced key of _tbl2 at the current pop
create or replace function contains_all(_tbl1 regclass, _tbl2 regclass, key_pairs text[], out result bool)
language plpgsql
as $$
declare
    index int := 1; -- pg uses 1 based array indexing :shrug:
    pk_fk_pair text[];
    col_exists bool;
    joiners text;
    statement text;
begin
    -- each _tbl must be bitemporal
    if not is_bitemporal(_tbl1) then
        raise invalid_table_definition using message = format('table %s is not bitemporal', _tbl1);
    end if;
    if not is_bitemporal(_tbl2) then
        raise invalid_table_definition using message = format('table %s is not bitemporal', _tbl2);
    end if;

    -- key_pairs must be even
    if cardinality(key_pairs) % 2 <> 0 then
        raise invalid_parameter_value using message = 'key_pairs must be an even array of pk,fk';
    end if;

    joiners := '';
    statement := 'with each_pov as (select bool_or(t1.pov @> t2.pov) as ok from %s t1 join %s t2 on %s and upper(t1.pop) is null and upper(t2.pop) is null group by t2.pov) select coalesce(bool_and(each_pov.ok), (select count(*) = 0 from %s where upper(pop) is null)) from each_pov';
    -- we need to map the pk,fk pairs into join clauses for the statement
    loop
        exit when index = cardinality(key_pairs) + 1;
        select pk_fk_pair(quote_ident(key_pairs[index]), quote_ident(key_pairs[index + 1])) into pk_fk_pair;
        index := index + 2;

        -- check if pk exists
        select exists(
            select from pg_attribute
            where attrelid = _tbl1
            and attname = pk_fk_pair[1]
            and not attisdropped
            and attnum > 0
        ) into col_exists;
        if not col_exists then
            raise invalid_column_reference using message = format('pk column %s not found in table %s', pk_fk_pair[1], _tbl1);
        end if;

        -- check if fk exists
        select exists(
            select from pg_attribute
            where attrelid = _tbl2
            and attname = pk_fk_pair[2]
            and not attisdropped
            and attnum > 0
        ) into col_exists;
        if not col_exists then
            raise invalid_column_reference using message = format('fk column %s not found in table %s', pk_fk_pair[2], _tbl2);
        end if;

        -- add the joiner to the result
        joiners := joiners || format('t1.%s = t2.%s', pk_fk_pair[1], pk_fk_pair[2]);
    end loop;

    execute format(
        statement,
        _tbl1, _tbl2, joiners, _tbl2
    ) into result;
end;
$$;

create or replace function sequenced_fk() returns trigger
language plpgsql
as $$
begin
    -- tg_relid is the id of the table that caused the trigger to invoke
    -- tg_argv is a text array of the arguments given in the create trigger statement, which must be the reference table
    -- followed by primary key/foreign key pairs
    -- the argv is 0 indexed unlike other pg arrays :shrug:
    if not contains_all(tg_argv[0]::regclass, tg_relid, tg_argv[1:]) then
        raise foreign_key_violation using message = 'value violates sequenced foreign key';
    end if;
    return null;
end;
$$;

create or replace function sequenced_fk_ref() returns trigger
language plpgsql
as $$
begin
    -- this early break out is required to update and insert on a referenced table because Yugabyte does not support
    -- deferrable triggers
    if upper(new.pop) is not null and tg_op = 'UPDATE' then
        return null;
    end if;

    if not contains_all(tg_relid, tg_argv[0]::regclass, tg_argv[1:]) then
        raise foreign_key_violation using message = 'value violates sequenced foreign key';
    end if;
    return null;
end;
$$;

create table a (
    id int,
    pov tstzrange,
    pop tstzrange
);

create table b (
    id int,
    pov tstzrange,
    pop tstzrange,
    a_id int
);

create trigger b_fk_a
after insert or update on b
for each row execute procedure sequenced_fk('a', 'id', 'a_id');

create trigger b_fk_a_ref
after insert or update on a
for each row execute procedure sequenced_fk_ref('b', 'id', 'a_id');


-- a.1 is valid from March 2021 to forever
insert into a (id, pov, pop) values (1, '[2021-03-01,)', pop_now());

-- b.1 is valid from March 2021 to forever and must exist within the valid time of a.1
insert into b (id, pov, pop, a_id) values (1, '[2021-03-01,)', pop_now(), 1);

-- try to update the remaining a pov without disabling b, while following bitemporal table semantics
-- works because of our hack in the reference table trigger to allow updates that end the pop even if they break the fk.
-- ideally we would simply use a deferrable trigger here, but that isn't supported in Yugabyte.
begin;
update a set pop = pop_end(a.pop) where id = 1 and upper(a.pov) is null; -- this would fail normally because the fk has to exist at the best known time
insert into a (id, pov, pop) values (1, '[2021-02-01,)', pop_now());
commit;

The goal is to be able to remove this:

    if upper(new.pop) is not null and tg_op = 'UPDATE' then
        return null;
    end if;

from the sequenced_fk_ref() trigger function and replace it with a deferrable constraint trigger.

[tyteen4a03]

Any updates on this?