Ashutosh Bapat ashutosh.ba...@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
On Mon, Apr 29, 2013 at 7:31 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Ashutosh Bapat ashutosh.ba...@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
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
Hi All,
If group by clause has primary key, the targetlist may have columns which
are not part of the aggregate and not part of group by clause. The relevant
commit is e49ae8d3bc588294d07ce1a1272b31718cfca5ef and relevant mail thread
has subject Functional dependencies and GROUP BY.
As a result,
Ashutosh Bapat ashutosh.ba...@enterprisedb.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
On Fri, Apr 26, 2013 at 7:54 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Ashutosh Bapat ashutosh.ba...@enterprisedb.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.
Ashutosh Bapat ashutosh.ba...@enterprisedb.com writes:
On Fri, Apr 26, 2013 at 7:54 PM, Tom Lane t...@sss.pgh.pa.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
On 7 August 2010 03:51, Tom Lane t...@sss.pgh.pa.us wrote:
Peter Eisentraut pete...@gmx.net writes:
Next version. Changed dependencies to pg_constraint, removed handling
of unique constraints for now, and made some enhancements so that views
track dependencies on constraints even in
Dean Rasheed dean.a.rash...@gmail.com writes:
On 7 August 2010 03:51, Tom Lane t...@sss.pgh.pa.us wrote:
I was testing out this feature this morning and discovered that the
results may be non-deterministic if the PK is deferrable.
Good point.
The original version of the patch had that check
On 5 September 2010 16:15, Tom Lane t...@sss.pgh.pa.us wrote:
Dean Rasheed dean.a.rash...@gmail.com writes:
On 7 August 2010 03:51, Tom Lane t...@sss.pgh.pa.us wrote:
I was testing out this feature this morning and discovered that the
results may be non-deterministic if the PK is deferrable.
Dean Rasheed dean.a.rash...@gmail.com writes:
On 5 September 2010 16:15, Tom Lane t...@sss.pgh.pa.us wrote:
I don't recall having thought about it one way or the other. What did
the check look like?
Well originally it was searching indexes rather than constraints, and
funcdeps_check_pk()
On sön, 2010-09-05 at 11:35 -0400, Tom Lane wrote:
Dean Rasheed dean.a.rash...@gmail.com writes:
On 5 September 2010 16:15, Tom Lane t...@sss.pgh.pa.us wrote:
I don't recall having thought about it one way or the other. What did
the check look like?
Well originally it was searching
On mån, 2010-07-26 at 10:46 -0600, Alex Hunsaker wrote:
On Sat, Jul 24, 2010 at 06:23, Peter Eisentraut pete...@gmx.net wrote:
Another open question I thought of was whether we should put the
dependency record on the pg_index row, or the pg_constraint row, or
perhaps the pg_class row.
Peter Eisentraut pete...@gmx.net writes:
Next version. Changed dependencies to pg_constraint, removed handling
of unique constraints for now, and made some enhancements so that views
track dependencies on constraints even in subqueries. Should be close
to final now. :-)
I've committed this
On Sat, Jul 24, 2010 at 06:23, Peter Eisentraut pete...@gmx.net wrote:
Another open question I thought of was whether we should put the
dependency record on the pg_index row, or the pg_constraint row, or
perhaps the pg_class row. Right now, it is using pg_index, because that
was easiest to
On fre, 2010-07-23 at 11:00 -0600, Alex Hunsaker wrote:
I just read that patch is getting pushed till at least the next commit
fest: http://archives.postgresql.org/pgsql-hackers/2010-07/msg01219.php
Should we push this patch back to? Alternatively we could make it
work with just primary
On Sun, Jul 18, 2010 at 02:40, Peter Eisentraut pete...@gmx.net wrote:
On lör, 2010-07-17 at 11:13 -0600, Alex Hunsaker wrote:
So I would expect the more indexes you
had or group by items to slow it down. Not so much the number of
columns. Right?
At the outer level (which is not visible in
On Fri, Jul 23, 2010 at 11:00, Alex Hunsaker bada...@gmail.com wrote:
Alternatively we could make it
work with just primary keys until the other patch gets in. I think
that makes sense, find that attached. Thoughts?
*sigh*, find attached a version that removes talk of unique not null
On lör, 2010-07-17 at 11:13 -0600, Alex Hunsaker wrote:
its really no surprise that your test with 1600 columns had little
effect. As it loops over the the indexes, then the index keys and
then the group by items right? So I would expect the more indexes you
had or group by items to slow it
On fre, 2010-07-16 at 22:29 -0600, Alex Hunsaker wrote:
The only corner case I have run into is creating a view with what I
would call an implicit 'not null' constraint. Demonstration below:
create table nn (a int4 not null, b int4, unique (a));
select * from nn group by a; -- should this
On Sat, Jul 17, 2010 at 04:15, Peter Eisentraut pete...@gmx.net wrote:
On fre, 2010-07-16 at 22:29 -0600, Alex Hunsaker wrote:
The only corner case I have run into is creating a view with what I
would call an implicit 'not null' constraint. Demonstration below:
create table nn (a int4 not
On Fri, Jul 16, 2010 at 22:29, Alex Hunsaker bada...@gmail.com wrote:
(FYI I do plan on doing some performance testing with large columns
later, any other requests?)
And here are the results. All tests are with an empty table with 1500
int4 columns. There is a unique non null index on the
On Sat, Jul 17, 2010 at 11:13, Alex Hunsaker bada...@gmail.com wrote:
On Sat, Jul 17, 2010 at 04:15, Peter Eisentraut pete...@gmx.net wrote:
On fre, 2010-07-16 at 22:29 -0600, Alex Hunsaker wrote:
The only corner case I have run into is creating a view with what I
would call an implicit 'not
On Fri, Jun 25, 2010 at 14:06, Peter Eisentraut pete...@gmx.net wrote:
Second version:
Hi!
Ive looked this over. Looks great! I have some nits about the
documentation and comments ( non issues like referencing primary keys
when it really means not null unique indexes :-P ), but on the whole
On mån, 2010-06-07 at 21:33 +0300, Peter Eisentraut wrote:
I have developed a patch that partially implements the functional
dependency feature that allows some columns to be omitted from the
GROUP BY clause if it can be shown that the columns are functionally
dependent on the columns in the
On tis, 2010-06-08 at 10:21 -0400, Tom Lane wrote:
The question is why bother to recognize *any* cases of this form.
I find it really semantically ugly to have the parser effectively
doing one deduction of this form when the main engine for that type
of deduction is elsewhere; so unless there
On tis, 2010-06-08 at 10:05 -0400, Tom Lane wrote:
Perhaps the correct fix would be to mark stored query trees as having
a
dependency on the index, so that dropping the index/constraint would
force a drop of the rule too. Just pushing the check to plan time, as
I suggested yesterday, isn't a
2010/6/7 Greg Stark gsst...@mit.edu:
On Mon, Jun 7, 2010 at 7:33 PM, Peter Eisentraut pete...@gmx.net wrote:
I have developed a patch that partially implements the functional
dependency feature
Nice! :)
I like this idea too. It can simplify some queries and I believe - it
is very good
On tis, 2010-06-08 at 09:59 +0900, Hitoshi Harada wrote:
Also, when a column is compared with a constant, it can appear
ungrouped:
SELECT x, y FROM tab2 WHERE y = 5 GROUP BY x;
I don't see why it should be allowed. I see the insist that y must be
unique value so it is ok to be
On Mon, Jun 7, 2010 at 6:41 PM, Stephen Frost sfr...@snowman.net wrote:
* Peter Eisentraut (pete...@gmx.net) wrote:
This is frequently requested by MySQL converts (and possibly others).
I'd certainly love to see it- but let's not confuse people by implying
that it would actually act the way
On Tue, Jun 8, 2010 at 4:16 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Peter Eisentraut pete...@gmx.net writes:
I have developed a patch that partially implements the functional
dependency feature that allows some columns to be omitted from the
GROUP BY clause if it can be shown that the columns
Greg Stark gsst...@mit.edu writes:
On Tue, Jun 8, 2010 at 4:16 AM, Tom Lane t...@sss.pgh.pa.us wrote:
The main objection to this is the same one I've had all along: it makes
the syntactic validity of a query dependent on what indexes exist for
the table. At minimum, that means that enforcing
On Tue, Jun 8, 2010 at 3:05 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Well, no, any cached plan will get invalidated if the index goes away.
The big problem with this implementation is that you could create a
*rule* (eg a view) containing a query whose validity depends on the
existence of an
Peter Eisentraut pete...@gmx.net writes:
On tis, 2010-06-08 at 09:59 +0900, Hitoshi Harada wrote:
In addition, what if y is implicitly a constant? For example,
SELECT x, y FROM tab2 WHERE y = a AND a = 5 GROUP BY x;
Yes, as I said, my implementation is incomplete in the sense that it
only
On 6/8/10 5:21 PM +0300, Tom Lane wrote:
Peter Eisentrautpete...@gmx.net writes:
On tis, 2010-06-08 at 09:59 +0900, Hitoshi Harada wrote:
In addition, what if y is implicitly a constant? For example,
SELECT x, y FROM tab2 WHERE y = a AND a = 5 GROUP BY x;
Yes, as I said, my implementation
* Tom Lane (t...@sss.pgh.pa.us) wrote:
Perhaps the correct fix would be to mark stored query trees as having a
dependency on the index, so that dropping the index/constraint would
force a drop of the rule too. Just pushing the check to plan time, as
I suggested yesterday, isn't a very nice
On Tue, Jun 8, 2010 at 3:21 PM, Tom Lane t...@sss.pgh.pa.us wrote:
The question is why bother to recognize *any* cases of this form.
I find it really semantically ugly to have the parser effectively
doing one deduction of this form when the main engine for that type
of deduction is elsewhere;
Stephen Frost sfr...@snowman.net writes:
* Tom Lane (t...@sss.pgh.pa.us) wrote:
Perhaps the correct fix would be to mark stored query trees as having a
dependency on the index, so that dropping the index/constraint would
force a drop of the rule too.
Alternatively, we could rewrite the rule
* Tom Lane (t...@sss.pgh.pa.us) wrote:
Hm. The problem with that is that one of the benefits we'd like to get
from this is an efficiency win: the generated plan ought to only group
by the PK, not uselessly sort/group by everything in the row. I suppose
we could have the planner
I have developed a patch that partially implements the functional
dependency feature that allows some columns to be omitted from the
GROUP BY clause if it can be shown that the columns are functionally
dependent on the columns in the group by clause and therefore guaranteed
to be unique per group.
On Mon, Jun 7, 2010 at 7:33 PM, Peter Eisentraut pete...@gmx.net wrote:
I have developed a patch that partially implements the functional
dependency feature
Nice! :)
--
greg
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
2010/6/8 Peter Eisentraut pete...@gmx.net:
I have developed a patch that partially implements the functional
dependency feature that allows some columns to be omitted from the
GROUP BY clause if it can be shown that the columns are functionally
dependent on the columns in the group by clause
* Hitoshi Harada (umi.tan...@gmail.com) wrote:
I don't see why it should be allowed. I see the insist that y must be
unique value so it is ok to be ungrouped but the point of discussion
is far from that; Semantically y is not grouping key.
Ignoring the fact that it's terribly useful- isn't it
* Peter Eisentraut (pete...@gmx.net) wrote:
This is frequently requested by MySQL converts (and possibly others).
I'd certainly love to see it- but let's not confuse people by implying
that it would actually act the way MySQL does. It wouldn't, because
what MySQL does is alot closer to
Peter Eisentraut pete...@gmx.net writes:
I have developed a patch that partially implements the functional
dependency feature that allows some columns to be omitted from the
GROUP BY clause if it can be shown that the columns are functionally
dependent on the columns in the group by clause and
45 matches
Mail list logo
