Thread

1. Summary of Changes since last release (7.4.1)

   Simon Riggs <simon@2ndquadrant.com> — 2004-02-10T23:29:36Z

   POSTGRESQL: Summary of Changes since last release (7.4.1)
   ----------------------------------------------------------
   10 Feb 2004
   
   This is a summary of most changes since code versions marked 7_4_1. The
   intention is to help everybody understand what's coming and what might
   be affected, though most importantly, where YOU might add value to the
   community as a designer, developer, tester, technical author or
   advocate. It seeks to complement other information sources such as
   Robert Treat's weekly news bulletin, the TODO summary of desired future
   items, or the README files - which are the definitive list of what's in
   any particular release. Your feedback is appreciated.
   
   So far in this release dev cycle, major functionality will effect
   - PERFORMANCE
   - OPTIMIZER/EXECUTOR
   - ROBUSTNESS
   - SECURITY
   Other code changes are summarised and their major impacts noted.
   
   These notes cover major changes and are not guaranteed complete, or even
   fully tested. Many additional patches to the latest full release have
   been submitted and these are appreciated just as much, even though they
   have *mostly* more isolated effects. Documentation changes continue,
   though aren't described here, neither are client side
   utilities/interfaces.
   
   Nothing mentioned here is DEFINITELY in 7.5 or any future release;
   testing of everything mentioned here is encouraged and appreciated, for
   regression, performance and robustness. There is not yet a CVS branch
   specifically for any later release than 7_4_1; these changes are not yet
   even guaranteed to build into a consistent release when taken together.
   
   Description of changes is designed to highlight benefit and impact, as
   well as identifying specific areas of code change and potential knock-on
   effects.
   
   MAJOR FUNCTIONALITY
   
    PERFORMANCE
   
   - Major new memory buffer cache algorithm has now been implemented using
   the Adaptive Replacement Cache algorithm. The implementation should have
   positive benefit for everybody's workload, since ARC will adapt to a
   variety of situations and has been designed to allow Vacuum to avoid
   interfering with user applications. (Jan) src/backend/buffer
   
   - New performance profiling of Intel CPU has allowed new spinlock code
   to achieve performance/throughput gains of up to 10% using DBT-2 (OLTP)
   workloads. Further gains to follow? (Manfred Spraul/Tom)
   src/backend/storage/lmgr/s_lock.c
   
   - TIP 9 now needs changing! Cross-data-type comparisons are now
   indexable by btrees. All the existing cross-type comparison operators
   (int2/int4/int8 and float4/float8) have appropriate support. (Tom)
   Implications for user defined types and indices also? [HACKERS] 8-Nov-03
   
   - All operations on TEMP relations are no longer logged in WAL, nor are
   they involved in checkpoints, thus improving performance. (Tom)
   
   - Index performance improved when scanning highly non-unique indices;
   will greatly improve performance of cursor/fetch logic. B-tree's
   initial-positioning-strategy code has been improved so that we start
   scan at first entry, rather than reading in all entries that share that
   index value before we begin to scan. (Tom, after Dimitry Tkach)
   
   - Heap access code is now faster when using compressed columns in-line;
   previous assumption was that all compressed columns were also toasted
   (Tom)
   
   - Optimized calling performance for dynamically loaded C functions. Hash
   table added to cache lookups of 'C'-language functions. Some limited
   testing suggests that this puts the lookup speed for external functions
   just about on par with built-in functions. (Tom)
   
   - New delay feature added to VACUUM, allowing it to be executed in at a
   lower priority, ensuring other concurrent transaction performance can be
   maintained at a predictable level. Detailed analysis and graphs of
   run-time behaviour available at
   http://developer.postgresql.org/~wieck/vacuum_cost/ (Jan)
   Extended to include VACUUM FULL,ANALYZE and non-btree index vacuums.
   Centralize implementation of delay code by creating a pg_usleep()
   subroutine in src/port/pgsleep.c. (Tom)
   
   - More flexible memory control will allow large memory allocations to
   large maintenance operations such as CREATE INDEX, without effecting
   normal memory usage for queries. Rename server parameters SortMem and
   VacuumMem to work_mem and maintenance_work_mem; old names still
   available via new backward compatibility feature. Make btree index
   creation and initial validation of foreign-key constraints use
   maintenance_work_mem rather than work_mem as their memory limit. (Tom)
   
   - Restructure smgr API as per detailed proposal of 6 Feb, to improve
   performance in bgwriter and background checkpoint processes. Possibly
   also a precursor to later implementation of Tablespaces... (Tom)
   
    OPTIMIZER/EXECUTOR IMPROVEMENTS
   
   - Genetic Optimizer usage has been re-analyzed; geqo defaults have now
   been set to more effective values which are expected to significantly
   improve plan selection for complex multi-way joins (> 10-way).
   geqo_effort setting now offers an easy 1..10 setting (like IBM DB2),
   that allows this to be controlled realistically by user/DBA. New
   heuristic added to significantly reduce number of join plans attempted
   before geqo begins. (Tom)
   
   - Avoid redundant unique-ification step on subqueries where the result
   is already known to be unique (i.e. it is a SELECT DISTINCT ...
   subquery, IN subqueries that use UNION/INTERSECT/EXCEPT (without ALL)).
   Also set join_in_selectivity correctly. (Tom)
   
   - Avoid redundant projection step when scanning a table that we need all
   the columns from.  In case of SELECT INTO, we have to check that the
   hasoids flag matches the desired output type, too. (Tom)
   
   - Repair mis-estimation of indexscan CPU costs.  When an indexqual
   contains a run-time key (that is, a nonconstant expression compared to
   the index variable), the key is evaluated just once per scan, but we
   were charging costs as though it were evaluated once per visited index
   entry. (Tom)
   
   - Avoid planner failure for cases involving Cartesian products inside IN
   (sub-SELECT) constructs. (Tom)
   
   
    ROBUSTNESS
   
   - Local buffer manager is no longer used for newly-created non-TEMP
   relations; a new non-TEMP relation goes through the shared bufmgr and
   thus will participate normally in checkpoints. TEMP relations use the
   local buffer manager throughout their lifespan. (Tom)
   
   - Massive overhaul of pg_dump: make use of dependency information from
   pg_depend to determine a safe dump order.  Defaults and check
   constraints can be emitted either as part of a table or domain
   definition, or separately if that's needed to break a dependency loop.
   Lots of old half-baked code for controlling dump order removed.
   Performance work has also occurred to address some regressions in
   performance this caused.
   (Tom)
   
   - Changes to ALTER .. SET PATH allows ordered dumps to restore without
   error - pg_restore options to select restore order now removed - not
   needed (Tom)
   
   - In backend/access/transam/ add warning to AtEOXact_SPI() to catch
   cases where the current txn has been committed without SPI_finish()
   being called first. Allows detection of resource leaks... (Joe)
   
   - psql memory allocation is being cleaned up, using safer calls
   (Bruce/Neil)
   
   - GetNewTransactionId() logic sequence now enhanced to stay intact even
   at final stage of resource failure conditions, such as running out of
   disk space etc (Tom)
   
   - Add checks for close() and fclose() failure, applicable to some
   filesystems. Various locations affected in backend,initdb,copy (Tom)
   
   
    SECURITY
   
   - New permission-checking code. Rather than relying on the query context
   of a rangetable entry to identify what permissions it wants checked,
   store a full AclMode mask in each RTE, and check exactly those bits.
   This allows an RTE specifying, say, INSERT privilege on a view to be
   copied into a derived UPDATE query without changing meaning. (Tom)
   
   - Parsing of quoted keywords in pg_hba.conf enhances client-server
   specific combination security (Andrew)
   
    OTHER NEW FUNCTIONALITY
   
   - Add "WITH / WITHOUT OIDS" clause to CREATE TABLE AS. This allows the
   user to explicitly specify whether OIDs should be included in the
   newly-created relation; useful because it provides a way for application
   authors to ensure their applications are compatible with future versions
   of (in which the relation created by CREATE TABLE AS won't include OIDs
   by default). (Neil)
   
   - Add more kinds of exprs that can be accepted after a CREATE SCHEMA
   (Neil)
   
   - Info Schema enhanced further to support named function parameters
   (Dennis)
   
   - Change factorial to return a numeric (Gavin)
   
   - Comments can now be set on individual Cast, Conversion, Op Class,
   Large Object and Language (s) (Chris)
   
   - Have psql \dn show only visible temp schemas using current_schemas()
   
   - Have psql '\i ~/<tab><tab>' actually load files it displays from home
   dir
   
   - Allow psql \du to show groups, and add \dg for groups
   
   - Allow pg_dump to dump CREATE CONVERSION (Chris)
   
   - Make USING and WITH optional to bring the syntax of \copy into exact
   agreement with what the backend grammar actually accepts and what the
   documentation already says (Tom, Bill Moran)
   
   - Remove platform dependencies from miscadmin.h and put them in port.h
   (Tom)
   
   - New generate_series() function; first of new class of Set Returning
   functions - can return more than one row. (Joe)
   
    REFACTORING AND OTHER CODE CHANGES
   
   - Remove the explicit casting of NULL literals to a pointer in a wide
   variety of code locations (Neil)
   
   - Streamline local buffer manager code: Since it's no longer necessary
   to fsync relations as they move out of the local buffers into shared
   buffers, quite a lot of smgr.c/md.c/fd.c code is no longer needed and
   has been removed: there's no concept of a dirty relation anymore in
   md.c/fd.c, and we never fsync anything but WAL. (Tom)
   
   - Add operator strategy and comparison-value datatype fields to ScanKey.
   Remove the 'strategy map' code - Passing the strategy number to the
   index AM directly should now be simpler and faster. Changes to
   ScanKeyEntryInitialize() API touches quite a lot of files. (Tom)
   
   - nbtree function _bt_first is now substantially changed/simplified
   (Tom)
   
   - Change PG_DELAY from msec to usec and use it consistently rather than
   select(). Add Win32 Sleep() for delay. (Bruce)
   
   - Supporting relaxing of ALTER...SET PATH requires changing the API for
   GUC assign_hook functions, which touches a lot of places (Tom)
   
   - In src/backend/access/ we have major changes in heap, nbtree, transam
   and common code
   
   - Almost all files src/backend/commands have changes; mostly robustness
   
   - JDBC interface has been moved out into its own project to improve the
   focus on this popular and important area of client code.
   
   - initdb has now been completely re-written from shell script to C
   (Andrew)
   
   - Add some code to guc.c to allow backwards compatibility for server
   config parameters. Variable renaming is now more easily possible, since
   parameters can potentially be referenced by both their old and new names
   in SHOW and SET commands. (Tom)
   
   - Remove the long-dead 'persistent main memory' storage manager (mm.c),
   since it seems quite unlikely to ever get resurrected. (Tom)
   
   - Add configure support for determining UINT64_FORMAT, the appropriate
   snprintf format for uint64 items (Tom)
   
   MAJOR IMPACTS NOTED (Upgrades, performance, change of defaults etc)
   
   - initdb forced due to pg_proc change to support Named function
   parameters
   
   - initdb forced due to change of stored rule representation.
   
   - initdb recommended to allow picking up Info Schema changes
   
   - geqo defaults have now been set to significantly more effective
   values; we should expect increased optimization elapsed times for very
   large queries, though hopefully outweighed by a significant improvement
   in plan selection
   
   - security tightening may cause errors with some existing applications -
   be aware, but also be thankful!
   
   MAJOR WORK IN PROGRESS (Much less accurate than the above...)
   
   - Win32 - Changes to many areas, especially the postmaster, ipc, libpq
   etc, to streamline and allow a single source Win32 port - the Win32 port
   is coming closer! (Claudio)
   
   - Replication - involving remote copying of WAL logs, then cut-over and
   automatic catch-up on secondary node (Jan)
   
   - Background writer work progresses, which is likely to improve overall
   scalability/performance by smoothing dirty blocks writes; forms the
   basis for increased server availability also (Jan)
   
   - Initial stages of Named Function parameter support have been
   committed. pg_proc has a column to store names, and CREATE FUNCTION can
   insert data into it, but that's all as yet. (Dennis)
   
   - Buffer manager locking changes: lock contention data have shown that
   the BufMgrLock is the major source of contention under heavy load,
   effecting multi-CPU SMP scalability. Patch to rework the bufmgr's
   locking scheme to be more granular and further perf testing may yield
   further perf gains. (Neil)
   
   - Backend internal data structure changes: list rewrite: the linked list
   implementation used throughout the backend is being redesigned for
   constant-time length and append operations. This was done because
   lappend() is called quite frequently, and allows some ugly code
   (FastList) to be removed. (Neil)
   
   - Hash index changes: complete wrap up of unique hash indexes, as well
   as some improvements to hash index concurrent performance. (Neil)
   
   - Customizable ANALYZE function for user definable functions is generic
   functionality being added, though as base for PostGIS enhancements
   (Mark)
   
   - Re-evaluation of Genetic Query Optimizer parameters and usage will
   likely continue for some and any real usage scenarios/observations are
   welcome
   
   
   WHAT **ISN'T** IN THIS RELEASE (YET!)
   
   - Many TODO items still to be claimed...
   
   DEVELOPER FEATURES (logging, debugging etc)
   - bootstrap can now be cancelled using CTRL-C (Tom)
   - add a console control handler for Ctrl-C (Magnus Hagander)
   - use debug_shared_buffers = <seconds> to show ARC mem buffer contents
   (Jan)
   
   All corrections and changes welcome...this summary is updated every
   Tuesday based upon committed changes to the public community code base.
   Please draw my attention to other changes in other parts of PostgreSQL
   utilities etc that may affect the backend code, portability or run-time
   behaviour.
   
   More exactly, the changes listed here are ones that have occurred since
   the 7_4 branchpoint in CVS. They might have been applied to more than
   one branch so could potentially be in 7_4_STABLE and 7_4_1. The bottom
   line is that they haven't been cast in stone yet...
    
   -- Simon Riggs, simon@2ndquadrant.com
   
   
   
   

2. Re: Summary of Changes since last release (7.4.1)

   Mike Mascari <mascarm@mascari.com> — 2004-02-11T00:16:18Z

   Simon Riggs wrote:
   
   > - All operations on TEMP relations are no longer logged in WAL, nor are
   > they involved in checkpoints, thus improving performance. (Tom)
   
   That is great news!
   
   Looking forward to 7.5 already,
   
   Mike Mascari
   
   
   
   

3. Re: Summary of Changes since last release (7.4.1)

   Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2004-02-11T00:41:34Z

   >> - All operations on TEMP relations are no longer logged in WAL, nor are
   >> they involved in checkpoints, thus improving performance. (Tom)
   > 
   > 
   > That is great news!
   > 
   > Looking forward to 7.5 already,
   
   I could have sworn that the above was done in 7.4, by Tom...?
   
   Chris
   
   
   

4. Re: Summary of Changes since last release (7.4.1)

   Mike Mascari <mascarm@mascari.com> — 2004-02-11T01:08:37Z

   Christopher Kings-Lynne wrote:
   
   >>> - All operations on TEMP relations are no longer logged in WAL, nor are
   >>> they involved in checkpoints, thus improving performance. (Tom)
   >>
   >>
   >>
   >> That is great news!
   >>
   >> Looking forward to 7.5 already,
   > 
   > 
   > I could have sworn that the above was done in 7.4, by Tom...?
   
   Yeah. 7.4 performed a lot better for me. I was a little too greedy 
   in my hopes that the temp table issue hadn't been addressed and 
   would see further temp table-related performance gains in 7.5. I see 
   the CVS commit Aug. 6 before the pgindent run Sept. 4:
   
   http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/storage/smgr/smgr.c?rev=1.58&content-type=text/x-cvsweb-markup
   
   Mike Mascari
   
   
   
   

5. Re: Summary of Changes since last release (7.4.1)

   Mike Mascari <mascarm@mascari.com> — 2004-02-11T01:13:30Z

   I wrote:
   
   > Yeah. 7.4 performed a lot better for me. I was a little too greedy in my 
   > hopes that the temp table issue hadn't been addressed and would see 
   > further temp table-related performance gains in 7.5. I see the CVS 
   > commit Aug. 6 before the pgindent run Sept. 4:
   > 
   > http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/storage/smgr/smgr.c?rev=1.58&content-type=text/x-cvsweb-markup 
   
   Actually, that was an Aug 6, 2002 commit, not 2003 which would make 
   it 7.3, right? So Simon, my I humbly ask from where you culled this 
   change in CVS tip?
   
   Mike Mascari
   
   
   
   

6. Re: Summary of Changes since last release (7.4.1)

   Simon Riggs <simon@2ndquadrant.com> — 2004-02-11T08:21:24Z

   >Mike Mascari
   > Actually, that was an Aug 6, 2002 commit, not 2003 which would make
   > it 7.3, right? So Simon, my I humbly ask from where you culled this
   > change in CVS tip?
   
   Wow, looks like you got me there. It is I who humbly apologises.
    
   Humble AND Zealous though :)
   
   Overall, each item has been researched a bit more than simply copying
   the committers' notes, but as you can see, I still have much to learn
   about both the codebase itself and the way it is developed. The notes
   are an attempt to help others do the same - accuracy is important - I'll
   do better next time.
   
   Thanks for pointing out my error, Simon Riggs
   
   
   

7. Re: Summary of Changes since last release (7.4.1)

   Mike <ipso@snappymail.ca> — 2004-02-11T17:32:22Z

   Simon,
   
   	Excellent job, your summaries have saved me hours of reading the
   hackers mailing list. 
   
   Keep up the good work!
   
   On Wed, 2004-02-11 at 00:21, Simon Riggs wrote:
   > >Mike Mascari
   > > Actually, that was an Aug 6, 2002 commit, not 2003 which would make
   > > it 7.3, right? So Simon, my I humbly ask from where you culled this
   > > change in CVS tip?
   > 
   > Wow, looks like you got me there. It is I who humbly apologises.
   >  
   > Humble AND Zealous though :)
   > 
   > Overall, each item has been researched a bit more than simply copying
   > the committers' notes, but as you can see, I still have much to learn
   > about both the codebase itself and the way it is developed. The notes
   > are an attempt to help others do the same - accuracy is important - I'll
   > do better next time.
   > 
   > Thanks for pointing out my error, Simon Riggs
   > 
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
   -- 
   Mike Benoit <ipso@snappymail.ca>