Обсуждение: BUG #14177: ARRAYs in VIEWs are inconsistently cast

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDE3NwpMb2dnZWQgYnk6ICAg
ICAgICAgIFRheWxvciBSZWVjZQpFbWFpbCBhZGRyZXNzOiAgICAgIHRheWxv
ci5yZWVjZUB6dWVyY2hlcnRlY2guY29tClBvc3RncmVTUUwgdmVyc2lvbjog
OS41LjMKT3BlcmF0aW5nIHN5c3RlbTogICBVYnVudHUgU2VydmVyIDE0LjA0
IGFuZCAxNi4wNApEZXNjcmlwdGlvbjogICAgICAgIAoKVGhpcyBpcyBhbiBp
c3N1ZSBJJ3ZlIG5vdGljZWQgdGhyb3VnaG91dCA5LjMuKiwgYW5kIG5vdyBp
biA5LjUuKi4gIElmIEkKZGVmaW5lIGEgVklFVyAoJ3YxJywgYmVsb3cpLCBh
bmQgdGhlbiB1c2UgdGhhdCBWSUVXJ3MgY29tcGlsZWQgZGVmaW5pdGlvbiB0
bwpjcmVhdGUgYSBzZWNvbmQgVklFVyAoJ3YyJywgYmVsb3cpLCB0aGUgdHdv
IFZJRVdzIGhhdmUgZGlmZmVyZW50IGNvbXBpbGVkCmRlZmluaXRpb25zLg0K
DQpJIGZpcnN0IGRpc2NvdmVyZWQgdGhpcyBpbmNvbnNpc3RlbmN5IHdoZW4g
SSByYW4gcGdfZHVtcCBvbiBhIGRhdGFiYXNlIGFuZApyZXN0b3JlZCB0aGUg
YmFja3VwIHRvIGFub3RoZXIgc2VydmVyLiAgVGhlIHNlcnZlcnMnIHNjaGVt
YXMgd2VyZSBub3QKY29tcGxldGVseSBjb25zaXN0ZW50IHdpdGggb25lIGFu
b3RoZXIsIGRlc3BpdGUgcnVubmluZyBpZGVudGljYWwgdmVyc2lvbnMKb2Yg
cHNxbC4NCg0KSSByZWNvZ25pemUgdGhhdCB0aGUgZGlmZmVyZW5jZXMgaW4g
VklFVyBkZWZpbml0aW9ucyBhcmUgY29zbWV0aWMgLS0gdGhleSdyZQpmdW5j
dGlvbmFsbHkgZXF1aXZhbGVudC4gIE9uZSBjYXN0cyBhbiBBUlJBWSBvZiBW
QVJDSEFSIHRvIDo6dGV4dFtdIHdoaWxlCnRoZSBvdGhlciBjYXN0cyB0aGUg
ZWxlbWVudHMgaW5kaXZpZHVhbGx5IHRvIFZBUkNIQVIgYW5kIHRoZW4gVEVY
VC4gSW4gYW4KZWZmb3J0IHRvIGtlZXAgZGF0YWJhc2Ugc2NoZW1hIGNvbXBs
ZXRlbHkgaWRlbnRpY2FsLCB0aG91Z2gsIGl0IHdvdWxkIGJlCm5pY2UgaWYg
YSBwZ19kdW1wIGludG8gYSBwZ19yZXN0b3JlIHJlc3VsdGVkIGluIGV4YWN0
bHkgaWRlbnRpY2FsIHNjaGVtYS4NCg0KQW4gZXhhbXBsZSBvZiBob3cgdG8g
cmVwbGljYXRlIHRoZSBpc3N1ZSBmb2xsb3dzLiAgVGhhbmsgeW91IGluIGFk
dmFuY2UgZm9yCnlvdXIgZXhwZXJ0aXNlLCBhbmQgZm9yIHN1Y2ggYW4gYXdl
c29tZSBEQiBwcm9kdWN0IQ0KDQotVGF5bG9yDQoNCi4uLg0KDQp1c2VyQHNl
cnZlcjovdG1wIyBwc3FsIGQxDQpwc3FsICg5LjUuMykNClR5cGUgImhlbHAi
IGZvciBoZWxwLg0KDQpkMT0jIENSRUFURSBUQUJMRSB0IChhIENIQVJBQ1RF
UiBWQVJZSU5HKTsNCkNSRUFURSBUQUJMRQ0KZDE9IyBDUkVBVEUgVklFVyB2
MSBBUyBTRUxFQ1QgYSBGUk9NIHQgV0hFUkUgdC5hIElOICgnYicsJ2MnKTsN
CkNSRUFURSBWSUVXDQpkMT0jIFxkKyB2MQ0KICAgICAgICAgICAgICAgICAg
ICAgICAgIFZpZXcgInB1YmxpYy52MSINCiBDb2x1bW4gfCAgICAgICBUeXBl
ICAgICAgICB8IE1vZGlmaWVycyB8IFN0b3JhZ2UgIHwgRGVzY3JpcHRpb24g
DQotLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tKy0t
LS0tLS0tLS0rLS0tLS0tLS0tLS0tLQ0KIGEgICAgICB8IGNoYXJhY3RlciB2
YXJ5aW5nIHwgICAgICAgICAgIHwgZXh0ZW5kZWQgfCANClZpZXcgZGVmaW5p
dGlvbjoNCiBTRUxFQ1QgdC5hDQogICBGUk9NIHQNCiAgV0hFUkUgdC5hOjp0
ZXh0ID0gQU5ZIChBUlJBWVsnYic6OmNoYXJhY3RlciB2YXJ5aW5nLCAnYyc6
OmNoYXJhY3Rlcgp2YXJ5aW5nXTo6dGV4dFtdKTsNCg0KZDE9IyBDUkVBVEUg
VklFVyB2MiBBUyAgU0VMRUNUIHQuYQ0KZDEtIyAgICBGUk9NIHQNCmQxLSMg
ICBXSEVSRSB0LmE6OnRleHQgPSBBTlkgKEFSUkFZWydiJzo6Y2hhcmFjdGVy
IHZhcnlpbmcsICdjJzo6Y2hhcmFjdGVyCnZhcnlpbmddOjp0ZXh0W10pOw0K
Q1JFQVRFIFZJRVcNCmQxPSMgXGQrIHYyDQogICAgICAgICAgICAgICAgICAg
ICAgICBWaWV3ICJwdWJsaWMudjIiDQogQ29sdW1uIHwgICAgICAgVHlwZSAg
ICAgICAgfCBNb2RpZmllcnMgfCBTdG9yYWdlICB8IERlc2NyaXB0aW9uIA0K
LS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLSstLS0t
LS0tLS0tKy0tLS0tLS0tLS0tLS0NCiBhICAgICAgfCBjaGFyYWN0ZXIgdmFy
eWluZyB8ICAgICAgICAgICB8IGV4dGVuZGVkIHwgDQpWaWV3IGRlZmluaXRp
b246DQogU0VMRUNUIHQuYQ0KICAgRlJPTSB0DQogIFdIRVJFIHQuYTo6dGV4
dCA9IEFOWSAoQVJSQVlbJ2InOjpjaGFyYWN0ZXIgdmFyeWluZzo6dGV4dCwg
J2MnOjpjaGFyYWN0ZXIKdmFyeWluZzo6dGV4dF0pOwoK

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:

https://www.postgresql.org/message-id/17675.1459353646%40sss.pgh.pa.us

Thanks,
Amit