BUG #14177: ARRAYs in VIEWs are inconsistently cast

On Sat, Jun 4, 2016 at 7:17 AM, <taylor.reece@zuerchertech.com> wrote:

The following bug has been logged on the website:

Bug reference: 14177
Logged by: Taylor Reece
Email address: taylor.reece@zuerchertech.com
PostgreSQL version: 9.5.3
Operating system: Ubuntu Server 14.04 and 16.04
Description:

This is an issue I've noticed throughout 9.3.*, and now in 9.5.*. If I
define a VIEW ('v1', below), and then use that VIEW's compiled definition to
create a second VIEW ('v2', below), the two VIEWs have different compiled
definitions.

I first discovered this inconsistency when I ran pg_dump on a database and
restored the backup to another server. The servers' schemas were not
completely consistent with one another, despite running identical versions
of psql.

I recognize that the differences in VIEW definitions are cosmetic -- they're
functionally equivalent. One casts an ARRAY of VARCHAR to ::text[] while
the other casts the elements individually to VARCHAR and then TEXT. In an
effort to keep database schema completely identical, though, it would be
nice if a pg_dump into a pg_restore resulted in exactly identical schema.

An example of how to replicate the issue follows. Thank you in advance for
your expertise, and for such an awesome DB product!

-Taylor

...

user@server:/tmp# psql d1
psql (9.5.3)
Type "help" for help.

d1=# CREATE TABLE t (a CHARACTER VARYING);
CREATE TABLE
d1=# CREATE VIEW v1 AS SELECT a FROM t WHERE t.a IN ('b','c');
CREATE VIEW
d1=# \d+ v1
View "public.v1"
Column | Type | Modifiers | Storage | Description
--------+-------------------+-----------+----------+-------------
a | character varying | | extended |
View definition:
SELECT t.a
FROM t
WHERE t.a::text = ANY (ARRAY['b'::character varying, 'c'::character
varying]::text[]);

d1=# CREATE VIEW v2 AS SELECT t.a
d1-# FROM t
d1-# WHERE t.a::text = ANY (ARRAY['b'::character varying, 'c'::character
varying]::text[]);
CREATE VIEW
d1=# \d+ v2
View "public.v2"
Column | Type | Modifiers | Storage | Description
--------+-------------------+-----------+----------+-------------
a | character varying | | extended |
View definition:
SELECT t.a
FROM t
WHERE t.a::text = ANY (ARRAY['b'::character varying::text, 'c'::character
varying::text]);

Patch to fix this behavior was posted (not applied yet though) a
little while ago:

/messages/by-id/17675.1459353646@sss.pgh.pa.us

Thanks,
Amit

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs