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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Attachment Content-Type Size
at-foreign-key-v1.patch text/plain 16.4 KB

Responses

Browse pgsql-hackers by date

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