On Mon, Apr 29, 2013 at 7:31 PM, Tom Lane wrote:
> Ashutosh Bapat 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 throwin
Ashutosh Bapat 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.
> 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
Ashutosh Bapat writes:
> On Fri, Apr 26, 2013 at 7:54 PM, Tom Lane 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 views if we applied it after
>> rewriting, r
On Fri, Apr 26, 2013 at 7:54 PM, Tom Lane wrote:
> Ashutosh Bapat 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
Ashutosh Bapat 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 ba
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, f
On sön, 2010-09-05 at 11:35 -0400, Tom Lane wrote:
> Dean Rasheed writes:
> > On 5 September 2010 16:15, Tom Lane 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, an
Dean Rasheed writes:
> On 5 September 2010 16:15, Tom Lane 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() included the following check:
> if (!
On 5 September 2010 16:15, Tom Lane wrote:
> Dean Rasheed writes:
>> On 7 August 2010 03:51, Tom Lane 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
Dean Rasheed writes:
> On 7 August 2010 03:51, Tom Lane 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 in it, but it
> vanished from the final
On 7 August 2010 03:51, Tom Lane wrote:
> Peter Eisentraut 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 f
Peter Eisentraut 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 with some re
On mån, 2010-07-26 at 10:46 -0600, Alex Hunsaker wrote:
> On Sat, Jul 24, 2010 at 06:23, Peter Eisentraut 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,
On Sat, Jul 24, 2010 at 06:23, Peter Eisentraut 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 code up, but
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 Fri, Jul 23, 2010 at 11:00, Alex Hunsaker 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
constraints from the d
On Sun, Jul 18, 2010 at 02:40, Peter Eisentraut 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 this p
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 i
On Sat, Jul 17, 2010 at 11:13, Alex Hunsaker wrote:
> On Sat, Jul 17, 2010 at 04:15, Peter Eisentraut 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. Demons
On Fri, Jul 16, 2010 at 22:29, Alex Hunsaker 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 first column.
(non
On Sat, Jul 17, 2010 at 04:15, Peter Eisentraut 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 null, b i
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 th
On Fri, Jun 25, 2010 at 14:06, Peter Eisentraut 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
it works and loo
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
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
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 the
* 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 reverse-en
Stephen Frost 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 (not unlike
On Tue, Jun 8, 2010 at 3:21 PM, 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 ther
* 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 6/8/10 5:21 PM +0300, Tom Lane wrote:
Peter Eisentraut 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 i
Peter Eisentraut 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 recogni
On Tue, Jun 8, 2010 at 3:05 PM, Tom Lane 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 index. Dropping th
Greg Stark writes:
> On Tue, Jun 8, 2010 at 4:16 AM, Tom Lane 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 the check at parse
>> time
On Tue, Jun 8, 2010 at 4:16 AM, Tom Lane wrote:
> Peter Eisentraut 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
>> depend
On Mon, Jun 7, 2010 at 6:41 PM, Stephen Frost 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 MySQL does. I
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 b
2010/6/7 Greg Stark :
> On Mon, Jun 7, 2010 at 7:33 PM, Peter Eisentraut 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 marketing bonus for us.
P
Peter Eisentraut 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 therefore
* 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 'distinc
* 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 i
2010/6/8 Peter Eisentraut :
> 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 therefo
On Mon, Jun 7, 2010 at 7:33 PM, Peter Eisentraut 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:
http://www.postgresql
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
45 matches
Mail list logo
