Re: Functional dependencies and GROUP BY - for subqueries - Mailing list pgsql-hackers

From Ashutosh Bapat
Subject Re: Functional dependencies and GROUP BY - for subqueries
Date
Msg-id CAFjFpRfAEtoSSJ+9J2C-ijqfRJMOkbgxPWGedQ57KZf1s_y3Cg@mail.gmail.com
Whole thread Raw
In response to Re: Functional dependencies and GROUP BY - for subqueries  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers

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

pgsql-hackers by date:

Previous
From: Jaime Casanova
Date:
Subject: Re: Graph datatype addition
Next
From: Simon Riggs
Date:
Subject: Re: Substituting Checksum Algorithm (was: Enabling Checksums)