aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/utils/adt/ri_triggers.c187
-rw-r--r--src/test/regress/expected/foreign_key.out32
-rw-r--r--src/test/regress/sql/foreign_key.sql6
3 files changed, 31 insertions, 194 deletions
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index ac252b8de27..f1fe5fa7f68 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -207,11 +207,6 @@ static void ri_BuildQueryKeyPkCheck(RI_QueryKey *key,
int32 constr_queryno);
static bool ri_KeysEqual(Relation rel, HeapTuple oldtup, HeapTuple newtup,
const RI_ConstraintInfo *riinfo, bool rel_is_pk);
-static bool ri_AllKeysUnequal(Relation rel, HeapTuple oldtup, HeapTuple newtup,
- const RI_ConstraintInfo *riinfo, bool rel_is_pk);
-static bool ri_OneKeyEqual(Relation rel, int column,
- HeapTuple oldtup, HeapTuple newtup,
- const RI_ConstraintInfo *riinfo, bool rel_is_pk);
static bool ri_AttributesEqual(Oid eq_opr, Oid typeid,
Datum oldvalue, Datum newvalue);
static bool ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
@@ -1950,7 +1945,6 @@ RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
RI_QueryKey qkey;
SPIPlanPtr qplan;
int i;
- bool use_cached_query;
/*
* Check that this is a valid trigger call on the right time and event.
@@ -1985,7 +1979,7 @@ RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
/* ----------
* SQL3 11.9 <referential constraint definition>
* General rules 7) a) ii) 2):
- * MATCH FULL
+ * MATCH SIMPLE/FULL
* ... ON UPDATE SET NULL
* ----------
*/
@@ -2027,28 +2021,9 @@ RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
elog(ERROR, "SPI_connect failed");
/*
- * "MATCH SIMPLE" only changes columns corresponding to the
- * referenced columns that have changed in pk_rel. This means the
- * "SET attrn=NULL [, attrn=NULL]" string will be change as well.
- * In this case, we need to build a temporary plan rather than use
- * our cached plan, unless the update happens to change all
- * columns in the key. Fortunately, for the most common case of a
- * single-column foreign key, this will be true.
- *
- * In case you're wondering, the inequality check works because we
- * know that the old key value has no NULLs (see above).
- */
-
- use_cached_query = (riinfo.confmatchtype == FKCONSTR_MATCH_FULL) ||
- ri_AllKeysUnequal(pk_rel, old_row, new_row,
- &riinfo, true);
-
- /*
* Fetch or prepare a saved plan for the set null update operation
- * if possible, or build a temporary plan if not.
*/
- if (!use_cached_query ||
- (qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
+ if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
{
StringInfoData querybuf;
StringInfoData qualbuf;
@@ -2080,37 +2055,23 @@ RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
quoteOneName(attname,
RIAttName(fk_rel, riinfo.fk_attnums[i]));
-
- /*
- * MATCH SIMPLE - only change columns corresponding
- * to changed columns in pk_rel's key
- */
- if (riinfo.confmatchtype == FKCONSTR_MATCH_FULL ||
- !ri_OneKeyEqual(pk_rel, i, old_row, new_row,
- &riinfo, true))
- {
- appendStringInfo(&querybuf,
- "%s %s = NULL",
- querysep, attname);
- querysep = ",";
- }
+ appendStringInfo(&querybuf,
+ "%s %s = NULL",
+ querysep, attname);
sprintf(paramname, "$%d", i + 1);
ri_GenerateQual(&qualbuf, qualsep,
paramname, pk_type,
riinfo.pf_eq_oprs[i],
attname, fk_type);
+ querysep = ",";
qualsep = "AND";
queryoids[i] = pk_type;
}
appendStringInfoString(&querybuf, qualbuf.data);
- /*
- * Prepare the plan. Save it only if we're building the
- * "standard" plan.
- */
+ /* Prepare and save the plan */
qplan = ri_PlanCheck(querybuf.data, riinfo.nkeys, queryoids,
- &qkey, fk_rel, pk_rel,
- use_cached_query);
+ &qkey, fk_rel, pk_rel, true);
}
/*
@@ -2463,25 +2424,15 @@ RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
quoteOneName(attname,
RIAttName(fk_rel, riinfo.fk_attnums[i]));
-
- /*
- * MATCH SIMPLE - only change columns corresponding
- * to changed columns in pk_rel's key
- */
- if (riinfo.confmatchtype == FKCONSTR_MATCH_FULL ||
- !ri_OneKeyEqual(pk_rel, i, old_row, new_row,
- &riinfo, true))
- {
- appendStringInfo(&querybuf,
- "%s %s = DEFAULT",
- querysep, attname);
- querysep = ",";
- }
+ appendStringInfo(&querybuf,
+ "%s %s = DEFAULT",
+ querysep, attname);
sprintf(paramname, "$%d", i + 1);
ri_GenerateQual(&qualbuf, qualsep,
paramname, pk_type,
riinfo.pf_eq_oprs[i],
attname, fk_type);
+ querysep = ",";
qualsep = "AND";
queryoids[i] = pk_type;
}
@@ -3858,120 +3809,6 @@ ri_KeysEqual(Relation rel, HeapTuple oldtup, HeapTuple newtup,
/* ----------
- * ri_AllKeysUnequal -
- *
- * Check if all key values in OLD and NEW are not equal.
- * ----------
- */
-static bool
-ri_AllKeysUnequal(Relation rel, HeapTuple oldtup, HeapTuple newtup,
- const RI_ConstraintInfo *riinfo, bool rel_is_pk)
-{
- TupleDesc tupdesc = RelationGetDescr(rel);
- const int16 *attnums;
- const Oid *eq_oprs;
- int i;
-
- if (rel_is_pk)
- {
- attnums = riinfo->pk_attnums;
- eq_oprs = riinfo->pp_eq_oprs;
- }
- else
- {
- attnums = riinfo->fk_attnums;
- eq_oprs = riinfo->ff_eq_oprs;
- }
-
- for (i = 0; i < riinfo->nkeys; i++)
- {
- Datum oldvalue;
- Datum newvalue;
- bool isnull;
-
- /*
- * Get one attribute's oldvalue. If it is NULL - they're not equal.
- */
- oldvalue = SPI_getbinval(oldtup, tupdesc, attnums[i], &isnull);
- if (isnull)
- continue;
-
- /*
- * Get one attribute's newvalue. If it is NULL - they're not equal.
- */
- newvalue = SPI_getbinval(newtup, tupdesc, attnums[i], &isnull);
- if (isnull)
- continue;
-
- /*
- * Compare them with the appropriate equality operator.
- */
- if (ri_AttributesEqual(eq_oprs[i], RIAttType(rel, attnums[i]),
- oldvalue, newvalue))
- return false; /* found two equal items */
- }
-
- return true;
-}
-
-
-/* ----------
- * ri_OneKeyEqual -
- *
- * Check if one key value in OLD and NEW is equal. Note column is indexed
- * from zero.
- *
- * ri_KeysEqual could call this but would run a bit slower. For
- * now, let's duplicate the code.
- * ----------
- */
-static bool
-ri_OneKeyEqual(Relation rel, int column, HeapTuple oldtup, HeapTuple newtup,
- const RI_ConstraintInfo *riinfo, bool rel_is_pk)
-{
- TupleDesc tupdesc = RelationGetDescr(rel);
- const int16 *attnums;
- const Oid *eq_oprs;
- Datum oldvalue;
- Datum newvalue;
- bool isnull;
-
- if (rel_is_pk)
- {
- attnums = riinfo->pk_attnums;
- eq_oprs = riinfo->pp_eq_oprs;
- }
- else
- {
- attnums = riinfo->fk_attnums;
- eq_oprs = riinfo->ff_eq_oprs;
- }
-
- /*
- * Get one attribute's oldvalue. If it is NULL - they're not equal.
- */
- oldvalue = SPI_getbinval(oldtup, tupdesc, attnums[column], &isnull);
- if (isnull)
- return false;
-
- /*
- * Get one attribute's newvalue. If it is NULL - they're not equal.
- */
- newvalue = SPI_getbinval(newtup, tupdesc, attnums[column], &isnull);
- if (isnull)
- return false;
-
- /*
- * Compare them with the appropriate equality operator.
- */
- if (!ri_AttributesEqual(eq_oprs[column], RIAttType(rel, attnums[column]),
- oldvalue, newvalue))
- return false;
-
- return true;
-}
-
-/* ----------
* ri_AttributesEqual -
*
* Call the appropriate equality comparison operator for two values.
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index f86b39f396b..305dfe20d47 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -557,7 +557,7 @@ SELECT * from FKTABLE;
2 | | 3 | 3
| 2 | 7 | 4
| 3 | 4 | 5
- 1 | | 3 | 1
+ | | | 1
(6 rows)
-- Try to delete something that should set default
@@ -578,7 +578,7 @@ SELECT * from FKTABLE;
2 | | 3 | 3
| 2 | 7 | 4
| 3 | 4 | 5
- 1 | | 3 | 1
+ | | | 1
0 | | | 1
(6 rows)
@@ -599,7 +599,7 @@ SELECT * from FKTABLE;
2 | | 3 | 3
| 2 | 7 | 4
| 3 | 4 | 5
- 1 | | 3 | 1
+ | | | 1
0 | | | 1
(6 rows)
@@ -608,7 +608,7 @@ DROP TABLE PKTABLE;
-- set default update / set null delete
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
-CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int, ftest4 int, CONSTRAINT constrname3
+CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int DEFAULT -2, ftest4 int, CONSTRAINT constrname3
FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
ON DELETE SET NULL ON UPDATE SET DEFAULT);
-- Insert Primary Key values
@@ -645,9 +645,9 @@ SELECT * from FKTABLE;
-- Try to update something that will fail
UPDATE PKTABLE set ptest2=5 where ptest2=2;
ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3"
-DETAIL: Key (ftest1, ftest2, ftest3)=(1, -1, 3) is not present in table "pktable".
+DETAIL: Key (ftest1, ftest2, ftest3)=(0, -1, -2) is not present in table "pktable".
-- Try to update something that will set default
-UPDATE PKTABLE set ptest1=0, ptest2=5, ptest3=10 where ptest2=2;
+UPDATE PKTABLE set ptest1=0, ptest2=-1, ptest3=-2 where ptest2=2;
UPDATE PKTABLE set ptest2=10 where ptest2=4;
-- Try to update something that should not set default
UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
@@ -657,7 +657,7 @@ SELECT * from PKTABLE;
--------+--------+--------+--------
2 | 3 | 4 | test3
2 | -1 | 5 | test5
- 0 | 5 | 10 | test1
+ 0 | -1 | -2 | test1
2 | 10 | 5 | test4
1 | 2 | 3 | test2
(5 rows)
@@ -670,8 +670,8 @@ SELECT * from FKTABLE;
2 | | 3 | 3
| 2 | 7 | 4
| 3 | 4 | 5
- 0 | -1 | | 1
- 2 | -1 | 5 | 1
+ 0 | -1 | -2 | 1
+ 0 | -1 | -2 | 1
(7 rows)
-- Try to delete something that should set null
@@ -681,7 +681,7 @@ SELECT * from PKTABLE;
ptest1 | ptest2 | ptest3 | ptest4
--------+--------+--------+--------
2 | -1 | 5 | test5
- 0 | 5 | 10 | test1
+ 0 | -1 | -2 | test1
2 | 10 | 5 | test4
1 | 2 | 3 | test2
(4 rows)
@@ -693,18 +693,18 @@ SELECT * from FKTABLE;
2 | | 3 | 3
| 2 | 7 | 4
| 3 | 4 | 5
- 0 | -1 | | 1
- 2 | -1 | 5 | 1
+ 0 | -1 | -2 | 1
+ 0 | -1 | -2 | 1
| | | 1
(7 rows)
-- Try to delete something that should not set null
-DELETE FROM PKTABLE where ptest2=5;
+DELETE FROM PKTABLE where ptest2=-1 and ptest3=5;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
ptest1 | ptest2 | ptest3 | ptest4
--------+--------+--------+--------
- 2 | -1 | 5 | test5
+ 0 | -1 | -2 | test1
2 | 10 | 5 | test4
1 | 2 | 3 | test2
(3 rows)
@@ -716,8 +716,8 @@ SELECT * from FKTABLE;
2 | | 3 | 3
| 2 | 7 | 4
| 3 | 4 | 5
- 0 | -1 | | 1
- 2 | -1 | 5 | 1
+ 0 | -1 | -2 | 1
+ 0 | -1 | -2 | 1
| | | 1
(7 rows)
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 2c4c0c5606a..874d9f7a331 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -367,7 +367,7 @@ DROP TABLE PKTABLE;
-- set default update / set null delete
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
-CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int, ftest4 int, CONSTRAINT constrname3
+CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int DEFAULT -2, ftest4 int, CONSTRAINT constrname3
FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
ON DELETE SET NULL ON UPDATE SET DEFAULT);
@@ -397,7 +397,7 @@ SELECT * from FKTABLE;
UPDATE PKTABLE set ptest2=5 where ptest2=2;
-- Try to update something that will set default
-UPDATE PKTABLE set ptest1=0, ptest2=5, ptest3=10 where ptest2=2;
+UPDATE PKTABLE set ptest1=0, ptest2=-1, ptest3=-2 where ptest2=2;
UPDATE PKTABLE set ptest2=10 where ptest2=4;
-- Try to update something that should not set default
@@ -415,7 +415,7 @@ SELECT * from PKTABLE;
SELECT * from FKTABLE;
-- Try to delete something that should not set null
-DELETE FROM PKTABLE where ptest2=5;
+DELETE FROM PKTABLE where ptest2=-1 and ptest3=5;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-30 15:06:08 +0000