Обсуждение: pg_restore casts check constraints differently
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.
# \d arrayed_library
CONSTRAINT arrayed_library_step_check CHECK (((step)::text = ANY ((ARRAY['ADD_RESERVED_SEQUENCES'::character varying, 'ANALYZE_DESIGN_WARNINGS'::character varying, 'COMPLETE_ORDER'::character varying, 'DEFINE_VARIANTS'::character varying, 'LABEL_TRANSLATION'::character varying])::text[])))
$ dropdb db && createdb db
$ pg_dump db --schema-only --no-owner > migration_base.sql
# migration_base.sql has the same CONSTRAINT as above
$ psql db -q -f migration_base.sql
# \d arrayed_library
CONSTRAINT arrayed_library_step_check CHECK (((step)::text = ANY (ARRAY[('ADD_RESERVED_SEQUENCES'::character varying)::text, ('ANALYZE_DESIGN_WARNINGS'::character varying)::text, ('COMPLETE_ORDER'::character varying)::text, ('DEFINE_VARIANTS'::character varying)::text, ('LABEL_TRANSLATION'::character varying)::text])))
Note that the restored constraint has ARRAY('a'::text, 'b'::text, ...) while the original had (ARRAY['a', 'b', ...])::text[]
Is there any way to have postgres NOT do the extra conversions?
- Josh
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.
regards, tom lane
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.
Why don't we just use ruleutils.c to generate \d results so that what we end up showing is canonical?
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tue, Mar 29, 2016 at 2:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.
> Why don't we just use ruleutils.c to generate \d results so that what we
> end up showing is canonical?
We do. AFAIK, what psql's \d shows in these cases is the same as what
pg_dump will print. Joshua's complaint is that it isn't necessarily
identical to what was input.
regards, tom lane
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tue, Mar 29, 2016 at 2:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.
> Why don't we just use ruleutils.c to generate \d results so that what we
> end up showing is canonical?
We do. AFAIK, what psql's \d shows in these cases is the same as what
pg_dump will print. Joshua's complaint is that it isn't necessarily
identical to what was input.
Then I must be lacking info here because given that the two constraints shown using \d are equivalent if we were to output a canonical form there could only be one valid representation that could be output.
Looking at it in this manner Joshua's goal is achieved even if we don't output exactly what was input - because at least regardless of the input form the attempt to compare direct HEAD and migration result would be the same result.
I guess my "so that" clause is overly optimistic - we'd likely need to expend more effort to actually derive a canonical version of a given arbitrary constraint and our current implementation is allowed to simplify without deriving a canonical form: in this case failing to consistently choose whether to cast the array elements and leave the array type itself implied versus leaving the array elements in their natural form and casting the final array to the necessary type. And, at the same time, ideally recognizing that the built-in types "character varying" and "text" are compatible and thus ('value'::varchar)::text should be simplified to 'value'::text.
David J.
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
Amit Langote <amitlangote09@gmail.com> writes:
> destdb=# ALTER TABLE c ADD CONSTRAINT p_a_check CHECK (a IN ('a', 'b', 'c'));
> destdb=# \d c
> ...
> Check constraints:
> "p_a_check" CHECK (a::text = ANY (ARRAY['a'::character varying,
> 'b'::character varying, 'c'::character varying]::text[]))
Hm. It seems like the parser is doing something weird with IN there.
If you just do a simple comparison the constant ends up as TEXT to start
with:
regression=# CREATE TABLE pp (a varchar, CHECK (a = 'a'));
regression=# \d pp
...
Check constraints:
"pp_a_check" CHECK (a::text = 'a'::text)
Or for that matter
regression=# CREATE TABLE p (a varchar, CHECK (a = any(array['a', 'b', 'c'])));
regression=# \d p
...
Check constraints:
"p_a_check" CHECK (a::text = ANY (ARRAY['a'::text, 'b'::text, 'c'::text]))
I wonder why you don't get an array of text constants in the IN case.
regards, tom lane