Re: pg_restore casts check constraints differently

On Tue, Mar 29, 2016 at 2:45 PM, 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.
>

​Why don't we just use ruleutils.c to generate \d results so that what we
end up showing is canonical?

David J.