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/[email protected]
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
