Обсуждение: Updated 6.5 HISTORY
Here is the updated list of changes in 6.5. I will roll them into the
sgml and HISTORY files, and put it up on the web site soon. Note the
marker in the list that shows the new items since I last posted this
list in March. Please review and suggest changes. Thanks.
---------------------------------------------------------------------------
POSTGRESQL 6.5
This release marks the development team's final mastery of the source
code we inherited from Berkeley. You will see we are now easily adding
major features, thanks to the increasing size and experience of our
world-wide development team:
Multi-version concurrency control(MVCC): This removes our old
table-level locking, and replaces it with a locking system that is
superior to most commercial database systems. In a traditional system,
each row that is modified is locked until committed, preventing reads by
other users. MVCC uses the natural multi-version nature of PostgreSQL
to allow readers to continue reading consistent data during writer
activity. Writers continue to use the compact pg_log transaction
system. This is all preformed without having to allocate a lock for
every row like traditional database systems. So, basically, we no
longer have table-level locking, we have something better than row-level
locking.
Numeric data type: We now have a true numeric data type, with
user-specified precision.
Temporary tables: Temporary tables are guaranteed to have unique names
within a database session, and are destroyed on session exit.
New SQL features: We now have CASE, INTERSECT, and EXCEPT statement
support. We have new LIMIT/OFFSET, SET TRANSACTION ISOLATION LEVEL,
SELECT ... FOR UPDATE, and an improved LOCK command.
Speedups: We continue to speed up PostgreSQL, thanks to the variety of
talents within our team. We have sped up memory allocation,
optimization, table joins, and row transfers routines.
Other: We continue to expand our port list, this time including
Win32/NT. Most interfaces have new versions, and existing functionality
has been improved.
Please look through the list to see the full extent of our changes in
this PostgreSQL 6.5 release.
---------------------------------------------------------------------------
Add "vacuumdb" utility
Fix text<->float8 and text<->float4 conversion functions(Thomas)
Fix for creating tables with mixed-case constraints(Billy)
Speed up libpq by allocating memory better(Tom)
EXPLAIN all indices used(Thomas)
Improve port matching(Tom)
Portability fixes for SunOS
Change exp()/pow() behavior to generate error on underflow/overflow(Jan)
Implement CASE expression(Thomas)
Fix bug in pg_dump -z
New pg_dump table output format(Constantin)
Add string min()/max() functions(Thomas)
Extend new type coersion techniques to aggregates(Thomas)
New moddatetime contrib(Terry)
Update to pgaccess(Constantin)
Fix problems in the muti-byte code(Tatsuo)
Fix case where executor evaluates functions twice(Tatsuo)
Memory overrun cleanups(Tatsuo)
Fix for lo_import crash(Tatsuo)
Adjust handling of data type names to suppress double quotes(Thomas)
Add routines for single-byte "char" type(Thomas)
Improved substr() function(Thomas)
Use type coersion for matching columns and DEFAULT(Thomas)
Add CASE statement support(Thomas)
Improved multi-byte handling(Tatsuo)
Add NT/Win32 backend port and enable dynamic loading(Magnus and Daniel Horak)
Multi-version concurrency control/MVCC(Vadim)
New Serialized mode(Vadim)
Fix fix for tables over 2gigs(Peter)
Upgrade to Pygress(D'Arcy)
New SET TRANSACTION ISOLATION LEVEL(Vadim)
New LOCK TABLE IN ... MODE(Vadim)
New port to Cobalt Qube(Mips) running Linux(Tatsuo)
Fix deadlock so it only checks once after one second of sleep(Bruce)
Port to NetBSD/m68k(Mr. Mutsuki Nakajima)
Port to NetBSD/sun3(Mr. Mutsuki Nakajima)
Port to NetBSD/macppc(Toshimi Aoki)
Update odbc version
New NUMERIC data type(Jan)
New SELECT FOR UPDATE(Vadim)
Handle "NaN" and "Infinity" for input values(Jan)
Better date/year handling(Thomas)
Improved handling of backend connections(Magnus)
New options ELOG_TIMESTAMPS and USE_SYSLOG options for log files(Massimo)
New TCL_ARRAYS option(Massimo)
New INTERSECT and EXCEPT(Stefan)
New pg_index.indisprimary for primary key tracking(D'Arcy)
New pg_dump option to allow dropping of tables before creation(Brook)
Fixes for aggregates and PL/pgsql(Hiroshi)
Speedup of row output routines(Tom)
JDBC improvements(Peter)
Fix for subquery crash(Vadim)
New READ COMMITTED isolation level(Vadim)
New TEMP tables/indexes(Bruce)
Prevent sorting if result is already sorted(Jan)
Fix for libpq function PQfnumber and case-insensitive names(Bahman Rafatjoo)
Fix for large object write-into-middle, remove extra block(Tatsuo)
New memory allocation optimization(Jan)
Allow psql to do \p\g(Bruce)
Allow multiple rule actions(Jan)
Fix for pg_dump -d or -D and quote special characters in INSERT
Added LIMIT/OFFSET functionality(Jan)
Removed CURRENT keyword for rule queries(Jan)
Improve optimizer when joining a large number of tables(Bruce)
Addition of Stefan Simkovics' Master's Thesis to docs(Stefan)
Improved int8 support(Thomas, Marc)
New routines to convert between int8 and text/varchar types(Thomas)
New bushy plans, where meta-tables are joined(Bruce)
Enable right-hand queries by default(Bruce)
Allow reliable maximum number of backends to be set at configure time (--with-maxbackends and postmaster switch (-N
backends))(Tom)
Repair serious problems with dynahash(Tom)
Fix INET/CIDR portability problems
Fix problem with selectivity error in ALTER TABLE ADD COLUMN(Bruce)
Fix executor so mergejoin of different column types works(Tom)
GEQO default now 11 tables because of optimizer speedups(Tom)
Fix for Alpha OR selectivity bug
Fix OR index selectivity problem(Bruce)
Allow btree/hash index on the int8 type(Ryan)
Allow Var = NULL for MS-SQL portability(Michael)
Fix so \d shows proper length for char()/varchar()(Ryan)
Fix tutorial code(Clark)
Improve destroyuser checking(Oliver)
Fix for Kerberos(Rodney McDuff)
Modify contrib check_primary_key() so either "automatic" or "dependent"(Anand)
Allow psql \d on a view show query(Ryan)
Speedup for LIKE(Bruce)
Fix for dropping database while dirty buffers(Bruce)
Fix so sequence nextval() can be case-sensitive(Bruce)
Fix for tcl/tk configuration(Vince)
Ecpg fixes/features, see src/interfaces/ecpg/ChangeLog file(Michael)
Jdbc fixes/features, see src/interfaces/jdbc/CHANGELOG(Peter)
--new since 1999/03/15-----------------------------------------------
Have psql \d on a view show the query
Fix !!= operator
Drop buffers before destroying database files(Bruce)
Allow sequence nextval actions to be case-sensitive(Bruce)
tcl/tk configure improvements
Make % operator have precedence like /.
Add new postgres -O option to allow system table structure changes(Bruce)
Fix optimizer indexing not working for negative numbers(Bruce)
Fix for memory leak in executor with fjIsNull
Allow WHERE specification of NULL = Var(Bruce)
Fix for aggregate memory leaks(Erik Riedel)
Allow username containing a dash grant permissions
Cleanup of NULL in inet types
NT dynamic loading now works(Daniel Horak)
Update contrib/pginterface/findoidjoins script(Tom)
Clean up system�table bugs(Tom)
Major speedup in vacuum of deleted rows(Vadim)
Allow non-SQL functions to run different versions based on arguments(Tom)
Fix problems of PAGER and \? command(Masaaki Sakaida)
Add -E option that shows actual queries sent by \dt and friends(Masaaki Sakaida)
Add version number in startup banners for psql(Masaaki Sakaida)
Reduce default multi-segment file size limit to 1GB(Peter)
New contrib/vacuumlo removes large objects not referenced(Peter)
Fix for dumping of CREATE OPERATOR(Tom)
Fix for backward scanning of cursors(Hiroshi Inoue)
Add ARM32 support(Andrew McMurry)
Date/time fixes(Thomas)
Fix for COPY FROM STDIN when using \i(Tom)
New initialization for table sizes so non-vacuumed tables perform better(Tom)
Improve error messages when a connection is rejected(Tom)
Fix for subselect is compared inside an expression(Jan)
Fixes for HPUX 11 and Unixware
Fix handling of error reporting while returning rows(Tom)
Fix problems with reference to array types(Tom,Jan)
Prevent UPDATE SET oid(Jan)
Better optimization statistics for system table access(Tom)
Support for arrays of char() and varchar() fields(Massimo)
Better handling of non-default block sizes(Massimo)
Fix pg_dump so -t option can handle case-sensitive tablenames
Overhaul of hash code to increase reliability and performance(Tom)
Improve file handling to be more uniform, prevent file descriptor leak(Tom)
Large object fixes for overlapping writes and memory consumption(Tatsuo)
Update to PyGreSQL 2.4(D'Arcy)
Update to JDBC(Peter)
Changed debug options so -d4 and -d5 produce different node displays(Jan)
New pg_options: pretty_plan, pretty_parse, pretty_rewritten(Jan)
Fixes for GROUP BY in special cases(Tom, Jan)
Fix for memory leak in failed queries(Tom)
DEFAULT now supports mixed-case identifiers(Tom)
Fix for multi-segment uses of DROP/RENAME table, indexes(Ole Gjerde)
Improve GEQO optimizer memory consumption(Tom)
UNION now suppports ORDER BY of columns not in target list(Tom)
New install commands for plpgsql(Jan)
-- 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
> Here is the updated list of changes in 6.5. I will roll them into the > sgml and HISTORY files, and put it up on the web site soon. Note the > marker in the list that shows the new items since I last posted this > list in March. Please review and suggest changes. Thanks. I'm pretty sure that I can generate HISTORY from the sgml, just before release. Also, it looks like we could freeze the release notes pretty soon, since we know what *features* are going into this release. OK? We should mention the NetBSD/arm32 port in the introduction. Also, let's move bug fix notes to below feature notes... <snip> > EXPLAIN all indices used(Thomas) Not me, must be someone else... > Implement CASE expression(Thomas) Implement CASE, COALESCE, NULLIF expressions > Add CASE statement support(Thomas) This is a duplicate mention of CASE... > Upgrade to Pygress(D'Arcy) PyGres > Update odbc version Update ODBC driver(Byron) > Better date/year handling(Thomas) Improve date/time handling(Thomas) > Addition of Stefan Simkovics' Master's Thesis to docs(Stefan) New intro to SQL from S. Simkovics' Master's Thesis (Stefan, Thomas) New intro to backend processing from " " " " ... > Improved int8 support(Thomas, Marc) Improved int8 support(Ryan, Thomas, Tom) Marc, did you do something with int8s?? Ryan is Ryan Bradetich, if he doesn't show up yet in the contributor's list... > Allow btree/hash index on the int8 type(Ryan) Consolidate this with above? Ryan's contribution was probably the biggest feature for int8 support in this release. > --new since 1999/03/15----------------------------------------------- > Date/time fixes(Thomas) Duplicate of earlier entries. > Update to PyGreSQL 2.4(D'Arcy) Duplicate (though you may want to keep this one instead) > Update to JDBC(Peter) Duplicate. -- Thomas Lockhart lockhart@alumni.caltech.edu South Pasadena, California
1.
Today I tried my test script (many tables joining) and got a crash.
It works several days before. CPU usage shows only 5% loading during this test.
what postgres is doing ? Hmm, just rerun the test and it works :-)
OK, one more again and it crashed. Again - works. Again - crashed, crashed,
crashed. Very unstable situation.
I attached perl script to generate date set and sql commands
mkjoindata.pl | psql test
2.
Does anybody tried sqlbench, posted by
Edmund ? I tried several times on Linux box, FreeBSD box with current cvs
and it never finished. RAM usage during the test was about 11-13 Mb
and CPU usage was extremly low - 5-9% only !
Regards,
Oleg
select t0.a,t1.a as t1,t2.a as t2,t3.a as t3,t4.a as t4,t5.a as t5,t6.a as t6,t7
.a as t7,t8.a as t8,t9.a as t9,t10.a as t10,t11.a as t11,t12.a as t12,t13.a as t
13,t14.a as t14from t0 ,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14where t1.a_id = t0.a_t1_id and
t2.a_id=t0.a_t2_idand t3.a_id=t0.a_t3_id and t4
.a_id=t0.a_t4_id and t5.a_id=t0.a_t5_id and t6.a_id=t0.a_t6_id and t7.a_id=t0.a_
t7_id and t8.a_id=t0.a_t8_id and t9.a_id=t0.a_t9_id and t10.a_id=t0.a_t10_id andt11.a_id=t0.a_t11_id and
t12.a_id=t0.a_t12_idand t13.a_id=t0.a_t13_id and t14.
a_id=t0.a_t14_id ;
pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is impossible. Terminating.
_____________________________________________________________
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
Thus spake Thomas Lockhart
> > Upgrade to Pygress(D'Arcy)
> PyGres
PyGreSQL
> > Update to PyGreSQL 2.4(D'Arcy)
>
> Duplicate (though you may want to keep this one instead)
I agree.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
Oleg Bartunov <oleg@sai.msu.su> writes:
> Today I tried my test script (many tables joining) and got a crash.
> It works several days before. CPU usage shows only 5% loading during
> this test. what postgres is doing ? Hmm, just rerun the test and it
> works :-) OK, one more again and it crashed. Again - works. Again -
> crashed, crashed, crashed. Very unstable situation.
Oh dear ... can you provide a backtrace from one of the crashes?
> Does anybody tried sqlbench, posted by
> Edmund ? I tried several times on Linux box, FreeBSD box with current cvs
> and it never finished. RAM usage during the test was about 11-13 Mb
> and CPU usage was extremly low - 5-9% only !
It ran through to completion for me, but took hours. (Unfortunately
I only have access to one machine that has 700Mb of free disk space,
and I mustn't shut off the data collection task that is its primary load.
So I'm stuck with taking a long time to try the sqlbench stuff...
it does seem to work, but I can't say much about performance...)
regards, tom lane