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

Commit578e71f

Browse files
committed
This should fix a bug where a row that was updated or
deleted that had another row inserted/updated to its oldvalue during the same statement or other statements before theintegrity check for noaction would incorrectly error. Thiscould happen in deferred constraints or due to triggers orfunctions. It's effectively a reworking of the previous patch thatdid a not exists to instead do a separate check.Stephan Szabo
1 parent9f1fc10 commit578e71f

File tree

1 file changed

+243
-4
lines changed

1 file changed

+243
-4
lines changed

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

Lines changed: 243 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,7 @@
1717
*
1818
* Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
1919
*
20-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.39 2002/06/21 02:59:38 momjian Exp $
20+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.40 2002/07/30 16:33:21 momjian Exp $
2121
*
2222
* ----------
2323
*/
@@ -130,19 +130,24 @@ static void ri_BuildQueryKeyFull(RI_QueryKey *key, Oid constr_id,
130130
int32constr_queryno,
131131
Relationfk_rel,Relationpk_rel,
132132
intargc,char**argv);
133+
staticvoidri_BuildQueryKeyPkCheck(RI_QueryKey*key,Oidconstr_id,
134+
int32constr_queryno,
135+
Relationpk_rel,
136+
intargc,char**argv);
133137
staticboolri_KeysEqual(Relationrel,HeapTupleoldtup,HeapTuplenewtup,
134138
RI_QueryKey*key,intpairidx);
135139
staticboolri_AllKeysUnequal(Relationrel,HeapTupleoldtup,HeapTuplenewtup,
136140
RI_QueryKey*key,intpairidx);
137141
staticboolri_OneKeyEqual(Relationrel,intcolumn,HeapTupleoldtup,
138142
HeapTuplenewtup,RI_QueryKey*key,intpairidx);
139143
staticboolri_AttributesEqual(Oidtypeid,Datumoldvalue,Datumnewvalue);
144+
staticboolri_Check_Pk_Match(Relationpk_rel,HeapTupleold_row,
145+
Oidtgoid,intmatch_type,inttgnargs,char**tgargs);
140146

141147
staticvoidri_InitHashTables(void);
142148
staticvoid*ri_FetchPreparedPlan(RI_QueryKey*key);
143149
staticvoidri_HashPreparedPlan(RI_QueryKey*key,void*plan);
144150

145-
146151
/* ----------
147152
* RI_FKey_check -
148153
*
@@ -385,6 +390,7 @@ RI_FKey_check(PG_FUNCTION_ARGS)
385390
if (SPI_connect()!=SPI_OK_CONNECT)
386391
elog(WARNING,"SPI_connect() failed in RI_FKey_check()");
387392

393+
388394
/*
389395
* Fetch or prepare a saved plan for the real check
390396
*/
@@ -512,6 +518,161 @@ RI_FKey_check_upd(PG_FUNCTION_ARGS)
512518
}
513519

514520

521+
/* ----------
522+
* ri_Check_Pk_Match
523+
*
524+
* Check for matching value of old pk row in current state for
525+
* noaction triggers. Returns false if no row was found and a fk row
526+
* could potentially be referencing this row, true otherwise.
527+
* ----------
528+
*/
529+
staticbool
530+
ri_Check_Pk_Match(Relationpk_rel,HeapTupleold_row,Oidtgoid,intmatch_type,inttgnargs,char**tgargs) {
531+
void*qplan;
532+
RI_QueryKeyqkey;
533+
boolisnull;
534+
Datumcheck_values[RI_MAX_NUMKEYS];
535+
charcheck_nulls[RI_MAX_NUMKEYS+1];
536+
inti;
537+
Oidsave_uid;
538+
boolresult;
539+
save_uid=GetUserId();
540+
541+
ri_BuildQueryKeyPkCheck(&qkey,tgoid,
542+
RI_PLAN_CHECK_LOOKUPPK,pk_rel,
543+
tgnargs,tgargs);
544+
545+
switch (ri_NullCheck(pk_rel,old_row,&qkey,RI_KEYPAIR_PK_IDX))
546+
{
547+
caseRI_KEYS_ALL_NULL:
548+
/*
549+
* No check - nothing could have been referencing this row anyway.
550+
*/
551+
return true;
552+
553+
caseRI_KEYS_SOME_NULL:
554+
555+
/*
556+
* This is the only case that differs between the three kinds
557+
* of MATCH.
558+
*/
559+
switch (match_type)
560+
{
561+
caseRI_MATCH_TYPE_FULL:
562+
caseRI_MATCH_TYPE_UNSPECIFIED:
563+
564+
/*
565+
* MATCH <unspecified>/FULL - if ANY column is null, we
566+
* can't be matching to this row already.
567+
*/
568+
return true;
569+
570+
caseRI_MATCH_TYPE_PARTIAL:
571+
572+
/*
573+
* MATCH PARTIAL - all non-null columns must match.
574+
* (not implemented, can be done by modifying the
575+
* query below to only include non-null columns, or by
576+
* writing a special version here)
577+
*/
578+
elog(ERROR,"MATCH PARTIAL not yet implemented");
579+
break;
580+
}
581+
582+
caseRI_KEYS_NONE_NULL:
583+
584+
/*
585+
* Have a full qualified key - continue below for all three
586+
* kinds of MATCH.
587+
*/
588+
break;
589+
}
590+
591+
if (SPI_connect()!=SPI_OK_CONNECT)
592+
elog(WARNING,"SPI_connect() failed in RI_FKey_check()");
593+
594+
595+
/*
596+
* Fetch or prepare a saved plan for the real check
597+
*/
598+
if ((qplan=ri_FetchPreparedPlan(&qkey))==NULL)
599+
{
600+
charquerystr[MAX_QUOTED_REL_NAME_LEN+100+
601+
(MAX_QUOTED_NAME_LEN+32)*RI_MAX_NUMKEYS];
602+
charpkrelname[MAX_QUOTED_REL_NAME_LEN];
603+
charattname[MAX_QUOTED_NAME_LEN];
604+
constchar*querysep;
605+
Oidqueryoids[RI_MAX_NUMKEYS];
606+
607+
/* ----------
608+
* The query string built is
609+
*SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...]
610+
* The type id's for the $ parameters are those of the
611+
* corresponding FK attributes. Thus, SPI_prepare could
612+
* eventually fail if the parser cannot identify some way
613+
* how to compare these two types by '='.
614+
* ----------
615+
*/
616+
quoteRelationName(pkrelname,pk_rel);
617+
sprintf(querystr,"SELECT 1 FROM ONLY %s x",pkrelname);
618+
querysep="WHERE";
619+
for (i=0;i<qkey.nkeypairs;i++)
620+
{
621+
quoteOneName(attname,
622+
tgargs[RI_FIRST_ATTNAME_ARGNO+i*2+RI_KEYPAIR_PK_IDX]);
623+
sprintf(querystr+strlen(querystr)," %s %s = $%d",
624+
querysep,attname,i+1);
625+
querysep="AND";
626+
queryoids[i]=SPI_gettypeid(pk_rel->rd_att,
627+
qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
628+
}
629+
strcat(querystr," FOR UPDATE OF x");
630+
631+
/*
632+
* Prepare, save and remember the new plan.
633+
*/
634+
qplan=SPI_prepare(querystr,qkey.nkeypairs,queryoids);
635+
qplan=SPI_saveplan(qplan);
636+
ri_HashPreparedPlan(&qkey,qplan);
637+
}
638+
639+
/*
640+
* We have a plan now. Build up the arguments for SPI_execp() from the
641+
* key values in the new FK tuple.
642+
*/
643+
for (i=0;i<qkey.nkeypairs;i++)
644+
{
645+
check_values[i]=SPI_getbinval(old_row,
646+
pk_rel->rd_att,
647+
qkey.keypair[i][RI_KEYPAIR_PK_IDX],
648+
&isnull);
649+
if (isnull)
650+
check_nulls[i]='n';
651+
else
652+
check_nulls[i]=' ';
653+
}
654+
check_nulls[i]='\0';
655+
656+
/*
657+
* Now check that foreign key exists in PK table
658+
*/
659+
660+
SetUserId(RelationGetForm(pk_rel)->relowner);
661+
662+
if (SPI_execp(qplan,check_values,check_nulls,1)!=SPI_OK_SELECT)
663+
elog(ERROR,"SPI_execp() failed in ri_Check_Pk_Match()");
664+
665+
SetUserId(save_uid);
666+
667+
result= (SPI_processed!=0);
668+
669+
if (SPI_finish()!=SPI_OK_FINISH)
670+
elog(WARNING,"SPI_finish() failed in ri_Check_Pk_Match()");
671+
672+
returnresult;
673+
}
674+
675+
515676
/* ----------
516677
* RI_FKey_noaction_del -
517678
*
@@ -535,6 +696,7 @@ RI_FKey_noaction_del(PG_FUNCTION_ARGS)
535696
chardel_nulls[RI_MAX_NUMKEYS+1];
536697
boolisnull;
537698
inti;
699+
intmatch_type;
538700
Oidsave_uid;
539701

540702
save_uid=GetUserId();
@@ -581,7 +743,18 @@ RI_FKey_noaction_del(PG_FUNCTION_ARGS)
581743
pk_rel=trigdata->tg_relation;
582744
old_row=trigdata->tg_trigtuple;
583745

584-
switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
746+
match_type=ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]);
747+
if (ri_Check_Pk_Match(pk_rel,old_row,trigdata->tg_trigger->tgoid,
748+
match_type,tgnargs,tgargs)) {
749+
/*
750+
* There's either another row, or no row could match this
751+
* one. In either case, we don't need to do the check.
752+
*/
753+
heap_close(fk_rel,RowShareLock);
754+
returnPointerGetDatum(NULL);
755+
}
756+
757+
switch (match_type)
585758
{
586759
/* ----------
587760
* SQL3 11.9 <referential constraint definition>
@@ -746,6 +919,7 @@ RI_FKey_noaction_upd(PG_FUNCTION_ARGS)
746919
charupd_nulls[RI_MAX_NUMKEYS+1];
747920
boolisnull;
748921
inti;
922+
intmatch_type;
749923
Oidsave_uid;
750924

751925
save_uid=GetUserId();
@@ -793,7 +967,18 @@ RI_FKey_noaction_upd(PG_FUNCTION_ARGS)
793967
new_row=trigdata->tg_newtuple;
794968
old_row=trigdata->tg_trigtuple;
795969

796-
switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
970+
match_type=ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]);
971+
if (ri_Check_Pk_Match(pk_rel,old_row,trigdata->tg_trigger->tgoid,
972+
match_type,tgnargs,tgargs)) {
973+
/*
974+
* There's either another row, or no row could match this
975+
* one. In either case, we don't need to do the check.
976+
*/
977+
heap_close(fk_rel,RowShareLock);
978+
returnPointerGetDatum(NULL);
979+
}
980+
981+
switch (match_type)
797982
{
798983
/* ----------
799984
* SQL3 11.9 <referential constraint definition>
@@ -3042,6 +3227,59 @@ ri_BuildQueryKeyFull(RI_QueryKey *key, Oid constr_id, int32 constr_queryno,
30423227
}
30433228
}
30443229

3230+
/* ----------
3231+
* ri_BuildQueryKeyPkCheck -
3232+
*
3233+
*Build up a new hashtable key for a prepared SPI plan of a
3234+
*check for PK rows in noaction triggers.
3235+
*
3236+
*constr_type is FULL
3237+
*constr_id is the OID of the pg_trigger row that invoked us
3238+
*constr_queryno is an internal number of the query inside the proc
3239+
*pk_relid is the OID of referenced relation
3240+
*nkeypairs is the number of keypairs
3241+
*following are the attribute number keypairs of the trigger invocation
3242+
*
3243+
*At least for MATCH FULL this builds a unique key per plan.
3244+
* ----------
3245+
*/
3246+
staticvoid
3247+
ri_BuildQueryKeyPkCheck(RI_QueryKey*key,Oidconstr_id,int32constr_queryno,
3248+
Relationpk_rel,
3249+
intargc,char**argv)
3250+
{
3251+
inti;
3252+
intj;
3253+
intfno;
3254+
3255+
/*
3256+
* Initialize the key and fill in type, oid's and number of keypairs
3257+
*/
3258+
memset((void*)key,0,sizeof(RI_QueryKey));
3259+
key->constr_type=RI_MATCH_TYPE_FULL;
3260+
key->constr_id=constr_id;
3261+
key->constr_queryno=constr_queryno;
3262+
key->fk_relid=0;
3263+
key->pk_relid=pk_rel->rd_id;
3264+
key->nkeypairs= (argc-RI_FIRST_ATTNAME_ARGNO) /2;
3265+
3266+
/*
3267+
* Lookup the attribute numbers of the arguments to the trigger call
3268+
* and fill in the keypairs.
3269+
*/
3270+
for (i=0,j=RI_FIRST_ATTNAME_ARGNO+RI_KEYPAIR_PK_IDX;j<argc;i++,j+=2)
3271+
{
3272+
fno=SPI_fnumber(pk_rel->rd_att,argv[j]);
3273+
if (fno==SPI_ERROR_NOATTRIBUTE)
3274+
elog(ERROR,"constraint %s: table %s does not have an attribute %s",
3275+
argv[RI_CONSTRAINT_NAME_ARGNO],
3276+
RelationGetRelationName(pk_rel),
3277+
argv[j+1]);
3278+
key->keypair[i][RI_KEYPAIR_PK_IDX]=fno;
3279+
key->keypair[i][RI_KEYPAIR_FK_IDX]=0;
3280+
}
3281+
}
3282+
30453283

30463284
/* ----------
30473285
* ri_NullCheck -
@@ -3378,3 +3616,4 @@ ri_AttributesEqual(Oid typeid, Datum oldvalue, Datum newvalue)
33783616
returnDatumGetBool(FunctionCall2(&(entry->oprfmgrinfo),
33793617
oldvalue,newvalue));
33803618
}
3619+

0 commit comments

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