On Mon, Apr 29, 2013 at 7:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
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
