OK, here is the list.  Please send me changes.  I will post this
periodically.  I realize many are not show-stoppers, but it doesn't hurt
to have everyone see the items.  I will move them to the main TODO list
as we get closer to final.

---------------------------------------------------------------------------


Can not insert/update oid
Default of '' causes crash in some cases
EXPLAIN SELECT 1 UNION SELECT 2 crashes, rewrite system?
shift/reduce conflict in grammar, SELECT ... FOR [UPDATE|CURSOR]
move UNION stuff into rewrite files
CLUSTER failure if vacuum has not been performed in a while
Do we want pg_dump -z to be the default?
Improve Subplan list handling
Allow Subplans to use efficient joins(hash, merge) with upper variable
Improve NULL parameter passing into functions
Table with an element of type inet, will show "0.0.0.0/0" as "00/0"
When creating a table with either type inet or type cidr as a primary,unique
   key, the "198.68.123.0/24" and "198.68.123.0/27" are considered equal
store binary-compatible type information in the system somewhere 
create table "AA" ( x int4 , y serial ); insert into "AA" (x) values (1); fails
add ability to add comments to system tables using table/colname combination
improve handling of oids >2 gigs by making unsigned int usage consistent
	oidin, oidout, pg_atoi
Allow ESCAPE '\' at the end of LIKE for ANSI compliance, or rewrite the
	LIKE handling by rewriting the user string with the supplied ESCAPE
Vacuum of tables >2 gigs - NOTICE:  Can't truncate multi-segments relation
Make Serial its own type?
Add support for & operator
Fix leak for expressions?, aggregates?
Remove ERROR:  check_primary_key: even number of arguments should be specified
make oid use oidin/oidout not int4in/int4out in pg_type.h
missing optimizer selectivities for date, etc.
process const=const parts of OR clause in separate pass
improve LIMIT processing by using index to limit rows processed
int8 indexing needs work?
Allow "col AS name" to use name in WHERE clause?  Is this ANSI? 
	Works in GROUP BY
Update reltuples from COPY command
hash on int2/char only looks a first bytes, and big-endian machines hash poorly
Docs for INSERT INTO/SELECT INTO fix
Some CASE() statements involving two tables crash
Trigger regression test fails
CREATE FUNCTION fails
nodeResults.c and parse_clause.c give compiler warnings
Markup sql.sgml, Stefan's intro to SQL
Markup cvs.sgml, cvs and cvsup howto
Add figures to sql.sgml and arch-dev.sgml, both from Stefan
Include Jose's date/time history in User's Guide (neat!)
Generate Admin, User, Programmer hardcopy postscript
move LIKE index optimization handling to the optimizer?
DROP TABLE/RENAME TABLE doesn't remove extended files, *.1, *.2
CREATE VIEW ignores DISTINCT?
ORDER BY mixed with DISTINCT causes duplicates
select * from test where test in (select * from test) fails with strange error
MVCC locking, deadlock, priorities?
Make sure pg_internal.init generation can't cause unreliability
SELECT ... WHERE col ~ '(foo|bar)' works, but CHECK on table always fails
GROUP BY expression?
CREATE TABLE t1 (a int4, b int4); CREATE VIEW v1 AS SELECT b, count(b)
	FROM t1 GROUP BY b; SELECT count FROM v1; fails
CREATE INDEX zman_index ON test (date_trunc( 'day', zman ) datetime_ops) fails
	index can't store constant parameters, allow SQL function indexes?
pg_dump of groups problem
select 1; select 2 fails when sent not via psql, semicolon problem
create operator *= (leftarg=_varchar, rightarg=varchar, 
	procedure=array_varchareq); fails, varchar is reserved word, quotes work
have hashjoins use portals, not fixed-size memory
pg_dump -o -D does not work, and can not work currently, generate error?
problem with ALTER TABLE on inherited
pg_dump does not preserver NUMERIC precision, psql \d should show precision
DROP TABLE leaves INDEX file descriptor open
pg_interal.init, check for reliability on rebuild
ALTER TABLE ADD COLUMN to inherited table put column in wrong place
dumping out sequences should not be counted in pg_dump display
GROUP BY can reference columns not in target list
SELECT name, value FROM t1 as touter WHERE (value/(SELECT AVG(value) 
	FROM t1 WHERE name = touter.name)) > 0.75; fails
resno's, sublevelsup corrupt when reaching rewrite system
crypt_loadpwdfile() is mixing and (mis)matching memory allocation
  protocols, trying to use pfree() to release pwd_cache vector from realloc()
3 = sum(x) in rewrite system is a problem


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

Mon, 10 May 1999 15:32:11 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
6.5 TODO list
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

Mon, 10 May 1999 17:49:01 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] 6.5 TODO list 
> 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

Mon, 10 May 1999 18:01:18 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.5 TODO list
> int8 indexing needs work?

afaik it is OK now. It worked with my limited testing anyway, and the
problems I thought I saw earlier were much more fundamental.

> Docs for INSERT INTO/SELECT INTO fix

Done. You did it, I did it (but hadn't committed yet), gotta be done
by now...

> Some CASE() statements involving two tables crash

Done. Tom Lane reenabled the regression test queries which illustrated
this.

New items:

Write up CASE(), COALESCE(), IFNULL()
Add Vadim's isolation level syntax to gram.y, preproc.y. (Currently
done with string parsing outside of yacc.)

I have some other stuff on my list for the docs, but will send along a
list of what's left in a few days.

                     - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Tue, 11 May 1999 03:47:33 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] 6.5 TODO list

Done.


> > int8 indexing needs work?
> 
> afaik it is OK now. It worked with my limited testing anyway, and the
> problems I thought I saw earlier were much more fundamental.
> 
> > Docs for INSERT INTO/SELECT INTO fix
> 
> Done. You did it, I did it (but hadn't committed yet), gotta be done
> by now...
> 
> > Some CASE() statements involving two tables crash
> 
> Done. Tom Lane reenabled the regression test queries which illustrated
> this.
> 
> New items:
> 
> Write up CASE(), COALESCE(), IFNULL()
> Add Vadim's isolation level syntax to gram.y, preproc.y. (Currently
> done with string parsing outside of yacc.)
> 
> I have some other stuff on my list for the docs, but will send along a
> list of what's left in a few days.
> 
>                      - Thomas
> 
> -- 
> Thomas Lockhart				lockhart@alumni.caltech.edu
> South Pasadena, California
> 


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

Tue, 11 May 1999 00:30:53 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.5 TODO list
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) #



Tue, 11 May 1999 15:20:32 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] 6.5 TODO list
Bruce Momjian <maillist@candle.pha.pa.us> writes:
>>>> 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.

I already did it to fix EXPLAIN of unions.

			regards, tom lane

Tue, 11 May 1999 09:54:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] 6.5 TODO list 
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> >>>> 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.
> 
> I already did it to fix EXPLAIN of unions.

I see now.  Thanks.  

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

Tue, 11 May 1999 11:09:26 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.5 TODO list
>
> 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) #



Tue, 11 May 1999 18:01:54 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] 6.5 TODO list
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 


Tue, 11 May 1999 14:28:44 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] 6.5 TODO list
jwieck@debis.com (Jan Wieck) writes:
>     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.

Right --- I imagine that doing that in the planner is a hangover from
ancient history before the rewriter existed at all.  It does need to
be done, but if you think it'd be better done in the rewriter that's
fine with me.

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

Ugh.  I thought that was a pretty unrobust way of doing things :-(
If you change the lines in planner.c:

        /* Is it safe to use just resno to match tlist and glist items?? */
        if (grpcl->entry->resdom->resno == resdom->resno)

to use equal() on the expr's of the two TLEs, does it work any better?

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

OK with me, but possibly rather a major change to be making this late
in beta...

			regards, tom lane

Tue, 11 May 1999 14:20:21 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] 6.5 TODO list 
> 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

Tue, 11 May 1999 14:23:26 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.5 TODO list
> Ugh.  I thought that was a pretty unrobust way of doing things :-(
> If you change the lines in planner.c:
> 
>         /* Is it safe to use just resno to match tlist and glist items?? */
>         if (grpcl->entry->resdom->resno == resdom->resno)
> 
> to use equal() on the expr's of the two TLEs, does it work any better?
> 
> >     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?
> 
> OK with me, but possibly rather a major change to be making this late
> in beta...

But it is already broken.  Can't get worse, can it?

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

Tue, 11 May 1999 14:37:41 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.5 TODO list
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/

Tue, 11 May 1999 14:44:53 -0400
Christopher Masto <chris@netmonger.net>
Re: [HACKERS] 6.5 TODO list
Christopher Masto <chris@netmonger.net> writes:
> 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).

Great, we need some people keeping us honest.  I don't think any of the
core developers have >2Gb databases (I sure don't).

I think there are two known issues right now: the VACUUM one and
something about DROP TABLE neglecting to delete the additional files
for a multi-segment table.  To my mind the VACUUM problem is a "must
fix" because you can't really live without VACUUM, especially not on
a huge database.  The DROP problem is less severe since you could
clean up by hand if necessary (not that it shouldn't get fixed of
course, but we have more critical issues to deal with for 6.5).

In theory, you can test the behavior for >2Gb tables without actually
needing to expend much time in creating huge tables: just reduce the
value of RELSEG_SIZE in include/config.h to some more convenient value,
like a couple meg, so that you can get a segmented table without so
much effort.  This doesn't speak to performance issues of course,
but at least you can check for showstopper bugs.

(BTW, has anyone been thinking about increasing OID to more than 4
bytes?  Folks are going to start hitting the 4G-tuples-per-database
mark pretty soon.)

			regards, tom lane

Tue, 11 May 1999 15:06:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] 6.5 TODO list 
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 


Tue, 11 May 1999 16:14:49 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] 6.5 TODO list
> (BTW, has anyone been thinking about increasing OID to more than 4
> bytes?  Folks are going to start hitting the 4G-tuples-per-database
> mark pretty soon.)

I have started to think about making our oid type more reliable as an
unsigned int.  Internally, it is unsigned int, but there are some issues
where this is not portably hadled.  It would double the current oid
maximum.

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

Tue, 11 May 1999 15:42:26 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.5 TODO list
On Tue, 11 May 1999, Tom Lane wrote:
> I think there are two known issues right now: the VACUUM one and
> something about DROP TABLE neglecting to delete the additional files
> for a multi-segment table.  To my mind the VACUUM problem is a "must
> fix" because you can't really live without VACUUM, especially not on
> a huge database.  The DROP problem is less severe since you could
> clean up by hand if necessary (not that it shouldn't get fixed of
> course, but we have more critical issues to deal with for 6.5).

I have been looking at the code for dropping the table.  The code in
mdunlink() seems to be correct, and *should* work.  Of course it don't, so
I'll do some more testing tonight and hopefully I can figure out why it
doesn't work.

As far as the VACUUM problem, I still haven't seen that.  I have a couple
~3GB tables, with one growing to 5-6GB in the next month or so.  VACUUM
runs just fine on both.

I just got to thinking, what about indexes > 2GB?  With my 3GB table one
of the index is 540MB.  Both with growth I might get there.  Does that
work and does it use RELSEG_SIZE?
I would guess that the same function(mdunlink, mdcreate, etc) is called
for all DROPs and CREATEs(through DestroyStmt, CreateStmt)?  I don't
understand postgres good enough to answer that for sure(but it does make
sense).

Ole Gjerde


Tue, 11 May 1999 15:11:40 -0500 (CDT)
gjerde@icebox.org
Re: [HACKERS] 6.5 TODO list 
Hi!
I found a bug, with $SUBJ - i mean ... so look at this:
t=> begin;
BEGIN
t=> select nextval('some_seq');
nextval
-------
      4
(1 row)
t=> rollback;
ROLLBACK
t=> select nextval('some_seq');
nextval
-------
      5 <<<< five! and NOT 4 again, but 4 expected i think
(1 row)

Or seqences are special case in transations?

--
  Neko the Servant of Crash
  neko@(kornel.szif.hu|kva.hu)   http://lsc.kva.hu


Tue, 11 May 1999 21:00:20 +0000 (/etc/localtime)
Vazsonyi Peter <neko@sp1.szif.hu>
sequences vs. transactions
gjerde@icebox.org writes:
> I have been looking at the code for dropping the table.  The code in
> mdunlink() seems to be correct, and *should* work.  Of course it don't, so
> I'll do some more testing tonight and hopefully I can figure out why it
> doesn't work.

Actually it looks a little peculiar to me.  The FileUnlink() routine in
fd.c is defined to delete (unlink) the physical file after closing the
virtual file descriptor for it.  If a VFD is being held for each segment
of the table then the calls to FileNameUnlink ought to be unnecessary,
not to mention possible causes of trouble on NFS disks since there might
still be open file descriptors for the files.

> I just got to thinking, what about indexes > 2GB?  With my 3GB table one
> of the index is 540MB.  Both with growth I might get there.  Does that
> work and does it use RELSEG_SIZE?

index_destroy in backend/catalog/index.c looks to be coping with only
one segment file ... not sure why it doesn't go through md.c for this.

Good luck tracking it all down...

			regards, tom lane

Tue, 11 May 1999 19:44:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] 6.5 TODO list 
> Christopher Masto <chris@netmonger.net> writes:
> > 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).
> 
> Great, we need some people keeping us honest.  I don't think any of the
> core developers have >2Gb databases (I sure don't).

I have one, but it's not for production, just a test data.

> I think there are two known issues right now: the VACUUM one and
> something about DROP TABLE neglecting to delete the additional files
> for a multi-segment table.  To my mind the VACUUM problem is a "must
> fix" because you can't really live without VACUUM, especially not on
> a huge database.  The DROP problem is less severe since you could
> clean up by hand if necessary (not that it shouldn't get fixed of
> course, but we have more critical issues to deal with for 6.5).

I have to admit that >2GB support is one of the most important issues
but not so sure if it's a show stopper for 6.5.

o even if it's solved, still other many issues for huge databases
still remain:
	- 4G-tuples-per-database problem (as you said)
	- index files cannot extend >2GB
	- vacuuming a huge table will take unacceptable long time (I
	have heard vacuum speeds up for 6.5, but I have not tried it
	yet for a big table. so this is just a my guess)

o it will take long time to solve all of them.
---
Tatsuo Ishii

Wed, 12 May 1999 09:57:54 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: [HACKERS] 6.5 TODO list 
> Hi!
> I found a bug, with $SUBJ - i mean ... so look at this:
> t=> begin;
> BEGIN
> t=> select nextval('some_seq');
> nextval
> -------
>       4
> (1 row)
> t=> rollback;
> ROLLBACK
> t=> select nextval('some_seq');
> nextval
> -------
>       5 <<<< five! and NOT 4 again, but 4 expected i think
> (1 row)
> 
> Or seqences are special case in transations?

Sequence numbers are not re-used like normal transactions.  The reason
for this is that we would have to lock the sequence table for the entire
duration of the transaction until it was committed if we wanted to do
that.


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

Tue, 11 May 1999 22:05:58 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: sequences vs. transactions
> > I just got to thinking, what about indexes > 2GB?  With my 3GB table one
> > of the index is 540MB.  Both with growth I might get there.  Does that
> > work and does it use RELSEG_SIZE?
> 
> index_destroy in backend/catalog/index.c looks to be coping with only
> one segment file ... not sure why it doesn't go through md.c for this.

Added to Open Items:

	Multi-segment indexes?

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

Tue, 11 May 1999 22:11:22 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.5 TODO list
> o even if it's solved, still other many issues for huge databases
> still remain:
> 	- 4G-tuples-per-database problem (as you said)

This will be hard.

> 	- index files cannot extend >2GB
> 	- vacuuming a huge table will take unacceptable long time (I
> 	have heard vacuum speeds up for 6.5, but I have not tried it
> 	yet for a big table. so this is just a my guess)
> 
> o it will take long time to solve all of them.

Vacuum is already faster.  Vacuum across segments, multi-segment
indexes may be possible for 6.5.

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

Tue, 11 May 1999 22:15:13 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.5 TODO list
Bruce Momjian wrote:

>
> > Ugh.  I thought that was a pretty unrobust way of doing things :-(
> > If you change the lines in planner.c:
> >
> >         /* Is it safe to use just resno to match tlist and glist items?? */
> >         if (grpcl->entry->resdom->resno == resdom->resno)
> >
> > to use equal() on the expr's of the two TLEs, does it work any better?
> >
> > >     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?
> >
> > OK with me, but possibly rather a major change to be making this late
> > in beta...
>
> But it is already broken.  Can't get worse, can it?

    And  just  using  equal()  wouldn't  be enough. I tested what
    happens if the rewriter add's junk TLE's  for  group  clauses
    coming  from  a  view.  If the query is a SELECT, anything is
    fine, but if it is an INSERT ...  SELECT then the added TLE's
    needed    by    the    group    clauses   get   lost   during
    preprocess_targetlist().


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) #



Wed, 12 May 1999 12:26:50 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] 6.5 TODO list

On Tue, 11 May 1999, Bruce Momjian wrote:
> Sequence numbers are not re-used like normal transactions.  The reason
> for this is that we would have to lock the sequence table for the entire
> duration of the transaction until it was committed if we wanted to do
> that.
> 
> 
> -- 
>   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
> 
Correct. ;-/. Thanx.


Sun, 16 May 1999 15:11:55 +0000 (/etc/localtime)
Vazsonyi Peter <neko@sp1.szif.hu>
Re: [HACKERS] Re: sequences vs. transactions