Re: Functional dependencies and GROUP BY - for subqueries

On Fri, Apr 26, 2013 at 7:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> writes:
> > The reason being, it doesn't look into the subqueries (in FROM clause) to
> > infer that p.product_id is essentially product.product_id which is a
> > primary key.
>
> Right.
>
> > Attached find a crude patch to infer the same by traversing subqueries.
>
> I think this is probably a bad idea. We could spend an infinite amount
> of code this way, with ever-increasing runtime cost and ever-decreasing
> usefulness, and where would we stop? I'm also a bit concerned about
> allowing illegal queries due to bugs of omission in the
> ever-more-complex checking code, which could be quite hard to find, and
> when we did find them we'd be faced with a backwards compatibility break
> if we fix them.
>
> A larger point is that the patch as proposed doesn't fix the stated
> problem, because it only descends into written-out subqueries.

That's correct. I tested it only with the written-out subqueries, to see if
the idea works. But it started with the case involving views.

> 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?

Will we extend this functionality for written-out subqueries queries and/or
views or none?

I am not touchy about the approach, I have taken. I am interested in the
feature extension, whichever way it gets implemented.

regards, tom lane
>

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