Re: Functional dependencies and GROUP BY - for subqueries

Ashutosh Bapat <ashutosh.bapat@enterprisedb.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.

                        regards, tom lane