Re: Functional dependencies and GROUP BY - for subqueries

Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> writes:
> On Fri, Apr 26, 2013 at 7:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> A larger point is that the patch as proposed doesn't fix the stated
>> problem, because it only descends into written-out subqueries. It
>> would only succeed at looking into views if we applied it after
>> rewriting, rather than in the parser. That's really not going to work.
>> It would be a complete disaster if the dependencies of a query that
>> references a view depend on the view's contents.

> Can you please elaborate, why would it be a disaster?

Consider that we've done

create table t1 (id int primary key, ... other stuff ...);
create view v1 as select * from t1;
create view v2 as select * from v1 group by id;

Currently, v2 would be rejected but you would like to make it legal.
Now consider

alter table t1 drop primary key;

This ALTER would have to be rejected, or else (with CASCADE) lead to
dropping v2 but not v1. That's pretty ugly action-at-a-distance
if you ask me. But worse, consider

create or replace view v1 as select * from t2;

where t2 exposes the same columns as t1 but lacks a primary-key
constraint on id. This likewise would need to invalidate v2. We lack
any dependency mechanism that could enforce that, and it seems seriously
ugly that such a view redefinition could fail at all. (Note for
instance that there's no place to put a CASCADE/RESTRICT option in
CREATE OR REPLACE VIEW.)

So quite aside from the implementation difficulties of looking into
views for such constraints, I don't think the behavior would be pleasant
if we did do it. Views are not supposed to expose properties of the
underlying tables.

regards, tom lane