Обсуждение: 6.5 TODO list
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 consistentoidin, oidout, pg_atoi Allow ESCAPE '\' at the end of LIKE for ANSI compliance, or rewrite theLIKE handling by rewriting the user string with thesupplied 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) failsindex can't store constant parameters, allowSQL 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, quoteswork 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 vectorfrom 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, Pennsylvania19026
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
> 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, Pennsylvania19026
> 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
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, Pennsylvania19026
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) #
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
> 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, Pennsylvania19026
>
> 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) #
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
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
> 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, Pennsylvania19026
> 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, Pennsylvania19026
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/
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
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
> (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, Pennsylvania19026
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
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
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
> 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 willtake unacceptable long time (Ihave heard vacuum speeds up for 6.5, but I have not tried ityet for a big table. so thisis just a my guess) o it will take long time to solve all of them. --- Tatsuo Ishii
> 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,
Pennsylvania19026
> > 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, Pennsylvania19026
> 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, Pennsylvania19026
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) #
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.