Domains and subtypes, a brief proposal

As many of you know I've been contemplating the implementation
of Domains and subtypes.

DISCLAIMER: This is a proposal only. The actual work needs to
be picked up by someone in a better place to work on the code
than I am. For various reasons, I can only be an active reference
and tester on this projects.

DISCLAIMER: This is a proposal, not a detailed spec. A more detailed
spec can be written with the help of a developer.

Domains and subtypes.

* Create new child type from values in parent type.
* Maintain only checks for constraints
* Create implicit casts from child to parent

With this model all of the type checking that exists should
work and domain checking of types in any place except for
constraint checking can be removed.

* Add proper SQL standard CREATE childtype UNDER parenttype
This should include all of the ordinary create type options.

Constraints on types:

* Change the pg_types to hold a NULLABLE constraint text column
OR add a type constraint lookup table (pg_domains?)

This is a big deal, I know. Constraints for domains would be in
that field. All domain checking should be done from that source.

This would theoretically enable type constraint checking for any type
if we chose to add this feature to create type. This would be appropriate
for base types only, including UDTs. There may be an argument for complex
types, though, for example certain types of integer-ish arrays may only contain
unique values. Usually types have the constraint definition encoded in
their input routines. This gives the type developers an easier option
to validate their types since constraints can be written in pl languages.

Problems and Issues:

There are a few known issues with the current implementation of domains
with overriding operators.

* Creating the table with an domain PRIMARY KEY did not use the subtype comparison function.
It was necessary to create a unique index which explicitly used the domain operator class.
This should be fixed by having the domain as a proper type. No domain checking should be necessary.
* ORDER BY requires USING op clause.
This may be fixed by having the domain as a proper type. No domain checking should be necessary.
* LIKE requires explicit casting of the second argument to text.
(I need to double check this. It may work OK on cvs head.)
* COPY has a problem which still need investigating.

Why do we want this.

* We need subtypes
* Domains are *almost* subtypes because you can override the operators
as you can with any type.
* Use the types as designed

The current code jumps through hoops to check for domain types. Ideally
this change should remove a lot of that code in favor of checking just
for the existence of a constraint and then only where constraint checking
is needed.

By using this technique Illustra easily added subtypes, but did not add the
domains with their additional complexities. No special type checking for
simple subtypes was necessary.

I may have missed some stuff here. Obviously. For example how to divide and
conquer the various aspects of the issues raised here. But this is a high, high
level proposal at this time.

Comments, volunteers are welcome.

--elein

--------------------------------------------------------------
elein(at)varlena(dot)com Varlena, LLC www.varlena.com
(510)655-2584(o) (510)543-6079(c)
PostgreSQL Consulting, Support & Training
--------------------------------------------------------------