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

The following bug has been logged on the website:

Bug reference:      11411
Logged by:          Ondřej Bouda
Email address:      obouda@email.cz
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.

Ond=C5=99ej Bouda wrote
> The following bug has been logged on the website:
>=20
> Bug reference:      11411
> Logged by:          Ond=C5=99ej Bouda
> Email address:     =20

> obouda@

> PostgreSQL version: 9.3.5
> Operating system:   Windows 7 64bit
> Description:       =20
>=20
> The documentation for ALTER DOMAIN notes that "ALTER DOMAIN ADD CONSTRAIN=
T
> and ALTER DOMAIN SET NOT NULL will fail if the named domain or any derive=
d
> domain is used within a composite-type column of any table in the
> database."
>=20
> It appears current Postgres also fails on ALTER DOMAIN VALIDATE CONSTRAIN=
T
> in such a case, as demonstrated by the following script:
>=20
> 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;
>=20
> 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.
>=20
> Instead, the following error is reported:
> [0A000] ERROR: cannot alter type "test_domain" because column
> "test_table.val" uses it
>=20
> 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/Linar=
o
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) =3D 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 varian=
t
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. =20

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. =20

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 no=
t
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. =20

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.=20
Actual experiences to the contrary would help. This report is too limited t=
o
qualify for that.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-1=
1411-ALTER-DOMAIN-VALIDATE-CONSTRAINT-fails-if-the-domain-is-used-in-a-comp=
osite-type-tp5818899p5818911.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

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. +

On Fri, Sep 12, 2014 at 08:51:48PM +0000, obouda@email.cz wrote:
> The following bug has been logged on the website:
>
> Bug reference:      11411
> Logged by:          Ondřej Bouda
> Email address:      obouda@email.cz
> 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.

Attached doc patch applied.  Thanks for the report.

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

  + Everyone has their own god. +

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