Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitfec58f6

Browse files
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 eachrow individually. Stephan Szabo, with some kibitzing from Tom Lane andJan Wieck.
1 parenta0ab31d commitfec58f6

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
intcount;
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
staticvoidri_ReportViolation(RI_QueryKey*qkey,constchar*constrname,
166167
Relationpk_rel,Relationfk_rel,
167-
HeapTupleviolator,boolspi_err);
168+
HeapTupleviolator,TupleDesctupdesc,
169+
boolspi_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
2561+
RI_Initial_Check(FkConstraint*fkconstraint,Relationrel,Relationpkrel)
2562+
{
2563+
constchar*constrname=fkconstraint->constr_name;
2564+
charquerystr[MAX_QUOTED_REL_NAME_LEN*2+250+
2565+
(MAX_QUOTED_NAME_LEN+32)* ((RI_MAX_NUMKEYS*4)+1)];
2566+
charpkrelname[MAX_QUOTED_REL_NAME_LEN];
2567+
charrelname[MAX_QUOTED_REL_NAME_LEN];
2568+
charattname[MAX_QUOTED_NAME_LEN];
2569+
charfkattname[MAX_QUOTED_NAME_LEN];
2570+
constchar*sep;
2571+
List*list;
2572+
List*list2;
2573+
intspi_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+
caseFKCONSTR_MATCH_UNSPECIFIED:
2647+
sep=" AND ";
2648+
break;
2649+
caseFKCONSTR_MATCH_FULL:
2650+
sep=" OR ";
2651+
break;
2652+
caseFKCONSTR_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+
HeapTupletuple=SPI_tuptable->vals[0];
2694+
TupleDesctupdesc=SPI_tuptable->tupdesc;
2695+
intnkeys=length(fkconstraint->fk_attrs);
2696+
inti;
2697+
RI_QueryKeyqkey;
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+
boolisnull= 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+
constrname),
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
returnSPI_processed!=0;
@@ -2950,15 +3155,15 @@ ri_ExtractValues(RI_QueryKey *qkey, int key_idx,
29503155
staticvoid
29513156
ri_ReportViolation(RI_QueryKey*qkey,constchar*constrname,
29523157
Relationpk_rel,Relationfk_rel,
2953-
HeapTupleviolator,boolspi_err)
3158+
HeapTupleviolator,TupleDesctupdesc,
3159+
boolspi_err)
29543160
{
29553161
#defineBUFLENGTH512
29563162
charkey_names[BUFLENGTH];
29573163
charkey_values[BUFLENGTH];
29583164
char*name_ptr=key_names;
29593165
char*val_ptr=key_values;
29603166
boolonfk;
2961-
Relationrel;
29623167
intidx,
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
externboolRI_FKey_keyequal_upd(TriggerData*trigdata);
200+
externboolRI_Initial_Check(FkConstraint*fkconstraint,
201+
Relationrel,
202+
Relationpkrel);
200203

201204
#endif/* TRIGGER_H */

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp