Re: pg_restore casts check constraints differently - Mailing list pgsql-general

On Wed, Mar 30, 2016 at 6:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Joshua Ma <josh@benchling.com> writes:
>> This might not be a common case, but we're using pg_dump in a testing
>> environment to check migrations - 1) we initialize the db from HEAD,
>> pg_dump it, 2) we initialize the db from migration_base.sql, apply
>> migrations, pg_dump it, and 3) compare the two dumps to verify that our
>> migrations are correct wrt schema.
>
>> However, we're seeing pg_restore transforming our check constraints with
>> different casting.
>
> It's not really different.  What you're seeing is pg_dump (or actually
> ruleutils.c) choosing to dump some implicit casts explicitly to ensure
> that the expression is parsed the same way next time.  It might be
> overly conservative to do so, but we've found that erring in this
> direction tends to avoid breakage when the result is loaded into another
> server version; it's a bit like the intentional overparenthesization.

Saw a post on pgsql-bugs awhile back that looked related:

http://www.postgresql.org/message-id/011001d17b05$4e70c000$eb524000$@commoninf.com

In their case, the restored expression in different shape caused some
problems elsewhere. An example:

$ createdb srcdb
$ psql srcdb
psql (9.6devel)
Type "help" for help.

srcdb=# CREATE TABLE p (a varchar, CHECK (a IN ('a', 'b', 'c')));
CREATE TABLE

srcdb=# ^D\q

$ createdb destdb
$ pg_dump srcdb | psql destdb
$ psql destdb
psql (9.6devel)
Type "help" for help.

destdb=# \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | p    | table | amit
(1 row)

destdb=# CREATE TABLE c (LIKE p);
CREATE TABLE

destdb=# ALTER TABLE c ADD CONSTRAINT p_a_check CHECK (a IN ('a', 'b', 'c'));
ALTER TABLE

destdb=# \d c
            Table "public.c"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 a      | character varying |
Check constraints:
    "p_a_check" CHECK (a::text = ANY (ARRAY['a'::character varying,
'b'::character varying, 'c'::character varying]::text[]))

destdb=# INSERT INTO c VALUES ('a'), ('b'), ('c');
INSERT 0 3

destdb=# ALTER TABLE c INHERIT p;
ERROR:  child table "c" has different definition for check constraint
"p_a_check"

Hmm, how to go about to get it to match what p_a_check looks on p?  Maybe:

destdb=# CREATE TABLE c (LIKE p INCLUDING CONSTRAINTS);

destdb=# \d c
            Table "public.c"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 a      | character varying |
Check constraints:
    "p_a_check" CHECK (a::text = ANY (ARRAY['a'::character
varying::text, 'b'::character varying::text, 'c'::character
varying::text]))

Thanks,
Amit