Re: [GENERAL] pg_restore casts check constraints differently

On Thu, Mar 31, 2016 at 1:00 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I wrote:
>> Amit Langote <amitlangote09(at)gmail(dot)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.
>> I wonder why you don't get an array of text constants in the IN case.
>
> I poked into this and determined that it happens because transformAExprIn
> identifies the array type to use without considering whether an additional
> coercion will have to happen before the array elements can be compared to
> the lefthand input.
>
> I tried to fix that in a principled fashion, by resolving the actual
> comparison operator and using its righthand input type as the array
> element type (see first patch attached). That fixes this case all right,
> but it also makes several existing regression test cases fail, for
> example:
>
> ***************
> *** 381,392 ****
> FROM pg_class
> WHERE oid::regclass IN ('a_star', 'c_star')
> ORDER BY 1;
> ! relname | has_toast_table
> ! ---------+-----------------
> ! a_star | t
> ! c_star | t
> ! (2 rows)
> !
> --UPDATE b_star*
> -- SET a = text 'gazpacho'
> -- WHERE aa > 4;
> --- 381,389 ----
> FROM pg_class
> WHERE oid::regclass IN ('a_star', 'c_star')
> ORDER BY 1;
> ! ERROR: invalid input syntax for type oid: "a_star"
> ! LINE 3: WHERE oid::regclass IN ('a_star', 'c_star')
> ! ^
> --UPDATE b_star*
> -- SET a = text 'gazpacho'
> -- WHERE aa > 4;
>
> The problem is that regclass, like varchar, has no comparison operators
> of its own, relying on OID's operators. So this patch causes us to choose
> OID not regclass as the type of the unknown literals, which in this case
> seems like a loss of useful behavior.

Agreed; no need to break that.

> I'm tempted to just improve the situation for varchar with a complete
> kluge, ie the second patch attached. Thoughts?

Fixes for me.

Thanks,
Amit