one more minor issue related to error reporting. I've only applied v28, 0001 to 0005.
-- (parent_id, valid_at) REFERENCES [implicit] -- FOREIGN KEY part should specify PERIOD CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at daterange, parent_id int4range, CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) REFERENCES temporal_rng ); ERROR: number of referencing and referenced columns for foreign key disagree -- (parent_id, PERIOD valid_at) REFERENCES (id) CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at daterange, parent_id int4range, CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng (id) ); ERROR: foreign key uses PERIOD on the referencing table but not the referenced table these error messages seem somehow inconsistent with the comments above? + else + { + /* + * Check it's a btree; currently this can never fail since no other + * index AMs support unique indexes. If we ever did have other types + * of unique indexes, we'd need a way to determine which operator + * strategy number is equality. (Is it reasonable to insist that + * every such index AM use btree's number for equality?) + */ + if (amid != BTREE_AM_OID) + elog(ERROR, "only b-tree indexes are supported for foreign keys"); + eqstrategy = BTEqualStrategyNumber; + } the comments say never fail. but it actually failed. see: +-- (parent_id) REFERENCES [implicit] +-- This finds the PK (omitting the WITHOUT OVERLAPS element), +-- but it's not a b-tree index, so it fails anyway. +-- Anyway it must fail because the two sides have a different definition of "unique". +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id) + REFERENCES temporal_rng +); +ERROR: only b-tree indexes are supported for foreign keys because in transformFkeyGetPrimaryKey. we have `if (indexStruct->indisexclusion && i == indexStruct->indnatts - 1)` we have pk_with_period, fk_with_period in Constraint struct. maybe we can add a bool argument to transformFkeyGetPrimaryKey indicate, this primary key is a conperiod constraint. then we can check condition: the primary key is a conperiod constraint and fk_with_period or is pk_with_period is false I've made a patch to make these error reporting more accurate. you can further refine it.
v28-0001-refactor-transformFkeyGetPrimaryKey-for-bette.no-cfbot
Description: Binary data