sql: support user-defined DOMAIN types

[knz]

Special case of #25123.

A domain is essentially a data type with optional constraints (restrictions on the allowed set of values).

Example:

CREATE DOMAIN us_postal_code AS TEXT
CHECK(
   VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);

Jira issue: CRDB-4941

[ketzacoatl]

I stumbled into this when debugging why Haskell's Yesod Web framework was unable to run migrations on a new db. Turns out, Yesod is smart, takes domain types into account, and so it also fails when looking up domain_name in the information_schema table.

See more info here:
https://github.com/yesodweb/persistent/blob/6ff44bb89d0da21590c11ed8279dbf3d1a40763b/persistent-postgresql/Database/Persist/Postgresql.hs#L647-L670

While supporting domain might seem like an odd use-case, Yesod's integration with Postgres takes that into account and is thus unable to be used with CockroachDB in current state.

---

To see this in some code/examples, building a brand new demo that should "just work" with postgres (it does), but fails with cdb due to missing support for the domain types:

With CockroachDB:

ᐅ stack new my-project yesodweb/postgres
ᐅ cd my-project && stack build
ᐅ yesod-service-directory config/settings.yml
yesod-service-directory: SqlError {sqlState = "42703", sqlExecStatus = FatalError, sqlErrorMsg = "column \"domain_name\" does not exist", sqlErrorDetail = "", sqlErrorHint = ""}

If I point at true postgres, migrations happen as expected:

ᐅ yesod-service-directory config/settings.yml
Migrating: CREATe TABLE "user"("id" SERIAL8  PRIMARY KEY UNIQUE,"ident" VARCHAR NOT NULL,"password" VARCHAR NULL)
Migrating: CREATe TABLE "email"("id" SERIAL8  PRIMARY KEY UNIQUE,"email" VARCHAR NOT NULL,"user_id" INT8 NULL,"verkey" VARCHAR NULL)
Migrating: CREATe TABLE "comment"("id" SERIAL8  PRIMARY KEY UNIQUE,"message" VARCHAR NOT NULL,"user_id" INT8 NULL)
Migrating: ALTER TABLE "user" ADD CONSTRAINT "unique_user" UNIQUE("ident")
Migrating: ALTER TABLE "email" ADD CONSTRAINT "unique_email" UNIQUE("email")
Migrating: ALTER TABLE "email" ADD CONSTRAINT "email_user_id_fkey" FOREIGN KEY("user_id") REFERENCES "user"("id")
Migrating: ALTER TABLE "comment" ADD CONSTRAINT "comment_user_id_fkey" FOREIGN KEY("user_id") REFERENCES "user"("id")

[knz]

@ketzacoatl thank you for your notice and sorry for the late answer. Although CockroachDB will likely not support domain types any time soon, it appears as if your Yesod migration does not really need them and just wants the domain information to be available in information_schema.colums. We'll be doing this in #33267. Thank you again.

[ketzacoatl]

That's great! Thanks for the heads up, I hope to check this out soon.

[rohany]

I was thinking about this a bit the other day, and wanted to write down my thoughts. Overall, this seems like a medium sized project. There are a couple of things to do now, now that we have a framework for user defined types.

• Parsing support -- just add the needed rules
• Datum/types.T work -- the types.T can hold on to the particular constraints applied to the domain (i.e. checks and not null), and the creation of the datum itself can just apply these checks. We generally have an *tree.EvalContext anywhere Datum related operations are done, so this isn't a big lift.
• Optimizer -- like ENUMs, the constraints on any domain types can be synthesized into check constraints for the optimizer to use.
• Execution -- since domains just operate on existing underlying types, I don't think that many existing changes are needed to the execution layer to support domains. The only thing that sticks out is that casts to domain types need to evaluate the check constraints.
• Backup/restore -- all that I imagine needs to be done is to define when two domain types are equivalent to restore into each other (which would just be that all the constraints are the same)

[giangpham712]

@fqazi This affect efcore.pg tests