From 66cd057266c442969e30963344b898bf43727283 Mon Sep 17 00:00:00 2001 From: jian he Date: Mon, 18 Mar 2024 12:39:57 +0800 Subject: [PATCH v28 1/1] refactor transformFkeyGetPrimaryKey for better report. we use fk_with_period, pk_with_period to check the constraint is specified period or not. there is another case, does the primary key is conperiod or not. we need this information in ATAddForeignKeyConstraint for better error report. we can get the information in transformFkeyGetPrimaryKey. --- src/backend/commands/tablecmds.c | 21 +++++++++++++++---- .../regress/expected/without_overlaps.out | 8 +++---- 2 files changed, 21 insertions(+), 8 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 5f3cdf3d..497339f9 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -386,7 +386,7 @@ static int transformColumnNameList(Oid relId, List *colList, static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, List **attnamelist, int16 *attnums, Oid *atttypids, - Oid *opclasses); + Oid *opclasses, bool *is_period); static Oid transformFkeyCheckAttrs(Relation pkrel, int numattrs, int16 *attnums, bool with_period, Oid *opclasses); @@ -9820,6 +9820,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, Oid ffeqoperators[INDEX_MAX_KEYS] = {0}; int16 fkdelsetcols[INDEX_MAX_KEYS] = {0}; bool with_period; + bool is_period = false; /* is the foreign key corresponding primey key is period */ int i; int numfks, numpks, @@ -9951,7 +9952,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, numpks = transformFkeyGetPrimaryKey(pkrel, &indexOid, &fkconstraint->pk_attrs, pkattnum, pktypoid, - opclasses); + opclasses, &is_period); } else { @@ -9964,6 +9965,15 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, with_period, opclasses); } + if (is_period && !fkconstraint->fk_with_period) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("foreign key should specify PERIOD on the referencing table"))); + + if (is_period && !fkconstraint->pk_with_period) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("foreign key corresponding primary key should specify PERIOD on the referenced table"))); /* * Now we can check permissions. */ @@ -12130,14 +12140,14 @@ transformColumnNameList(Oid relId, List *colList, * * All parameters except pkrel are output parameters. Also, the function * return value is the number of attributes in the primary key. - * + * is_period return true is this primary key is period. * Used when the column list in the REFERENCES specification is omitted. */ static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, List **attnamelist, int16 *attnums, Oid *atttypids, - Oid *opclasses) + Oid *opclasses, bool *is_period) { List *indexoidlist; ListCell *indexoidscan; @@ -12213,7 +12223,10 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, * This should cause an error downstream if the FK uses PERIOD---and also if it doesn't! */ if (indexStruct->indisexclusion && i == indexStruct->indnatts - 1) + { + *is_period = true; break; /* don't include this item in the number of attributes returned */ + } attnums[i] = pkattno; atttypids[i] = attnumTypeId(pkrel, pkattno); diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index a3eecf1d..2258dc87 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -536,7 +536,7 @@ CREATE TABLE temporal_fk_rng2rng ( 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 +ERROR: foreign key should specify PERIOD on the referencing table -- (parent_id, PERIOD valid_at) REFERENCES (id) CREATE TABLE temporal_fk_rng2rng ( id int4range, @@ -580,7 +580,7 @@ CREATE TABLE temporal_fk_rng2rng ( CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id) REFERENCES temporal_rng ); -ERROR: only b-tree indexes are supported for foreign keys +ERROR: foreign key should specify PERIOD on the referencing table -- should fail because of duplicate referenced columns: CREATE TABLE temporal_fk_rng2rng ( id int4range, @@ -950,7 +950,7 @@ CREATE TABLE temporal_fk_mltrng2mltrng ( CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at) REFERENCES temporal_mltrng ); -ERROR: number of referencing and referenced columns for foreign key disagree +ERROR: foreign key should specify PERIOD on the referencing table -- (parent_id, PERIOD valid_at) REFERENCES (id) CREATE TABLE temporal_fk_mltrng2mltrng ( id int4range, @@ -994,7 +994,7 @@ CREATE TABLE temporal_fk_mltrng2mltrng ( CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id) REFERENCES temporal_mltrng ); -ERROR: only b-tree indexes are supported for foreign keys +ERROR: foreign key should specify PERIOD on the referencing table -- should fail because of duplicate referenced columns: CREATE TABLE temporal_fk_mltrng2mltrng ( id int4range, base-commit: 949300402bde06699a23f216e26343e3503ba78b prerequisite-patch-id: 346209dd958e3cf2b4906b330ec5b3055ef22f41 prerequisite-patch-id: 1efe6850582096511f42fb15f5b6d4976489f09d prerequisite-patch-id: 0a55c313b54326b39c9fc57a46a288107b6758ee prerequisite-patch-id: ac0c24f89266f810b290c3f666748735c7e97742 prerequisite-patch-id: 61f1f978d24421ead74798d102f5022d4f5ef77d -- 2.34.1