Skip site navigation (1) Skip section navigation (2)

Avoid FK validations for no-rewrite ALTER TABLE ALTER TYPE

From: Noah Misch <noah(at)leadboat(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Avoid FK validations for no-rewrite ALTER TABLE ALTER TYPE
Date: 2012-01-04 20:24:27
Message-ID: 20120104202427.GB27982@tornado.leadboat.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Git master can already avoid rewriting the table for column type changes like
varchar(10) -> varchar(20).  However, if the column in question is on either
side of a FK relationship, we always revalidate the foreign key.  Concretely,
I wanted these no-rewrite type changes to also assume FK validity:
- Any typmod-only change
- text <-> varchar
- domain <-> base type

To achieve that, this patch skips the revalidation when 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.
However, no core type would benefit, and my head exploded when I tried to
define the more-generous test correctly.

(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.)

These follow from the rules used to decide when to rebuild an index.  I
further justify them in source comments.

To implement this, I have ATPostAlterTypeParse() stash the content of the old
pg_constraint.conpfeqop in the Constraint node.  ATAddForeignKeyConstraint()
notices that and evaluates the above rules.  If they both pass, it omits the
validation step just as though skip_validation had been given.

Thanks,
nm

Responses

pgsql-hackers by date

Next:From: Alex HunsakerDate: 2012-01-04 20:27:07
Subject: Re: PL/Perl Does not Like vstrings
Previous:From: Andrew DunstanDate: 2012-01-04 20:13:28
Subject: Re: PL/Perl Does not Like vstrings

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group