Thread: 7.1 features list
Here is the list of features in 7.1. -- Bruce Momjian | http://candle.pha.pa.us pgman@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 Release 7.1 This release focuses on removing limitations that have existed in the PostgreSQL code for many years. Major changes in this release: Write-ahead Log(WAL) - To maintain database consistency in case of an operating system crash, previous releases of PostgreSQL have forced all data modifications to disk before each transaction commit. With WAL, only one log file must be flushed to disk, greatly improving performance. If you have been using -F in previous releases to disable disk flushes, you may want to consider discontinuing its use. TOAST - Previous releases had an 8k (or 32k) row length limit. This limit made storage of long text fields difficult. With TOAST, long rows of any length can be stored with good performance. Outer Joins - We now support outer joins. The UNION/NOT IN workaround for outer joins is no longer required. We use the SQL92 outer join syntax. Function Manager - The previous C function manager did not handle NULLs properly, nor did it support 64-bit CPU's. The new function manager does. You can continue using your old custom functions, but you may want to rewrite them in the future to use the new function manager call interface. Complex Queries - A large number of complex queries that were unsupported in previous releases now work. Many combinations of views, aggregates, UNION, LIMIT, cursors, subqueries, and inherited tables now work properly. Inherited tables are now accessed by default. Subqueries in FROM are now supported. Migration to v7.0 A dump/restore using pg_dump is required for those wishing to migrate data from any previous release. Last updated from CVS logs: 2000-12-11 Bug Fixes --------- Many multi-byte/Unicode/locale fixes (Tatsuo and others) More reliable ALTER TABLE RENAME (Tom) Kerberos V fixes (David Wragg) Fix for INSERT INTO...SELECT where targetlist has subqueries (Tom) Prompt username/password on standard error (Bruce) Large objects inv_read/inv_write fixes (Tom) Fixes for to_char(), to_date(), to_ascii(), and to_timestamp() (Karel, Daniel Baldoni) Prevent query expressions from leaking memory (Tom) Allow UPDATE of arrays elements (Tom) Wake up lock waiters during cancel (Hiroshi) Fix rare cursor crash when using hash join (Tom) Fix for DROP TABLE/INDEX in rolled-back transaction (Hiroshi) Fix psql crash from \l+ if MULTIBYTE enabled (Peter E) Fix truncation of rule names during CREATE VIEW (Ross Reedstrom) Fix PL/perl (Alex Kapranoff) Disallow LOCK on views (Mark Holloman) Disallow INSERT/UPDATE/DELETE on views (Mark Holloman) Disallow DROP RULE, CREATE INDEX, TRUNCATE on views (Mark Holloman) Allow PL/pgSQL accept non-ASCII identifiers (Tatsuo) Allow views to proper handle GROUP BY, aggregates, DISTINCT (Tom) Fix rare failure with TRUNCATE command (Tom) Allow UNION/INTERSECT/EXCEPT to be used with ALL, subqueries, views, DISTINCT, ORDER BY, SELECT...INTO (Tom) Fix parser failures during aborted transactions (Tom) Allow temporary relations to properly clean up indexes (Bruce) Fix VACUUM problem with moving rows in same page (Tom) Modify pg_dump so it dumps only user-defined items, not system-defined (Philip) Allow LIMIT in VIEW (Tom) Require cursor FETCH to honor LIMIT (Tom) Allow PRIMARY/FOREIGN Key definitions on inherited columns (Stephan) Allow ORDER BY, LIMIT in sub-selects (Tom) Allow UNION in CREATE RULE (Tom) Make DROP TABLE rollback-able (Tom) Store initdb collation in pg_control so collation cannot be changed (Tom) Fix INSERT...SELECT with rules (Tom) Fix FOR UPDATE inside views and subselects (Tom) Fix OVERLAPS operators conform to SQL92 spec regarding NULLs (Tom) Fix lpad() and rpad() to handle length less than input string (Tom) Enhancements ------------ Add OUTER JOINs (Tom) Function manager overhaul (Tom) Allow ALTER TABLE RENAME on indexes(Tom) Improve CLUSTER(Tom) Improve ps status display for more platforms(Marc) Improve CREATE FUNCTION failure message(Ross) JDBC improvements (Peter, Travis Bauer, Christopher Cain, William Webber, Gunnar) Grand Unified Configuration scheme/GUC. Many options can now be set in data/postgresql.conf, postmaster/postgres flags, or SET commands (Peter E) Improved handling of file descriptor cache (Tom) New warning code about auto-created table alias entries (Bruce) Overhaul initdb process (Tom, Peter E) Overhaul of inherited tables; inherited tables now accessed by default; new ONLY keyword prevents it (Chris Bitmead, Tom) ODBC cleanups/improvements (Nick Gorham, Stephan Szabo, Zoltan Kovacs, Michael Fork) Allow renaming of temp tables (Tom) Overhaul memory manager contexts (Tom) pg_dump uses CREATE USER or CREATE GROUP rather using COPY (Peter E) Overhaul pg_dump (Philip Warner) Allow pg_hba.conf secondary password file to specify username (Peter E) Allow TEMPORARY or TEMP keyword when creating temporary tables (Bruce) New memory leak checker (Karel) New SET SESSION CHARACTERISTICS and SET DefaultXactIsoLevel (Thomas, Peter E) Allow nested block comments (Thomas) Add WITHOUT TIME ZONE type qualifier (Thomas) New ALTER TABLE ADD CONSTRAINT (Stephan) Use NUMERIC accumulators for INTEGER aggregates (Tom) Overhaul aggregate code (Tom) New VARIANCE and STDDEV() aggregates Improve dependency ordering of pg_dump (Philip) New pg_restore command (Philip) New pg_dump tar output option (Philip) New pg_dump of large objects (Philip) New ESCAPE option to LIKE (Thomas) New case-insensitive LIKE - ILIKE (Thomas) Allow functional indexes to use binary-compatible type (Tom) Allow SQL functions to be used in more contexts (Tom) New pg_config utility (Peter E) New PL/pgSQL EXECUTE command which allows dynamic SQL and utility statements (Jan) New PL/pgSQL GET DIAGNOSTICS statement for SPI value access (Jan) New quote_identifiers() and quote_literal() functions (Jan) New ALTER TABLE table OWNER TO user command (Mark Holloman) Allow subselects in FROM, i.e. FROM (SELECT ...) [AS] alias (Tom) Update PyGreSQL to version 3.1 (D'Arcy) Store tables as files named by OID (Vadim) New SQL function setval(seq,val,bool) for use in pg_dump (Philip) New pg_service.conf file (Mario Weilguni) Require DROP VIEW to remove views, no DROP TABLE (Mark) Allow DROP VIEW view1, view2 (Mark) Allow multiple objects in DROP INDEX, DROP RULE, and DROP TYPE (Tom) Allow automatic conversion to Unicode (Tatsuo) New /contrib/pgcrypto hashing functions (Marko Kreen) New pg_dumpall --accounts-only option (Peter E) New CHECKPOINT command for WAL which creates new WAL log file (Vadim) New AT TIME ZONE syntax (Thomas) Allow location of Unix domain socket to be configurable (David J. MacKenzie) Allow postmaster to listen on a specific IP address (David J. MacKenzie) Allow socket path name to be specified in hostname by using leading slash (David J. MacKenzie) Allow CREATE DATABASE to specify template database (Tom) New template0 database that contains no user additions(Tom) Types ----- Fix INET/CIDR type ordering and add new functions (Tom) Make OID behave as an unsigned type (Tom) Allow BIGINT as synonym for INT8 (Peter E) New int2 and int8 comparison operators (Tom) New BIT and BIT VARYING types (Adriaan Joubert, Tom) CHAR() no longer faster than VARCHAR() because of TOAST (Tom) Performance ----------- Write-Ahead Log (WAL) to provide crash recovery with less performance overhead (Vadim) ANALYZE stage of VACUUM no longer exclusively locks table (Bruce) Reduced file seeks (Denis Perchine) Improve BTREE code for duplicate keys (Tom) Store all large objects in a single operating system file (Denis Perchine, Tom) Improve memory allocation performance (Karel, Tom) Source Code ----------- New function manager call conventions (Tom) SGI portability fixes (David Kaelbling) New configure --enable-syslog option (Marc) New BSDI README (Bruce) configure script moved to top level, not /src (Peter E) Makefile/configuration/compilation cleanups (Peter E) New configure --with-python option (Peter E) Solaris cleanups (Peter E) Overhaul /contrib Makefiles (Karel) New OpenSSL configuration option (Magnus, Peter E) AIX fixes (Andreas) New heap_open(), heap_openr() API (Tom) Remove colon and semi-colon operators (Thomas) New pg_class.relkind value for views (Mark Holloman) Rename ichar() to chr() (Karel) New documentation for btrim(), ascii(), chr(), repeat() (Karel) Fixes for NT/Cygwin (Pete Forman) AIX port fixes (Andreas) New BeOS port (David Reid, Cyril Velter) Add proofreader's changes to docs (Addison-Wesley, Bruce) New Alpha spinlock code (Adriaan Joubert, Compaq) Unixware port overhaul (Peter E) New Darwin/Mac OSX port (Bruce Hartzler) New FreeBSD Alpha port (Alfred) Overhaul shared memory segments (Tom) Add IBM S/390 support (Neale Ferguson)
We're working hardly on bugfixes for GiST (I've posted patch for 7.0.3)
and probably could finish in 1-2 weeks.
regards,
Oleg
On Sat, 16 Dec 2000, Bruce Momjian wrote:
> Date: Sat, 16 Dec 2000 15:16:22 -0500 (EST)
> From: Bruce Momjian <pgman@candle.pha.pa.us>
> To: PostgreSQL-development <pgsql-hackers@postgresql.org>,
> PostgreSQL-documentation <pgsql-docs@postgresql.org>
> Subject: [HACKERS] 7.1 features list
>
> Here is the list of features in 7.1.
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@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
>
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
At 3:16 PM -0500 12/16/00, Bruce Momjian wrote: >Here is the list of features in 7.1. >New Darwin/Mac OSX port (Bruce Hartzler) Not to be a snob, but I probably did 80% of this. (BTW- tons of stuff at www.postgresql.org is busted. Searching mailing list archives for example.) -pmb -- "Every time you provide an option, you're asking the user to make a decision. That means they will have to think about something and decide about it. It's not necessarily a bad thing, but, in general, you should always try to minimize the number of decisions that people have to make." http://joel.editthispage.com/stories/storyReader$51
Peter Bierman <bierman@apple.com> writes:
> At 3:16 PM -0500 12/16/00, Bruce Momjian wrote:
>> Here is the list of features in 7.1.
>> New Darwin/Mac OSX port (Bruce Hartzler)
> Not to be a snob, but I probably did 80% of this.
Bruce had submitted an earlier patch, but IIRC Peter's version was the
one that got applied. (Or was Peter doing mopup work on Bruce's first
cut? I forget.) At the very least Peter should get 50% credit...
regards, tom lane
Hi, I checked 7.1 feature list and didn't find any mention about GiST but there are changes in GiST code. Who is a maintainer of GiST code ? We have several problems with GiSt and would like to communicate with somebody who understand these code. Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes:
> I checked 7.1 feature list and didn't find any mention about GiST
> but there are changes in GiST code. Who is a maintainer of GiST code ?
You are ;-). If you expect to find someone who understands GiST better
than you, you're probably out of luck.
I recall having made a number of changes that applied to all of the
index access methods, including GiST --- but I was just changing
similar code in all the methods. I don't claim to know anything
about GiST in particular.
regards, tom lane
On Sat, 16 Dec 2000, Peter Bierman wrote: > At 3:16 PM -0500 12/16/00, Bruce Momjian wrote: > >Here is the list of features in 7.1. > >New Darwin/Mac OSX port (Bruce Hartzler) > > Not to be a snob, but I probably did 80% of this. > > (BTW- tons of stuff at www.postgresql.org is busted. Searching mailing > list archives for example.) Please provide URLs where you are trying to search ... we did extensive work over the past few weeks to speed up searching, and I tend randomly to make sure things are still running fine, and haven't had any problems with either speed or broken links ... its possible its one of the mirror sites?
At 9:43 PM -0400 12/17/00, The Hermit Hacker wrote: >On Sat, 16 Dec 2000, Peter Bierman wrote: > >> (BTW- tons of stuff at www.postgresql.org is busted. Searching mailing >> list archives for example.) > >Please provide URLs where you are trying to search ... we did extensive >work over the past few weeks to speed up searching, and I tend randomly to >make sure things are still running fine, and haven't had any problems with >either speed or broken links ... Clicking on the "search" pic/link at http://www.postgresql.org/ always brought up a dialog (IE5-Mac) that said "the attemptto load http://www.postgresql.org/search.mpl" failed. But just now I pasted that URL into the location, and it loads fine. And now the pic/link works fine too. I have no ideawhat's with that. Just now I went to http://www.postgresql.org/mhonarc/pgsql-hackers/ typed 'foo' in the search field, and I get a dialog a few seconds later: "The attempt to load:"Accessing URL: http://www.postgresql.org/mhonarc/pgsql-hackers/search.mpl?<stuff>" (runs offscreen). Maybe it's some javascript that's trying to load a "still loading" page, and has a bogus URL with some explanatory text prepended?(Note the URL:"Accessing URL:http...") I loaded "http://www.postgresql.org/mhonarc/pgsql-hackers/search.mpl?" by hand, and then went back and tried a search again,and now it works. Dunno what's going on here. Since it never worked for me, I never tried loading the URL by hand. Obviously it's more complicatedthan the outright broken link that I thought it was, sorry about that. -pmb -- "Every time you provide an option, you're asking the user to make a decision. That means they will have to think about something and decide about it. It's not necessarily a bad thing, but, in general, you should always try to minimize the number of decisions that people have to make." http://joel.editthispage.com/stories/storyReader$51
> Peter Bierman <bierman@apple.com> writes:
> > At 3:16 PM -0500 12/16/00, Bruce Momjian wrote:
> >> Here is the list of features in 7.1.
> >> New Darwin/Mac OSX port (Bruce Hartzler)
>
> > Not to be a snob, but I probably did 80% of this.
>
> Bruce had submitted an earlier patch, but IIRC Peter's version was the
> one that got applied. (Or was Peter doing mopup work on Bruce's first
> cut? I forget.) At the very least Peter should get 50% credit...
Modified entry. Thanks for the correction:
New Darwin/Mac OSX port (Peter Bierman, Bruce Hartzler)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@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
Yes, I will take a pass over the logs before final. As noted in the file, the list is accurate as of December 11. > We're working hardly on bugfixes for GiST (I've posted patch for 7.0.3) > and probably could finish in 1-2 weeks. > > regards, > Oleg > On Sat, 16 Dec 2000, Bruce Momjian wrote: > > > Date: Sat, 16 Dec 2000 15:16:22 -0500 (EST) > > From: Bruce Momjian <pgman@candle.pha.pa.us> > > To: PostgreSQL-development <pgsql-hackers@postgresql.org>, > > PostgreSQL-documentation <pgsql-docs@postgresql.org> > > Subject: [HACKERS] 7.1 features list > > > > Here is the list of features in 7.1. > > -- > > Bruce Momjian | http://candle.pha.pa.us > > pgman@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 > > > > _____________________________________________________________ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > -- Bruce Momjian | http://candle.pha.pa.us pgman@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
> Oleg Bartunov <oleg@sai.msu.su> writes: > > I checked 7.1 feature list and didn't find any mention about GiST > > but there are changes in GiST code. Who is a maintainer of GiST code ? > > You are ;-). If you expect to find someone who understands GiST better > than you, you're probably out of luck. > > I recall having made a number of changes that applied to all of the > index access methods, including GiST --- but I was just changing > similar code in all the methods. I don't claim to know anything > about GiST in particular. I know I met someone who said they invented Gist. Tom, was that at the Database Summit? I can't think of that person's name now. I think there are some papers at Berkeley or a web site that goes into it in detail. -- Bruce Momjian | http://candle.pha.pa.us pgman@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 <pgman@candle.pha.pa.us> writes: > I think > there are some papers at Berkeley or a web site that goes into it in > detail. I imagine there's some GiST stuff at the Berkeley papers repository http://s2k-ftp.CS.Berkeley.EDU:8000/postgres/papers/ but I'd be surprised if it's more than an overview... regards, tom lane
Peter Bierman <bierman@apple.com> writes: > Just now I went to http://www.postgresql.org/mhonarc/pgsql-hackers/ > typed 'foo' in the search field, and I get a dialog a few seconds later: > "The attempt to load:"Accessing URL: http://www.postgresql.org/mhonarc/pgsql-hackers/search.mpl?<stuff>" (runs offscreen). Odd, the same experiment seems to work fine for me. Maybe a browser dependency? I'm using Netscape 4.75 on HPUX ... > Maybe it's some javascript I don't see any javascript on the loaded page. regards, tom lane
On Mon, 18 Dec 2000, Tom Lane wrote: > Peter Bierman <bierman@apple.com> writes: > > Just now I went to http://www.postgresql.org/mhonarc/pgsql-hackers/ > > > typed 'foo' in the search field, and I get a dialog a few seconds later: > > > "The attempt to load:"Accessing URL: http://www.postgresql.org/mhonarc/pgsql-hackers/search.mpl?<stuff>" (runs offscreen). > > Odd, the same experiment seems to work fine for me. Maybe a browser > dependency? I'm using Netscape 4.75 on HPUX ... Just went to the above URL using IE 5.5, types in 'foo' and it came back with 909 matches found ... > > Maybe it's some javascript > > I don't see any javascript on the loaded page. none used that I'm aware of either ... > > regards, tom lane > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
OK, now I understand the situation. Another question: Who is a maintainer of Rtree code ? We have a problem with handling NULL values in GiST. Any thought how NULL values are handle in Rtree. regards, Oleg On Sun, 17 Dec 2000, Bruce Momjian wrote: > Date: Sun, 17 Dec 2000 23:23:32 -0500 (EST) > From: Bruce Momjian <pgman@candle.pha.pa.us> > To: Tom Lane <tgl@sss.pgh.pa.us> > Cc: Oleg Bartunov <oleg@sai.msu.su>, > PostgreSQL-development <pgsql-hackers@postgresql.org> > Subject: Re: [HACKERS] Who is a maintainer of GiST code ? > > > Oleg Bartunov <oleg@sai.msu.su> writes: > > > I checked 7.1 feature list and didn't find any mention about GiST > > > but there are changes in GiST code. Who is a maintainer of GiST code ? > > > > You are ;-). If you expect to find someone who understands GiST better > > than you, you're probably out of luck. > > > > I recall having made a number of changes that applied to all of the > > index access methods, including GiST --- but I was just changing > > similar code in all the methods. I don't claim to know anything > > about GiST in particular. > > I know I met someone who said they invented Gist. Tom, was that at the > Database Summit? I can't think of that person's name now. I think > there are some papers at Berkeley or a web site that goes into it in > detail. > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@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 > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes:
> We have a problem with
> handling NULL values in GiST. Any thought how NULL values
> are handle in Rtree.
AFAIR, none of the index access methods except btree handle NULLs at
all --- they just ignore NULL values and don't store them in the index.
Feel free to improve on that ;-). The physical representation of index
tuples can handle NULLs, the problem is teaching the index logic where
they should go in the index.
regards, tom lane
Tom Lane wrote: > > Oleg Bartunov <oleg@sai.msu.su> writes: > > We have a problem with > > handling NULL values in GiST. Any thought how NULL values > > are handle in Rtree. > > AFAIR, none of the index access methods except btree handle NULLs at > all --- they just ignore NULL values and don't store them in the index. > Feel free to improve on that ;-). The physical representation of index > tuples can handle NULLs, the problem is teaching the index logic where > they should go in the index. > > regards, tom lane and I can't see why btree stores them (as it seems to do judging by the index file size) - at least it does not use it for searching for "IS NULL" --8<--------8<--------8<--------8<--------8<--------8<--------8<--------8<------ hannu=# explain select * from nulltest where i is null; NOTICE: QUERY PLAN: Seq Scan on nulltest (cost=0.00..293.80 rows=5461 width=8) EXPLAIN hannu=# explain select * from nulltest where i =1; NOTICE: QUERY PLAN: Index Scan using nulltest_i_ndx on nulltest (cost=0.00..96.95 rows=164 width=8) --8<--------8<--------8<--------8<--------8<--------8<--------8<--------8<------ nulltest is a 16k record table with numbers 1 to 16384 in field i If it just ignored them we would have a nice way to fake partial indexes - just define a function that returns field value or null and then index on that ;) ----------- Hannu
Hannu Krosing <hannu@tm.ee> writes:
> and I can't see why btree stores them (as it seems to do judging by the
> index file size) - at least it does not use it for searching for "IS
> NULL"
That's another thing that needs improvement ;-). Seems to me it should
be able to do that.
The reason why btree *has* to be able to deal with null entries is to
cope with multi-column indexes; you don't want it refusing to index a
row at all just because some of the columns are null. The others don't
currently handle multi-column indexes, so they're not really forced
to deal with that issue.
From a purely semantic point of view I'm not sure why Oleg is worried
about being able to store nulls in a GiST index ... seems like leaving
them out is OK, modulo the occasional complaint from VACUUM's
insufficiently intelligent tuple-count comparison ...
regards, tom lane
On Sat, 16 Dec 2000, Bruce Momjian wrote:
> Here is the list of features in 7.1.
One thing that I think ought to be added is that with 7.1,
PostgreSQL will compile out of the box (i.e. without any extra patches)
for Linux/Alpha. This might not be a big deal for most people, but for
those of who run pgsql on Linux/Alpha, it is, and I feel it at least
deserves a mention in the 7.1 feature list.
I looked for it (i.e. grep -i alpha) in the list, but did not see
it. Your choice which heading it goes under.
Also, I have not tested any recent snapshots or betas on
Linux/Alpha lately, but I plan to shortly and will let the hackers list
know of any problems. I have every intention of making sure the 7.1
release does indeed work out of box on Linux/Alpha. Thanks, TTYL.
---------------------------------------------------------------------------
| "For to me to live is Christ, and to die is gain." |
| --- Philippians 1:21 (KJV) |
---------------------------------------------------------------------------
| Ryan Kirkpatrick | Boulder, Colorado | http://www.rkirkpat.net/ |
---------------------------------------------------------------------------
On Tue, 19 Dec 2000, Hannu Krosing wrote: > Date: Tue, 19 Dec 2000 02:04:02 +0200 > From: Hannu Krosing <hannu@tm.ee> > To: Tom Lane <tgl@sss.pgh.pa.us> > Cc: Oleg Bartunov <oleg@sai.msu.su>, > Bruce Momjian <pgman@candle.pha.pa.us>, > PostgreSQL-development <pgsql-hackers@postgresql.org> > Subject: Re: [HACKERS] Who is a maintainer of GiST code ? > > Tom Lane wrote: > > > > Oleg Bartunov <oleg@sai.msu.su> writes: > > > We have a problem with > > > handling NULL values in GiST. Any thought how NULL values > > > are handle in Rtree. > > > > AFAIR, none of the index access methods except btree handle NULLs at > > all --- they just ignore NULL values and don't store them in the index. > > Feel free to improve on that ;-). The physical representation of index > > tuples can handle NULLs, the problem is teaching the index logic where > > they should go in the index. > > > > regards, tom lane > > > and I can't see why btree stores them (as it seems to do judging by the > index file size) - at least it does not use it for searching for "IS > NULL" and what does this error means ? create table rtree_test ( r box ); copy rtree_test from stdin; \N \N \N \N ........ total 10,000 NULLS \. create index rtree_test_idx on rtree_test using rtree ( r ); --ERROR: floating point exception! The last floating point operation either exceeded legal ranges or was a divide by zero seems rtree doesn't ignore NULL ? Regards, Oleg > > --8<--------8<--------8<--------8<--------8<--------8<--------8<--------8<------ > > hannu=# explain select * from nulltest where i is null; > NOTICE: QUERY PLAN: > > Seq Scan on nulltest (cost=0.00..293.80 rows=5461 width=8) > > EXPLAIN > hannu=# explain select * from nulltest where i =1; > NOTICE: QUERY PLAN: > > Index Scan using nulltest_i_ndx on nulltest (cost=0.00..96.95 rows=164 > width=8) > > --8<--------8<--------8<--------8<--------8<--------8<--------8<--------8<------ > > nulltest is a 16k record table with numbers 1 to 16384 in field i > > If it just ignored them we would have a nice way to fake partial indexes > - > just define a function that returns field value or null and then index > on that ;) > > ----------- > Hannu > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
On Sun, Dec 17, 2000 at 11:30:23PM -0500, Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I think > > there are some papers at Berkeley or a web site that goes into it in > > detail. > > I imagine there's some GiST stuff at the Berkeley papers repository > http://s2k-ftp.CS.Berkeley.EDU:8000/postgres/papers/ > but I'd be surprised if it's more than an overview... Well, there's this: http://gist.cs.berkeley.edu/ and this: http://gist.cs.berkeley.edu/pggist/ -- 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/
On Tue, 19 Dec 2000, Christopher Masto wrote: > Date: Tue, 19 Dec 2000 13:33:58 -0500 > From: Christopher Masto <chris@netmonger.net> > To: Tom Lane <tgl@sss.pgh.pa.us>, Bruce Momjian <pgman@candle.pha.pa.us> > Cc: Oleg Bartunov <oleg@sai.msu.su>, > PostgreSQL-development <pgsql-hackers@postgresql.org> > Subject: Re: [HACKERS] Who is a maintainer of GiST code ? > > On Sun, Dec 17, 2000 at 11:30:23PM -0500, Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > I think > > > there are some papers at Berkeley or a web site that goes into it in > > > detail. > > > > I imagine there's some GiST stuff at the Berkeley papers repository > > http://s2k-ftp.CS.Berkeley.EDU:8000/postgres/papers/ > > but I'd be surprised if it's more than an overview... > > Well, there's this: http://gist.cs.berkeley.edu/ > and this: http://gist.cs.berkeley.edu/pggist/ Thanks, we do know this sites. We're working on implementation of RD (Russian Doll) Tree using GiST interface. Current GiST sources have some bugs, some of them we already fixed and currently we're a working with handling of NULL values. We're getting broken index for data with NULLs. btw, how many people use GiST ? It would be nice to test our changes after we solve our problems. Regards, Oleg > -- > 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/ > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
I added (Alpha) next to the mention of 64-bit CPUs on the Function Manager section at the top. > On Sat, 16 Dec 2000, Bruce Momjian wrote: > > > Here is the list of features in 7.1. > > One thing that I think ought to be added is that with 7.1, > PostgreSQL will compile out of the box (i.e. without any extra patches) > for Linux/Alpha. This might not be a big deal for most people, but for > those of who run pgsql on Linux/Alpha, it is, and I feel it at least > deserves a mention in the 7.1 feature list. > I looked for it (i.e. grep -i alpha) in the list, but did not see > it. Your choice which heading it goes under. > Also, I have not tested any recent snapshots or betas on > Linux/Alpha lately, but I plan to shortly and will let the hackers list > know of any problems. I have every intention of making sure the 7.1 > release does indeed work out of box on Linux/Alpha. Thanks, TTYL. > > --------------------------------------------------------------------------- > | "For to me to live is Christ, and to die is gain." | > | --- Philippians 1:21 (KJV) | > --------------------------------------------------------------------------- > | Ryan Kirkpatrick | Boulder, Colorado | http://www.rkirkpat.net/ | > --------------------------------------------------------------------------- > > -- Bruce Momjian | http://candle.pha.pa.us pgman@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
Oleg Bartunov <oleg@sai.msu.su> writes:
> seems rtree doesn't ignore NULL ?
Hm, maybe not. There are explicit tests to ignore null inputs in hash
indexes (hash/hash.c), and I'd just sort of assumed that rtree and gist
do the same.
FWIW, your example doesn't seem to provoke an error in current sources;
but it does take quite a long time (far longer than building a btree
index on 10000 nulls). That makes me think that indexing nulls in rtree
might be a bad idea even if it works.
regards, tom lane
We finished (cross fingers) our changes in GiST code for 7.0.3 and in next 2 days we plan to send patches for upcoming 7.1 release. Isn't this too late ? What else we need to submit ? We have int4array contribution package which added index support for integer arrays and it's probably should come to contrib directory. We got about 3 orders of magnitude speedup using RD-Tree. Probably we need to add regression test for GiST. We have following fixes for GiST (7.0.3): 1. indices now store on disk in compressed form - was decompressed which causes broken index when using compression function (now compression of indices is really works) 2. NULLs now don't broken index 3. added support for keys with variable length - was fixed-length Patches and contribution package could be prepared in several days, documentation with some benchmarks and blurbs require more time. We plan to use these last changes to speedup our full-text-search ( killer application ) and write article to popularize GiST+Postgres. Any thoughts ? Regards, Oleg On Wed, 20 Dec 2000, Tom Lane wrote: > Date: Wed, 20 Dec 2000 15:00:21 -0500 > From: Tom Lane <tgl@sss.pgh.pa.us> > To: Oleg Bartunov <oleg@sai.msu.su> > Cc: Hannu Krosing <hannu@tm.ee>, Bruce Momjian <pgman@candle.pha.pa.us>, > PostgreSQL-development <pgsql-hackers@postgresql.org> > Subject: Re: [HACKERS] Who is a maintainer of GiST code ? > > Oleg Bartunov <oleg@sai.msu.su> writes: > > seems rtree doesn't ignore NULL ? > > Hm, maybe not. There are explicit tests to ignore null inputs in hash > indexes (hash/hash.c), and I'd just sort of assumed that rtree and gist > do the same. > > FWIW, your example doesn't seem to provoke an error in current sources; > but it does take quite a long time (far longer than building a btree > index on 10000 nulls). That makes me think that indexing nulls in rtree > might be a bad idea even if it works. > > regards, tom lane > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes:
> Probably we need to add regression test for GiST.
That would be nice, but let's not hold up the patches for it.
regards, tom lane
> We finished (cross fingers) our changes in GiST code for 7.0.3
> and in next 2 days we plan to send patches for upcoming 7.1 release.
> Isn't this too late ? What else we need to submit ?
Not too late, especially if it is fixing bugs. And even if not, anything
we can do to encourage active development and use of GiST is worth the
effort imho. I'll guess that unless we are talking about some extreme,
far-reaching changes in the backend others will agree.
> We have int4array contribution package which added index support for
> integer arrays and it's probably should come to contrib directory.
> We got about 3 orders of magnitude speedup using RD-Tree.
> Probably we need to add regression test for GiST.
> We have following fixes for GiST (7.0.3):
> 1. indices now store on disk in compressed form - was decompressed which
> causes broken index when using compression function
> (now compression of indices is really works)
> 2. NULLs now don't broken index
> 3. added support for keys with variable length - was fixed-length
> Patches and contribution package could be prepared in several days,
> documentation with some benchmarks and blurbs require more time.
> We plan to use these last changes to speedup our full-text-search
> ( killer application ) and write article to popularize GiST+Postgres.
> Any thoughts ?
Go go go !!! :)
- Thomas
Tom Lane wrote: > > Oleg Bartunov <oleg@sai.msu.su> writes: > > seems rtree doesn't ignore NULL ? > > Hm, maybe not. There are explicit tests to ignore null inputs in hash > indexes (hash/hash.c), and I'd just sort of assumed that rtree and gist > do the same. > > FWIW, your example doesn't seem to provoke an error in current sources; > but it does take quite a long time (far longer than building a btree > index on 10000 nulls). That makes me think that indexing nulls in rtree > might be a bad idea even if it works. Or maybe just some optimisations done for large number of similar keys ( probabilistic page-splitting or some such ;) in btree are not done in rtree ? ---------- Hannu
Oleg Bartunov wrote:
>
> We finished (cross fingers) our changes in GiST code for 7.0.3
Are tha patches for 7.0.3 already available ?
> and in next 2 days we plan to send patches for upcoming 7.1 release.
> Isn't this too late ? What else we need to submit ?
> We have int4array contribution package which added index support for
> integer arrays
what exactly does it mean ?
can i now query for an int4array which contains number 42 or just
an int array that = '{1,2,3}' with index support ?
> and it's probably should come to contrib directory.
> We got about 3 orders of magnitude speedup using RD-Tree.
> Probably we need to add regression test for GiST.
Some samples will do fine for start ;)
> We have following fixes for GiST (7.0.3):
> 1. indices now store on disk in compressed form - was decompressed which
> causes broken index when using compression function
> (now compression of indices is really works)
> 2. NULLs now don't broken index
> 3. added support for keys with variable length - was fixed-length
>
> Patches and contribution package could be prepared in several days,
> documentation with some benchmarks and blurbs require more time.
> We plan to use these last changes to speedup our full-text-search
> ( killer application ) and write article to popularize GiST+Postgres.
> Any thoughts ?
Great!
-------
Hannu
On Thu, 21 Dec 2000, Hannu Krosing wrote:
> Date: Thu, 21 Dec 2000 11:12:41 +0200
> From: Hannu Krosing <hannu@tm.ee>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: Tom Lane <tgl@sss.pgh.pa.us>, Bruce Momjian <pgman@candle.pha.pa.us>,
> PostgreSQL-development <pgsql-hackers@postgresql.org>
> Subject: Re: [HACKERS] Who is a maintainer of GiST code ?
>
> Oleg Bartunov wrote:
> >
> > We finished (cross fingers) our changes in GiST code for 7.0.3
>
> Are tha patches for 7.0.3 already available ?
>
yes, we could post it right now
> > and in next 2 days we plan to send patches for upcoming 7.1 release.
> > Isn't this too late ? What else we need to submit ?
> > We have int4array contribution package which added index support for
> > integer arrays
>
> what exactly does it mean ?
>
> can i now query for an int4array which contains number 42 or just
> an int array that = '{1,2,3}' with index support ?
>
yes, you need to apply out patch to GiST and functions for
RD-tree. I'll send you in private mail to conserve bandwidth.
It would be great if you test our code in your application.
Regards,
Oleg
> -------
> Hannu
>
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov wrote:
>
> On Thu, 21 Dec 2000, Hannu Krosing wrote:
>
> > Date: Thu, 21 Dec 2000 11:12:41 +0200
> > From: Hannu Krosing <hannu@tm.ee>
> > To: Oleg Bartunov <oleg@sai.msu.su>
> > Cc: Tom Lane <tgl@sss.pgh.pa.us>, Bruce Momjian <pgman@candle.pha.pa.us>,
> > PostgreSQL-development <pgsql-hackers@postgresql.org>
> > Subject: Re: [HACKERS] Who is a maintainer of GiST code ?
> >
> > Oleg Bartunov wrote:
> > >
> > > We finished (cross fingers) our changes in GiST code for 7.0.3
> >
> > Are tha patches for 7.0.3 already available ?
>
> yes, we could post it right now
Great!
> > > and in next 2 days we plan to send patches for upcoming 7.1 release.
> > > Isn't this too late ? What else we need to submit ?
> > > We have int4array contribution package which added index support for
> > > integer arrays
> >
> > what exactly does it mean ?
> >
> > can i now query for an int4array which contains number 42 or just
> > an int array that = '{1,2,3}' with index support ?
> >
>
> yes, you need to apply out patch to GiST and functions for
> RD-tree. I'll send you in private mail to conserve bandwidth.
> It would be great if you test our code in your application.
Thanks, I'm looking forwad to testing it.
---------------
Hannu
Hi,
we've almost totally rewrite gist.c because old code and algorithm
were not suitable for variable size keys. I think it might be
submitted into 7.1 beta source tree. We have fixed several bugs and
memory leaks. Version for 7.0.3 is also available.
Sampe application for contrib area - implementation RD-Tree and index support
for int arrays I'll submit later (Need some documentation).
Regards,
Oleg
Here is a README.gist
--------------------------------
New version of gist.c for PostgreSQL 7.1
New feature: 1. Support of variable size keys - new algorithm of insertion to tree (GLI - gist layrered
insertion).Previous algorithm was implemented as described in paper by Joseph M. Hellerstein et.al "Generalized
SearchTrees for Database Systems". This (old) algorithm was not suitable for variable size keys and could be
noteffective ( walking up-down ) in case of multiple levels split
Bug fixed: 1. fixed bug in gistPageAddItem - key values were written to disk uncompressed. This caused failure if
decompressionfunction does real job. 2. NULLs handling - we keep NULLs in tree. Right way is to remove them,
butwe don't know how to inform vacuum about index statistics. This is just cosmetic warning message (like in case
withR-Tree), but I'm not sure how to recognize real problem if we remove NULLs and suppress this warning as Tom
suggested. 3. various memory leaks
All our tests and Gene Selcov's regression tests passed ok.
We have version also for 7.0.3
Sample application which utilize RD-Tree for index support of
int arrays is in contrib/intarray (will be submitted separately).
TODO:
1. Description of GLI algorithm
2. regression test for GiST (based on RD-Tree)
This work was done by Teodor Sigaev (teodor@stack.net) and
Oleg Bartunov (oleg@sai.msu.su).
On Sun, 17 Dec 2000, Tom Lane wrote:
> Oleg Bartunov <oleg@sai.msu.su> writes:
> > I checked 7.1 feature list and didn't find any mention about GiST
> > but there are changes in GiST code. Who is a maintainer of GiST code ?
>
> You are ;-). If you expect to find someone who understands GiST better
> than you, you're probably out of luck.
>
> I recall having made a number of changes that applied to all of the
> index access methods, including GiST --- but I was just changing
> similar code in all the methods. I don't claim to know anything
> about GiST in particular.
>
> regards, tom lane
>
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes:
> we've almost totally rewrite gist.c because old code and algorithm
> were not suitable for variable size keys. I think it might be
> submitted into 7.1 beta source tree.
Urgh. Dropping in a total rewrite when we're already past beta3 doesn't
strike me as good project management practice --- especially if the
rewrite was done to add features (ie variable-size keys) not merely
fix bugs. I think it might be more prudent to hold this for 7.2.
regards, tom lane
On Wed, 10 Jan 2001, Tom Lane wrote: > Oleg Bartunov <oleg@sai.msu.su> writes: > > we've almost totally rewrite gist.c because old code and algorithm > > were not suitable for variable size keys. I think it might be > > submitted into 7.1 beta source tree. > > Urgh. Dropping in a total rewrite when we're already past beta3 doesn't > strike me as good project management practice --- especially if the > rewrite was done to add features (ie variable-size keys) not merely > fix bugs. I think it might be more prudent to hold this for 7.2. OK. If our changes will not go to 7.1, is't possible to create feature archive and announce it somewhere. It would be nice if people could test it. Anyway, I'll create web page with all docs and patches. I'm afraid one more year to 7.2 is enough for GiST to die :-) > > regards, tom lane > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg ... how about a contrib/patches directory that we put this into for v7.1 release, so that ppl have access to it, and then we apply the patch first thing for part of v7.2? On Thu, 11 Jan 2001, Oleg Bartunov wrote: > On Wed, 10 Jan 2001, Tom Lane wrote: > > > Oleg Bartunov <oleg@sai.msu.su> writes: > > > we've almost totally rewrite gist.c because old code and algorithm > > > were not suitable for variable size keys. I think it might be > > > submitted into 7.1 beta source tree. > > > > Urgh. Dropping in a total rewrite when we're already past beta3 doesn't > > strike me as good project management practice --- especially if the > > rewrite was done to add features (ie variable-size keys) not merely > > fix bugs. I think it might be more prudent to hold this for 7.2. > > OK. If our changes will not go to 7.1, is't possible to create > feature archive and announce it somewhere. It would be nice if > people could test it. Anyway, I'll create web page with all > docs and patches. I'm afraid one more year to 7.2 is enough for > GiST to die :-) > > > > > regards, tom lane > > > > Regards, > Oleg > _____________________________________________________________ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
The Hermit Hacker wrote: > > Oleg ... how about a contrib/patches directory that we put this into for > v7.1 release, so that ppl have access to it, and then we apply the patch > first thing for part of v7.2? And have Mandrake ship postgresql-v7.1-GiST-1mdk.rpm by default ;) I would even vote for including the ability to index int(4) arrays in the main distribution and not in contrib, similar to the current state of plpgsq and other pl* - ie they are compiled by default but not "activated". Assumption that arrays are indexable seems to come up once or twice a month on the mailing lists. ------------------- Hannu