Re: [GENERAL] pg_restore casts check constraints differently

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.

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

regards, tom lane