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