Thread: CHECK constraint (true) causes dumps with parse errors

Dear Gurus,

Version: 7.3.3, 7.4.8

I don't know for sure, but something like this happened when dumping our
server from 7.3 to 7.4. Actually, it seems to be a problem in 7.4 (maybe
also in 7.3, but unlikely -- see below, "our original way").

# create table foo (bar int, constraint foobar check (true or bar=1));
CREATE TABLE
# \d foo      Table "public.foo" Column |  Type   | Modifiers
--------+---------+----------- bar    | integer |
Check constraints:    "foobar" CHECK (true OR bar = 1)

# drop table foo;
DROP TABLE
# create table foo (bar int, constraint foobar check (true));
CREATE TABLE
# \d foo      Table "public.foo" Column |  Type   | Modifiers
--------+---------+----------- bar    | integer |
Check constraints:    "foobar" CHECK ()

Yours,
--
G.

---------------- our original way we found the problem:
-- original create command:
CREATE TABLE foo (    bar int,    CONSTRAINT foobar CHECK (true or bar=1)
);

-- 7.3 dump:
CREATE TABLE foo (    bar int,    CONSTRAINT foobar CHECK (true)
);

-- fed to 7.4, then \d
...
Check constraints:    "foobar" CHECK ()
-- note the empty parentheses!

-- mirroring the database caused:
ERROR:  syntax error at or near ")" at character xxx

-- recreated table (actually, readded constraint) to 7.4, then \d
...
Check constraints:    "foobar" CHECK (true OR bar=1)

Szűcs Gábor wrote:
> # create table foo (bar int, constraint foobar check (true));
> CREATE TABLE
> # \d foo
>       Table "public.foo"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  bar    | integer |
> Check constraints:
>     "foobar" CHECK ()

Yeah, I can repro this with current REL7_4_STABLE sources, but it looks
fixed in HEAD and REL8_0_STABLE.

-Neil

Neil Conway <neilc@samurai.com> writes:
> Szűcs Gábor wrote:
>> Check constraints:
>> "foobar" CHECK ()

> Yeah, I can repro this with current REL7_4_STABLE sources, but it looks
> fixed in HEAD and REL8_0_STABLE.

I think the reason is that 7.4 applies make_ands_implicit before storing
the constraint, while 8.0 and up don't.  I'm inclined to think it's not
worth trying to fix in the back branches.

            regards, tom lane

I agree it's not worth the effort, just found it "interesting" (duh)
and hastily sent a bugreport before checking upstream.

[off]
Another interesting story was 7.3 silently discarding unneeded
typecasts: create a view with a field ('a' || 'b')::varchar. The same
definition worked in 7.3 and 7.4 (both created a varchar field), but
7.3 dropped the unneeded typecast, and when dumping from 7.3 to 7.4
all these fields became text. I think 7.3 was simply "too smart for
our dumb problems" :)
[/off]

On 5/20/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Neil Conway <neilc@samurai.com> writes:
> > Szűcs Gábor wrote:
> >> Check constraints:
> >> "foobar" CHECK ()
>
> > Yeah, I can repro this with current REL7_4_STABLE sources, but it looks
> > fixed in HEAD and REL8_0_STABLE.
>
> I think the reason is that 7.4 applies make_ands_implicit before storing
> the constraint, while 8.0 and up don't.  I'm inclined to think it's not
> worth trying to fix in the back branches.
>
>                         regards, tom lane
>


--
G.