From: | Amit Langote <amitlangote09(at)gmail(dot)com> |
---|---|
To: | taylor(dot)reece(at)zuerchertech(dot)com |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #14177: ARRAYs in VIEWs are inconsistently cast |
Date: | 2016-06-06 14:44:05 |
Message-ID: | CA+HiwqEVKCNcBuDnKbyVX8E6nmobFnXL8mo3uzuNvfC6BhhoeQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Sat, Jun 4, 2016 at 7:17 AM, <taylor(dot)reece(at)zuerchertech(dot)com> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 14177
> Logged by: Taylor Reece
> Email address: taylor(dot)reece(at)zuerchertech(dot)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(at)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-06-06 18:38:00 | Re: [BUGS] Routine analyze of single column prevents standard autoanalyze from running at all |
Previous Message | Tomasz Ostrowski | 2016-06-06 14:25:29 | Routine analyze of single column prevents standard autoanalyze from running at all |