88 Major changes in this release:
99
1010 Performance
11- IN/NOT IN subqueries are now much more efficient
11+ IN/NOT IN subqueries are now much more efficient [1]
1212
13- Improved GROUP BY processing by using hash buckets
13+ Improved GROUP BY processing by using hash buckets [2]
1414
15- New multi-key hash join capability
15+ New multi-key hash join capability [3]
1616
17- ANSI joins are now better optimized
17+ ANSI joins are now better optimized [4]
1818
19- Faster and more powerful regular expression code
19+ Faster and more powerful regular expression code [5]
2020
21- Function-inlining for simple SQL functions
21+ Function-inlining for simple SQL functions [6]
2222
2323 IPv6
2424 Full support for IPv6 connections and IPv6 address data types
25+ [7]
2526
2627 SSL
27- Major improvements in SSL performance and reliability
28+ Major improvements in SSL performance and reliability [8]
2829
2930 Index Growth Prevention
3031 Allow free space map to efficiently reuse empty index pages,
31- and other free space management improvements.
32+ and other free space management improvements. [9]
3233
3334 Standards Compliance
3435 Implement information schema
4748
4849 Threads
4950 libpq and ecpg are now fully thread-safe with
50- --enable-thread-safety
51+ --enable-thread-safety [10]
5152
5253 Contrib
5354 New version of full text indexing (tsearch2)
5455
55- New autovacuum tool
56+ New autovacuum tool [11]
5657
5758 Array handling has been improved and moved into the main server
59+ [12]
5860 _________________________________________________________________
5961
6062 Migration to version 7.4
6567 Observe the following incompatibilities:
6668
6769 * The server-side autocommit setting was removed and reimplemented
68- in client applications and languages.
70+ in client applications and languages. [13]
6971 * Error message wording has changed substantially in this release,
7072 and error codes have been added.
7173 * ANSI inner joins may behave differently because they are now
7274 better optimized
73- * A number of server variables have been renamed for clarity
74- * MOVE/FETCH 0 now does nothing; return value of MOVE/FETCH 1/0
75- varies based on the current offset in the cursor
76- * COPY now can process carriage-return/line-feed and carriage-return
77- terminated files. Literal carriage-returns and line-feeds are no
78- longer accepted as data values; use \r and \n instead.
75+ * A number of server variables have been renamed for clarity,
76+ primarily those related to logging
77+ * MOVE/FETCH 0 now does nothing [14]
78+ * MOVE/FETCH now returns the actual number of rows moved/fetched, or
79+ zero if at the beginning/end of the cursor [15]
80+ * COPY now can process carriage-return and carriage-return/line-feed
81+ end-of-line terminated files.
82+ * Literal carriage-returns and line-feeds are no longer accepted as
83+ data values; use \r and \n instead.
7984 * Trailing spaces are now trimmed when converting from CHAR(n) to
8085 VARCHAR(n) / TEXT
8186 * FLOAT(p) now measures 'p' in bits, not digits
8287 * Ambiguous date values now must match the ordering specified by
83- DateStyle
88+ DateStyle [16]
8489 * The oidrand(), oidsrand(), and userfntest() functions have been
85- removed.
90+ removed. [17]
8691 * 'now' will no longer work as a column default; now() or
87- CURRENT_TIMESTAMP should be used instead
92+ CURRENT_TIMESTAMP should be used instead [18]
8893 * 'today' will no longer work as a column default; CURRENT_DATE
89- should be used instead
94+ should be used instead [19]
9095 * Dollar sign ($) is no longer allowed in operator names
91- * Dollar sign ($) can be a non-first character in identifiers
96+ * Dollar sign ($) can be a non-first character in identifiers [20]
9297 _________________________________________________________________
9398
9499 Changes
@@ -97,19 +102,20 @@ Server Operation
97102
98103 * Allow IPv6 server connections (Nigel Kukard, Johan Jordaan, Bruce,
99104 Tom, Kurt Roeckx, Andrew Dunstan)
100- * Fix SSL to handle errors cleanly (Nathan Mueller)
105+ * Fix SSL to handle errors cleanly (Nathan Mueller) [21]
101106 * SSL protocol security and performance improvements (Sean
102- Chittenden)
103- * Print lock information when a deadlock is detected (Tom)
107+ Chittenden) [22]
108+ * Print lock information when a deadlock is detected (Tom) [23]
104109 * Update "/tmp" socket mod. times regularly to avoid their removal
105- (Tom)
110+ (Tom) [24]
106111 * Enable PAM for MAC OS X (Aaron Hillegass)
107- * Make btree indexes fully WAL-safe (Tom)
112+ * Make btree indexes fully WAL-safe (Tom) [25]
108113 * Allow btree index compaction and empty page reuse (Tom)
109114 * Fix inconsistent index lookups during split of first root page
110- (Tom)
115+ (Tom) [26]
111116 * Improve free space map allocation logic (Tom)
112117 * Preserve free space information between postmaster restarts (Tom)
118+ [27]
113119 * Set proper schema permissions in initdb (Peter)
114120 * Add start time to pg_stat_activity (Neil)
115121 * New code to detect corrupt disk pages; erase with
@@ -132,13 +138,13 @@ Performance
132138 * Allow multi-key hash joins (Tom)
133139 * Improve constant folding (Tom)
134140 * Add ability to inline simple SQL functions (Tom)
135- * Reduce memory usage for queries using complex functions (Tom)
136- * Improve GEQO optimizer performance (Tom)
141+ * Reduce memory usage for queries using complex functions (Tom) [28]
142+ * Improve GEQO optimizer performance (Tom) [29]
137143 * Allow IN/NOT IN to be handled via hash tables (Tom)
138144 * Improve NOT IN (subquery) performance (Tom)
139145 * Allow most IN subqueries to be processed as joins (Tom)
140146 * Allow the postmaster to preload libraries using preload_libraries
141- (Joe)
147+ (Joe) [30]
142148 * Improve optimizer cost computations, particularly for subqueries
143149 (Tom)
144150 * Avoid sort when subquery ORDER BY matches upper query (Tom)
@@ -156,17 +162,17 @@ Performance
156162 * Improve trigger/constraint performance (Stephan)
157163 * Improve speed of col IN (const, const, const, ...) (Tom)
158164 * Fix hash indexes which were broken in rare cases (Tom)
159- * Improve hash index concurrency and speed (Tom)
165+ * Improve hash index concurrency and speed (Tom) [31]
160166 * Align shared buffers on 32-byte boundary for copy speed
161- improvement (Manfred Spraul)
167+ improvement (Manfred Spraul) [32]
162168 * The NUMERIC datatype has been reimplemented for better performance
163- (Tom)
169+ (Tom) [33]
164170 _________________________________________________________________
165171
166172Server Configuration
167173
168174 * Rename server parameter server_min_messages to log_min_messages
169- (Bruce)
175+ (Bruce) [34]
170176 * Rename show_*_stats to log_*_stats (Bruce)
171177 * Rename show_source_port to log_source_port (Bruce)
172178 * Rename hostname_lookup to log_hostname (Bruce)
@@ -243,10 +249,9 @@ Utility Commands
243249
244250 * Add ON COMMIT clause to CREATE TABLE for temp tables (Gavin)
245251 * Allow cursors outside transactions using WITH HOLD (Neil)
246- * Make MOVE/FETCH 0 actually move/fetch 0 (Bruce)
247- * Cause FETCH 1 to return the next cursor row number, or zero if at
248- beginning/end of cursor, per SQL spec (Bruce)
249- * Have MOVE return 0 or 1 depending on cursor position (Bruce)
252+ * MOVE/FETCH 0 now does nothing (Bruce)
253+ * Cause MOVE/FETCH to return the number of rows moved/fetched, or
254+ zero if at the beginning/end of cursor, per SQL spec (Bruce)
250255 * Properly handle SCROLL with cursors, or report an error (Neil)
251256 * Implement SQL92-compatible FIRST, LAST, ABSOLUTE n, RELATIVE n
252257 options for FETCH and MOVE (Tom)
@@ -4704,3 +4709,171 @@ The following bugs have been fixed in postgres95-beta-0.02:
47044709 Release date: 1995-05-01
47054710
47064711 Initial release.
4712+
4713+ Notes
4714+
4715+ [1]
4716+
4717+ In previous releases, IN/NOT IN subqueries were joined to the upper
4718+ query by sequentially scanning the subquery looking for a join. The
4719+ 7.4 code uses the same sophisticated techniques used by ordinary joins
4720+ and so is much faster, and is now faster than EXISTS subqueries.
4721+ [2]
4722+
4723+ In previous releases, GROUP BY totals were accumulated by sequentially
4724+ scanning the list of groups looking for a match; the 7.4 code places
4725+ GROUP BY values in hash buckets so the proper match can be found much
4726+ quicker. This is particularly significant in speeding up queries that
4727+ have a large number of distinct GROUP BY values.
4728+ [3]
4729+
4730+ In previous releases, hash joins could only occur on single-column
4731+ joins. This release allows multi-column hash joins.
4732+ [4]
4733+
4734+ Prior releases evaluated ANSI join syntax only in the order specified
4735+ by the query; 7.4 allows full optimization of queries using ANSI join
4736+ syntax, meaning the optimizer considers all possible join orderings
4737+ and chooses the most efficient.
4738+ [5]
4739+
4740+ The entire regular expression module has been replaced with a new
4741+ version by Henry Spencer, originally written for TCL. The code greatly
4742+ improves performance and supports several flavors of regular
4743+ expressions.
4744+ [6]
4745+
4746+ Simple SQL functions can now be inlined by including their SQL in the
4747+ main query. This improves performance by preventing repeated calls to
4748+ the SQL function --- this allows simple SQL functions to behave like
4749+ macros.
4750+ [7]
4751+
4752+ Prior releases allowed only IPv6 connections and IP data types only
4753+ supported IPv4 addresses. This release adds full IPv6 support in both
4754+ of these areas.
4755+ [8]
4756+
4757+ Several people very familiar with the SSL API have overhauled our SSL
4758+ code to improve SSL key negotiation and error recovery.
4759+ [9]
4760+
4761+ In prior releases, index pages that were left empty because of deleted
4762+ rows could only be reused by rows with index values similar to the
4763+ original rows indexed on that page. In 7.4, VACUUM records empty index
4764+ pages and allows them to be used for any future index rows.
4765+ [10]
4766+
4767+ While prior libpq releases already supported threads, this release
4768+ improves thread safety by fixing some non-thread-safe code that was
4769+ used in the database connection routines.
4770+ [11]
4771+
4772+ This new tool monitors the database statistics tables for
4773+ INSERT/UPDATE/DELETE activity and automatically vacuums tables when
4774+ needed.
4775+ [12]
4776+
4777+ Many array limitations have been removed and they behave more like
4778+ fully-supported data types.
4779+ [13]
4780+
4781+ Server-side autocommit was causing too many problems with languages
4782+ and applications that wanted to control their own autocommit behavior
4783+ so autocommit was removed from the server and added to individual
4784+ client API's as appropriate.
4785+ [14]
4786+
4787+ In prior releases, FETCH 0 would fetch all remaining rows, and MOVE 0
4788+ would move to the end of the cursor.
4789+ [15]
4790+
4791+ Prior releases would return the tuple count passed to the command, not
4792+ the actual number of rows FETCHed or MOVEd.
4793+ [16]
4794+
4795+ In prior releases, a date of 10/20/03 was interpreted as a date in
4796+ October even if the DateStyle specified the day should be first. In
4797+ 7.4, DateStyle is honored when converting such values and will throw
4798+ an error if the date is invalid for the current DateStyle.
4799+ [17]
4800+
4801+ These functions were determined to be no longer useful.
4802+ [18]
4803+
4804+ In prior releases, there was special code so the string 'now' was
4805+ interpreted at "INSERT" time and not at table creation time, but this
4806+ work around didn't cover all cases. Release 7.4 now requires that
4807+ defaults be defined properly using the now() or the special value
4808+ CURRENT_TIMESTAMP. These will work in all situations.
4809+ [19]
4810+
4811+ Same description as above.
4812+ [20]
4813+
4814+ This was done to improve compatibility with other database systems.
4815+ [21]
4816+
4817+ In prior releases, certain rare SSL API error reports were not handled
4818+ correctly. This release fixes those problems. gracefully.
4819+ [22]
4820+
4821+ SSL key renegotiation was happening too frequently, causing poor SSL
4822+ performance. Also, initial key handling was improved.
4823+ [23]
4824+
4825+ This allows easier debugging of deadlock situations.
4826+ [24]
4827+
4828+ This should help prevent "/tmp" directory cleaner administration
4829+ scripts from removing server socket files.
4830+ [25]
4831+
4832+ In prior releases, under certain rare cases, a server crash could
4833+ cause btree indexes to become corrupt. This release removes those last
4834+ few rare cases.
4835+ [26]
4836+
4837+ In prior releases, when a single-page index split into two page, there
4838+ was a brief period when another database session would miss seeing an
4839+ index entry. This failure was possible primarly on multi-cpu machines.
4840+ This release fixes that rare failure case.
4841+ [27]
4842+
4843+ In prior releases, the free space map was not saved when the
4844+ postmaster was stopped, so newly started servers has no free space
4845+ information. This release saves the free space map, which is loaded
4846+ when the server is restarted.
4847+ [28]
4848+
4849+ In prior releases, functions returning allocated memory would not free
4850+ it until the query completed. This release allows the freeing of
4851+ function-allocated memory when the function call completes, reducing
4852+ the total memory used by functions.
4853+ [29]
4854+
4855+ There were several inefficiencies in the way the GEQO optimizer
4856+ managed potential query paths. This release fixes this.
4857+ [30]
4858+
4859+ For shared libraries that require a long time to load, this option is
4860+ available so the library can be pre-loaded in the postmaster and
4861+ inherited by all database sessions.
4862+ [31]
4863+
4864+ Prior releases suffered from poor hash index performance, particularly
4865+ for high concurrency situations. This release fixes that, and the
4866+ development group is interested in reports comparing btree and hash
4867+ index performance.
4868+ [32]
4869+
4870+ Certain CPU's perform faster data copies when addresses are 32-bit
4871+ aligned.
4872+ [33]
4873+
4874+ NUMERIC used to be stored in base-100. The new code uses base-10000,
4875+ for significantly better performance.
4876+ [34]
4877+
4878+ This was done so most parameters that control the server logs being
4879+ with log_.