Re: Re: BUG #11411: ALTER DOMAIN VALIDATE CONSTRAINT fails if the domain is used in a composite type

David G Johnston wrote:
> Ondřej Bouda wrote

> > The documentation for ALTER DOMAIN notes that "ALTER DOMAIN ADD CONSTRAINT
> > and ALTER DOMAIN SET NOT NULL will fail if the named domain or any derived
> > domain is used within a composite-type column of any table in the
> > database."
> >
> > It appears current Postgres also fails on ALTER DOMAIN VALIDATE CONSTRAINT
> > in such a case, as demonstrated by the following script:

> TL/DR; The documentation needs some work here; and the code could be more
> helpful - even without overcoming the limitation - though it likely isn't
> worth the effort.

Um.  I have vague recollections about going some lengths to make sure
this behaved sanely and usefully, i.e. validate tables that were using
the domains.  I guess I didn't cover all bases; seemed difficult enough
as it was, back then.  This behavior certainly seems unhelpful, because
in effect you can never get a constraint validated.  It doesn't look
unduly complicated to drill down to composite types; maybe a good GSoC
project?


For reference, see this commit, in which Robert's last name was
misspelled once again (sorry about that):

commit 897795240cfaaed724af2f53ed2c50c9862f951f
Author: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date:   Wed Jun 1 18:43:50 2011 -0400

    Enable CHECK constraints to be declared NOT VALID

    This means that they can initially be added to a large existing table
    without checking its initial contents, but new tuples must comply to
    them; a separate pass invoked by ALTER TABLE / VALIDATE can verify
    existing data and ensure it complies with the constraint, at which point
    it is marked validated and becomes a normal part of the table ecosystem.

    An non-validated CHECK constraint is ignored in the planner for
    constraint_exclusion purposes; when validated, cached plans are
    recomputed so that partitioning starts working right away.

    This patch also enables domains to have unvalidated CHECK constraints
    attached to them as well by way of ALTER DOMAIN / ADD CONSTRAINT / NOT
    VALID, which can later be validated with ALTER DOMAIN / VALIDATE
    CONSTRAINT.

    Thanks to Thom Brown, Dean Rasheed and Jaime Casanova for the various
    reviews, and Robert Hass for documentation wording improvement
    suggestions.

    This patch was sponsored by Enova Financial.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services