Thread

1. Re: [HACKERS] 6.5 TODO list

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-05-10T21:49:01Z

   Bruce Momjian <maillist@candle.pha.pa.us> writes:
   > OK, here is the list.  Please send me changes.
   
   > Can not insert/update oid
   
   I think this is very unlikely to get fixed for 6.5, might as well just
   put it on the to-do-later list.
   
   > EXPLAIN SELECT 1 UNION SELECT 2 crashes, rewrite system?
   
   Already fixed, I believe, unless someone can produce a case that
   fails...
   
   > move UNION stuff into rewrite files
   
   Is this the same as EXPLAIN issue above, or a different concern?
   
   > Vacuum of tables >2 gigs - NOTICE:  Can't truncate multi-segments relation
   
   This is definitely a "must fix" item for 6.5, IMHO...
   
   > missing optimizer selectivities for date, etc.
   
   The selectivity-estimation code needs major work.  Again, I'd say just
   shove it to the 6.6 list...
   
   > int8 indexing needs work?
   
   Is this done, or are there still problems?
   
   > hash on int2/char only looks a first bytes, and big-endian machines hash poorly
   
   Fixed.
   
   > Some CASE() statements involving two tables crash
   
   Fixed.
   
   > CREATE FUNCTION fails
   
   Isn't this fixed?  I wasn't aware of any remaining problems...
   
   > Make sure pg_internal.init generation can't cause unreliability
   > ...
   > pg_interal.init, check for reliability on rebuild
   
   Duplicate items, I think.
   
   > GROUP BY expression?
   
   I think I've fixed the problems here.
   
   > problem with ALTER TABLE on inherited
   > ...
   > ALTER TABLE ADD COLUMN to inherited table put column in wrong place
   
   Duplicates?
   
   > GROUP BY can reference columns not in target list
   
   What's wrong with that?
   
   > SELECT name, value FROM t1 as touter WHERE (value/(SELECT AVG(value) 
   > 	FROM t1 WHERE name = touter.name)) > 0.75; fails
   
   Fixed.
   
   			regards, tom lane
   
   

2. Re: [HACKERS] 6.5 TODO list

   Bruce Momjian <maillist@candle.pha.pa.us> — 1999-05-10T22:01:18Z

   > Bruce Momjian <maillist@candle.pha.pa.us> writes:
   > > OK, here is the list.  Please send me changes.
   > 
   > > Can not insert/update oid
   > 
   > I think this is very unlikely to get fixed for 6.5, might as well just
   > put it on the to-do-later list.
   
   Moved.
   
   
   > 
   > > EXPLAIN SELECT 1 UNION SELECT 2 crashes, rewrite system?
   > 
   > Already fixed, I believe, unless someone can produce a case that
   > fails...
   
   Good.  Thanks.
   
   > 
   > > move UNION stuff into rewrite files
   > 
   > Is this the same as EXPLAIN issue above, or a different concern?
   
   That is for me.  I currently do UNION in postgres.c, shoud perhaps move
   to rewrite system.  Not sure yet.
   
   > 
   > > Vacuum of tables >2 gigs - NOTICE:  Can't truncate multi-segments relation
   > 
   > This is definitely a "must fix" item for 6.5, IMHO...
   
   Yes.  It shows we are getting bigger.  Most/all? of my commerical
   database clients don't have 2-gig tables.
   
   
   > 
   > > missing optimizer selectivities for date, etc.
   > 
   > The selectivity-estimation code needs major work.  Again, I'd say just
   > shove it to the 6.6 list...
   
   Moved.
   
   
   > 
   > > int8 indexing needs work?
   > 
   > Is this done, or are there still problems?
   
   Not sure.  Just asking.  I saw Thomas complaining about some testing
   problems.  Let's what he says.
   
   > 
   > > hash on int2/char only looks a first bytes, and big-endian machines hash poorly
   > 
   > Fixed.
   
   Removed.
   
   > > Some CASE() statements involving two tables crash
   > 
   > Fixed.
   
   Good.
   
   
   > > CREATE FUNCTION fails
   > 
   > Isn't this fixed?  I wasn't aware of any remaining problems...
   
   OK.
   
   > 
   > > Make sure pg_internal.init generation can't cause unreliability
   > > ...
   > > pg_interal.init, check for reliability on rebuild
   > 
   > Duplicate items, I think.
   
   Yes.
   
   > 
   > > GROUP BY expression?
   > 
   > I think I've fixed the problems here.
   
   Removed.
   
   > 
   > > problem with ALTER TABLE on inherited
   > > ...
   > > ALTER TABLE ADD COLUMN to inherited table put column in wrong place
   > 
   > Duplicates?
   
   Yes.
   
   > > GROUP BY can reference columns not in target list
   > 
   > What's wrong with that?
   
   Is that not a problem.  What possible use would GROUP BY on columns not
   in target list be of use.  Maybe it is.  I remember someone asking for
   something like this.  I will remove the item unless someone complains.
   I thought you were the one complaining about it.
   
   > 
   > > SELECT name, value FROM t1 as touter WHERE (value/(SELECT AVG(value) 
   > > 	FROM t1 WHERE name = touter.name)) > 0.75; fails
   > 
   > Fixed.
   
   Already removed.
   
   Thanks for the updates.
   
   -- 
     Bruce Momjian                        |  http://www.op.net/~candle
     maillist@candle.pha.pa.us            |  (610) 853-3000
     +  If your life is a hard drive,     |  830 Blythe Avenue
     +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
   
   

3. Re: [HACKERS] 6.5 TODO list

   Jan Wieck <jwieck@debis.com> — 1999-05-11T13:20:32Z

   Bruce Momjian wrote:
   
   > > > GROUP BY can reference columns not in target list
   > >
   > > What's wrong with that?
   >
   > Is that not a problem.  What possible use would GROUP BY on columns not
   > in target list be of use.  Maybe it is.  I remember someone asking for
   > something like this.  I will remove the item unless someone complains.
   > I thought you were the one complaining about it.
   
       This can happen if the GROUP BY clause is coming from a view,
       but  the  grouping  columns  of  the  view  aren't   in   the
       targetlist.
   
       Usually  the  view's  grouping  is required because of use of
       aggregates in the view, so omitting them isn't a good idea.
   
       I'm actually testing what happens if I  use  junk  TLE's  for
       rule generated GROUP BY entries...
   
   
   Jan
   
   --
   
   #======================================================================#
   # It's easier to get forgiveness for being wrong than for being right. #
   # Let's break this rule - forgive me.                                  #
   #======================================== jwieck@debis.com (Jan Wieck) #
   
   
   
   

4. Re: [HACKERS] 6.5 TODO list

   Jan Wieck <jwieck@debis.com> — 1999-05-11T16:01:54Z

   >
   > Bruce Momjian wrote:
   >
   > > > > GROUP BY can reference columns not in target list
   > > >
   > > > What's wrong with that?
   > >
   > > Is that not a problem.  What possible use would GROUP BY on columns not
   > > in target list be of use.  Maybe it is.  I remember someone asking for
   > > something like this.  I will remove the item unless someone complains.
   > > I thought you were the one complaining about it.
   >
   >     This can happen if the GROUP BY clause is coming from a view,
   >     but  the  grouping  columns  of  the  view  aren't   in   the
   >     targetlist.
   >
   >     Usually  the  view's  grouping  is required because of use of
   >     aggregates in the view, so omitting them isn't a good idea.
   >
   >     I'm actually testing what happens if I  use  junk  TLE's  for
   >     rule generated GROUP BY entries...
   
       Oh jesus - what a mess!
   
       I've tested it and it solved the problem with
   
       CREATE TABLE t1 (a int4, b int4);
       CREATE VIEW v1 AS SELECT b, count(b) AS n
         FROM t1 GROUP BY b;
       SELECT n FROM v1;
   
       This  one  produces  now  the correct output. But it does not
       handle
   
       SELECT n FROM v1 WHERE 2 < n;
   
       because  the  group  clause  isn't  added  to  the  aggregate
       subplan,  the  rule  system  generated  for the qual - that's
       maybe fixable. Worse is, that one of the queries in the rules
       regression  test  fails,  because a GROUP BY attribute wasn't
       found in the targetlist.
   
       The problem is that the planner modifies  the  targetlist  if
       the  operation  is an INSERT/DELETE by first creating a clean
       one representing the result relation and then moving the  old
       expressions  into. Then it adds some junk stuff and specially
       marked TLE's from the original targetlist.
   
       BUT - during this (preprocess_targetlist()) all  the  resno's
       can  get  reassigned and later the planner tries to match the
       GROUP BY entries only by resno. But the resno's in the  group
       clauses haven't been adjusted!
   
       Another interesting detail I found is this:
   
       CREATE TABLE t1 (a int4, b int4);
       -- insert some stuff into t1
       CREATE TABLE t2 (b int4, n int4);
   
       -- This one is working correct:
       SELECT b, count(b) FROM t1 GROUP BY b;
   
       -- This one doesn't
       INSERT INTO t2 SELECT b, count(b) FROM t1 GROUP BY b;
       ERROR:  Illegal use of aggregates or non-group column in target list
   
       Ooops - I think it should work - especially because the plain
       SELECT returned the correct result.  But it fail  during  the
       parse  already  and  I don't get a parser debug output at all
       from tcop.  As soon as this is fixed, I assume a problem with
       a query like this:
   
       INSERT INTO t2 (n) SELECT count(b) FROM t1 GROUP BY b;
   
       (currently  it  tells  "Aggregates  not  allowed  in GROUP BY
       clause" - what's totally braindead) The problem I  expect  is
       that the parser creates resno 1 for "count(b)" and a junk TLE
       with resno 2 for "b" which is referenced in the group clause.
       preprocess_targetlist()  will  now  create the new targetlist
       with resno 1 = "b" = NULL, resno 2 =  "n"  =  "count(b)"  and
       maybe  the  junk  resno  3 for the grouping. Voila, the group
       clause will reference the wrong TLE (still resno 2)!
   
       Currently I think the correct solution would be to expand the
       targetlist  already  in  the  rewrite  system  and  leave  it
       untouched in the planner. Comments?
   
   
   Jan
   
   --
   
   #======================================================================#
   # It's easier to get forgiveness for being wrong than for being right. #
   # Let's break this rule - forgive me.                                  #
   #======================================== jwieck@debis.com (Jan Wieck) #
   
   
   
   

5. Re: [HACKERS] 6.5 TODO list

   Marc G. Fournier <scrappy@hub.org> — 1999-05-11T17:28:44Z

   On Mon, 10 May 1999, Bruce Momjian wrote:
   
   > > > Vacuum of tables >2 gigs - NOTICE:  Can't truncate multi-segments relation
   > > 
   > > This is definitely a "must fix" item for 6.5, IMHO...
   > 
   > Yes.  It shows we are getting bigger.  Most/all? of my commerical
   > database clients don't have 2-gig tables.
   
   I'm not sure what is involved in fixing this, but...>2gig tables, in the
   past, were iffy to start with.  if we can fix before v6.5, fine...if not,
   my opinion is that isn't a release stopper.  Or, rather...is it something
   that, other then in testing, has affected anyone?
   
   > > > GROUP BY can reference columns not in target list
   > > 
   > > What's wrong with that?
   > 
   > Is that not a problem.  What possible use would GROUP BY on columns not
   > in target list be of use.  Maybe it is.  I remember someone asking for
   > something like this.  I will remove the item unless someone complains.
   > I thought you were the one complaining about it.
   
   Even if we don't remove it, sounds like a feature vs bug fix...
   
   Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
   Systems Administrator @ hub.org 
   primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 
   
   
   

6. Re: [HACKERS] 6.5 TODO list

   Bruce Momjian <maillist@candle.pha.pa.us> — 1999-05-11T18:23:26Z

   > On Mon, 10 May 1999, Bruce Momjian wrote:
   > 
   > > > > Vacuum of tables >2 gigs - NOTICE:  Can't truncate multi-segments relation
   > > > 
   > > > This is definitely a "must fix" item for 6.5, IMHO...
   > > 
   > > Yes.  It shows we are getting bigger.  Most/all? of my commerical
   > > database clients don't have 2-gig tables.
   > 
   > I'm not sure what is involved in fixing this, but...>2gig tables, in the
   > past, were iffy to start with.  if we can fix before v6.5, fine...if not,
   > my opinion is that isn't a release stopper.  Or, rather...is it something
   > that, other then in testing, has affected anyone?
   > 
   > > > > GROUP BY can reference columns not in target list
   > > > 
   > > > What's wrong with that?
   > > 
   > > Is that not a problem.  What possible use would GROUP BY on columns not
   > > in target list be of use.  Maybe it is.  I remember someone asking for
   > > something like this.  I will remove the item unless someone complains.
   > > I thought you were the one complaining about it.
   > 
   > Even if we don't remove it, sounds like a feature vs bug fix...
   
   We are not saying these are show-stoppers, but things that should be
   resolved before final, no?
   
   -- 
     Bruce Momjian                        |  http://www.op.net/~candle
     maillist@candle.pha.pa.us            |  (610) 853-3000
     +  If your life is a hard drive,     |  830 Blythe Avenue
     +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
   
   

7. Re: [HACKERS] 6.5 TODO list

   Christopher Masto <chris@netmonger.net> — 1999-05-11T18:44:53Z

   On Tue, May 11, 1999 at 02:28:44PM -0300, The Hermit Hacker wrote:
   > On Mon, 10 May 1999, Bruce Momjian wrote:
   > 
   > > > > Vacuum of tables >2 gigs - NOTICE:  Can't truncate multi-segments relation
   > > > 
   > > > This is definitely a "must fix" item for 6.5, IMHO...
   > > 
   > > Yes.  It shows we are getting bigger.  Most/all? of my commerical
   > > database clients don't have 2-gig tables.
   > 
   > I'm not sure what is involved in fixing this, but...>2gig tables, in the
   > past, were iffy to start with.  if we can fix before v6.5, fine...if not,
   > my opinion is that isn't a release stopper.  Or, rather...is it something
   > that, other then in testing, has affected anyone?
   
   Hrm.. I've been hanging around on the postgres lists for six months or
   so, but I admit, other than a couple of toy projects, I haven't really
   done much.
   
   However.. recently I have gotten involved in a project that
   is.. well.. huge.  And it involves databases in a big way.  I'd love
   to be able to use Postgres.  I'd love to be able to say at the end
   that _______ (very very large and well-known organization) uses, and
   in fact depends on, FreeBSD, Apache, Perl, and Postgres.  We're a bit
   concerned, though, since the databases will be a _lot_ bigger than
   2GB.  Part of the reason for using free software is that if it does
   blow up, we can help fix it.
   
   Anyway, I guess my point is that there is some incentive here for
   having a postgres that is completely non-iffy when it comes to >2GB
   databases.  Shortly we will be filling the system with test data and I
   will be glad to help out as much as possible (which may not be much in
   the way of code, as I've got my hands rather full right now).
   -- 
   Christopher Masto         Senior Network Monkey      NetMonger Communications
   chris@netmonger.net        info@netmonger.net        http://www.netmonger.net
   
   Free yourself, free your machine, free the daemon -- http://www.freebsd.org/
   
   

8. Re: [HACKERS] 6.5 TODO list

   Marc G. Fournier <scrappy@hub.org> — 1999-05-11T19:14:49Z

   On Tue, 11 May 1999, Bruce Momjian wrote:
   
   > > On Mon, 10 May 1999, Bruce Momjian wrote:
   > > 
   > > > > > Vacuum of tables >2 gigs - NOTICE:  Can't truncate multi-segments relation
   > > > > 
   > > > > This is definitely a "must fix" item for 6.5, IMHO...
   > > > 
   > > > Yes.  It shows we are getting bigger.  Most/all? of my commerical
   > > > database clients don't have 2-gig tables.
   > > 
   > > I'm not sure what is involved in fixing this, but...>2gig tables, in the
   > > past, were iffy to start with.  if we can fix before v6.5, fine...if not,
   > > my opinion is that isn't a release stopper.  Or, rather...is it something
   > > that, other then in testing, has affected anyone?
   > > 
   > > > > > GROUP BY can reference columns not in target list
   > > > > 
   > > > > What's wrong with that?
   > > > 
   > > > Is that not a problem.  What possible use would GROUP BY on columns not
   > > > in target list be of use.  Maybe it is.  I remember someone asking for
   > > > something like this.  I will remove the item unless someone complains.
   > > > I thought you were the one complaining about it.
   > > 
   > > Even if we don't remove it, sounds like a feature vs bug fix...
   > 
   > We are not saying these are show-stoppers, but things that should be
   > resolved before final, no?
   
   But, if htey should be resolved before final, doesn't taht make it a
   show-stopper? *raised eyebrow*
   
   Christopher brings up a point about the >2gig vacuum problem...I'm kinda
   curious as to what sort of time frame we'd be looking at on fixing that
   (1)...what sort of demographic this affects (2) and whether or not this
   could be done as a 'side patch' so that those it does affects would have
   access to it (3) ...
   
   Assuming that 1 is large and 2 small, 3 could/should be released sometime
   after v6.5 is released and provided for those that it affects.
   
   If 1 is small (<1week?), then the point is moot...
   
   
   Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
   Systems Administrator @ hub.org 
   primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org