Re: [GENERAL] pg_restore casts check constraints differently

Hey, I work with Josh Ma and we were troubleshooting this problem together.

We ended up creating a workaround by taking the dumps from different DBs,
initializing new DBs based on those dumps, and then dumping these new DBs.
This work around worked since the dumps of databases that were initialized
via a psql script outputted the text array constraint in the same way.

So there are definitely ways to workaround this inconsistent representation
for our use case.

Thanks again for the help!

--
Victor Pontis
Benchling
Engineer
858-761-5232

On Wed, Mar 30, 2016 at 9:51 AM, Amit Langote <amitlangote09(at)gmail(dot)com>
wrote:

> 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
>