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

Commitdf87074

Browse files
committed
Improve conversion of legacy CREATE CONSTRAINT TRIGGER representation of
foreign keys, one more time. Insist on matching up all three triggers beforewe create a constraint; this will avoid creation of duplicate constraintsin scenarios where a broken FK constraint was repaired by re-adding theconstraint without removing the old partial trigger set. Basically, this willwork nicely in all cases where the FK was actually functioning correctly inthe database that was dumped. It will fail to restore an FK in just one casewhere we theoretically could restore it: where we find the referenced table'striggers and not the referencing table's trigger. However, in such a scenarioit's likely that the user doesn't even realize he still has an FK at all(since the more-likely-to-fail cases aren't enforced), and we'd probably notaccomplish much except to cause the reload to fail because the data doesn'tmeet the FK constraint. Also make the NOTICE logging still more verbose, byadding detail about which of the triggers were found. This seems about allwe can do without solving the problem of getting the user's attention atsession end.
1 parentc1a03be commitdf87074

File tree

1 file changed

+81
-56
lines changed

1 file changed

+81
-56
lines changed

‎src/backend/commands/trigger.c‎

Lines changed: 81 additions & 56 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.221 2007/11/04 21:25:55 tgl Exp $
10+
* $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.222 2007/11/05 19:00:25 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -222,11 +222,11 @@ CreateTrigger(CreateTrigStmt *stmt, Oid constraintOid)
222222
(list_length(stmt->args) %2)==0&&
223223
RI_FKey_trigger_type(funcoid)!=RI_TRIGGER_NONE)
224224
{
225-
ConvertTriggerToFK(stmt,funcoid);
226-
227225
/* Keep lock on target rel until end of xact */
228226
heap_close(rel,NoLock);
229227

228+
ConvertTriggerToFK(stmt,funcoid);
229+
230230
returnInvalidOid;
231231
}
232232

@@ -462,33 +462,44 @@ CreateTrigger(CreateTrigStmt *stmt, Oid constraintOid)
462462
*
463463
* The conversion is complex because a pre-7.3 foreign key involved three
464464
* separate triggers, which were reported separately in dumps. While the
465-
* single trigger on the referencing table can be ignored, we need info
466-
* from both of the triggers on the referenced table to build the constraint
467-
* declaration. Our approach is to save info from the first trigger seen
468-
* in a list in TopMemoryContext. When we see the second trigger we can
469-
* create the FK constraint and remove the list entry. We match triggers
470-
* together by comparing the trigger arguments (which include constraint
471-
* name, table and column names, so should be good enough).
465+
* single trigger on the referencing table adds no new information, we need
466+
* to know the trigger functions of both of the triggers on the referenced
467+
* table to build the constraint declaration. Also, due to lack of proper
468+
* dependency checking pre-7.3, it is possible that the source database had
469+
* an incomplete set of triggers resulting in an only partially enforced
470+
* FK constraint. (This would happen if one of the tables had been dropped
471+
* and re-created, but only if the DB had been affected by a 7.0 pg_dump bug
472+
* that caused loss of tgconstrrelid information.) We choose to translate to
473+
* an FK constraint only when we've seen all three triggers of a set. This is
474+
* implemented by storing unmatched items in a list in TopMemoryContext.
475+
* We match triggers together by comparing the trigger arguments (which
476+
* include constraint name, table and column names, so should be good enough).
472477
*/
473478
typedefstruct {
474479
List*args;/* list of (T_String) Values or NIL */
475-
Oidfuncoid;/*OID of triggerfunction */
476-
boolisupd;/*is ittheUPDATE trigger? */
480+
Oidfuncoids[3];/*OIDs of triggerfunctions */
481+
/*The three function OIDs are stored intheorder update, delete, child */
477482
}OldTriggerInfo;
478483

479484
staticvoid
480485
ConvertTriggerToFK(CreateTrigStmt*stmt,Oidfuncoid)
481486
{
482487
staticList*info_list=NIL;
483488

489+
staticconstchar*constfuncdescr[3]= {
490+
gettext_noop("Found referenced table's UPDATE trigger."),
491+
gettext_noop("Found referenced table's DELETE trigger."),
492+
gettext_noop("Found referencing table's trigger.")
493+
};
494+
484495
char*constr_name;
485496
char*fk_table_name;
486497
char*pk_table_name;
487498
charfk_matchtype=FKCONSTR_MATCH_UNSPECIFIED;
488499
List*fk_attrs=NIL;
489500
List*pk_attrs=NIL;
490501
StringInfoDatabuf;
491-
boolisupd;
502+
intfuncnum;
492503
OldTriggerInfo*info=NULL;
493504
ListCell*l;
494505
inti;
@@ -548,73 +559,99 @@ ConvertTriggerToFK(CreateTrigStmt *stmt, Oid funcoid)
548559
/* Identify class of trigger --- update, delete, or referencing-table */
549560
switch (funcoid)
550561
{
551-
caseF_RI_FKEY_CASCADE_DEL:
552-
caseF_RI_FKEY_RESTRICT_DEL:
553-
caseF_RI_FKEY_SETNULL_DEL:
554-
caseF_RI_FKEY_SETDEFAULT_DEL:
555-
caseF_RI_FKEY_NOACTION_DEL:
556-
isupd= false;
557-
break;
558-
559562
caseF_RI_FKEY_CASCADE_UPD:
560563
caseF_RI_FKEY_RESTRICT_UPD:
561564
caseF_RI_FKEY_SETNULL_UPD:
562565
caseF_RI_FKEY_SETDEFAULT_UPD:
563566
caseF_RI_FKEY_NOACTION_UPD:
564-
isupd= true;
567+
funcnum=0;
568+
break;
569+
570+
caseF_RI_FKEY_CASCADE_DEL:
571+
caseF_RI_FKEY_RESTRICT_DEL:
572+
caseF_RI_FKEY_SETNULL_DEL:
573+
caseF_RI_FKEY_SETDEFAULT_DEL:
574+
caseF_RI_FKEY_NOACTION_DEL:
575+
funcnum=1;
565576
break;
566577

567578
default:
568-
/* Ignore triggers on referencing table */
569-
ereport(NOTICE,
570-
(errmsg("ignoring incomplete trigger group for constraint \"%s\" %s",
571-
constr_name,buf.data)));
572-
return;
579+
funcnum=2;
580+
break;
573581
}
574582

575583
/* See if we have a match to this trigger */
576584
foreach(l,info_list)
577585
{
578586
info= (OldTriggerInfo*)lfirst(l);
579-
if (info->isupd!=isupd&&equal(info->args,stmt->args))
587+
if (info->funcoids[funcnum]==InvalidOid&&
588+
equal(info->args,stmt->args))
589+
{
590+
info->funcoids[funcnum]=funcoid;
580591
break;
592+
}
581593
}
582594

583595
if (l==NULL)
584596
{
585-
/* First trigger ofpair, sosave away what we need */
597+
/* First trigger ofset, socreate a new list entry */
586598
MemoryContextoldContext;
587599

588600
ereport(NOTICE,
589601
(errmsg("ignoring incomplete trigger group for constraint \"%s\" %s",
590-
constr_name,buf.data)));
602+
constr_name,buf.data),
603+
errdetail(funcdescr[funcnum])));
591604
oldContext=MemoryContextSwitchTo(TopMemoryContext);
592-
info= (OldTriggerInfo*)palloc(sizeof(OldTriggerInfo));
605+
info= (OldTriggerInfo*)palloc0(sizeof(OldTriggerInfo));
593606
info->args=copyObject(stmt->args);
594-
info->funcoid=funcoid;
595-
info->isupd=isupd;
607+
info->funcoids[funcnum]=funcoid;
596608
info_list=lappend(info_list,info);
597609
MemoryContextSwitchTo(oldContext);
598610
}
611+
elseif (info->funcoids[0]==InvalidOid||
612+
info->funcoids[1]==InvalidOid||
613+
info->funcoids[2]==InvalidOid)
614+
{
615+
/* Second trigger of set */
616+
ereport(NOTICE,
617+
(errmsg("ignoring incomplete trigger group for constraint \"%s\" %s",
618+
constr_name,buf.data),
619+
errdetail(funcdescr[funcnum])));
620+
}
599621
else
600622
{
601-
/* OK, we have apair, so make the FK constraint ALTER TABLE cmd */
623+
/* OK, we have aset, so make the FK constraint ALTER TABLE cmd */
602624
AlterTableStmt*atstmt=makeNode(AlterTableStmt);
603625
AlterTableCmd*atcmd=makeNode(AlterTableCmd);
604626
FkConstraint*fkcon=makeNode(FkConstraint);
605-
Oidupdfunc,
606-
delfunc;
607627

608628
ereport(NOTICE,
609629
(errmsg("converting trigger group into constraint \"%s\" %s",
610-
constr_name,buf.data)));
611-
612-
if (stmt->constrrel)
613-
atstmt->relation=stmt->constrrel;
630+
constr_name,buf.data),
631+
errdetail(funcdescr[funcnum])));
632+
if (funcnum==2)
633+
{
634+
/* This trigger is on the FK table */
635+
atstmt->relation=stmt->relation;
636+
if (stmt->constrrel)
637+
fkcon->pktable=stmt->constrrel;
638+
else
639+
{
640+
/* Work around ancient pg_dump bug that omitted constrrel */
641+
fkcon->pktable=makeRangeVar(NULL,pk_table_name);
642+
}
643+
}
614644
else
615645
{
616-
/* Work around ancient pg_dump bug that omitted constrrel */
617-
atstmt->relation=makeRangeVar(NULL,fk_table_name);
646+
/* This trigger is on the PK table */
647+
fkcon->pktable=stmt->relation;
648+
if (stmt->constrrel)
649+
atstmt->relation=stmt->constrrel;
650+
else
651+
{
652+
/* Work around ancient pg_dump bug that omitted constrrel */
653+
atstmt->relation=makeRangeVar(NULL,fk_table_name);
654+
}
618655
}
619656
atstmt->cmds=list_make1(atcmd);
620657
atstmt->relkind=OBJECT_TABLE;
@@ -624,22 +661,10 @@ ConvertTriggerToFK(CreateTrigStmt *stmt, Oid funcoid)
624661
fkcon->constr_name=NULL;
625662
else
626663
fkcon->constr_name=constr_name;
627-
fkcon->pktable=stmt->relation;
628664
fkcon->fk_attrs=fk_attrs;
629665
fkcon->pk_attrs=pk_attrs;
630666
fkcon->fk_matchtype=fk_matchtype;
631-
632-
if (isupd)
633-
{
634-
updfunc=funcoid;
635-
delfunc=info->funcoid;
636-
}
637-
else
638-
{
639-
updfunc=info->funcoid;
640-
delfunc=funcoid;
641-
}
642-
switch (updfunc)
667+
switch (info->funcoids[0])
643668
{
644669
caseF_RI_FKEY_NOACTION_UPD:
645670
fkcon->fk_upd_action=FKCONSTR_ACTION_NOACTION;
@@ -660,7 +685,7 @@ ConvertTriggerToFK(CreateTrigStmt *stmt, Oid funcoid)
660685
/* can't get here because of earlier checks */
661686
elog(ERROR,"confused about RI update function");
662687
}
663-
switch (delfunc)
688+
switch (info->funcoids[1])
664689
{
665690
caseF_RI_FKEY_NOACTION_DEL:
666691
fkcon->fk_del_action=FKCONSTR_ACTION_NOACTION;

0 commit comments

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