Skip to content

Commit 7bddca3

Browse files
tglsfdctglsfdc
committed
Fix up foreign-key mechanism so that there is a sound semantic basis for the
equality checks it applies, instead of a random dependence on whatever operators might be named "=". The equality operators will now be selected from the opfamily of the unique index that the FK constraint depends on to enforce uniqueness of the referenced columns; therefore they are certain to be consistent with that index's notion of equality. Among other things this should fix the problem noted awhile back that pg_dump may fail for foreign-key constraints on user-defined types when the required operators aren't in the search path. This also means that the former warning condition about "foreign key constraint will require costly sequential scans" is gone: if the comparison condition isn't indexable then we'll reject the constraint entirely. All per past discussions. Along the way, make the RI triggers look into pg_constraint for their information, instead of using pg_trigger.tgargs; and get rid of the always error-prone fixed-size string buffers in ri_triggers.c in favor of building up the RI queries in StringInfo buffers. initdb forced due to columns added to pg_constraint and pg_trigger.
1 parent 65e2f55 commit 7bddca3

33 files changed

+1709
-1643
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 49 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.144 2007/01/31 20:56:16 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.145 2007/02/14 01:58:55 tgl Exp $ -->
22
<!--
33
Documentation of the system catalogs, directed toward PostgreSQL developers
44
-->
@@ -1870,6 +1870,27 @@
18701870
<entry>If a foreign key, list of the referenced columns</entry>
18711871
</row>
18721872

1873+
<row>
1874+
<entry><structfield>conpfeqop</structfield></entry>
1875+
<entry><type>oid[]</type></entry>
1876+
<entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</></entry>
1877+
<entry>If a foreign key, list of the equality operators for PK = FK comparisons</entry>
1878+
</row>
1879+
1880+
<row>
1881+
<entry><structfield>conppeqop</structfield></entry>
1882+
<entry><type>oid[]</type></entry>
1883+
<entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</></entry>
1884+
<entry>If a foreign key, list of the equality operators for PK = PK comparisons</entry>
1885+
</row>
1886+
1887+
<row>
1888+
<entry><structfield>conffeqop</structfield></entry>
1889+
<entry><type>oid[]</type></entry>
1890+
<entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</></entry>
1891+
<entry>If a foreign key, list of the equality operators for FK = FK comparisons</entry>
1892+
</row>
1893+
18731894
<row>
18741895
<entry><structfield>conbin</structfield></entry>
18751896
<entry><type>text</type></entry>
@@ -1899,8 +1920,8 @@
18991920
<note>
19001921
<para>
19011922
<literal>pg_class.relchecks</literal> needs to agree with the
1902-
number of check-constraint entries found in this table for the
1903-
given relation.
1923+
number of check-constraint entries found in this table for each
1924+
relation.
19041925
</para>
19051926
</note>
19061927

@@ -4166,35 +4187,42 @@
41664187
<entry><structfield>tgisconstraint</structfield></entry>
41674188
<entry><type>bool</type></entry>
41684189
<entry></entry>
4169-
<entry>True if trigger implements a referential integrity constraint</entry>
4190+
<entry>True if trigger is a <quote>constraint trigger</></entry>
41704191
</row>
41714192

41724193
<row>
41734194
<entry><structfield>tgconstrname</structfield></entry>
41744195
<entry><type>name</type></entry>
41754196
<entry></entry>
4176-
<entry>Referential integrity constraint name</entry>
4197+
<entry>Constraint name, if a constraint trigger</entry>
41774198
</row>
41784199

41794200
<row>
41804201
<entry><structfield>tgconstrrelid</structfield></entry>
41814202
<entry><type>oid</type></entry>
41824203
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
4183-
<entry>The table referenced by an referential integrity constraint</entry>
4204+
<entry>The table referenced by a referential integrity constraint</entry>
4205+
</row>
4206+
4207+
<row>
4208+
<entry><structfield>tgconstraint</structfield></entry>
4209+
<entry><type>oid</type></entry>
4210+
<entry><literal><link linkend="catalog-pg-constraint"><structname>pg_constraint</structname></link>.oid</literal></entry>
4211+
<entry>The <structname>pg_constraint</> entry owning the trigger, if any</entry>
41844212
</row>
41854213

41864214
<row>
41874215
<entry><structfield>tgdeferrable</structfield></entry>
41884216
<entry><type>bool</type></entry>
41894217
<entry></entry>
4190-
<entry>True if deferrable</entry>
4218+
<entry>True if constraint trigger is deferrable</entry>
41914219
</row>
41924220

41934221
<row>
41944222
<entry><structfield>tginitdeferred</structfield></entry>
41954223
<entry><type>bool</type></entry>
41964224
<entry></entry>
4197-
<entry>True if initially deferred</entry>
4225+
<entry>True if constraint trigger is initially deferred</entry>
41984226
</row>
41994227

42004228
<row>
@@ -4221,10 +4249,22 @@
42214249
</tgroup>
42224250
</table>
42234251

4252+
<note>
4253+
<para>
4254+
When <structfield>tgconstraint</> is nonzero,
4255+
<structfield>tgisconstraint</> must be true, and
4256+
<structfield>tgconstrname</>, <structfield>tgconstrrelid</>,
4257+
<structfield>tgdeferrable</>, <structfield>tginitdeferred</> are redundant
4258+
with the referenced <structname>pg_constraint</> entry. The reason we
4259+
keep these fields is that we support <quote>stand-alone</> constraint
4260+
triggers with no corresponding <structname>pg_constraint</> entry.
4261+
</para>
4262+
</note>
4263+
42244264
<note>
42254265
<para>
42264266
<literal>pg_class.reltriggers</literal> needs to agree with the
4227-
number of triggers found in this table for the given relation.
4267+
number of triggers found in this table for each relation.
42284268
</para>
42294269
</note>
42304270

‎doc/src/sgml/trigger.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.49 2007/02/01 00:28:18 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.50 2007/02/14 01:58:56 tgl Exp $ -->
22

33
<chapter id="triggers">
44
<title>Triggers</title>
@@ -467,6 +467,7 @@ typedef struct Trigger
467467
bool tgenabled;
468468
bool tgisconstraint;
469469
Oid tgconstrrelid;
470+
Oid tgconstraint;
470471
bool tgdeferrable;
471472
bool tginitdeferred;
472473
int16 tgnargs;

‎src/backend/catalog/aclchk.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/aclchk.c,v 1.136 2007/02/01 19:10:25 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/aclchk.c,v 1.137 2007/02/14 01:58:56 tgl Exp $
1212
*
1313
* NOTES
1414
* See acl.h.
@@ -2314,7 +2314,7 @@ pg_conversion_ownercheck(Oid conv_oid, Oid roleid)
23142314
if (superuser_arg(roleid))
23152315
return true;
23162316

2317-
tuple = SearchSysCache(CONOID,
2317+
tuple = SearchSysCache(CONVOID,
23182318
ObjectIdGetDatum(conv_oid),
23192319
0, 0, 0);
23202320
if (!HeapTupleIsValid(tuple))

‎src/backend/catalog/dependency.c

Lines changed: 10 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/dependency.c,v 1.63 2007/02/01 19:10:25 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/dependency.c,v 1.64 2007/02/14 01:58:56 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1758,29 +1758,16 @@ getObjectDescription(const ObjectAddress *object)
17581758

17591759
case OCLASS_CONSTRAINT:
17601760
{
1761-
Relation conDesc;
1762-
ScanKeyData skey[1];
1763-
SysScanDesc rcscan;
1764-
HeapTuple tup;
1761+
HeapTuple conTup;
17651762
Form_pg_constraint con;
17661763

1767-
conDesc = heap_open(ConstraintRelationId, AccessShareLock);
1768-
1769-
ScanKeyInit(&skey[0],
1770-
ObjectIdAttributeNumber,
1771-
BTEqualStrategyNumber, F_OIDEQ,
1772-
ObjectIdGetDatum(object->objectId));
1773-
1774-
rcscan = systable_beginscan(conDesc, ConstraintOidIndexId, true,
1775-
SnapshotNow, 1, skey);
1776-
1777-
tup = systable_getnext(rcscan);
1778-
1779-
if (!HeapTupleIsValid(tup))
1780-
elog(ERROR, "could not find tuple for constraint %u",
1764+
conTup = SearchSysCache(CONSTROID,
1765+
ObjectIdGetDatum(object->objectId),
1766+
0, 0, 0);
1767+
if (!HeapTupleIsValid(conTup))
1768+
elog(ERROR, "cache lookup failed for constraint %u",
17811769
object->objectId);
1782-
1783-
con = (Form_pg_constraint) GETSTRUCT(tup);
1770+
con = (Form_pg_constraint) GETSTRUCT(conTup);
17841771

17851772
if (OidIsValid(con->conrelid))
17861773
{
@@ -1794,16 +1781,15 @@ getObjectDescription(const ObjectAddress *object)
17941781
NameStr(con->conname));
17951782
}
17961783

1797-
systable_endscan(rcscan);
1798-
heap_close(conDesc, AccessShareLock);
1784+
ReleaseSysCache(conTup);
17991785
break;
18001786
}
18011787

18021788
case OCLASS_CONVERSION:
18031789
{
18041790
HeapTuple conTup;
18051791

1806-
conTup = SearchSysCache(CONOID,
1792+
conTup = SearchSysCache(CONVOID,
18071793
ObjectIdGetDatum(object->objectId),
18081794
0, 0, 0);
18091795
if (!HeapTupleIsValid(conTup))

‎src/backend/catalog/heap.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.316 2007/01/05 22:19:24 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.317 2007/02/14 01:58:56 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -1463,6 +1463,9 @@ StoreRelCheck(Relation rel, char *ccname, char *ccbin)
14631463
InvalidOid, /* not a domain constraint */
14641464
InvalidOid, /* Foreign key fields */
14651465
NULL,
1466+
NULL,
1467+
NULL,
1468+
NULL,
14661469
0,
14671470
' ',
14681471
' ',

‎src/backend/catalog/index.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.278 2007/02/01 19:10:25 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.279 2007/02/14 01:58:56 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -680,6 +680,9 @@ index_create(Oid heapRelationId,
680680
InvalidOid, /* no domain */
681681
InvalidOid, /* no foreign key */
682682
NULL,
683+
NULL,
684+
NULL,
685+
NULL,
683686
0,
684687
' ',
685688
' ',

‎src/backend/catalog/namespace.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
* Portions Copyright (c) 1994, Regents of the University of California
1414
*
1515
* IDENTIFICATION
16-
* $PostgreSQL: pgsql/src/backend/catalog/namespace.c,v 1.91 2007/02/01 19:10:25 momjian Exp $
16+
* $PostgreSQL: pgsql/src/backend/catalog/namespace.c,v 1.92 2007/02/14 01:58:56 tgl Exp $
1717
*
1818
*-------------------------------------------------------------------------
1919
*/
@@ -1199,7 +1199,7 @@ ConversionIsVisible(Oid conid)
11991199
Oid connamespace;
12001200
bool visible;
12011201

1202-
contup = SearchSysCache(CONOID,
1202+
contup = SearchSysCache(CONVOID,
12031203
ObjectIdGetDatum(conid),
12041204
0, 0, 0);
12051205
if (!HeapTupleIsValid(contup))

0 commit comments

Comments
 (0)