Thread: Weird constraint output

[Please hold me on the to:/cc: list since I am not subscribed]

After talking this over with some of the great guys on IRC it was
suggested I ask here.

I am currently working on a document about how to convert from MySQL to
PostgreSQL (Sybase, Oracle, DB2, MS SQL Server are also going to be
done).  I am now messing with CONSTRAINT CHECK and am encountering
something I don't see the logic of.

I decided to make sure Bugzilla works on pgsql 7.3.x and started to
convert the MySQL database schema to a pgsql one.  One of the parts has
a layout like:

CREATE TABLE bugs ( -- ...skipping to relevant point bug_severity character varying(50) -- in MySQL this is enum()
);

Next I did an:

ALTER TABLE ONLY bugs ADD CONSTRAINT bugs_severity_cstr CHECK
(bug_severity IN ('blocker', 'critical', 'major'));

Now, when I do a \d bugs I get:

Check constraints: "bugs_severity_cstr" (((bug_severity =
'blocker'::character varying) OR (bug_severity = 'critical'::character
varying)) OR (bug_severity = 'major'::character varying))

I would've expected:

Check constraints: "bugs_severity_cstr" ((bug_severity =
'blocker'::character varying) OR (bug_severity = 'critical'::character
varying) OR (bug_severity = 'major'::character varying))

If you have even more choices there (as Bugzilla does) you even get:

CONSTRAINT bugs_severity_cstr CHECK ((((((((bug_severity =
'blocker'::character varying) OR (bug_severity = 'critical'::character
varying)) OR (bug_severity = 'major'::character varying)) OR
(bug_severity = 'normal'::character varying)) OR (bug_severity =
'minor'::character varying)) OR (bug_severity = 'trivial'::character
varying)) OR (bug_severity = 'enhancement'::character varying)))
);

But there is no logic to have all those parens plus it makes pg_dump -s
and \d tablename a whole lot messier to read.

Can anyone clarify why we have it like this?  Or whether or not it is a
bug perhaps?  I could understand micro optimizations, but in this case?

Thanks,

-- 
Jeroen Ruigrok van der Werven <asmodai(at)wxs.nl> / asmodai
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7  9D88 97E6 839B 2EAC 625B
http://www.tendra.org/   | http://www.in-nomine.org/~asmodai/diary/
One moon shows in every pool; in every pool, the one moon...

Jeroen Ruigrok/asmodai wrote:

>Check constraints: "bugs_severity_cstr" ((bug_severity =
>'blocker'::character varying) OR (bug_severity = 'critical'::character
>varying) OR (bug_severity = 'major'::character varying))
>
>If you have even more choices there (as Bugzilla does) you even get:
>
>CONSTRAINT bugs_severity_cstr CHECK ((((((((bug_severity =
>'blocker'::character varying) OR (bug_severity = 'critical'::character
>varying)) OR (bug_severity = 'major'::character varying)) OR
>(bug_severity = 'normal'::character varying)) OR (bug_severity =
>'minor'::character varying)) OR (bug_severity = 'trivial'::character
>varying)) OR (bug_severity = 'enhancement'::character varying)))
>);
>
>But there is no logic to have all those parens plus it makes pg_dump -s
>and \d tablename a whole lot messier to read.
>
>Can anyone clarify why we have it like this?  Or whether or not it is a
>bug perhaps?  I could understand micro optimizations, but in this case?
>

Obviously psql uses either use pg_constraint.consrc or 
pg_get_expr(conbin, conrelid) which both will give this lot of 
parentheses, so it's not a bug, but a feature
For easier reengineering, I invented several pg_get_xxx functions with a 
pretty-print option, which omits this messy parentheses, because I 
noticed how ugly this looks while writing pgAdmin3 (especially for large 
views with many joins and big expressions).
There have been suggestions to use the pretty-print options for psql (I 
don't know which have been realized so far), and very harsh rejects 
doing this in pg_dump, namely from Tom).

Regards,
Andreas

> Obviously psql uses either use pg_constraint.consrc or
> pg_get_expr(conbin, conrelid) which both will give this lot of
> parentheses, so it's not a bug, but a feature
> For easier reengineering, I invented several pg_get_xxx functions with a
> pretty-print option, which omits this messy parentheses, because I
> noticed how ugly this looks while writing pgAdmin3 (especially for large
> views with many joins and big expressions).
> There have been suggestions to use the pretty-print options for psql (I
> don't know which have been realized so far), and very harsh rejects
> doing this in pg_dump, namely from Tom).

I have changed psql to use pg_get_viewdef(oid, true).  I agree with Tom for
not using it in dumps just yet though.  Is there a function for getting nice
constraint defs?

Chris

Christopher Kings-Lynne wrote:

>I have changed psql to use pg_get_viewdef(oid, true).  I agree with Tom for
>not using it in dumps just yet though.
>
While there still might be a pg_dump option to do this.

>Is there a function for getting nice constraint defs?
>
Of course there is, use pg_get_constraintdef(text, bool) for this.
pg_get_constraintdef(text, bool)
pg_get_expr(text, oid, bool)
pg_get_indexdef(text, int4, bool)
pg_get_ruledef(text, bool)
pg_get_viewdef(text, bool)
all have the pretty-print bool. Note that pg_get_indexdef's second 
parameter selects if the complete definition is returned (=0) or only 
that column's definition.

Regards,
Andreas