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

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.