Обсуждение: (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.