Обсуждение: Pulling data from a constraint def
Hi:
I have a constraint defined on a table....
constraint design_style_is_invalid check (design_style in ('rls','sdp','rf','ssa','rom','rpt','analog','sdprpt','clkdist','global')),
Is there a way to get the valid values in the list from the metadata somehow? Specifically, when someone hits this constraint, I want to not oonly tell them they entered an invalid value, but give them the list of valid choices at the same time.
I’d rather not put these in a table and implement with a foreogn key constraint for performance reasons. (Does that make sense?)
-dave
On Tue, May 11, 2010 at 9:38 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote:
> I have a constraint defined on a table....
>
>
>
> constraint design_style_is_invalid check (design_style in
> ('rls','sdp','rf','ssa','rom','rpt','analog','sdprpt','clkdist','global')),
>
>
>
> Is there a way to get the valid values in the list from the metadata
> somehow? Specifically, when someone hits this constraint, I want to not
> oonly tell them they entered an invalid value, but give them the list of
> valid choices at the same time.
It shouldn't be too difficult to get the constraint definition out of
the pg_catalog tables (specifically pg_constraint). I used the "-E"
flag to psql to have it show me how "\d tablename" pulled the
constraint definitions, and it gave me something like this (you should
test whatever PG version you're using with psql -E, the following is
from 9.0beta1):
-- Find table OID:
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(design)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
-- Find constraint names and definitions for the table returned
-- above with OID 16391:
SELECT r.conname, pg_catalog.pg_get_constraintdef(r.oid, true)
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '16391' AND r.contype = 'c'
ORDER BY 1
which returns:
design_style_is_invalid | CHECK (design_style = ANY
(ARRAY['rls'::text, 'sdp'::text, 'rf'::text, 'ssa'::text, 'rom'::text,
'rpt'::text, 'analog'::text, 'sdprpt'::text, 'clkdist'::text,
'global'::text]))
for me. You should be able to parse the ARRAY[...] text to present
your users with valid choices from there.
> I’d rather not put these in a table and implement with a foreogn key
> constraint for performance reasons. (Does that make sense?)
I think this is quite reasonable, as long as your list of acceptable
design styles rarely changes and is reasonably small.
Josh
On Tue, May 11, 2010 at 06:38:59PM -0700, Gauthier, Dave wrote:
> Hi:
>
> I have a constraint defined on a table....
>
> constraint design_style_is_invalid check (design_style in
> ('rls','sdp','rf','ssa','rom','rpt','analog','sdprpt','clkdist','global')),
>
> Is there a way to get the valid values in the list from the metadata
> somehow?
There may be, but you're much better off either creating a small table
design_style with one column of text and making foreign key references
to it.
> Specifically, when someone hits this constraint, I want to
> not oonly tell them they entered an invalid value, but give them the
> list of valid choices at the same time.
>
> I'd rather not put these in a table and implement with a foreogn key
> constraint for performance reasons. (Does that make sense?)
If you've measured a performance issue for a table that tiny, it's a
bug that needs fixing in PostgreSQL. What measurements have you done
so far?
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
In response to Gauthier, Dave :
> I have a constraint defined on a table....
>
> constraint design_style_is_invalid check (design_style in
> ('rls','sdp','rf','ssa','rom','rpt','analog','sdprpt','clkdist','global')),
>
> Is there a way to get the valid values in the list from the metadata somehow?
Sure, read the answer from Josh Kupershmidt. But keep in mind, internal
tables maybe changed in the next version -> your code isn't portable.
> I?d rather not put these in a table and implement with a foreogn key constraint
> for performance reasons. (Does that make sense?)
If i were you, i would use exactly this way.
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
On Wed, May 12, 2010 at 12:58 AM, David Fetter <david@fetter.org> wrote: > If you've measured a performance issue for a table that tiny, it's a > bug that needs fixing in PostgreSQL. What measurements have you done > so far? Just for fun, I tried it out myself. Here are the times I got on my modest laptop: CHECK constraint: * 500k INSERTs: 3.8 seconds * 500k UPDATEs: 6.0 seconds Foreign Key: * 500k INSERTs: 18.7 seconds * 500k UPDATEs: 21.2 seconds Test SQL is attached. Josh
Вложения
Josh Kupershmidt <schmiddy@gmail.com> writes:
> On Wed, May 12, 2010 at 12:58 AM, David Fetter <david@fetter.org> wrote:
>> If you've measured a performance issue for a table that tiny, it's a
>> bug that needs fixing in PostgreSQL. �What measurements have you done
>> so far?
> Just for fun, I tried it out myself. Here are the times I got on my
> modest laptop:
> CHECK constraint:
> * 500k INSERTs: 3.8 seconds
> * 500k UPDATEs: 6.0 seconds
> Foreign Key:
> * 500k INSERTs: 18.7 seconds
> * 500k UPDATEs: 21.2 seconds
I'm surprised no one has yet suggested an ENUM type.
regards, tom lane
Hmmmmm.... Very Interesting idea. I';l have to try that one.
One difference that may be an issue in other apps (not mine though) might be the inability to defer constraint checking
usingenum. Still, enum looks very appealing. And more portable, cleaner than parsing the values out of
pg_constraints.
Thanks Tom for that idea!
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, May 13, 2010 12:33 AM
To: Josh Kupershmidt
Cc: David Fetter; Gauthier, Dave; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Pulling data from a constraint def
Josh Kupershmidt <schmiddy@gmail.com> writes:
> On Wed, May 12, 2010 at 12:58 AM, David Fetter <david@fetter.org> wrote:
>> If you've measured a performance issue for a table that tiny, it's a
>> bug that needs fixing in PostgreSQL. What measurements have you done
>> so far?
> Just for fun, I tried it out myself. Here are the times I got on my
> modest laptop:
> CHECK constraint:
> * 500k INSERTs: 3.8 seconds
> * 500k UPDATEs: 6.0 seconds
> Foreign Key:
> * 500k INSERTs: 18.7 seconds
> * 500k UPDATEs: 21.2 seconds
I'm surprised no one has yet suggested an ENUM type.
regards, tom lane
On Thu, May 13, 2010 at 12:33:08AM -0400, Tom Lane wrote: > Josh Kupershmidt <schmiddy@gmail.com> writes: > > On Wed, May 12, 2010 at 12:58 AM, David Fetter <david@fetter.org> wrote: > >> If you've measured a performance issue for a table that tiny, it's a > >> bug that needs fixing in PostgreSQL. �What measurements have you done > >> so far? > > > Just for fun, I tried it out myself. Here are the times I got on my > > modest laptop: > > > CHECK constraint: > > * 500k INSERTs: 3.8 seconds > > * 500k UPDATEs: 6.0 seconds > > > Foreign Key: > > * 500k INSERTs: 18.7 seconds > > * 500k UPDATEs: 21.2 seconds > > I'm surprised no one has yet suggested an ENUM type. I didn't suggest it because I didn't know about it, but because I've found ENUM to be a trap for the unwary. Very seldom are people absolutely certain that they'll have one particular list of things forever. The list may grow or shrink, or the order may change, and in those cases where the list changes somehow, ENUM causes more problems than it solves. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> On Thu, May 13, 2010 at 12:33:08AM -0400, Tom Lane wrote:
>> I'm surprised no one has yet suggested an ENUM type.
> I didn't suggest it because I didn't know about it, but because I've
> found ENUM to be a trap for the unwary.
> Very seldom are people absolutely certain that they'll have one
> particular list of things forever. The list may grow or shrink, or
> the order may change, and in those cases where the list changes
> somehow, ENUM causes more problems than it solves.
Well, the inability to change the list of values is certainly an
unpleasant limitation, but is it so fatal that we should hide the
feature from people who could possibly use it? I think not.
regards, tom lane
On Thu, May 13, 2010 at 08:08:31PM -0400, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > On Thu, May 13, 2010 at 12:33:08AM -0400, Tom Lane wrote: > >> I'm surprised no one has yet suggested an ENUM type. > > > I didn't suggest it because I didn't know about it, but because > > I've found ENUM to be a trap for the unwary. > > > Very seldom are people absolutely certain that they'll have one > > particular list of things forever. The list may grow or shrink, > > or the order may change, and in those cases where the list changes > > somehow, ENUM causes more problems than it solves. > > Well, the inability to change the list of values is certainly an > unpleasant limitation, but is it so fatal that we should hide the > feature from people who could possibly use it? I think not. It's enough of a foot-gun that I would not even mention it to start with, except to discuss its problems. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Thu, May 13, 2010 at 8:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Well, the inability to change the list of values is certainly an > unpleasant limitation, but is it so fatal that we should hide the > feature from people who could possibly use it? I think not. I happened upon this article relevant to the subject after googling a bit: http://www.justatheory.com/computers/databases/postgresql/enforce-set-of-values.html One of the comments suggests adding an entry to pg_enum to expand the legal values of an existing ENUM type. How safe is this idea? Josh
Josh Kupershmidt <schmiddy@gmail.com> writes: > On Thu, May 13, 2010 at 8:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Well, the inability to change the list of values is certainly an >> unpleasant limitation, but is it so fatal that we should hide the >> feature from people who could possibly use it? �I think not. > I happened upon this article relevant to the subject after googling a bit: > http://www.justatheory.com/computers/databases/postgresql/enforce-set-of-values.html > One of the comments suggests adding an entry to pg_enum to expand the > legal values of an existing ENUM type. How safe is this idea? The trick is for the OID of the added entry to sort in the position you want it relative to the existing entries. If you don't care about that, it's fine. regards, tom lane
Excerpts from Tom Lane's message of vie may 14 00:32:12 -0400 2010: > Josh Kupershmidt <schmiddy@gmail.com> writes: > > On Thu, May 13, 2010 at 8:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> Well, the inability to change the list of values is certainly an > >> unpleasant limitation, but is it so fatal that we should hide the > >> feature from people who could possibly use it? \xa0I think not. > > > I happened upon this article relevant to the subject after googling a bit: > > http://www.justatheory.com/computers/databases/postgresql/enforce-set-of-values.html > > > One of the comments suggests adding an entry to pg_enum to expand the > > legal values of an existing ENUM type. How safe is this idea? > > The trick is for the OID of the added entry to sort in the position you > want it relative to the existing entries. If you don't care about that, > it's fine. I was going to mention manual tweaking of the catalogs before Josh did, but I realized that it's pretty likely that all the OIDs that the enum pick are consecutive, and thus it's going to be very hard to insert values in the middle of their sequence. However it's easy to add a new enum value that sorts before all the existing ones or after all of them. --
On Thu, May 13, 2010 at 8:14 PM, David Fetter <david@fetter.org> wrote: >> Well, the inability to change the list of values is certainly an >> unpleasant limitation, but is it so fatal that we should hide the >> feature from people who could possibly use it? I think not. > > It's enough of a foot-gun that I would not even mention it to start > with, except to discuss its problems. > > I'm with David on this one. I always define them using an enumeration table + FK to that table.