On Wed, Mar 30, 2016 at 6:45 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Joshua Ma <josh@xxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
