Hi All,
While reviewing patch for similar problem in postgres_fdw [1], I
noticed that we don't use positional notation while creating the view.
This might introduced anomalies when GROUP BY entries are
non-immutable.
E.g.
postgres=# create view aggv as select c2 c21, c2 c22 from t1 group by 1, 2;
postgres=# \d+ aggv View "public.aggv"Column | Type | Collation | Nullable | Default |
Storage| Description
--------+---------+-----------+----------+---------+---------+-------------c21 | integer | | |
| plain |c22 | integer | | | | plain |
View definition:SELECT t1.c2 AS c21, t1.c2 AS c22 FROM t1 GROUP BY t1.c2, t1.c2;
That's not a problematic case, but following is
create view aggv_rand as select random() c21, random() c22 from t1
group by 1, 2;
CREATE VIEW
postgres=# \d+ aggv_rand View "public.aggv_rand"Column | Type | Collation |
Nullable| Default | Storage
| Description
--------+------------------+-----------+----------+---------+---------+-------------c21 | double precision |
| | | plain |c22 | double precision | | | | plain |
View definition:SELECT random() AS c21, random() AS c22 FROM t1 GROUP BY (random()), (random());
Notice four instances of random() instead of two in the original definition.
What is printed in \d+ output also goes into dump file, so when such a
view is restored, it will have a different behaviour that the intended
one.
[1] http://postgr.es/m/10660.1510093781@sss.pgh.pa.us
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
