Thread: pgsql: ALTER TABLE: skip FK validation when it's safe to do so

pgsql: ALTER TABLE: skip FK validation when it's safe to do so

From
Alvaro Herrera
Date:
ALTER TABLE: skip FK validation when it's safe to do so

We already skip rewriting the table in these cases, but we still force a
whole table scan to validate the data.  This can be skipped, and thus
we can make the whole ALTER TABLE operation just do some catalog touches
instead of scanning the table, when these two conditions hold:

(a) Old and new pg_constraint.conpfeqop match exactly.  This is actually
stronger than needed; we could loosen things by way of operator
families, but it'd require a lot more effort.

(b) The functions, if any, implementing a cast from the foreign type to
the primary opcintype are the same.  For this purpose, we can consider a
binary coercion equivalent to an exact type match.  When the opcintype
is polymorphic, require that the old and new foreign types match
exactly.  (Since ri_triggers.c does use the executor, the stronger check
for polymorphic types is no mere future-proofing.  However, no core type
exercises its necessity.)

Author: Noah Misch

Committer's note: catalog version bumped due to change of the Constraint
node.  I can't actually find any way to have such a node in a stored
rule, but given that we have "out" support for them, better be safe.

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/cb3a7c2b95a28e57c56562d48d2a3aa5eeb7fa29

Modified Files
--------------
src/backend/commands/tablecmds.c    |  186 ++++++++++++++++++++++++++++++++++-
src/backend/nodes/copyfuncs.c       |    1 +
src/backend/nodes/equalfuncs.c      |    1 +
src/backend/nodes/outfuncs.c        |    1 +
src/backend/utils/adt/ri_triggers.c |    1 +
src/include/catalog/catversion.h    |    2 +-
src/include/nodes/parsenodes.h      |    1 +
7 files changed, 187 insertions(+), 6 deletions(-)


Re: pgsql: ALTER TABLE: skip FK validation when it's safe to do so

From
Tom Lane
Date:
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Committer's note: catalog version bumped due to change of the Constraint
> node.  I can't actually find any way to have such a node in a stored
> rule, but given that we have "out" support for them, better be safe.

FYI, the easy way to determine this is to see if the node type has
readfuncs.c support.  If not, it's not usable in stored rules.  There
are a lot of node types that have outfuncs support for debugging
purposes, so looking at outfuncs alone can be misleading.

In this particular case I believe the catversion bump was unnecessary,
though of course it doesn't hurt.

            regards, tom lane