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