Обсуждение: limitations of constraints, possible bugs

i'm curious about how constraints work (perhaps vs how they are supposed to
work).

for instance, if i create a domain (D) with some constraint, and create a table
with a column of type D[], will that domain constraint be enforced for each
element of the array? could i create a column constraint on an array to enforce
some kind of relationship among the elements (say, that they are sorted, or
that there are no NULL elements if/when NULL elements are supported in arrays),
or would it be necessary to create a trigger? finally, is it meaningful to have
a foreign key refer to an array column (and if so, what does it mean)?

i have noticed in the docs that there are various limitations in the current
implementation: domain constraints aren't enforced when returned from
functions, rowtype (from create table) constraints aren't enforced, constraints
can't be defined for composite types, etc. i see the first one is on the TODO
list; what about the others? finally, oracle has a %ROWTYPE attribute for
tables; has there been any consideration for adopting such a feature (it might
clarify the difference between a table/relation with its constraints, indexes,
etc and the implied type)?

are these sort of questions answered anywhere? if i do some testing, and find
something i think is a bug, should i ask about it here or just post it as bug
and let it be resolved by that process?

thanks!
--craig


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

craigp <craigp98072@yahoo.com> writes:
> for instance, if i create a domain (D) with some constraint, and create a table
> with a column of type D[], will that domain constraint be enforced for each
> element of the array?

Currently we don't support arrays of domain types (this should get fixed
sometime).  If we did, then yeah, that's how it'd work.

> could i create a column constraint on an array to enforce
> some kind of relationship among the elements (say, that they are sorted, or
> that there are no NULL elements if/when NULL elements are supported in arrays),

If you could express it as a SQL boolean expression, then it'd work as a
constraint.  For something like a sortedness check, it'd likely be
easier to check with a loop in a trigger function.

> i have noticed in the docs that there are various limitations in the current
> implementation: domain constraints aren't enforced when returned from
> functions, rowtype (from create table) constraints aren't enforced, constraints
> can't be defined for composite types, etc. i see the first one is on the TODO
> list; what about the others?

The first of these is done in CVS HEAD, the others probably will get
done someday.  If you want to see them happen sooner rather than later,
consider working on 'em yourself ...

            regards, tom lane

Tom Lane wrote:
> craigp <craigp98072@yahoo.com> writes:
> > for instance, if i create a domain (D) with some constraint, and create a table
> > with a column of type D[], will that domain constraint be enforced for each
> > element of the array?
>
> Currently we don't support arrays of domain types (this should get fixed
> sometime).  If we did, then yeah, that's how it'd work.

Added to TODO:

    * Add support for arrays of domains

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

  + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> writes:
> Added to TODO:
>     * Add support for arrays of domains

I think BTW that this should be pretty low-hanging fruit given the
recent rewrite of domain_in().  I don't think array_in() should need to
care now whether the array elements are domains, whereas in the original
design there would have had to be explicit support for domains added to
the array code.  There might be some performance issues, ie, making sure
the stack of array_in and domain_in can cache data at both levels, but
that seems eminently solvable.

            regards, tom lane