Обсуждение: (check) constraints on composite type

Hi!

As of PostgreSQL 9.1 it is not possible to declare check constraints on composite types, according to
http://www.postgresql.org/docs/9.1/interactive/rowtypes.html.I cannot use domains in that particular context, because I
wantto have the check constraint check for a combination of fields in that composite type. 

Are there any plans to have this feature implemented in the near future?

Regards,
    Ralph

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ralph Graulich
Sent: Tuesday, January 10, 2012 2:30 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] (check) constraints on composite type

Are there any plans to have this feature implemented in the near future?

------------------------------------

Sorry for not answering the main question but you really need to avoid
phrases like "near future".  That said, new features are only released
during major releases and so at best you would have to wait for 9.2 which is
probably 9+ months out since 9.1 was just recently released.  The better
question is whether 9.2 is likely to have what you need or whether it has a
chance to be during 9.3 (1.5+ years out).  More generically the question is
whether anyone is currently working on the feature.

I have be over-complicating since "near future" may simply mean "the next
release" but being avoids the back-and-forth required to make sure
expectations are matched.

You do have other options depending on how critical such a feature is to
you.  There are possibly workarounds that can be implemented or you can
always commission the work and request a back-port to 9.1 with future
inclusion into 9.2 (or 9.3 is feature freeze has taken effect).

Just saying - though I am not involved with development efforts.

David J.

On Tue, Jan 10, 2012 at 1:44 PM, David Johnston <polobo@yahoo.com> wrote:
> Sorry for not answering the main question but you really need to avoid
> phrases like "near future".  That said, new features are only released
> during major releases and so at best you would have to wait for 9.2 which is
> probably 9+ months out since 9.1 was just recently released.  The better
> question is whether 9.2 is likely to have what you need or whether it has a
> chance to be during 9.3 (1.5+ years out).  More generically the question is
> whether anyone is currently working on the feature.

yeah -- I've seen a bit of interest in this feature, but AFAIK
nobody's working on it, and no plausible design has been submitted
(which is a giant red flag in terms of 9.2 acceptance).  fair warning:
this is one of those items that sounds neat but is a lot more
complicated than it looks.

in the meantime, just do the workaround I do: make an immutable
function that takes your type as input, and manually apply the
constraint in various places like (like as a table constraint).

merlin

Hi David!

> Sorry for not answering the main question but you really need to avoid
> phrases like "near future".  That said, new features are only released
> during major releases and so at best you would have to wait for 9.2 which is
> probably 9+ months out since 9.1 was just recently released.  The better
> question is whether 9.2 is likely to have what you need or whether it has a
> chance to be during 9.3 (1.5+ years out).  More generically the question is
> whether anyone is currently working on the feature.

Thanks alot for your input on that issue. I really appreciate it. -

However I posed my question using that wording intentionally, so it is open to any input, may it that someone states he
orshe is already working on that part of PostgreSQL, someone already made a plan and checked whether the request is
feasibleto add or any other decisions on that topic, that were probably made and I missed or were not published. "Near
future"for me means getting all possible input, not referring to a particular time line. If one tells me "it is
planned,no deadline given" that's as fine for me as "no plans, no idea" or "March, 12th, this year". 
Understanding your point, I found posing the question using "whether anyone is currently working on the feature" far
toolimiting, probably guiding the question into the wrong direction, like pushing someone to look into it between the
lines- in the sense of "why the heck is PostgreSQL lacking that feature of utmost importance?" - which I didn't intent. 

Now back to the point of question:

> You do have other options depending on how critical such a feature is to
> you.  There are possibly workarounds that can be implemented or you can
> always commission the work and request a back-port to 9.1 with future
> inclusion into 9.2 (or 9.3 is feature freeze has taken effect).

Let's assume I have three fields in my custom type:

field1 varchar(4)   // or text
field2 smallint
field3 char(1)      // or text

Now I want to add a complex check constraint involving the combination of those three fields, written in pseudo code:

field1 IN ('A') && field2 BETWEEN 1 AND 120 && field3 IS NULL
field1 IN ('B') && field2 BETWEEN 1 AND 40 && field3 IS NULL
field1 IN ('TFnr') && field3 BETWEEN 1600 AND 2200 && field3 IN ('a', 'b', 'c')
field1 IS NULL & field2 = 1 & field3 IN ('a'..'z') OR field3 IS NULL
...            & field2 = 2 & field3 IN ('a'..'h') OR field3 IS NULL
...            & field2 = 3 & field3 IN ('a'..'o', 'Z') OR field3 IS NULL

and so on (the problem is actually far more complex, just to give a hint of…). My point is putting all the validation
ofdata into the database itself, so just having to care for changes at one place and making sure data and data domains
ofvalid data are tightly integrated, which I favor. 

Regards,
     Ralph

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ralph Graulich
Sent: Tuesday, January 10, 2012 3:47 PM
To: David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] (check) constraints on composite type

Let's assume I have three fields in my custom type:

field1 varchar(4)   // or text
field2 smallint
field3 char(1)      // or text

Now I want to add a complex check constraint involving the combination of
those three fields, written in pseudo code:

field1 IN ('A') && field2 BETWEEN 1 AND 120 && field3 IS NULL
field1 IN ('B') && field2 BETWEEN 1 AND 40 && field3 IS NULL
field1 IN ('TFnr') && field3 BETWEEN 1600 AND 2200 && field3 IN ('a', 'b',
'c')
field1 IS NULL & field2 = 1 & field3 IN ('a'..'z') OR field3 IS NULL
...            & field2 = 2 & field3 IN ('a'..'h') OR field3 IS NULL
...            & field2 = 3 & field3 IN ('a'..'o', 'Z') OR field3 IS NULL

and so on (the problem is actually far more complex, just to give a hint
of.). My point is putting all the validation of data into the database
itself, so just having to care for changes at one place and making sure data
and data domains of valid data are tightly integrated, which I favor.

Regards,
     Ralph

----------------------------------------------------------------------------
---

I would suggest trying to make your constraints as "template" as possible
and then create a table that provides inputs for each of the constraint
"parameters".  You include a field which flags the constraint as either
"require" or "disallow" and then you write a function that iterates over the
table and compares each of the constraints against the value and ensure that
at least of the "require" constraints pass and that all the "disallow"
constraints fail.

RegularExpressions will likely come in handy and you will probably need to
create "field_n_can_be_null" booleans to allow them explicitly.

The implementation I've used is to "CROSS JOIN" the value and the constraint
table and then use ARRAY_AGG to create multiple arrays for the various test
results (the specific set of arrays varies depending on your needs).  I've
done this with a single text field check using reqularexpressions and the
results work fairly well (ignoring performance concerns which have not yet
been a factor due to the lite load I experience).  For an exclusion
constraint I add "Exclude" if the constraint matches otherwise I add
"Ignore"; for inclusion constraints I add "Include" for matches or "Ignore";
I then exclude if "Exclude" = ANY(match_results) OR "Ignore" =
ALL(match_results); otherwise there has to be at least one "Include" and
thus the check passes.

For example:

SELECT val, CASE WHEN 'Exclude' = ANY(results) THEN 'Fail - Excluded'
          WHEN 'Ignore' = ALL(results) THEN 'Fail - No Includes
Match'
          WHEN 'Include' = ANY(results) THEN 'Include'
          ELSE 'Fail - Not Possible As Coded But Just In Case' END
AS test_outcome
FROM (
SELECT val, ARRAY_AGG( CASE WHEN val ~ pattern THEN CASE WHEN include THEN
'Include' ELSE 'Exclude' END ELSE 'Ignore' END ) AS results
FROM (VALUES ('value to check')) vals (val)
CROSS JOIN (VALUES ('test pattern',true) [, ...] ) tests (pattern, include)
GROUP BY val
) val_test

David J.