Hello,
Please consider domains with domain constraints such as the following:
CREATE DOMAIN "t_txt" text NOT NULL
CONSTRAINT "dc_txt" CHECK (VALUE <> '');
CREATE DOMAIN "t_sha1" varchar(40) COLLATE "C" NOT NULL DEFAULT ''
CONSTRAINT "dc_sha1" CHECK (VALUE ~ '^([a-f0-9]{40})?$');
... and some table such as this contrived example:
CREATE TABLE "myusers" (
"name" "t_txt",
"email" "t_txt",
"token" "t_sha1"
);
Now, on inserting or updating the table with invalid data, appropriate domain constraint violations occur. But
unfortunatlythe associated error messages only reference the domain and not the underlying column, e.g. "ERROR: domain
public.t_txtdoes not allow null values".
That is a huge problem. When domain types are reused in a single table or across multiple tables/schemata it becomes
unclearand hard to debug because one doesn't know what specific data item caused the violation as there is no reference
tothe originating column. In the example above, even a single INSERT INTO "myusers" statement with a constraint
violationon the "t_txt" domain wouldn't be clear on whether it originated because of a bad value in "name" or "email".
Theuse of multi-statement queries and writable CTEs make this problem even worse.
On stackoverflow there are some comments suggesting that one should simply stick to single-use domains, but then what's
thebenefit of using custom domains in the first place? IMHO the biggest value of domain types is the reusability
accrossthe entire database. In our case whe have one base/public schema with a set of domain types and multiple uniform
schemata(think multi-tenancy) with thousands of tables that reference those few same domains. This allows us to make
universalschema changes easier and more efficient, and it also saves a lot of redundant catalog data, such as
repetitivecolumn level constraints and default expressions).
Is there any way you could display the underlying column and schema-qualified table names at least as DETAIL on
cosntraintviolation?
I'm aware that this is not per se a bug, but without the column info, domains are somewhat useless for all practical
purposes.Wouldn't you agree?
Thanks in advance.
Benjamin Coutu
ZeyOS, Inc.
ben.coutu@zeyos.com
http://www.zeyos.com