8000 During ALTER TABLE ADD FOREIGN KEY, try to check the existing rows using · postgrespro/postgres_cluster@fec58f6 · GitHub
[go: up one dir, main page]
Skip to content

Commit fec58f6

Browse files
tglsfdctglsfdc
committed
During ALTER TABLE ADD FOREIGN KEY, try to check the existing rows using
a single LEFT JOIN query instead of firing the check trigger for each row individually. Stephan Szabo, with some kibitzing from Tom Lane and Jan Wieck.
1 parent a0ab31d commit fec58f6

File tree

3 files changed

+229
-11
lines changed

3 files changed

+229
-11
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v 1.85 2003/10/02 06:36:37 petere Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v 1.86 2003/10/06 16:38:27 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -3454,6 +3454,13 @@ validateForeignKeyConstraint(FkConstraint *fkconstraint,
34543454
List *list;
34553455
int count;
34563456

3457+
/*
3458+
* See if we can do it with a single LEFT JOIN query. A FALSE result
3459+
* indicates we must proceed with the fire-the-trigger method.
3460+
*/
3461+
if (RI_Initial_Check(fkconstraint, rel, pkrel))
3462+
return;
3463+
34573464
/*
34583465
* Scan through each tuple, calling RI_FKey_check_ins (insert trigger)
34593466
* as if that tuple had just been inserted. If any of those fail, it

‎src/backend/utils/adt/ri_triggers.c

Lines changed: 217 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,7 @@
1717
*
1818
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
1919
*
20-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.61 2003/10/01 21:30:52 tgl Exp $
20+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.62 2003/10/06 16:38:28 tgl Exp $
2121
*
2222
* ----------
2323
*/
@@ -40,6 +40,7 @@
4040
#include "rewrite/rewriteHandler.h"
4141
#include "utils/lsyscache.h"
4242
#include "utils/typcache.h"
43+
#include "utils/acl.h"
4344
#include "miscadmin.h"
4445

4546

@@ -164,7 +165,8 @@ static void ri_ExtractValues(RI_QueryKey *qkey, int key_idx,
164165
Datum *vals, char *nulls);
165166
static void ri_ReportViolation(RI_QueryKey *qkey, const char *constrname,
166167
Relation pk_rel, Relation fk_rel,
167-
HeapTuple violator, bool spi_err);
168+
HeapTuple violator, TupleDesc tupdesc,
169+
bool spi_err);
168170

169171

170172
/* ----------
@@ -2540,7 +2542,205 @@ RI_FKey_keyequal_upd(TriggerData *trigdata)
25402542
}
25412543

25422544

2545+
/* ----------
2546+
* RI_Initial_Check -
2547+
*
2548+
* Check an entire table for non-matching values using a single query.
2549+
* This is not a trigger procedure, but is called during ALTER TABLE
2550+
* ADD FOREIGN KEY to validate the initial table contents.
2551+
*
2552+
* We expect that an exclusive lock has been taken on rel and pkrel;
2553+
* hence, we do not need to lock individual rows for the check.
2554+
*
2555+
* If the check fails because the current user doesn't have permissions
2556+
* to read both tables, return false to let our caller know that they will
2557+
* need to do something else to check the constraint.
2558+
* ----------
2559+
*/
2560+
bool
 9E88
2561+
RI_Initial_Check(FkConstraint *fkconstraint, Relation rel, Relation pkrel)
2562+
{
2563+
const char *constrname = fkconstraint->constr_name;
2564+
char querystr[MAX_QUOTED_REL_NAME_LEN * 2 + 250 +
2565+
(MAX_QUOTED_NAME_LEN + 32) * ((RI_MAX_NUMKEYS * 4)+1)];
2566+
char pkrelname[MAX_QUOTED_REL_NAME_LEN];
2567+
char relname[MAX_QUOTED_REL_NAME_LEN];
2568+
char attname[MAX_QUOTED_NAME_LEN];
2569+
char fkattname[MAX_QUOTED_NAME_LEN];
2570+
const char *sep;
2571+
List *list;
2572+
List *list2;
2573+
int spi_result;
2574+
void *qplan;
2575+
2576+
/*
2577+
* Check to make sure current user has enough permissions to do the
2578+
* test query. (If not, caller can fall back to the trigger method,
2579+
* which works because it changes user IDs on the fly.)
2580+
*
2581+
* XXX are there any other show-stopper conditions to check?
2582+
*/
2583+
if (pg_class_aclcheck(RelationGetRelid(rel), GetUserId(), ACL_SELECT) != ACLCHECK_OK)
2584+
return false;
2585+
if (pg_class_aclcheck(RelationGetRelid(pkrel), GetUserId(), ACL_SELECT) != ACLCHECK_OK)
2586+
return false;
2587+
2588+
/*----------
2589+
* The query string built is:
2590+
* SELECT fk.keycols FROM ONLY relname fk
2591+
* LEFT OUTER JOIN ONLY pkrelname pk
2592+
* ON (pk.pkkeycol1=fk.keycol1 [AND ...])
2593+
* WHERE pk.pkkeycol1 IS NULL AND
2594+
* For MATCH unspecified:
2595+
* (fk.keycol1 IS NOT NULL [AND ...])
2596+
* For MATCH FULL:
2597+
* (fk.keycol1 IS NOT NULL [OR ...])
2598+
*----------
2599+
*/
2600+
2601+
sprintf(querystr, "SELECT ");
2602+
sep="";
2603+
foreach(list, fkconstraint->fk_attrs)
2604+
{
2605+
quoteOneName(attname, strVal(lfirst(list)));
2606+
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr),
2607+
"%sfk.%s", sep, attname);
2608+
sep = ", ";
2609+
}
2610+
2611+
quoteRelationName(pkrelname, pkrel);
2612+
quoteRelationName(relname, rel);
2613+
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr),
2614+
" FROM ONLY %s fk LEFT OUTER JOIN ONLY %s pk ON (",
2615+
relname, pkrelname);
2616+
2617+
sep="";
2618+
for (list=fkconstraint->pk_attrs, list2=fkconstraint->fk_attrs;
2619+
list != NIL && list2 != NIL;
2620+
list=lnext(list), list2=lnext(list2))
2621+
{
2622+
quoteOneName(attname, strVal(lfirst(list)));
2623+
quoteOneName(fkattname, strVal(lfirst(list2)));
2624+
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr),
2625+
"%spk.%s=fk.%s",
2626+
sep, attname, fkattname);
2627+
sep = " AND ";
2628+
}
2629+
/*
2630+
* It's sufficient to test any one pk attribute for null to detect a
2631+
* join failure.
2632+
*/
2633+
quoteOneName(attname, strVal(lfirst(fkconstraint->pk_attrs)));
2634+
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr),
2635+
") WHERE pk.%s IS NULL AND (", attname);
2636+
2637+
sep="";
2638+
foreach(list, fkconstraint->fk_attrs)
2639+
{
2640+
quoteOneName(attname, strVal(lfirst(list)));
2641+
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr),
2642+
"%sfk.%s IS NOT NULL",
2643+
sep, attname);
2644+
switch (fkconstraint->fk_matchtype)
2645+
{
2646+
case FKCONSTR_MATCH_UNSPECIFIED:
2647+
sep=" AND ";
2648+
break;
2649+
case FKCONSTR_MATCH_FULL:
2650+
sep=" OR ";
2651+
break;
2652+
case FKCONSTR_MATCH_PARTIAL:
2653+
ereport(ERROR,
2654+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2655+
errmsg("MATCH PARTIAL not yet implemented")));
2656+
break;
2657+
default:
2658+
elog(ERROR, "unrecognized match type: %d",
2659+
fkconstraint->fk_matchtype);
2660+
break;
2661+
}
2662+
}
2663+
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr),
2664+
")");
2665+
2666+
if (SPI_connect() != SPI_OK_CONNECT)
2667+
elog(ERROR, "SPI_connect failed");
25432668

2669+
/*
2670+
* Generate the plan. We don't need to cache it, and there are no
2671+
* arguments to the plan.
2672+
*/
2673+
qplan = SPI_prepare(querystr, 0, NULL);
2674+
2675+
if (qplan == NULL)
2676+
elog(ERROR, "SPI_prepare returned %d for %s", SPI_result, querystr);
2677+
2678+
/*
2679+
* Run the plan. For safety we force a current query snapshot to be
2680+
* used. (In serializable mode, this arguably violates serializability,
2681+
* but we really haven't got much choice.) We need at most one tuple
2682+
* returned, so pass limit = 1.
2683+
*/
2684+
spi_result = SPI_execp_current(qplan, NULL, NULL, true, 1);
2685+
2686+
/* Check result */
2687+
if (spi_result != SPI_OK_SELECT)
2688+
elog(ERROR, "SPI_execp_current returned %d", spi_result);
2689+
2690+
/* Did we find a tuple violating the constraint? */
2691+
if (SPI_processed > 0)
2692+
{
2693+
HeapTuple tuple = SPI_tuptable->vals[0];
2694+
TupleDesc tupdesc = SPI_tuptable->tupdesc;
2695+
int nkeys = length(fkconstraint->fk_attrs);
2696+
int i;
2697+
RI_QueryKey qkey;
2698+
2699+
/*
2700+
* If it's MATCH FULL, and there are any nulls in the FK keys,
2701+
* complain about that rather than the lack of a match. MATCH FULL
2702+
* disallows partially-null FK rows.
2703+
*/
2704+
if (fkconstraint->fk_matchtype == FKCONSTR_MATCH_FULL)
2705+
{
2706+
bool isnull = false;
2707+
2708+
for (i = 1; i <= nkeys; i++)
2709+
{
2710+
(void) SPI_getbinval(tuple, tupdesc, i, &isnull);
2711+
if (isnull)
2712+
break;
2713+
}
2714+
if (isnull)
2715+
ereport(ERROR,
2716+
(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
2717+
errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"",
2718+
RelationGetRelationName(rel),
2719+
cons F438 trname),
2720+
errdetail("MATCH FULL does not allow mixing of null and nonnull key values.")));
2721+
}
2722+
2723+
/*
2724+
* Although we didn't cache the query, we need to set up a fake
2725+
* query key to pass to ri_ReportViolation.
2726+
*/
2727+
MemSet(&qkey, 0, sizeof(qkey));
2728+
qkey.constr_queryno = RI_PLAN_CHECK_LOOKUPPK;
2729+
qkey.nkeypairs = nkeys;
2730+
for (i = 0; i < nkeys; i++)
2731+
qkey.keypair[i][RI_KEYPAIR_FK_IDX] = i + 1;
2732+
2733+
ri_ReportViolation(&qkey, constrname,
2734+
pkrel, rel,
2735+
tuple, tupdesc,
2736+
false);
2737+
}
2738+
2739+
if (SPI_finish() != SPI_OK_FINISH)
2740+
elog(ERROR, "SPI_finish failed");
2741+
2742+
return true;
2743+
}
25442744

25452745

25462746
/* ----------
@@ -2782,6 +2982,9 @@ ri_PlanCheck(const char *querystr, int nargs, Oid *argtypes,
27822982
/* Create the plan */
27832983
qplan = SPI_prepare(querystr, nargs, argtypes);
27842984

2985+
if (qplan == NULL)
2986+
elog(ERROR, "SPI_prepare returned %d for %s", SPI_result, querystr);
2987+
27852988
/* Restore UID */
27862989
SetUserId(save_uid);
27872990

@@ -2905,6 +3108,7 @@ ri_PerformCheck(RI_QueryKey *qkey, void *qplan,
29053108
ri_ReportViolation(qkey, constrname ? constrname : "",
29063109
pk_rel, fk_rel,
29073110
new_tuple ? new_tuple : old_tuple,
3111+
NULL,
29083112
true);
29093113

29103114
/* XXX wouldn't it be clearer to do this part at the caller? */
@@ -2913,6 +3117,7 @@ ri_PerformCheck(RI_QueryKey *qkey, void *qplan,
29133117
ri_ReportViolation(qkey, constrname,
29143118
pk_rel, fk_rel,
29153119
new_tuple ? new_tuple : old_tuple,
3120+
NULL,
29163121
false);
29173122

29183123
return SPI_processed != 0;
@@ -2950,15 +3155,15 @@ ri_ExtractValues(RI_QueryKey *qkey, int key_idx,
29503155
static void
29513156
ri_ReportViolation(RI_QueryKey *qkey, const char *constrname,
29523157
Relation pk_rel, Relation fk_rel,
2953-
HeapTuple violator, bool spi_err)
3158+
HeapTuple violator, TupleDesc tupdesc,
3159+
bool spi_err)
29543160
{
29553161
#define BUFLENGTH 512
29563162
char key_names[BUFLENGTH];
29573163
char key_values[BUFLENGTH];
29583164
char *name_ptr = key_names;
29593165
char *val_ptr = key_values;
29603166
bool onfk;
2961-
Relation rel;
29623167
int idx,
29633168
key_idx;
29643169

@@ -2972,18 +3177,21 @@ ri_ReportViolation(RI_QueryKey *qkey, const char *constrname,
29723177
errhint("This is most likely due to a rule having rewritten the query.")));
29733178

29743179
/*
2975-
* rel is set to where the tuple description is coming from.
3180+
* Determine which relation to complain about. If tupdesc wasn't
3181+
* passed by caller, assume the violator tuple came from there.
29763182
*/
29773183
onfk = (qkey->constr_queryno == RI_PLAN_CHECK_LOOKUPPK);
29783184
if (onfk)
29793185
{
2980-
rel = fk_rel;
29813186
key_idx = RI_KEYPAIR_FK_IDX;
3187+
if (tupdesc == NULL)
3188+
tupdesc = fk_rel->rd_att;
29823189
}
29833190
else
29843191
{
2985-
rel = pk_rel;
29863192
key_idx = RI_KEYPAIR_PK_IDX;
3193+
if (tupdesc == NULL)
3194+
tupdesc = pk_rel->rd_att;
29873195
}
29883196

29893197
/*
@@ -3008,8 +3216,8 @@ ri_ReportViolation(RI_QueryKey *qkey, const char *constrname,
30083216
char *name,
30093217
*val;
30103218

3011-
name = SPI_fname(rel->rd_att, fnum);
3012-
val = SPI_getvalue(violator, rel->rd_att, fnum);
3219+
name = SPI_fname(tupdesc, fnum);
3220+
val = SPI_getvalue(violator, tupdesc, fnum);
30133221
if (!val)
30143222
val = "null";
30153223

‎src/include/commands/trigger.h

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
* $Id: trigger.h,v 1.43 2003/08/04 02:40:13 momjian Exp $
9+
* $Id: trigger.h,v 1.44 2003/10/06 16:38:28 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -197,5 +197,8 @@ extern void DeferredTriggerSetState(ConstraintsSetStmt *stmt);
197197
* in utils/adt/ri_triggers.c
198198
*/
199199
extern bool RI_FKey_keyequal_upd(TriggerData *trigdata);
200+
extern bool RI_Initial_Check(FkConstraint *fkconstraint,
201+
Relation rel,
202+
Relation pkrel);
200203

201204
#endif /* TRIGGER_H */

0 commit comments

Comments
 (0)
0