Re: pg_restore casts check constraints differently

On Tue, Mar 29, 2016 at 7:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Tue, Mar 29, 2016 at 2:45 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.