Re: pg_restore casts check constraints differently

On Wed, Mar 30, 2016 at 6:45 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Joshua Ma <josh(at)benchling(dot)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