pg_restore casts check constraints differently

From: Joshua Ma <josh(at)benchling(dot)com>
To: PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org>
Cc: Victor Pontis <victor(at)benchling(dot)com>
Subject: pg_restore casts check constraints differently
Date: 2016-03-29 21:05:47
Message-ID: CAG9XPVk0j9isO-q2Uv+2E3keaTGm8fxx=w558xe1vf0O7djT=Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2016-03-29 21:45:52 Re: pg_restore casts check constraints differently
Previous Message Adrian Klaver 2016-03-29 20:44:36 Re: debugging server connection issue

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2016-03-29 21:09:42 pgsql: Allow to_timestamp(float8) to convert float infinity to timestam
Previous Message Robbie Harwood 2016-03-29 21:05:45 Re: [PATCH v8] GSSAPI encryption support