Fix up foreign-key mechanism so that there is a sound semantic basis for the

Tom Lane <tgl@sss.pgh.pa.us>

Commit: 7bddca3450cc8631e5bf05e43988cf10ae32230e
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: 2007-02-14T01:58:58Z
Releases: 8.3.0
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.

Files

PathChange+/−
doc/src/sgml/catalogs.sgml modified +49 −9
doc/src/sgml/trigger.sgml modified +2 −1
src/backend/catalog/aclchk.c modified +2 −2
src/backend/catalog/dependency.c modified +10 −24
src/backend/catalog/heap.c modified +4 −1
src/backend/catalog/index.c modified +4 −1
src/backend/catalog/namespace.c modified +2 −2
src/backend/catalog/pg_constraint.c modified +80 −108
src/backend/catalog/pg_conversion.c modified +3 −3
src/backend/commands/conversioncmds.c modified +3 −3
src/backend/commands/explain.c modified +3 −3
src/backend/commands/tablecmds.c modified +130 −433
src/backend/commands/trigger.c modified +53 −72
src/backend/commands/typecmds.c modified +4 −1
src/backend/tcop/utility.c modified +2 −2
src/backend/utils/adt/ri_triggers.c modified +1013 −759
src/backend/utils/adt/ruleutils.c modified +16 −32
src/backend/utils/cache/lsyscache.c modified +31 −3
src/backend/utils/cache/syscache.c modified +15 −2
src/bin/pg_dump/pg_dump.c modified +21 −2
src/bin/psql/describe.c modified +5 −13
src/include/catalog/catversion.h modified +2 −2
src/include/catalog/indexing.h modified +1 −4
src/include/catalog/pg_constraint.h modified +28 −6
src/include/catalog/pg_trigger.h modified +28 −15
src/include/commands/trigger.h modified +5 −31
src/include/utils/lsyscache.h modified +2 −1
src/include/utils/rel.h modified +2 −1
src/include/utils/syscache.h modified +38 −37
src/test/regress/expected/alter_table.out modified +34 −14
src/test/regress/expected/foreign_key.out modified +56 −36
src/test/regress/sql/alter_table.sql modified +27 −8
src/test/regress/sql/foreign_key.sql modified +34 −12