Thread: pgsql: Fix up foreign-key mechanism so that there is a sound semantic
pgsql: Fix up foreign-key mechanism so that there is a sound semantic
From
tgl@postgresql.org (Tom Lane)
Date:
Log Message: ----------- 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. Modified Files: -------------- pgsql/doc/src/sgml: catalogs.sgml (r2.144 -> r2.145) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/catalogs.sgml.diff?r1=2.144&r2=2.145) trigger.sgml (r1.49 -> r1.50) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/trigger.sgml.diff?r1=1.49&r2=1.50) pgsql/src/backend/catalog: aclchk.c (r1.136 -> r1.137) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/catalog/aclchk.c.diff?r1=1.136&r2=1.137) dependency.c (r1.63 -> r1.64) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/catalog/dependency.c.diff?r1=1.63&r2=1.64) heap.c (r1.316 -> r1.317) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/catalog/heap.c.diff?r1=1.316&r2=1.317) index.c (r1.278 -> r1.279) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/catalog/index.c.diff?r1=1.278&r2=1.279) namespace.c (r1.91 -> r1.92) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/catalog/namespace.c.diff?r1=1.91&r2=1.92) pg_constraint.c (r1.34 -> r1.35) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/catalog/pg_constraint.c.diff?r1=1.34&r2=1.35) pg_conversion.c (r1.34 -> r1.35) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/catalog/pg_conversion.c.diff?r1=1.34&r2=1.35) pgsql/src/backend/commands: conversioncmds.c (r1.30 -> r1.31) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/conversioncmds.c.diff?r1=1.30&r2=1.31) explain.c (r1.153 -> r1.154) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/explain.c.diff?r1=1.153&r2=1.154) tablecmds.c (r1.213 -> r1.214) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/tablecmds.c.diff?r1=1.213&r2=1.214) trigger.c (r1.212 -> r1.213) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/trigger.c.diff?r1=1.212&r2=1.213) typecmds.c (r1.99 -> r1.100) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/typecmds.c.diff?r1=1.99&r2=1.100) pgsql/src/backend/tcop: utility.c (r1.271 -> r1.272) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/tcop/utility.c.diff?r1=1.271&r2=1.272) pgsql/src/backend/utils/adt: ri_triggers.c (r1.90 -> r1.91) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/ri_triggers.c.diff?r1=1.90&r2=1.91) ruleutils.c (r1.248 -> r1.249) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/ruleutils.c.diff?r1=1.248&r2=1.249) pgsql/src/backend/utils/cache: lsyscache.c (r1.147 -> r1.148) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/cache/lsyscache.c.diff?r1=1.147&r2=1.148) syscache.c (r1.110 -> r1.111) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/cache/syscache.c.diff?r1=1.110&r2=1.111) pgsql/src/bin/pg_dump: pg_dump.c (r1.459 -> r1.460) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_dump/pg_dump.c.diff?r1=1.459&r2=1.460) pgsql/src/bin/psql: describe.c (r1.150 -> r1.151) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/psql/describe.c.diff?r1=1.150&r2=1.151) pgsql/src/include/catalog: catversion.h (r1.383 -> r1.384) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/catalog/catversion.h.diff?r1=1.383&r2=1.384) indexing.h (r1.97 -> r1.98) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/catalog/indexing.h.diff?r1=1.97&r2=1.98) pg_constraint.h (r1.24 -> r1.25) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/catalog/pg_constraint.h.diff?r1=1.24&r2=1.25) pg_trigger.h (r1.26 -> r1.27) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/catalog/pg_trigger.h.diff?r1=1.26&r2=1.27) pgsql/src/include/commands: trigger.h (r1.60 -> r1.61) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/commands/trigger.h.diff?r1=1.60&r2=1.61) pgsql/src/include/utils: lsyscache.h (r1.116 -> r1.117) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/utils/lsyscache.h.diff?r1=1.116&r2=1.117) rel.h (r1.96 -> r1.97) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/utils/rel.h.diff?r1=1.96&r2=1.97) syscache.h (r1.67 -> r1.68) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/utils/syscache.h.diff?r1=1.67&r2=1.68) pgsql/src/test/regress/expected: alter_table.out (r1.100 -> r1.101) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/expected/alter_table.out.diff?r1=1.100&r2=1.101) foreign_key.out (r1.41 -> r1.42) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/expected/foreign_key.out.diff?r1=1.41&r2=1.42) pgsql/src/test/regress/sql: alter_table.sql (r1.55 -> r1.56) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/sql/alter_table.sql.diff?r1=1.55&r2=1.56) foreign_key.sql (r1.17 -> r1.18) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/sql/foreign_key.sql.diff?r1=1.17&r2=1.18)