Re: Functional dependencies and GROUP BY - for subqueries

On Mon, Apr 29, 2013 at 7:31 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> writes:
> > Is there any reason why do we want to check the functional dependencies
> at
> > the time of parsing and not after rewrite? Obviously, by doing so, we
> will
> > allow creation of certain views which will start throwing errors after
> the
> > underlying table changes the primary key. Is it mandatory that we throw
> > "functional dependency" related errors at the time of creation of views?
>
> From a usability standpoint, I would think so. And really the only
> excuse for the functional-dependency feature to exist at all is
> usability; it adds nothing you couldn't do without it.
>
> If we wanted to do something like this, I think the clean way to do it
> would be to invent a notion of unique/not-null/pkey constraints on
> views, so that the creator of a view could declaratively say that he
> wants such a property exposed. That is, the example would become
> something like
>
> create table t1 (id int primary key, ... other stuff ...);
> create view v1 as select * from t1;
> alter view v1 add primary key(id);
> create view v2 as select * from v1 group by id;
>
> The pkey constraint on v1 is just a catalog entry with a dependency on
> t1's pkey constraint; there's no actual index there. But now, v2 can
> be built with a dependency on v1's pkey, not t1's, and the action-at-
> a-distance problem goes away. For example, a "CREATE OR REPLACE v1"
> command could check that the new view definition still provides
> something for v1's pkey to depend on, and throw error or not without any
> need to examine the contents of other views. Dropping various elements
> of this schema would work unsurprisingly, too.
>
> This would, of course, require a significant chunk of new code, and
> personally I do not think the feature would be worth it. But it
> would be a clean and usable design.
>
>
Yes, this looks better design. But I do not see any interest as such. So,
if I have to spend time here, there is higher chance it would go waste.

Will it be useful to have primary key grouping functionality extended to
the subqueries? For example,

CREATE TEMP TABLE products (product_id int, name text, price numeric);
CREATE TEMP TABLE sales (product_id int, units int);
ALTER TABLE products ADD PRIMARY KEY (product_id);

SELECT product_id, p.name, (sum(s.units) * p.price) AS sales FROM (select *
from products) p LEFT JOIN (select * from sales) s USING (product_id) GROUP
BY product_id;

This subquery gives error (p.name should be part of group by clause), but
functionally it's grouping based on primary key. Is there a better way to
use the functional dependency for grouping?

> regards, tom lane
>

--
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Postgres Database Company