FAQ
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)

Search Discussions

  • Neil Conway at May 20, 2005 at 8:22 am

    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
  • Tom Lane at May 20, 2005 at 3:40 pm

    Neil Conway 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ábor SZŰCS at May 21, 2005 at 7:16 am
    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 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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedMay 20, '05 at 8:04a
activeMay 21, '05 at 7:16a
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase