Re: [GENERAL] pg_restore casts check constraints differently - Mailing list pgsql-hackers

On Thu, Mar 31, 2016 at 1:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
>> 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.
>> 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