Re: Re: BUG #11411: ALTER DOMAIN VALIDATE CONSTRAINT fails if the domain is used in a composite type - Mailing list pgsql-bugs

I have applied the doc patch that hopefully makes things a little clearer.

---------------------------------------------------------------------------

On Fri, Sep 12, 2014 at 06:06:21PM -0700, David G Johnston wrote:
> Ondřej Bouda wrote
> > The following bug has been logged on the website:
> >
> > Bug reference:      11411
> > Logged by:          Ondřej Bouda
> > Email address:
>
> > obouda@
>
> > PostgreSQL version: 9.3.5
> > Operating system:   Windows 7 64bit
> > Description:
> >
> > 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:
> >
> > CREATE DOMAIN test_domain AS TEXT CONSTRAINT some_check CHECK (TRUE);
> > CREATE TYPE test_composite AS (num INT, word test_domain);
> > CREATE TABLE test_table (val test_composite);
> > ALTER DOMAIN test_domain VALIDATE CONSTRAINT some_check;
> >
> > Since the documentation says nothing special regarding ALTER DOMAIN
> > VALIDATE
> > CONSTRAINT on a domain used by composite-type columns, the query is
> > expected
> > to run without errors.
> >
> > Instead, the following error is reported:
> > [0A000] ERROR: cannot alter type "test_domain" because column
> > "test_table.val" uses it
> >
> > It is questionable whether this is an implementation or documentation bug,
> > but it certainly is a bug.
>
> 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.
>
> Fix) Document that validating a table-embedded-composite domain check
> constraint is not currently implemented.  Furthermore, clarify that it is
> specifically the "VALID" version of ALTER DOMAIN ADD that fails - you can
> add a NOT VALID constraint and it will not fail until validation.
>
> 1) VALIDATE CONSTRAINT could early-exit if the named constraint is already
> valid (probably worth the effort)
>
> 2) Error upon ALTER ADD NOT VALID if the domain is already part of a
> table-embedded-composite (probably not...)
>
> 3) Error when adding a domain-embedded-composite, having one or more NOT
> VALID constraints, to a table (probably not ...)
>
>
> version
> PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro
> 4.6.3-1ubuntu5) 4.6.3, 64-bit
>
> No failures:
>
> BEGIN;
> CREATE DOMAIN testdom AS varchar;
> CREATE TYPE testdomtype AS (
>   col1 testdom
> , col2 testdom
> );
> CREATE TABLE testtbl ( tblcol1 testdomtype );
> ALTER DOMAIN testdom ADD CONSTRAINT testcon CHECK (length(VALUE) = 5) NOT
> VALID;
>
> Since "NOT VALID" is simply a modifier to ALTER DOMAIN ADD CONSTRAINT the
> sentence:
>
> "Currently, 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."
>
> is incomplete since it is not immediately obvious that the NOT VALID variant
> should be a special case.
>
> The act of making the constraint valid, which is what the OP is doing in
> theory (there is no NOT VALID constraint to validate), is what ends up
> causing the failure.
>
> Note that it would seem reasonable for the VALIDATE CONSTRAINT action to
> short-circuit - possibly with a NOTICE emitted - in the situation where
> there are no NOT VALID constraints to validate.  At least in the specific
> scenario posited by the OP the error would be avoided.
>
> I was thinking about the following scenario:
> CREATE DOMAIN
> CREATE TYPE
> ALTER DOMAIN NOT VALID
> CREATE TABLE
> ALTER DOMAIN VALIDATE
>
> Since NOT VALID does not error this is currently functionally equivalent to
> the test I posted above - where the ALTER ADD follows the CREATE TABLE.
>
> One question is whether we can prevent an embedded domain with an
> outstanding NOT VALID constraint from being added to a table in the first
> place.
>
> The other question is whether the error on validation can be moved up so
> that an attempt to ADD a NOT VALID constraint that is guaranteed to fail
> when validated (reasonably assumes dropping the column from the table is not
> a valid workaround) can be caught during the ADD instead.
>
> The documentation fix should be simple but the question is whether anyone
> wants to do more, with or without back-patching, to have the SQL interface
> being more helpful.
>
> Fixing the limitation would be best.  At this point being a little more
> helpful is probably of little benefit since an altered domain should be
> reasonably quickly validated and so funneling the error to that point
> doesn't seem to impose any meaningful risk or usage hazard on the user.
> Actual experiences to the contrary would help. This report is too limited to
> qualify for that.
>
> David J.
>
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-11411-ALTER-DOMAIN-VALIDATE-CONSTRAINT-fails-if-the-domain-is-used-in-a-composite-type-tp5818899p5818911.html
> Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +