V16 patch doc/src/sgml/html/sql-createtable.html doc SET NULL description:
`
SET NULL [ ( column_name [, ... ] ) ]
Set all of the referencing columns, or a specified subset of the
referencing columns, to null. A subset of columns can only be
specified for ON DELETE actions.
In a temporal foreign key, the change will use FOR PORTION OF
semantics to constrain the effect to the bounds of the referenced row.
`
I think it means, if the foreign key has PERIOD column[s], then the
PERIOD column[s] will not be set to NULL in {ON DELETE|ON UPDATE}. We
can also use FOR PORTION OF semantics to constrain the effect to the
bounds of the referenced row.
see below demo:
BEGIN;
drop table if exists temporal_rng CASCADE;
drop table if exists temporal_fk_rng2rng CASCADE;
CREATE unlogged TABLE temporal_rng (id int4range,valid_at tsrange);
ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY
(id, valid_at WITHOUT OVERLAPS);
CREATE unlogged TABLE temporal_fk_rng2rng (id int4range,valid_at
tsrange,parent_id int4range,
CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_rng (id, PERIOD valid_at) on update set null ON
DELETE SET NULL);
INSERT INTO temporal_rng VALUES ('[11,11]', tsrange('2018-01-01',
'2021-01-01'));
INSERT INTO temporal_fk_rng2rng VALUES ('[7,7]', tsrange('2018-01-01',
'2021-01-01'), '[11,11]');
DELETE FROM temporal_rng WHERE id = '[11,11]';
table temporal_fk_rng2rng;
commit;
-----------------------------------------------------
also
"REFERENCES temporal_rng (id, PERIOD valid_at) ON UPDATE SET NULL ON
DELETE SET NULL)"
is the same as
"REFERENCES temporal_rng (id, PERIOD valid_at) ON UPDATE SET NULL ON
DELETE SET NULL (parent_id)"
in the current implementation.
we might need to change the pg_constraint column "confdelsetcols" description.
-------
the above also applies to SET DEFAULT.
--------------------------------------------------------------------------------------------------------------------------
can you add the following for the sake of code coverage. I think
src/test/regress/sql/without_overlaps.sql can be simplified.
--- common template for test foreign key constraint.
CREATE OR REPLACE PROCEDURE overlap_template()
LANGUAGE SQL
AS $$
DROP TABLE IF EXISTS temporal_rng CASCADE;
DROP TABLE IF EXISTS temporal_fk_rng2rng CASCADE;
CREATE UNLOGGED TABLE temporal_rng (id int4range,valid_at tsrange);
ALTER TABLE temporal_rng
ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
CREATE UNLOGGED TABLE temporal_fk_rng2rng (
id int4range,
valid_at tsrange,
parent_id int4range,
CONSTRAINT temporal_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_rng (id, PERIOD valid_at)
ON UPDATE no action ON DELETE no action
DEFERRABLE
);
$$;
call overlap_template();
--- on update/delete restrict
-- coverage for TRI_FKey_restrict_upd,TRI_FKey_restrict_del.
BEGIN;
ALTER TABLE temporal_fk_rng2rng
DROP CONSTRAINT temporal_fk_rng2rng_fk,
ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_rng(id,PERIOD valid_at) ON UPDATE RESTRICT ON
DELETE RESTRICT;
INSERT INTO temporal_rng VALUES ('[11,11]', tsrange('2018-01-01',
'2021-01-01'));
INSERT INTO temporal_fk_rng2rng VALUES ('[7,7]', tsrange('2018-01-01',
'2020-01-01'), '[11,11]');
savepoint s;
UPDATE temporal_rng FOR PORTION OF valid_at FROM '2018-01-01' TO '2018-01-03'
SET id = '[9,9]' WHERE id = '[11,11]';
ROLLBACK to s;
delete from temporal_rng FOR PORTION OF valid_at FROM '2018-01-01' TO
'2020-01-01';
ROLLBACK to s;
--this one should not have error.
delete from temporal_rng FOR PORTION OF valid_at FROM '2020-01-01' TO
'2021-01-01';
table temporal_rng;
ROLLBACK;
-------------
--- on delete set column list coverage for function tri_set. branch
{if (riinfo->ndelsetcols != 0)}
BEGIN;
ALTER TABLE temporal_fk_rng2rng
DROP CONSTRAINT temporal_fk_rng2rng_fk,
ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_rng(id,PERIOD valid_at) ON DELETE set default(parent_id);
ALTER TABLE temporal_fk_rng2rng ALTER COLUMN parent_id SET DEFAULT '[2,2]';
ALTER TABLE temporal_fk_rng2rng ALTER COLUMN valid_at SET DEFAULT tsrange'(,)';
INSERT INTO temporal_rng VALUES ('[11,11]', tsrange('2018-01-01',
'2021-01-01'));
INSERT INTO temporal_fk_rng2rng VALUES ('[7,7]', tsrange('2018-01-01',
'2020-01-01'), '[11,11]');
insert into temporal_rng values('[2,2]','(,)');
savepoint s;
delete from temporal_rng FOR PORTION OF valid_at FROM '2018-01-01' TO
'2019-01-01' where id = '[11,11]';
-- delete from temporal_rng where id = '[11,11]';
table temporal_fk_rng2rng;
rollback;
