1 ------------------------------------------------------------------------
3 ------------------------------------------------------------------------
7 This release shows the continued growth of PostgreSQL. There are more
8 changes in 7.0 than in any previous release. Don't be concerned this
9 is a dot-zero release. We do our best to put out only solid releases,
10 and this one is no exception.
12 Major changes in this release:
15 Foreign keys are now implemented, with the exception of PARTIAL
16 MATCH foreign keys. Many users have been asking for this
17 feature, and we are pleased to offer it.
20 Continuing on work started a year ago, the optimizer has been
21 overhauled, allowing improved query execution and better
22 performance with less memory usage.
25 psql, our interactive terminal monitor, has been updated with a
26 variety of new features. See the psql manual page for details.
29 In 7.1, we plan to have outer joins, storage for very long
30 rows, and a write-ahead logging system.
34 A dump/restore using pg_dump is required for those wishing to migrate
35 data from any previous release. For those upgrading from 6.5.*, you
36 can use pg_upgrade to upgrade to this release.
41 Prevent function calls with more than maximum number of arguments (Tom)
42 Improve CASE construct (Tom)
43 Fix SELECT coalesce(f1,0) FROM int4_tbl GROUP BY f1 (Tom)
44 Fix SELECT sentence.words[0] FROM sentence GROUP BY sentence.words[0] (Tom)
45 Fix GROUP BY scan bug (Tom)
46 Improvements in SQL grammar processing (Tom)
47 Fix for views involved in INSERT ... SELECT ... (Tom)
48 Fix for SELECT a/2, a/2 FROM test_missing_target GROUP BY a/2 (Tom)
49 Fix for subselects in INSERT ... SELECT (Tom)
50 Prevent INSERT ... SELECT ... ORDER BY (Tom)
51 Fixes for relations greater than 2GB, including vacuum
52 Improve communication of system table changes to other running backends (Tom)
53 Improve communication of user table modifications to other running backends (Tom)
54 Fix handling of temp tables in complex situations (Bruce, Tom)
55 Allow table locking when tables opened, improving concurrent reliability (Tom)
56 Properly quote sequence names in pg_dump (Ross J. Reedstrom)
57 Prevent DROP DATABASE while others accessing
58 Prevent any rows from being returned by GROUP BY if no rows processed (Tom)
59 Fix SELECT COUNT(1) FROM table WHERE ...' if no rows matching WHERE (Tom)
60 Fix pg_upgrade so it works for MVCC(Tom)
61 Fix for SELECT ... WHERE x IN (SELECT ... HAVING SUM(x) > 1) (Tom)
62 Fix for "f1 datetime DEFAULT 'now'" (Tom)
63 Fix problems with CURRENT_DATE used in DEFAULT (Tom)
64 Allow comment-only lines, and ;;; lines too. (Tom)
65 Improve recovery after failed disk writes, disk full (Hiroshi)
66 Fix cases where table is mentioned in FROM but not joined (Tom)
67 Allow HAVING clause without aggregate functions (Tom)
68 Fix for "--" comment and no trailing newline, as seen in Perl
69 Improve pg_dump failure error reports (Bruce)
70 Allow sorts and hashes to exceed 2GB file sizes (Tom)
71 Fix for pg_dump dumping of inherited rules (Tom)
72 Fix for NULL handling comparisons (Tom)
73 Fix inconsistent state caused by failed CREATE/DROP commands (Hiroshi)
74 Fix for dbname with dash
75 Prevent DROP INDEX from interfering with other backends (Tom)
76 Fix file descriptor leak in verify_password()
77 Fix for "Unable to identify an operator =$" problem
78 Fix ODBC so no segfault if CommLog and Debug enabled (Dirk Niggemann)
79 Fix for recursive exit call (Massimo)
80 Fix for extra-long timezones (Jeroen van Vianen)
81 Make pg_dump preserve primary key information (Peter E)
82 Prevent databases with single quotes (Peter E)
83 Prevent DROP DATABASE inside transaction (Peter E)
84 ecpg memory leak fixes (Stephen Birch)
85 Fix for SELECT null::text, SELECT int4fac(null) and SELECT 2 + (null) (Tom)
86 Y2K timestamp fix (Massimo)
87 Fix for VACUUM 'HEAP_MOVED_IN was not expected' errors (Tom)
88 Fix for views with tables/columns containing spaces (Tom)
89 Prevent permissions on indexes (Peter E)
90 Fix for spinlock stuck problem when error is generated (Hiroshi)
92 Fix handling of NULL constraint conditions (Tom)
93 Fix memory leak in odbc driver (Nick Gorham)
94 Fix for permission check on UNION tables (Tom)
95 Fix to allow SELECT 'a' LIKE 'a' (Tom)
96 Fix for SELECT 1 + NULL (Tom)
98 Fix log() on numeric type (Tom)
99 Deprecate ':' and ';' operators
100 Allow vacuum of temporary tables
101 Disallow inherited columns with the same name as new columns
102 Recover or force failure when disk space is exhausted(Hiroshi)
103 Fix INSERT INTO ... SELECT with AS columns matching result columns
104 Fix INSERT ... SELECT ... GROUP BY groups by target columns not source columns(Tom)
105 Fix CREATE TABLE test (a char(5) DEFAULT text '', b int4) with INSERT(Tom)
107 Fix CREATE TABLE x AS SELECT 1 UNION SELECT 2
108 Fix CREATE TABLE test(col char(2) DEFAULT user)
109 Fix mismatched types in CREATE TABLE ... DEFAULT
110 Fix SELECT * FROM pg_class where oid in (0,-1)
111 Fix SELECT COUNT('asdf') FROM pg_class WHERE oid=12
112 Prevent user who can create databases can modifying pg_database table(Peter E)
113 Fix btree to give a useful elog when key > 1/2 (page - overhead)(Tom)
114 Fix INSERT of 0.0 into DECIMAL(4,4) field(Tom)
118 New CLI interface include file sqlcli.h, based on SQL3/SQL98
119 Remove all limits on query length, row length limit still exists (Tom)
120 Update jdbc protocol to 2.0 (<ulink url="mailto:jens@jens.de">Jens Glaser</ulink>)
121 Add TRUNCATE command to quickly truncate relation (Mike Mascari)
122 Fix to give super user and createdb user proper update catalog rights (Peter E)
123 Allow ecpg bool variables to have NULL values (Christof)
124 Issue ecpg error if NULL value is returned to variable with no NULL
126 Allow ^C to cancel COPY command (Massimo)
127 Add SET FSYNC and SHOW PG_OPTIONS commands(Massimo)
128 Function name overloading for dynamically-loaded C functions (Frankpitt)
129 Add CmdTuples() to libpq++(Vince)
130 New CREATE CONSTRAINT TRIGGER and SET CONSTRAINTS commands(Jan)
131 Allow CREATE FUNCTION/WITH clause to be used for all language types
132 configure --enable-debug adds -g (Peter E)
133 configure --disable-debug removes -g (Peter E)
134 Allow more complex default expressions (Tom)
135 First real FOREIGN KEY constraint trigger functionality (Jan)
136 Add FOREIGN KEY ... MATCH FULL ... ON DELETE CASCADE (Jan)
137 Add FOREIGN KEY ... MATCH <unspecified> referential actions (Don Baccus)
138 Allow WHERE restriction on ctid (physical heap location) (Hiroshi)
139 Move pginterface from contrib to interface directory, rename to pgeasy (Bruce)
140 Add DEC and SESSION_USER as reserved words (Thomas)
141 Require SELECT DISTINCT target list to have all ORDER BY columns (Tom)
142 Add Oracle's COMMENT ON command (<ulink url="mailto:mascarim@yahoo">Mike Mascari</ulink>)
143 libpq's PQsetNoticeProcessor function now returns previous hook(Peter E)
144 Prevent PQsetNoticeProcessor from being set to NULL (Peter E)
145 Make USING in COPY optional (Bruce)
146 Allow subselects in the target list (Tom)
147 Allow subselects on the left side of comparison operators (Tom)
148 New parallel regression test (Jan)
149 Change backend-side COPY to write files with permissions 644 not 666 (Tom)
150 Force permissions on PGDATA directory to be secure, even if it exists (Tom)
151 Added psql LASTOID variable to return last inserted oid (Peter E)
152 Allow concurrent vacuum and remove pg_vlock vacuum lock file (Tom)
153 Add permissions check so only Postgres superuser or table owner can
155 New libpq functions to allow asynchronous connections: PQconnectStart(),
156 PQconnectPoll(), PQresetStart(), PQresetPoll(), PQsetenvStart(),
157 PQsetenvPoll(), PQsetenvAbort (Ewan Mellor)
158 New libpq PQsetenv() function (Ewan Mellor)
159 create/alter user extension (Peter E)
160 New postmaster.pid and postmaster.opts under $PGDATA (Tatsuo)
161 New scripts for create/drop user/db (Peter E)
162 Major psql overhaul(Peter E)
163 Add const to libpq interface(Peter E)
164 New libpq function PQoidValue (Peter E)
165 Show specific non-aggregate causing problem with GROUP BY (Tom)
166 Make changes to pg_shadow recreate pg_pwd file (Peter E)
167 Add aggregate(DISTINCT ...) (Tom)
168 Allow flag to control COPY input/output of NULLs (Peter E)
169 Make postgres user have a password by default (Peter E)
170 Add CREATE/ALTER/DROP GROUP (Peter E)
171 All administration scripts now support --long options (Peter E, Karel)
172 Vacuumdb script now supports --all option (Peter E)
173 ecpg new portable FETCH syntax
174 Add ecpg EXEC SQL IFDEF, EXEC SQL IFNDEF, EXEC SQL ELSE, EXEC SQL ELIF
175 and EXEC SQL ENDIF directives
176 Add pg_ctl script to control backend startup (Tatsuo)
177 Add postmaster.opts.default file to store startup flags (Tatsuo)
178 Allow --with-mb=SQL_ASCII
179 Increase maximum number of index keys to 16 (Bruce)
180 Increase maximum number of function arguments to 16 (Bruce)
181 Allow configuration of maximum number of index keys and arguments (Bruce)
182 Allow unprivileged users to change their passwords (Peter E)
183 Password authentication enabled; required for new users (Peter E)
184 Disallow dropping a user who owns a database (Peter E)
185 Change initdb option --with-mb to --enable-multibyte
186 Add option for initdb to prompts for superuser password (Peter E)
187 Allow complex type casts like col::numeric(9,2) and col::int2::float8 (Tom)
188 Updated user interfaces on initdb, initlocation, pg_dump, ipcclean (Peter E)
189 New pg_char_to_encoding() and pg_encoding_to_char() functions (Tatsuo)
190 Libpq non-blocking mode (Alfred Perlstein)
191 Improve conversion of types in casts that don't specify a length
192 New plperl internal programming language (Mark Hollomon)
193 Allow COPY IN to read file that do not end with a newline (Tom)
194 Indicate when long identifiers are truncated (Tom)
195 Allow aggregates to use type equivalency (Peter E)
196 Add Oracle's to_char(), to_date(), to_datetime(), to_timestamp(), to_number()
197 conversion functions (Karel Zak <zakkr@zf.jcu.cz>)
198 Add SELECT DISTINCT ON (expr [, expr ...]) targetlist ... (Tom)
199 Check to be sure ORDER BY is compatible with the DISTINCT operation (Tom)
200 Add NUMERIC and int8 types to ODBC
201 Improve EXPLAIN results for Append, Group, Agg, Unique (Tom)
202 Add ALTER TABLE ... ADD FOREIGN KEY (Stephan Szabo)
203 Allow SELECT .. FOR UPDATE in PL/pgSQL (Hiroshi)
204 Enable backward sequential scan even after reaching EOF (Hiroshi)
205 Add btree indexing of boolean values, >= and <= (Don Baccus)
206 Print current line number when COPY FROM fails (Massimo)
207 Recognize special case of POSIX time zone: "GMT+8" and "GMT-8" (Thomas)
208 Add DEC as synonym for "DECIMAL" (Thomas)
209 Add SESSION_USER as SQL92 keyword, same as CURRENT_USER (Thomas)
210 Implement column aliases (aka correlation names) and join syntax (Thomas)
211 Allow queries like SELECT a FROM t1 tx (a) (Thomas)
212 Allow queries like SELECT * FROM t1 NATURAL JOIN t2 (Thomas)
213 Make INTERVAL reserved word allowed as a column identifier (Thomas)
214 Implement REINDEX command (Hiroshi)
215 Accept ALL in aggregate function SUM(ALL col) (Tom)
216 Prevent GROUP BY from using column aliases (Tom)
217 New psql \encoding option (Tatsuo)
218 Allow PQrequestCancel() to terminate when in waiting-for-lock state (Hiroshi)
219 Allow negation of a negative number in all cases
220 Add ecpg descriptors (Christof, Michael)
221 Allow CREATE VIEW v AS SELECT f1::char(8) FROM tbl
222 Allow casts with length, like foo::char(8)
223 New libpq functions PQsetClientEncoding(), PQclientEncoding() (Tatsuo)
224 Add support for SJIS user defined characters (Tatsuo)
225 Larger views/rules supported
226 Make libpq's PQconndefaults() thread-safe (Tom)
227 Disable // as comment to be ANSI conforming, should use -- (Tom)
228 Allow column aliases on views CREATE VIEW name (collist)
229 Fixes for views with subqueries (Tom)
230 Allow UPDATE table SET fld = (SELECT ...) (Tom)
231 SET command options no longer require quotes
232 Update pgaccess to 0.98.5
234 New pg_options.sample file
235 New SET FSYNC command (Massimo)
236 Allow pg_descriptions when creating tables
237 Allow pg_descriptions when creating types, columns, and functions
238 Allow psql \copy to allow delimiters(Peter E)
239 Allow psql to print nulls as distinct from "" [null](Peter E)
243 Many array fixes (Tom)
244 Allow bare column names to be subscripted as arrays (Tom)
245 Improve type casting of int and float constants (Tom)
246 Cleanups for int8 inputs, range checking, and type conversion (Tom)
247 Fix for SELECT timespan('21:11:26'::time) (Tom)
248 Fix for netmask('x.x.x.x/0') is 255.255.255.255 instead of 0.0.0.0
250 Add btree index on NUMERIC(Jan)
251 Perl fix for large objects containing NUL characters (Douglas Thomson)
252 ODBC fix for for large objects (free)
253 Fix indexing of cidr data type
254 Fix for Ethernet MAC addresses (macaddr type) comparisons
255 Fix for date/time types when overflows happened in computations (Tom)
256 Allow array on int8 (Peter E)
257 Fix for rounding/overflow of NUMERIC type, like NUMERIC(4,4) (Tom)
259 Fix bugs in NUMERIC ceil() and floor() functions (Tom)
260 Make char_length()/octet_length including trailing blanks (Tom)
261 Made abstime/reltime use int4 instead of time_t (Peter E)
262 New lztext data type for compressed text fields
263 Revise code to handle coercion of int and float constants (Tom)
264 New C-routines to implement a BIT and BIT VARYING type in /contrib
266 NUMERIC now accepts scientific notation (Tom)
267 NUMERIC to int4 rounds (Tom)
268 Convert float4/8 to NUMERIC properly (Tom)
269 Allow type conversion with NUMERIC (Thomas)
270 Make ISO date style (2000-02-16 09:33) the default (Thomas)
271 Add NATIONAL CHAR [ VARYING ]
272 Allow NUMERIC round and trunc to accept negative scales (Tom)
273 New TIME WITH TIME ZONE type (Thomas)
274 Add MAX()/MIN() on time type (Thomas)
275 Add abs(), mod(), fac() for int8 (Thomas)
276 Add round(), sqrt(), cbrt(), pow()
277 Rename NUMERIC power() to pow()
278 Improved TRANSLATE() function
279 Allow X=-Y operators (Tom)
280 Add exp() and ln() as NUMERIC types
281 Allow SELECT float8(COUNT(*)) / (SELECT COUNT(*) FROM int4_tbl) FROM int4_tbl
283 Allow LOCALE to use indexes in regular expression searches(Tom)
284 Allow creation of functional indexes to use default types
288 Prevent exponential space consumption with many AND's and OR's (Tom)
289 Collect attribute selectivity values for system columns (Tom)
290 Reduce memory usage of aggregates (Tom)
291 Fix for LIKE optimization to use indexes with multi-byte encodings (Tom)
292 Fix r-tree index optimizer selectivity (Thomas)
293 Improve optimizer selectivity computations and functions (Tom)
294 Optimize btree searching for cases where many equal keys exist (Tom)
295 Enable fast LIKE index processing only if index present (Tom)
296 Re-use free space on index pages with duplicates (Tom)
297 Improve hash join processing (Tom)
298 Prevent descending sort if result is already sorted(Hiroshi)
299 Allow commuting of index scan query qualifications (Tom)
300 Prefer index scans in cases where ORDER BY/GROUP BY is required (Tom)
301 Allocate large memory requests in fix-sized chunks for performance (Tom)
302 Fix vacuum's performance by reducing memory allocation requests (Tom)
303 Implement constant-expression simplification (Bernard Frankpitt, Tom)
304 Allow more than first column to be used to determine start of index scan
306 Prevent quadruple use of disk space when doing internal sorting (Tom)
307 Faster sorting by calling fewer functions (Tom)
308 Create system indexes to match all system caches (Bruce, Hiroshi)
309 Make system caches use system indexes(Bruce)
310 Make all system indexes unique(Bruce)
311 Improve pg_statistics management for VACUUM speed improvement (Tom)
312 Flush backend cache less frequently (Tom, Hiroshi)
313 COPY now reuses previous memory allocation, improving performance (Tom)
314 Improve optimization cost estimation (Tom)
315 Improve optimizer estimate of range queries x > lowbound AND x < highbound (Tom)
316 Use DNF instead of CNF where appropriate (Tom, Taral)
317 Further cleanup for OR-of-AND WHERE-clauses (Tom)
318 Make use of index in OR clauses (x = 1 AND y = 2) OR (x = 2 AND y = 4) (Tom)
319 Smarter optimizer computations for random index page access (Tom)
320 New SET variable to control optimizer costs (Tom)
321 Optimizer queries based on LIMIT, OFFSET, and EXISTS qualifications (Tom)
322 Reduce optimizer internal housekeeping of join paths for speedup (Tom)
323 Major subquery speedup (Tom)
324 Fewer fsync writes when fsync is not disabled(Tom)
325 Improved LIKE optimizer estimates(Tom)
326 Prevent fsync in SELECT-only queries(Vadim)
327 Make index creation use psort code, because it is now faster(Tom)
328 Allow creation of sort temp tables > 1 Gig
332 Fix for linux PPC compile
333 New generic expression-tree-walker subroutine (Tom)
334 Change form() to varargform() to prevent portability problems.
335 Improved range checking for large integers on Alphas
336 Clean up #include in /include directory (Bruce)
337 Add scripts for checking includes (Bruce)
338 Remove un-needed #include's from *.c files (Bruce)
339 Change #include's to use <> and "" as appropriate (Bruce)
340 Enable WIN32 compilation of libpq
341 Alpha spinlock fix from <ulink url="mailto:gatgul@voicenet.com">Uncle George</ulink>
342 Overhaul of optimizer data structures (Tom)
343 Fix to cygipc library (Yutaka Tanida)
344 Allow pgsql to work on newer Cygwin snapshots(Dan)
345 New catalog version number (Tom)
347 Rename heap_replace to heap_update
348 Update for QNX (Dr. Andreas Kardos)
349 New platform-specific regression handling (Tom)
350 Rename oid8 -> oidvector and int28 -> int2vector (Bruce)
351 Included all yacc and lex files into the distribution (Peter E.)
352 Remove lextest, no longer needed (Peter E)
353 Fix for libpq and psql on Win32 (Magnus)
354 Internally change datetime and timespan into timestamp and interval (Thomas)
355 Fix for plpgsql on BSDI
356 Add SQL_ASCII test case to the regression test (Tatsuo)
357 configure --with-mb now deprecated (Tatsuo)
359 NetBSD fixes <ulink url="mailto:lamj@stat.cmu.edu">Johnny C. Lam</ulink>
360 Fixes for Alpha compiles
361 New multibyte encodings
367 This is basically a cleanup release for 6.5.2. We have added a new pgaccess
368 that was missing in 6.5.2, and installed an NT-specific fix.
372 A dump/restore is not required for those running 6.5.*.
376 Updated version of pgaccess 0.98
379 ------------------------------------------------------------------------
382 ------------------------------------------------------------------------
386 This is basically a cleanup release for 6.5.1. We have fixed a variety of
387 problems reported by 6.5.1 users.
391 A dump/restore is not required for those running 6.5.*.
395 subselect+CASE fixes(Tom)
396 Add SHLIB_LINK setting for solaris_i386 and solaris_sparc ports(Daren Sefcik)
397 Fixes for CASE in WHERE join clauses(Tom)
398 Fix BTScan abort(Tom)
399 Repair the check for redundant UNIQUE and PRIMARY KEY indices(Thomas)
400 Improve it so that it checks for multi-column constraints(Thomas)
401 Fix for Win32 making problem with MB enabled(Hiroki Kataoka)
402 Allow BSD yacc and bison to compile pl code(Bruce)
403 Fix SET NAMES working
405 Fix vacuum's memory consumption(Hiroshi,Tatsuo)
406 Reduce the total memory consumption of vacuum(Tom)
407 Fix for timestamp(datetime)
408 Rule deparsing bugfixes(Tom)
409 Fix quoting problems in mkMakefile.tcldefs.sh.in and mkMakefile.tkdefs.sh.in(Tom)
410 This is to re-use space on index pages freed by vacuum(Vadim)
411 document -x for pg_dump(Bruce)
412 Fix for unary operators in rule deparser(Tom)
413 Comment out FileUnlink of excess segments during mdtruncate()(Tom)
414 Irix linking fix from Yu Cao yucao@falcon.kla-tencor.com
415 Repair logic error in LIKE: should not return LIKE_ABORT
416 when reach end of pattern before end of text(Tom)
417 Repair incorrect cleanup of heap memory allocation during transaction abort(Tom)
418 Updated version of pgaccess 0.98
420 ------------------------------------------------------------------------
421 Release Notes Release 6.5.1
423 ------------------------------------------------------------------------
427 This is basically a cleanup release for 6.5. We have fixed a variety of
428 problems reported by 6.5 users.
432 A dump/restore is not required for those running 6.5.
437 Portability fixes for linux_ppc, Irix, linux_alpha, OpenBSD, alpha
438 Remove QUERY_LIMIT, use SELECT...LIMIT
439 Fix for EXPLAIN on inheritance(Tom)
440 Patch to allow vacuum on multi-segment tables(Hiroshi)
441 R-Tree optimizer selectivity fix(Tom)
442 ACL file descriptor leak fix(Atsushi Ogawa)
443 New expresssion subtree code(Tom)
444 Avoid disk writes for read-only transactions(Vadim)
445 Fix for removal of temp tables if last transaction was aborted(Bruce)
446 Fix to prevent too large tuple from being created(Bruce)
448 Allow port numbers 32k - 64k(Bruce)
449 Add ^ precidence(Bruce)
450 Rename sort files called pg_temp to pg_sorttemp(Bruce)
451 Fix for microseconds in time values(Tom)
452 Tutorial source cleanup
454 Fix for sorting of NULL's in some cases(Tom)
455 Shared library dependencies fixed (Tom)
456 Fixed glitches affecting GROUP BY in subselects(Tom)
457 Fix some compiler warnings (Tomoaki Nishiyama)
458 Add Win1250 (Czech) support (Pavel Behal)
460 ------------------------------------------------------------------------
461 Release 6.5.2 Release 6.5
463 ------------------------------------------------------------------------
467 This release marks a major step in the development team's mastery of the
468 source code we inherited from Berkeley. You will see we are now easily
469 adding major features, thanks to the increasing size and experience of our
470 world-wide development team.
472 Here is a brief summary of the more notable changes:
474 Multi-version concurrency control(MVCC)
476 This removes our old table-level locking, and replaces it with a
477 locking system that is superior to most commercial database systems. In
478 a traditional system, each row that is modified is locked until
479 committed, preventing reads by other users. MVCC uses the natural
480 multi-version nature of PostgreSQL to allow readers to continue reading
481 consistent data during writer activity. Writers continue to use the
482 compact pg_log transaction system. This is all performed without having
483 to allocate a lock for every row like traditional database systems. So,
484 basically, we no longer are restricted by simple table-level locking;
485 we have something better than row-level locking.
487 Hot backups from pg_dump
489 pg_dump takes advantage of the new MVCC features to give a consistant
490 database dump/backup while the database stays online and available for
495 We now have a true numeric data type, with user-specified precision.
499 Temporary tables are guaranteed to have unique names within a database
500 session, and are destroyed on session exit.
504 We now have CASE, INTERSECT, and EXCEPT statement support. We have new
505 LIMIT/OFFSET, SET TRANSACTION ISOLATION LEVEL, SELECT ... FOR UPDATE,
506 and an improved LOCK TABLE command.
510 We continue to speed up PostgreSQL, thanks to the variety of talents
511 within our team. We have sped up memory allocation, optimization, table
512 joins, and row transfer routines.
516 We continue to expand our port list, this time including WinNT/ix86 and
521 Most interfaces have new versions, and existing functionality has been
526 New and updated material is present throughout the documentation. New
527 FAQs have been contributed for SGI and AIX platforms. The Tutorial has
528 introductory information on SQL from Stefan Simkovics. For the User's
529 Guide, there are reference pages covering the postmaster and more
530 utility programs, and a new appendix contains details on date/time
531 behavior. The Administrator's Guide has a new chapter on
532 troubleshooting from Tom Lane. And the Programmer's Guide has a
533 description of query processing, also from Stefan, and details on
534 obtaining the Postgres source tree via anonymous CVS and CVSup.
538 A dump/restore using pg_dump is required for those wishing to migrate data
539 from any previous release of Postgres. pg_upgrade can not be used to upgrade
540 to this release because the on-disk structure of the tables has changed
541 compared to previous releases.
543 The new Multi-Version Concurrency Control (MVCC) features can give somewhat
544 different behaviors in multi-user environments. Read and understand the
545 following section to ensure that your existing applications will give you
546 the behavior you need.
548 Multi-Version Concurrency Control
550 Because readers in 6.5 don't lock data, regardless of transaction isolation
551 level, data read by one transaction can be overwritten by another. In other
552 words, if a row is returned by SELECT it doesn't mean that this row really
553 exists at the time it is returned (i.e. sometime after the statement or
554 transaction began) nor that the row is protected from being deleted or
555 updated by concurrent transactions before the current transaction does a
558 To ensure the actual existence of a row and protect it against concurrent
559 updates one must use SELECT FOR UPDATE or an appropriate LOCK TABLE
560 statement. This should be taken into account when porting applications from
561 previous releases of Postgres and other environments.
563 Keep the above in mind if you are using contrib/refint.* triggers for
564 referential integrity. Additional technics are required now. One way is to
565 use LOCK parent_table IN SHARE ROW EXCLUSIVE MODE command if a transaction
566 is going to update/delete a primary key and use LOCK parent_table IN SHARE
567 MODE command if a transaction is going to update/insert a foreign key.
569 Note: Note that if you run a transaction in SERIALIZABLE mode then
570 you must execute the LOCK commands above before execution of any
571 DML statement (SELECT/INSERT/DELETE/UPDATE/FETCH/COPY_TO) in the
574 These inconveniences will disappear in the future when the ability to read
575 dirty (uncommitted) data (regardless of isolation level) and true
576 referential integrity will be implemented.
582 Fix text<->float8 and text<->float4 conversion functions(Thomas)
583 Fix for creating tables with mixed-case constraints(Billy)
584 Change exp()/pow() behavior to generate error on underflow/overflow(Jan)
585 Fix bug in pg_dump -z
586 Memory overrun cleanups(Tatsuo)
587 Fix for lo_import crash(Tatsuo)
588 Adjust handling of data type names to suppress double quotes(Thomas)
589 Use type coersion for matching columns and DEFAULT(Thomas)
590 Fix deadlock so it only checks once after one second of sleep(Bruce)
591 Fixes for aggregates and PL/pgsql(Hiroshi)
592 Fix for subquery crash(Vadim)
593 Fix for libpq function PQfnumber and case-insensitive names(Bahman Rafatjoo)
594 Fix for large object write-in-middle, no extra block, memory consumption(Tatsuo)
595 Fix for pg_dump -d or -D and quote special characters in INSERT
596 Repair serious problems with dynahash(Tom)
597 Fix INET/CIDR portability problems
598 Fix problem with selectivity error in ALTER TABLE ADD COLUMN(Bruce)
599 Fix executor so mergejoin of different column types works(Tom)
600 Fix for Alpha OR selectivity bug
601 Fix OR index selectivity problem(Bruce)
602 Fix so \d shows proper length for char()/varchar()(Ryan)
603 Fix tutorial code(Clark)
604 Improve destroyuser checking(Oliver)
605 Fix for Kerberos(Rodney McDuff)
606 Fix for dropping database while dirty buffers(Bruce)
607 Fix so sequence nextval() can be case-sensitive(Bruce)
609 Drop buffers before destroying database files(Bruce)
610 Fix case where executor evaluates functions twice(Tatsuo)
611 Allow sequence nextval actions to be case-sensitive(Bruce)
612 Fix optimizer indexing not working for negative numbers(Bruce)
613 Fix for memory leak in executor with fjIsNull
614 Fix for aggregate memory leaks(Erik Riedel)
615 Allow username containing a dash GRANT permissions
616 Cleanup of NULL in inet types
617 Clean up system table bugs(Tom)
618 Fix problems of PAGER and \? command(Masaaki Sakaida)
619 Reduce default multi-segment file size limit to 1GB(Peter)
620 Fix for dumping of CREATE OPERATOR(Tom)
621 Fix for backward scanning of cursors(Hiroshi Inoue)
622 Fix for COPY FROM STDIN when using \i(Tom)
623 Fix for subselect is compared inside an expression(Jan)
624 Fix handling of error reporting while returning rows(Tom)
625 Fix problems with reference to array types(Tom,Jan)
626 Prevent UPDATE SET oid(Jan)
627 Fix pg_dump so -t option can handle case-sensitive tablenames
628 Fixes for GROUP BY in special cases(Tom, Jan)
629 Fix for memory leak in failed queries(Tom)
630 DEFAULT now supports mixed-case identifiers(Tom)
631 Fix for multi-segment uses of DROP/RENAME table, indexes(Ole Gjerde)
632 Disable use of pg_dump with both -o and -d options(Bruce)
633 Allow pg_dump to properly dump GROUP permissions(Bruce)
634 Fix GROUP BY in INSERT INTO table SELECT * FROM table2(Jan)
635 Fix for computations in views(Jan)
636 Fix for aggregates on array indexes(Tom)
637 Fix for DEFAULT handles single quotes in value requiring too many quotes
638 Fix security problem with non-super users importing/exporting large objects(Tom)
639 Rollback of transaction that creates table cleaned up properly(Tom)
640 Fix to allow long table and column names to generate proper serial names(Tom)
644 Add "vacuumdb" utility
645 Speed up libpq by allocating memory better(Tom)
646 EXPLAIN all indices used(Tom)
647 Implement CASE, COALESCE, NULLIF expression(Thomas)
648 New pg_dump table output format(Constantin)
649 Add string min()/max() functions(Thomas)
650 Extend new type coersion techniques to aggregates(Thomas)
651 New moddatetime contrib(Terry)
652 Update to pgaccess 0.96(Constantin)
653 Add routines for single-byte "char" type(Thomas)
654 Improved substr() function(Thomas)
655 Improved multi-byte handling(Tatsuo)
656 Multi-version concurrency control/MVCC(Vadim)
657 New Serialized mode(Vadim)
658 Fix for tables over 2gigs(Peter)
659 New SET TRANSACTION ISOLATION LEVEL(Vadim)
660 New LOCK TABLE IN ... MODE(Vadim)
661 Update ODBC driver(Byron)
662 New NUMERIC data type(Jan)
663 New SELECT FOR UPDATE(Vadim)
664 Handle "NaN" and "Infinity" for input values(Jan)
665 Improved date/year handling(Thomas)
666 Improved handling of backend connections(Magnus)
667 New options ELOG_TIMESTAMPS and USE_SYSLOG options for log files(Massimo)
668 New TCL_ARRAYS option(Massimo)
669 New INTERSECT and EXCEPT(Stefan)
670 New pg_index.indisprimary for primary key tracking(D'Arcy)
671 New pg_dump option to allow dropping of tables before creation(Brook)
672 Speedup of row output routines(Tom)
673 New READ COMMITTED isolation level(Vadim)
674 New TEMP tables/indexes(Bruce)
675 Prevent sorting if result is already sorted(Jan)
676 New memory allocation optimization(Jan)
677 Allow psql to do \p\g(Bruce)
678 Allow multiple rule actions(Jan)
679 Added LIMIT/OFFSET functionality(Jan)
680 Improve optimizer when joining a large number of tables(Bruce)
681 New intro to SQL from S. Simkovics' Master's Thesis (Stefan, Thomas)
682 New intro to backend processing from S. Simkovics' Master's Thesis (Stefan)
683 Improved int8 support(Ryan Bradetich, Thomas, Tom)
684 New routines to convert between int8 and text/varchar types(Thomas)
685 New bushy plans, where meta-tables are joined(Bruce)
686 Enable right-hand queries by default(Bruce)
687 Allow reliable maximum number of backends to be set at configure time
688 (--with-maxbackends and postmaster switch (-N backends))(Tom)
689 GEQO default now 10 tables because of optimizer speedups(Tom)
690 Allow NULL=Var for MS-SQL portability(Michael, Bruce)
691 Modify contrib check_primary_key() so either "automatic" or "dependent"(Anand)
692 Allow psql \d on a view show query(Ryan)
693 Speedup for LIKE(Bruce)
694 Ecpg fixes/features, see src/interfaces/ecpg/ChangeLog file(Michael)
695 JDBC fixes/features, see src/interfaces/jdbc/CHANGELOG(Peter)
696 Make % operator have precedence like /(Bruce)
697 Add new postgres -O option to allow system table structure changes(Bruce)
698 Update contrib/pginterface/findoidjoins script(Tom)
699 Major speedup in vacuum of deleted rows with indexes(Vadim)
700 Allow non-SQL functions to run different versions based on arguments(Tom)
701 Add -E option that shows actual queries sent by \dt and friends(Masaaki Sakaida)
702 Add version number in startup banners for psql(Masaaki Sakaida)
703 New contrib/vacuumlo removes large objects not referenced(Peter)
704 New initialization for table sizes so non-vacuumed tables perform better(Tom)
705 Improve error messages when a connection is rejected(Tom)
706 Support for arrays of char() and varchar() fields(Massimo)
707 Overhaul of hash code to increase reliability and performance(Tom)
708 Update to PyGreSQL 2.4(D'Arcy)
709 Changed debug options so -d4 and -d5 produce different node displays(Jan)
710 New pg_options: pretty_plan, pretty_parse, pretty_rewritten(Jan)
711 Better optimization statistics for system table access(Tom)
712 Better handling of non-default block sizes(Massimo)
713 Improve GEQO optimizer memory consumption(Tom)
714 UNION now suppports ORDER BY of columns not in target list(Jan)
715 Major libpq++ improvements(Vince Vielhaber)
716 pg_dump now uses -z(ACL's) as default(Bruce)
717 backend cache, memory speedups(Tom)
718 have pg_dump do everything in one snapshot transaction(Vadim)
719 fix for large object memory leakage, fix for pg_dumping(Tom)
720 INET type now respects netmask for comparisons
721 Make VACUUM ANALYZE only use a readlock(Vadim)
722 Allow VIEWs on UNIONS(Jan)
723 pg_dump now can generate consistent snapshots on active databases(Vadim)
727 Improve port matching(Tom)
728 Portability fixes for SunOS
729 Add NT/Win32 backend port and enable dynamic loading(Magnus and Daniel Horak)
730 New port to Cobalt Qube(Mips) running Linux(Tatsuo)
731 Port to NetBSD/m68k(Mr. Mutsuki Nakajima)
732 Port to NetBSD/sun3(Mr. Mutsuki Nakajima)
733 Port to NetBSD/macppc(Toshimi Aoki)
734 Fix for tcl/tk configuration(Vince)
735 Removed CURRENT keyword for rule queries(Jan)
736 NT dynamic loading now works(Daniel Horak)
737 Add ARM32 support(Andrew McMurry)
738 Better support for HPUX 11 and Unixware
739 Improve file handling to be more uniform, prevent file descriptor leak(Tom)
740 New install commands for plpgsql(Jan)
743 ------------------------------------------------------------------------
744 Release 6.5.1 Release 6.4.2
746 ------------------------------------------------------------------------
750 The 6.4.1 release was improperly packaged. This also has one additional bug
755 A dump/restore is not required for those running 6.4.*.
759 Fix for datetime constant problem on some platforms(Thomas)
761 ------------------------------------------------------------------------
762 Release 6.5 Release 6.4.1
764 ------------------------------------------------------------------------
768 This is basically a cleanup release for 6.4. We have fixed a variety of
769 problems reported by 6.4 users.
773 A dump/restore is not required for those running 6.4.
777 Add pg_dump -N flag to force double quotes around identifiers. This is
779 Fix for NOT in where clause causing crash(Bruce)
780 EXPLAIN VERBOSE coredump fix(Vadim)
781 Fix shared-library problems on Linux
782 Fix test for table existance to allow mixed-case and whitespace in
783 the table name(Thomas)
784 Fix a couple of pg_dump bugs
785 Configure matches template/.similar entries better(Tom)
786 Change builtin function names from SPI_* to spi_*
787 OR WHERE clause fix(Vadim)
788 Fixes for mixed-case table names(Billy)
789 contrib/linux/postgres.init.csh/sh fix(Thomas)
790 libpq memory overrun fix
792 Change exp() behavior to generate error on underflow(Thomas)
793 pg_dump fixes for memory leak, inheritance constraints, layout change
794 update pgaccess to 0.93
795 Fix prototype for 64-bit platforms
796 Multi-byte fixes(Tatsuo)
798 Fix memory overruns(Tatsuo)
799 Fix for lo_import() crash(Bruce)
800 Better search for install program(Tom)
803 Use implicit type coersion for matching DEFAULT values(Thomas)
804 Add routines to help with single-byte (internal) character type(Thomas)
805 Compilation of libpq for Win32 fixes(Magnus)
806 Upgrade to PyGreSQL 2.2(D'Arcy)
808 ------------------------------------------------------------------------
809 Release 6.4.2 Release 6.4
811 ------------------------------------------------------------------------
815 There are many new features and improvements in this release. Thanks to our
816 developers and maintainers, nearly every aspect of the system has received
817 some attention since the previous release. Here is a brief, incomplete
820 * Views and rules are now functional thanks to extensive new code in the
821 rewrite rules system from Jan Wieck. He also wrote a chapter on it for
822 the Programmer's Guide.
824 * Jan also contributed a second procedural language, PL/pgSQL, to go with
825 the original PL/pgTCL procedural language he contributed last release.
827 * We have optional multiple-byte character set support from Tatsuo Iishi
828 to complement our existing locale support.
830 * Client/server communications has been cleaned up, with better support
831 for asynchronous messages and interrupts thanks to Tom Lane.
833 * The parser will now perform automatic type coersion to match arguments
834 to available operators and functions, and to match columns and
835 expressions with target columns. This uses a generic mechanism which
836 supports the type extensibility features of Postgres. There is a new
837 chapter in the User's Guide which covers this topic.
839 * Three new data types have been added. Two types, inet and cidr, support
840 various forms of IP network, subnet, and machine addressing. There is
841 now an 8-byte integer type available on some platforms. See the chapter
842 on data types in the User's Guide for details. A fourth type, serial,
843 is now supported by the parser as an amalgam of the int4 type, a
844 sequence, and a unique index.
846 * Several more SQL92-compatible syntax features have been added,
847 including INSERT DEFAULT VALUES
849 * The automatic configuration and installation system has received some
850 attention, and should be more robust for more platforms than it has
855 A dump/restore using pg_dump or pg_dumpall is required for those wishing to
856 migrate data from any previous release of Postgres.
862 Fix for a tiny memory leak in PQsetdb/PQfinish(Bryan)
863 Remove char2-16 data types, use char/varchar(Darren)
864 Pqfn not handles a NOTICE message(Anders)
865 Reduced busywaiting overhead for spinlocks with many backends (dg)
866 Stuck spinlock detection (dg)
867 Fix up "ISO-style" timespan decoding and encoding(Thomas)
868 Fix problem with table drop after rollback of transaction(Vadim)
869 Change error message and remove non-functional update message(Vadim)
870 Fix for COPY array checking
871 Fix for SELECT 1 UNION SELECT NULL
872 Fix for buffer leaks in large object calls(Pascal)
873 Change owner from oid to int4 type(Bruce)
874 Fix a bug in the oracle compatibility functions btrim() ltrim() and rtrim()
875 Fix for shared invalidation cache overflow(Massimo)
876 Prevent file descriptor leaks in failed COPY's(Bruce)
877 Fix memory leak in libpgtcl's pg_select(Constantin)
878 Fix problems with username/passwords over 8 characters(Tom)
879 Fix problems with handling of asynchronous NOTIFY in backend(Tom)
880 Fix of many bad system table entries(Tom)
884 Upgrade ecpg and ecpglib,see src/interfaces/ecpc/ChangeLog(Michael)
885 Show the index used in an EXPLAIN(Zeugswetter)
886 EXPLAIN invokes rule system and shows plan(s) for rewritten queries(Jan)
887 Multi-byte awareness of many data types and functions, via configure(Tatsuo)
888 New configure --with-mb option(Tatsuo)
889 New initdb --pgencoding option(Tatsuo)
890 New createdb -E multibyte option(Tatsuo)
891 Select version(); now returns PostgreSQL version(Jeroen)
892 Libpq now allows asynchronous clients(Tom)
893 Allow cancel from client of backend query(Tom)
894 Psql now cancels query with Control-C(Tom)
895 Libpq users need not issue dummy queries to get NOTIFY messages(Tom)
896 NOTIFY now sends sender's PID, so you can tell whether it was your own(Tom)
897 PGresult struct now includes associated error message, if any(Tom)
898 Define "tz_hour" and "tz_minute" arguments to date_part()(Thomas)
899 Add routines to convert between varchar and bpchar(Thomas)
900 Add routines to allow sizing of varchar and bpchar into target columns(Thomas)
901 Add bit flags to support timezonehour and minute in data retrieval(Thomas)
902 Allow more variations on valid floating point numbers (e.g. ".1", "1e6")(Thomas)
903 Fixes for unary minus parsing with leading spaces(Thomas)
904 Implement TIMEZONE_HOUR, TIMEZONE_MINUTE per SQL92 specs(Thomas)
905 Check for and properly ignore FOREIGN KEY column constraints(Thomas)
906 Define USER as synonym for CURRENT_USER per SQL92 specs(Thomas)
907 Enable HAVING clause but no fixes elsewhere yet.
908 Make "char" type a synonym for "char(1)" (actually implemented as bpchar)(Thomas)
909 Save string type if specified for DEFAULT clause handling(Thomas)
910 Coerce operations involving different data types(Thomas)
911 Allow some index use for columns of different types(Thomas)
912 Add capabilities for automatic type conversion(Thomas)
913 Cleanups for large objects, so file is truncated on open(Peter)
914 Readline cleanups(Tom)
915 Allow psql \f \ to make spaces as delimiter(Bruce)
916 Pass pg_attribute.atttypmod to the frontend for column field lengths(Tom,Bruce)
917 Msql compatibility library in /contrib(Aldrin)
918 Remove the requirement that ORDER/GROUP BY clause identifiers be
919 included in the target list(David)
920 Convert columns to match columns in UNION clauses(Thomas)
921 Remove fork()/exec() and only do fork()(Bruce)
923 Show backend status on ps command line(only works on some platforms)(Bruce)
924 Pg_hba.conf now has a sameuser option in the database field
925 Make lo_unlink take oid param, not int4
926 New DISABLE_COMPLEX_MACRO for compilers that can't handle our macros(Bruce)
927 Libpgtcl now handles NOTIFY as a Tcl event, need not send dummy queries(Tom)
928 libpgtcl cleanups(Tom)
929 Add -error option to libpgtcl's pg_result command(Tom)
930 New locale patch, see docs/README/locale(Oleg)
931 Fix for pg_dump so CONSTRAINT and CHECK syntax is correct(ccb)
932 New contrib/lo code for large object orphan removal(Peter)
933 New psql command "SET CLIENT_ENCODING TO 'encoding'" for multi-bytes
934 feature, see /doc/README.mb(Tatsuo)
935 /contrib/noupdate code to revoke update permission on a column
936 Libpq can now be compiled on win32(Magnus)
937 Add PQsetdbLogin() in libpq
938 New 8-byte integer type, checked by configure for OS support(Thomas)
939 Better support for quoted table/column names(Thomas)
940 Surround table and column names with double-quotes in pg_dump(Thomas)
941 PQreset() now works with passwords(Tom)
942 Handle case of GROUP BY target list column number out of range(David)
943 Allow UNION in subselects
944 Add auto-size to screen to \d? commands(Bruce)
945 Use UNION to show all \d? results in one query(Bruce)
946 Add \d? field search feature(Bruce)
947 Pg_dump issues fewer \connect requests(Tom)
948 Make pg_dump -z flag work better, document it in manual page(Tom)
949 Add HAVING clause with full support for subselects and unions(Stephan)
950 Full text indexing routines in contrib/fulltextindex(Maarten)
951 Transaction ids now stored in shared memory(Vadim)
952 New PGCLIENTENCODING when issuing COPY command(Tatsuo)
953 Support for SQL92 syntax "SET NAMES"(Tatsuo)
954 Support for LATIN2-5(Tatsuo)
955 Add UNICODE regression test case(Tatsuo)
956 Lock manager cleanup, new locking modes for LLL(Vadim)
957 Allow index use with OR clauses(Bruce)
958 Allows "SELECT NULL ORDER BY 1;"
959 Explain VERBOSE prints the plan, and now pretty-prints the plan to
960 the postmaster log file(Bruce)
961 Add Indices display to \d command(Bruce)
962 Allow GROUP BY on functions(David)
963 New pg_class.relkind for large objects(Bruce)
964 New way to send libpq NOTICE messages to a different location(Tom)
965 New \w write command to psql(Bruce)
966 New /contrib/findoidjoins scans oid columns to find join relationships(Bruce)
967 Allow binary-compatible indices to be considered when checking for valid
968 indices for restriction clauses containing a constant(Thomas)
969 New ISBN/ISSN code in /contrib/isbn_issn
970 Allow NOT LIKE, IN, NOT IN, BETWEEN, and NOT BETWEEN constraint(Thomas)
971 New rewrite system fixes many problems with rules and views(Jan)
972 * Rules on relations work
973 * Event qualifications on insert/update/delete work
974 * New OLD variable to reference CURRENT, CURRENT will be remove in future
975 * Update rules can reference NEW and OLD in rule qualifications/actions
976 * Insert/update/delete rules on views work
977 * Multiple rule actions are now supported, surrounded by parentheses
978 * Regular users can create views/rules on tables they have RULE permits
979 * Rules and views inherit the permissions on the creator
980 * No rules at the column level
981 * No UPDATE NEW/OLD rules
982 * New pg_tables, pg_indexes, pg_rules and pg_views system views
983 * Only a single action on SELECT rules
984 * Total rewrite overhaul, perhaps for 6.5
986 * handle aggregates on views
987 * handle insert into select from view works
988 System indexes are now multi-key(Bruce)
989 Oidint2, oidint4, and oidname types are removed(Bruce)
990 Use system cache for more system table lookups(Bruce)
991 New backend programming language PL/pgSQL in backend/pl(Jan)
992 New SERIAL data type, auto-creates sequence/index(Thomas)
993 Enable assert checking without a recompile(Massimo)
994 User lock enhancements(Massimo)
995 New setval() command to set sequence value(Massimo)
996 Auto-remove unix socket file on startup if no postmaster running(Massimo)
997 Conditional trace package(Massimo)
998 New UNLISTEN command(Massimo)
999 Psql and libpq now compile under win32 using win32.mak(Magnus)
1000 Lo_read no longer stores trailing NULL(Bruce)
1001 Identifiers are now truncated to 31 characters internally(Bruce)
1002 Createuser options now availble on the command line
1003 Code for 64-bit integer supported added, configure tested, int8 type(Thomas)
1004 Prevent file descriptor leaf from failed COPY(Bruce)
1005 New pg_upgrade command(Bruce)
1006 Updated /contrib directories(Massimo)
1007 New CREATE TABLE DEFAULT VALUES statement available(Thomas)
1008 New INSERT INTO TABLE DEFAULT VALUES statement available(Thomas)
1009 New DECLARE and FETCH feature(Thomas)
1010 libpq's internal structures now not exported(Tom)
1011 Allow up to 8 key indexes(Bruce)
1012 Remove ARCHIVE keyword, that is no longer used(Thomas)
1013 pg_dump -n flag to supress quotes around indentifiers
1014 disable system columns for views(Jan)
1015 new INET and CIDR types for network addresses(TomH, Paul)
1016 no more double quotes in psql output
1017 pg_dump now dumps views(Terry)
1018 new SET QUERY_LIMIT(Tatsuo,Jan)
1022 /contrib cleanup(Jun)
1023 Inline some small functions called for every row(Bruce)
1026 Multi-byte regression tests(Soonmyung.)
1027 Remove --disabled options from configure
1028 Define PGDOC to use POSTGRESDIR by default
1029 Make regression optional
1030 Remove extra braces code to pgindent(Bruce)
1031 Add bsdi shared library support(Bruce)
1032 New --without-CXX support configure option(Brook)
1034 Update backend flowchart in tools/backend(Bruce)
1035 Change atttypmod from int16 to int32(Bruce, Tom)
1036 Getrusage() fix for platforms that do not have it(Tom)
1037 Add PQconnectdb, PGUSER, PGPASSWORD to libpq man page
1038 NS32K platform fixes(Phil Nelson, John Buller)
1039 Sco 7/UnixWare 2.x fixes(Billy,others)
1040 Sparc/Solaris 2.5 fixes(Ryan)
1041 Pgbuiltin.3 is obsolete, move to doc files(Thomas)
1042 Even more documention(Thomas)
1043 Nextstep support(Jacek)
1045 pginterface manual page(Bruce)
1046 shared libraries all have version numbers
1047 merged all OS-specific shared library defines into one file
1048 smarter TCL/TK configuration checking(Billy)
1049 smarter perl configuration(Brook)
1050 configure uses supplied install-sh if no install script found(Tom)
1051 new Makefile.shlib for shared library configuration(Tom)
1053 ------------------------------------------------------------------------
1054 Release 6.4.1 Release 6.3.2
1056 ------------------------------------------------------------------------
1060 This is a bugfix release for 6.3.x. Refer to the release notes for v6.3 for
1061 a more complete summary of new features.
1065 * Repairs automatic configuration support for some platforms, including
1066 Linux, from breakage inadvertently introduced in v6.3.1.
1068 * Correctly handles function calls on the left side of BETWEEN and LIKE
1071 A dump/restore is NOT required for those running 6.3 or 6.3.1. A 'make
1072 distclean', 'make', and 'make install' is all that is required. This last
1073 step should be performed while the postmaster is not running. You should
1074 re-link any custom applications that use Postgres libraries.
1076 For upgrades from pre-v6.3 installations, refer to the installation and
1077 migration instructions for v6.3.
1079 Detailed Change List
1083 Configure detection improvements for tcl/tk(Brook Milligan, Alvin)
1084 Manual page improvements(Bruce)
1085 BETWEEN and LIKE fix(Thomas)
1086 fix for psql \connect used by pg_dump(Oliver Elphick)
1088 pgaccess, version 0.86
1089 qsort removed, now uses libc version, cleanups(Jeroen)
1090 fix for buffer over-runs detected(Maurice Gittens)
1091 fix for buffer overrun in libpgtcl(Randy Kunkee)
1092 fix for UNION with DISTINCT or ORDER BY(Bruce)
1093 gettimeofday configure check(Doug Winterburn)
1094 Fix "indexes not used" bug(Vadim)
1095 docs additions(Thomas)
1096 Fix for backend memory leak(Bruce)
1097 libreadline cleanup(Erwan MAS)
1098 Remove DISTDIR(Bruce)
1099 Makefile dependency cleanup(Jeroen van Vianen)
1102 ------------------------------------------------------------------------
1103 Release 6.4 Release 6.3.1
1105 ------------------------------------------------------------------------
1111 * Additional support for multi-byte character sets.
1113 * Repair byte ordering for mixed-endian clients and servers.
1115 * Minor updates to allowed SQL syntax.
1117 * Improvements to the configuration autodetection for installation.
1119 A dump/restore is NOT required for those running 6.3. A 'make distclean',
1120 'make', and 'make install' is all that is required. This last step should be
1121 performed while the postmaster is not running. You should re-link any custom
1122 applications that use Postgres libraries.
1124 For upgrades from pre-v6.3 installations, refer to the installation and
1125 migration instructions for v6.3.
1127 Detailed Change List
1131 ecpg cleanup/fixes, now version 1.1(Michael Meskes)
1132 pg_user cleanup(Bruce)
1133 large object fix for pg_dump and tclsh (alvin)
1134 LIKE fix for multiple adjacent underscores
1135 fix for redefining builtin functions(Thomas)
1137 upgrade to pg_access 0.83
1138 updated CLUSTER manual page
1139 multi-byte character set support, see doc/README.mb(Tatsuo)
1140 configure --with-pgport fix
1142 big-endian fix for backend communications(Kataoka)
1143 SUBSTR() and substring() fix(Jan)
1144 several jdbc fixes(Peter)
1145 libpgtcl improvements, see libptcl/README(Randy Kunkee)
1146 Fix for "Datasize = 0" error(Vadim)
1147 Prevent \do from wrapping(Bruce)
1148 Remove duplicate Russian character set entries
1150 Allow optional TABLE keyword in LOCK and SELECT INTO(Thomas)
1151 CREATE SEQUENCE options to allow a negative integer(Thomas)
1152 Add "PASSWORD" as an allowed column identifier(Thomas)
1153 Add checks for UNION target fields(Bruce)
1154 Fix Alpha port(Dwayne Bailey)
1155 Fix for text arrays containing quotes(Doug Gibson)
1156 Solaris compile fix(Albert Chin-A-Young)
1157 Better identify tcl and tk libs and includes(Bruce)
1159 ------------------------------------------------------------------------
1160 Release 6.3.2 Release 6.3
1162 ------------------------------------------------------------------------
1166 There are many new features and improvements in this release. Here is a
1167 brief, incomplete summary:
1169 * Many new SQL features, including full SQL92 subselect capability
1170 (everything is here but target-list subselects).
1172 * Support for client-side environment variables to specify time zone and
1175 * Socket interface for client/server connection. This is the default now
1176 so you may need to start postmaster with the "-i" flag.
1178 * Better password authorization mechanisms. Default table permissions
1181 * Old-style "time travel" has been removed. Performance has been
1184 Note: Bruce Momjian wrote the following notes to introduce the new
1187 There are some general 6.3 issues that I want to mention. These are only the
1188 big items that can not be described in one sentence. A review of the
1189 detailed changes list is still needed.
1191 First, we now have subselects. Now that we have them, I would like to
1192 mention that without subselects, SQL is a very limited language. Subselects
1193 are a major feature, and you should review your code for places where
1194 subselects provide a better solution for your queries. I think you will find
1195 that there are more uses for subselects than you may think. Vadim has put us
1196 on the big SQL map with subselects, and fully functional ones too. The only
1197 thing you can't do with subselects is to use them in the target list.
1199 Second, 6.3 uses unix domain sockets rather than TCP/IP by default. To
1200 enable connections from other machines, you have to use the new postmaster
1201 -i option, and of course edit pg_hba.conf. Also, for this reason, the format
1202 of pg_hba.conf has changed.
1204 Third, char() fields will now allow faster access than varchar() or text.
1205 Specifically, the text and varchar() have a penalty for access to any
1206 columns after the first column of this type. char() used to also have this
1207 access penalty, but it no longer does. This may suggest that you redesign
1208 some of your tables, especially if you have short character columns that you
1209 have defined as varchar() or text. This and other changes make 6.3 even
1210 faster than earlier releases.
1212 We now have passwords definable independent of any Unix file. There are new
1213 SQL USER commands. See the pg_hba.conf manual page for more information.
1214 There is a new table, pg_shadow, which is used to store user information and
1215 user passwords, and it by default only SELECT-able by the postgres
1216 super-user. pg_user is now a view of pg_shadow, and is SELECT-able by
1217 PUBLIC. You should keep using pg_user in your application without changes.
1219 User-created tables now no longer have SELECT permission to PUBLIC by
1220 default. This was done because the ANSI standard requires it. You can of
1221 course GRANT any permissions you want after the table is created. System
1222 tables continue to be SELECT-able by PUBLIC.
1224 We also have real deadlock detection code. No more sixty-second timeouts.
1225 And the new locking code implements a FIFO better, so there should be less
1226 resource starvation during heavy use.
1228 Many complaints have been made about inadequate documenation in previous
1229 releases. Thomas has put much effort into many new manuals for this release.
1230 Check out the doc/ directory.
1232 For performance reasons, time travel is gone, but can be implemented using
1233 triggers (see pgsql/contrib/spi/README). Please check out the new \d command
1234 for types, operators, etc. Also, views have their own permissions now, not
1235 based on the underlying tables, so permissions on them have to be set
1236 separately. Check /pgsql/interfaces for some new ways to talk to Postgres.
1238 This is the first release that really required an explanation for existing
1239 users. In many ways, this was necessary because the new release removes many
1240 limitations, and the work-arounds people were using are no longer needed.
1244 A dump/restore using pg_dump or pg_dumpall is required for those wishing to
1245 migrate data from any previous release of Postgres.
1247 Detailed Change List
1251 Fix binary cursors broken by MOVE implementation(Vadim)
1252 Fix for tcl library crash(Jan)
1253 Fix for array handling, from Gerhard Hintermayer
1254 Fix acl error, and remove duplicate pqtrace(Bruce)
1255 Fix psql \e for empty file(Bruce)
1256 Fix for textcat on varchar() fields(Bruce)
1257 Fix for DBT Sendproc (Zeugswetter Andres)
1258 Fix vacuum analyze syntax problem(Bruce)
1259 Fix for international identifiers(Tatsuo)
1260 Fix aggregates on inherited tables(Bruce)
1261 Fix substr() for out-of-bounds data
1262 Fix for select 1=1 or 2=2, select 1=1 and 2=2, and select sum(2+2)(Bruce)
1263 Fix notty output to show status result. -q option still turns it off(Bruce)
1264 Fix for count(*), aggs with views and multiple tables and sum(3)(Bruce)
1266 Fix for PQtrace start/stop several times(Bruce)
1267 Fix a variety of locking problems like newer lock waiters getting
1268 lock before older waiters, and having readlock people not share
1269 locks if a writer is waiting for a lock, and waiting writers not
1270 getting priority over waiting readers(Bruce)
1271 Fix crashes in psql when executing queries from external files(James)
1272 Fix problem with multiple order by columns, with the first one having
1274 Use correct hash table support functions for float8 and int4(Thomas)
1275 Re-enable JOIN= option in CREATE OPERATOR statement (Thomas)
1276 Change precedence for boolean operators to match expected behavior(Thomas)
1277 Generate elog(ERROR) on over-large integer(Bruce)
1278 Allow multiple-argument functions in constraint clauses(Thomas)
1279 Check boolean input literals for 'true','false','yes','no','1','0'
1280 and throw elog(ERROR) if unrecognized(Thomas)
1281 Major large objects fix
1282 Fix for GROUP BY showing duplicates(Vadim)
1283 Fix for index scans in MergeJion(Vadim)
1287 Subselects with EXISTS, IN, ALL, ANY keywords (Vadim, Bruce, Thomas)
1288 New User Manual(Thomas, others)
1289 Speedup by inlining some frequently-called functions
1290 Real deadlock detection, no more timeouts(Bruce)
1291 Add SQL92 "constants" CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP,
1292 CURRENT_USER(Thomas)
1293 Modify constraint syntax to be SQL92-compliant(Thomas)
1294 Implement SQL92 PRIMARY KEY and UNIQUE clauses using indices(Thomas)
1295 Recognize SQL92 syntax for FOREIGN KEY. Throw elog notice(Thomas)
1296 Allow NOT NULL UNIQUE constraint clause (each allowed separately before)(Thomas)
1297 Allow Postgres-style casting ("::") of non-constants(Thomas)
1298 Add support for SQL3 TRUE and FALSE boolean constants(Thomas)
1299 Support SQL92 syntax for IS TRUE/IS FALSE/IS NOT TRUE/IS NOT FALSE(Thomas)
1300 Allow shorter strings for boolean literals (e.g. "t", "tr", "tru")(Thomas)
1301 Allow SQL92 delimited identifiers(Thomas)
1302 Implement SQL92 binary and hexadecimal string decoding (b'10' and x'1F')(Thomas)
1303 Support SQL92 syntax for type coercion of literal strings
1304 (e.g. "DATETIME 'now'")(Thomas)
1305 Add conversions for int2, int4, and OID types to and from text(Thomas)
1306 Use shared lock when building indices(Vadim)
1307 Free memory allocated for an user query inside transaction block after
1308 this query is done, was turned off in <= 6.2.1(Vadim)
1309 New SQL statement CREATE PROCEDURAL LANGUAGE(Jan)
1310 New Postgres Procedural Language (PL) backend interface(Jan)
1311 Rename pg_dump -H option to -h(Bruce)
1312 Add Java support for passwords, European dates(Peter)
1313 Use indices for LIKE and ~, !~ operations(Bruce)
1314 Add hash functions for datetime and timespan(Thomas)
1315 Time Travel removed(Vadim, Bruce)
1316 Add paging for \d and \z, and fix \i(Bruce)
1317 Add Unix domain socket support to backend and to frontend library(Goran)
1318 Implement CREATE DATABASE/WITH LOCATION and initlocation utility(Thomas)
1319 Allow more SQL92 and/or Postgres reserved words as column identifiers(Thomas)
1320 Augment support for SQL92 SET TIME ZONE...(Thomas)
1321 SET/SHOW/RESET TIME ZONE uses TZ backend environment variable(Thomas)
1322 Implement SET keyword = DEFAULT and SET TIME ZONE DEFAULT(Thomas)
1323 Enable SET TIME ZONE using TZ environment variable(Thomas)
1324 Add PGDATESTYLE environment variable to frontend and backend initialization(Thomas)
1325 Add PGTZ, PGCOSTHEAP, PGCOSTINDEX, PGRPLANS, PGGEQO
1326 frontend library initialization environment variables(Thomas)
1327 Regression tests time zone automatically set with "setenv PGTZ PST8PDT"(Thomas)
1328 Add pg_description table for info on tables, columns, operators, types, and
1330 Increase 16 char limit on system table/index names to 32 characters(Bruce)
1331 Rename system indices(Bruce)
1332 Add 'GERMAN' option to SET DATESTYLE(Thomas)
1333 Define an "ISO-style" timespan output format with "hh:mm:ss" fields(Thomas)
1334 Allow fractional values for delta times (e.g. '2.5 days')(Thomas)
1335 Validate numeric input more carefully for delta times(Thomas)
1336 Implement day of year as possible input to date_part()(Thomas)
1337 Define timespan_finite() and text_timespan() functions(Thomas)
1338 Remove archive stuff(Bruce)
1339 Allow for a pg_password authentication database that is separate from
1340 the system password file(Todd)
1341 Dump ACLs, GRANT, REVOKE permissions(Matt)
1342 Define text, varchar, and bpchar string length functions(Thomas)
1343 Fix Query handling for inheritance, and cost computations(Bruce)
1344 Implement CREATE TABLE/AS SELECT (alternative to SELECT/INTO)(Thomas)
1345 Allow NOT, IS NULL, IS NOT NULL in constraints(Thomas)
1346 Implement UNIONs for SELECT(Bruce)
1347 Add UNION, GROUP, DISTINCT to INSERT(Bruce)
1348 varchar() stores only necessary bytes on disk(Bruce)
1349 Fix for BLOBs(Peter)
1350 Mega-Patch for JDBC...see README_6.3 for list of changes(Peter)
1351 Remove unused "option" from PQconnectdb()
1352 New LOCK command and lock manual page describing deadlocks(Bruce)
1353 Add new psql \da, \dd, \df, \do, \dS, and \dT commands(Bruce)
1354 Enhance psql \z to show sequences(Bruce)
1355 Show NOT NULL and DEFAULT in psql \d table(Bruce)
1356 New psql .psqlrc file startup(Andrew)
1357 Modify sample startup script in contrib/linux to show syslog(Thomas)
1358 New types for IP and MAC addresses in contrib/ip_and_mac(TomH)
1359 Unix system time conversions with date/time types in contrib/unixdate(Thomas)
1360 Update of contrib stuff(Massimo)
1361 Add Unix socket support to DBD::Pg(Goran)
1362 New python interface (PyGreSQL 2.0)(D'Arcy)
1363 New frontend/backend protocol has a version number, network byte order(Phil)
1364 Security features in pg_hba.conf enhanced and documented, many cleanups(Phil)
1365 CHAR() now faster access than VARCHAR() or TEXT
1366 ecpg embedded SQL preprocessor
1367 Reduce system column overhead(Vadmin)
1368 Remove pg_time table(Vadim)
1369 Add pg_type attribute to identify types that need length (bpchar, varchar)
1370 Add report of offending line when COPY command fails
1371 Allow VIEW permissions to be set separately from the underlying tables.
1372 For security, use GRANT/REVOKE on views as appropriate(Jan)
1373 Tables now have no default GRANT SELECT TO PUBLIC. You must
1374 explicitly grant such permissions.
1375 Clean up tutorial examples(Darren)
1379 Add new html development tools, and flow chart in /tools/backend
1380 Fix for SCO compiles
1381 Stratus computer port Robert Gillies
1382 Added support for shlib for BSD44_derived & i386_solaris
1383 Make configure more automated(Brook)
1384 Add script to check regression test results
1385 Break parser functions into smaller files, group together(Bruce)
1386 Rename heap_create to heap_create_and_catalog, rename heap_creatr
1387 to heap_create()(Bruce)
1388 Sparc/Linux patch for locking(TomS)
1389 Remove PORTNAME and reorganize port-specific stuff(Marc)
1390 Add optimizer README file(Bruce)
1391 Remove some recursion in optimizer and clean up some code there(Bruce)
1392 Fix for NetBSD locking(Henry)
1393 Fix for libptcl make(Tatsuo)
1395 Change IS TRUE, IS FALSE, ... to expressions using "=" rather than
1396 function calls to istrue() or isfalse() to allow optimization(Thomas)
1397 Various fixes NetBSD/Sparc related(TomH)
1398 Alpha linux locking(Travis,Ryan)
1399 Change elog(WARN) to elog(ERROR)(Bruce)
1400 FAQ for FreeBSD(Marc)
1401 Bring in the PostODBC source tree as part of our standard distribution(Marc)
1402 A minor patch for HP/UX 10 vs 9(Stan)
1403 New pg_attribute.atttypmod for type-specific info like varchar length(Bruce)
1404 Unixware patches(Billy)
1405 New i386 'lock' for spin lock asm(Billy)
1406 Support for multiplexed backends is removed
1407 Start an OpenBSD port
1410 Add string functions to regression suite(Thomas)
1411 Expand a few function names formerly truncated to 16 characters(Thomas)
1412 Remove un-needed malloc() calls and replace with palloc()(Bruce)
1414 ------------------------------------------------------------------------
1415 Release 6.3.1 Release 6.2.1
1417 ------------------------------------------------------------------------
1421 v6.2.1 is a bug-fix and usability release on v6.2.
1425 * Allow strings to span lines, per SQL92.
1427 * Include example trigger function for inserting user names on table
1430 This is a minor bug-fix release on v6.2. For upgrades from pre-v6.2 systems,
1431 a full dump/reload is required. Refer to the v6.2 release notes for
1434 Migration from v6.2 to v6.2.1
1436 This is a minor bug-fix release. A dump/reload is not required from v6.2,
1437 but is required from any release prior to v6.2.
1439 In upgrading from v6.2, if you choose to dump/reload you will find that
1440 avg(money) is now calculated correctly. All other bug fixes take effect upon
1441 updating the executables.
1443 Another way to avoid dump/reload is to use the following SQL command from
1444 psql to update the existing system table:
1446 update pg_aggregate set aggfinalfn = 'cash_div_flt8'
1447 where aggname = 'avg' and aggbasetype = 790;
1449 This will need to be done to every existing database, including template1.
1451 Detailed Change List
1453 Changes in this release
1454 -----------------------
1455 Allow TIME and TYPE column names(Thomas)
1456 Allow larger range of true/false as boolean values(Thomas)
1457 Support output of "now" and "current"(Thomas)
1458 Handle DEFAULT with INSERT of NULL properly(Vadim)
1459 Fix for relation reference counts problem in buffer manager(Vadim)
1460 Allow strings to span lines, like ANSI(Thomas)
1461 Fix for backward cursor with ORDER BY(Vadim)
1462 Fix avg(cash) computation(Thomas)
1463 Fix for specifying a column twice in ORDER/GROUP BY(Vadim)
1464 Documented new libpq function to return affected rows, PQcmdTuples(Bruce)
1465 Trigger function for inserting user names for INSERT/UPDATE(Brook Milligan)
1467 ------------------------------------------------------------------------
1468 Release 6.3 Release 6.2
1470 ------------------------------------------------------------------------
1474 A dump/restore is required for those wishing to migrate data from previous
1475 releases of Postgres.
1477 Migration from v6.1 to v6.2
1479 This migration requires a complete dump of the 6.1 database and a restore of
1480 the database in 6.2.
1482 Note that the pg_dump and pg_dumpall utility from 6.2 should be used to dump
1485 Migration from v1.x to v6.2
1487 Those migrating from earlier 1.* releases should first upgrade to 1.09
1488 because the COPY output format was improved from the 1.02 release.
1490 Detailed Change List
1494 Fix problems with pg_dump for inheritance, sequences, archive tables(Bruce)
1495 Fix compile errors on overflow due to shifts, unsigned, and bad prototypes
1496 from Solaris(Diab Jerius)
1497 Fix bugs in geometric line arithmetic (bad intersection calculations)(Thomas)
1498 Check for geometric intersections at endpoints to avoid rounding ugliness(Thomas)
1499 Catch non-functional delete attempts(Vadim)
1500 Change time function names to be more consistent(Michael Reifenberg)
1501 Check for zero divides(Michael Reifenberg)
1502 Fix very old bug which made tuples changed/inserted by a commnd
1503 visible to the command itself (so we had multiple update of
1504 updated tuples, etc)(Vadim)
1505 Fix for SELECT null, 'fail' FROM pg_am (Patrick)
1506 SELECT NULL as EMPTY_FIELD now allowed(Patrick)
1507 Remove un-needed signal stuff from contrib/pginterface
1508 Fix OR (where x != 1 or x isnull didn't return tuples with x NULL) (Vadim)
1509 Fix time_cmp function (Vadim)
1510 Fix handling of functions with non-attribute first argument in
1511 WHERE clauses (Vadim)
1512 Fix GROUP BY when order of entries is different from order
1513 in target list (Vadim)
1514 Fix pg_dump for aggregates without sfunc1 (Vadim)
1518 Default genetic optimizer GEQO parameter is now 8(Bruce)
1519 Allow use parameters in target list having aggregates in functions(Vadim)
1520 Added JDBC driver as an interface(Adrian & Peter)
1522 Return number of tuples inserted/affected by INSERT/UPDATE/DELETE etc.(Vadim)
1523 Triggers implemented with CREATE TRIGGER (SQL3)(Vadim)
1524 SPI (Server Programming Interface) allows execution of queries inside
1526 NOT NULL implemented (SQL92)(Robson Paniago de Miranda)
1527 Include reserved words for string handling, outer joins, and unions(Thomas)
1528 Implement extended comments ("/* ... */") using exclusive states(Thomas)
1529 Add "//" single-line comments(Bruce)
1530 Remove some restrictions on characters in operator names(Thomas)
1531 DEFAULT and CONSTRAINT for tables implemented (SQL92)(Vadim & Thomas)
1532 Add text concatenation operator and function (SQL92)(Thomas)
1533 Support WITH TIME ZONE syntax (SQL92)(Thomas)
1534 Support INTERVAL unit TO unit syntax (SQL92)(Thomas)
1535 Define types DOUBLE PRECISION, INTERVAL, CHARACTER,
1536 and CHARACTER VARYING (SQL92)(Thomas)
1537 Define type FLOAT(p) and rudimentary DECIMAL(p,s), NUMERIC(p,s) (SQL92)(Thomas)
1538 Define EXTRACT(), POSITION(), SUBSTRING(), and TRIM() (SQL92)(Thomas)
1539 Define CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP (SQL92)(Thomas)
1540 Add syntax and warnings for UNION, HAVING, INNER and OUTER JOIN (SQL92)(Thomas)
1541 Add more reserved words, mostly for SQL92 compliance(Thomas)
1542 Allow hh:mm:ss time entry for timespan/reltime types(Thomas)
1543 Add center() routines for lseg, path, polygon(Thomas)
1544 Add distance() routines for circle-polygon, polygon-polygon(Thomas)
1545 Check explicitly for points and polygons contained within polygons
1546 using an axis-crossing algorithm(Thomas)
1547 Add routine to convert circle-box(Thomas)
1548 Merge conflicting operators for different geometric data types(Thomas)
1549 Replace distance operator "<===>" with "<->"(Thomas)
1550 Replace "above" operator "!^" with ">^" and "below" operator "!|" with "<^"(Thomas)
1551 Add routines for text trimming on both ends, substring, and string position(Thomas)
1552 Added conversion routines circle(box) and poly(circle)(Thomas)
1553 Allow internal sorts to be stored in memory rather than in files(Bruce & Vadim)
1554 Allow functions and operators on internally-identical types to succeed(Bruce)
1555 Speed up backend startup after profiling analysis(Bruce)
1556 Inline frequently called functions for performance(Bruce)
1557 Reduce open() calls(Bruce)
1558 psql: Add PAGER for \h and \?,\C fix
1559 Fix for psql pager when no tty(Bruce)
1560 New entab utility(Bruce)
1561 General trigger functions for referential integrity (Vadim)
1562 General trigger functions for time travel (Vadim)
1563 General trigger functions for AUTOINCREMENT/IDENTITY feature (Vadim)
1564 MOVE implementation (Vadim)
1568 HPUX 10 patches (Vladimir Turin)
1569 Added SCO support, (Daniel Harris)
1570 mkLinux patches (Tatsuo Ishii)
1571 Change geometric box terminology from "length" to "width"(Thomas)
1572 Deprecate temporary unstored slope fields in geometric code(Thomas)
1573 Remove restart instructions from INSTALL(Bruce)
1574 Look in /usr/ucb first for install(Bruce)
1575 Fix c++ copy example code(Thomas)
1576 Add -o to psql manual page(Bruce)
1577 Prevent relname unallocated string length from being copied into database(Bruce)
1578 Cleanup for NAMEDATALEN use(Bruce)
1579 Fix pg_proc names over 15 chars in output(Bruce)
1580 Add strNcpy() function(Bruce)
1581 remove some (void) casts that are unnecessary(Bruce)
1582 new interfaces directory(Marc)
1583 Replace fopen() calls with calls to fd.c functions(Bruce)
1584 Make functions static where possible(Bruce)
1585 enclose unused functions in #ifdef NOT_USED(Bruce)
1586 Remove call to difftime() in timestamp support to fix SunOS(Bruce & Thomas)
1587 Changes for Digital Unix
1588 Portability fix for pg_dumpall(Bruce)
1589 Rename pg_attribute.attnvals to attdisbursion(Bruce)
1590 "intro/unix" manual page now "pgintro"(Bruce)
1591 "built-in" manual page now "pgbuiltin"(Bruce)
1592 "drop" manual page now "drop_table"(Bruce)
1593 Add "create_trigger", "drop_trigger" manual pages(Thomas)
1594 Add constraints regression test(Vadim & Thomas)
1595 Add comments syntax regression test(Thomas)
1596 Add PGINDENT and support program(Bruce)
1597 Massive commit to run PGINDENT on all *.c and *.h files(Bruce)
1598 Files moved to /src/tools directory(Bruce)
1599 SPI and Trigger programming guides (Vadim & D'Arcy)
1601 ------------------------------------------------------------------------
1602 Release 6.2.1 Release 6.1.1
1604 ------------------------------------------------------------------------
1608 Migration from v6.1 to v6.1.1
1610 This is a minor bug-fix release. A dump/reload is not required from v6.1,
1611 but is required from any release prior to v6.1. Refer to the release notes
1612 for v6.1 for more details.
1614 Detailed Change List
1616 Changes in this release
1617 -----------------------
1618 fix for SET with options (Thomas)
1619 allow pg_dump/pg_dumpall to preserve ownership of all tables/objects(Bruce)
1620 new psql \connect option allows changing usernames without changing databases
1621 fix for initdb --debug option(Yoshihiko Ichikawa))
1622 lextest cleanup(Bruce)
1624 fix date/time month boundary arithmetic(Thomas)
1625 fix timezone daylight handling for some ports(Thomas, Bruce, Tatsuo)
1626 timestamp overhauled to use standard functions(Thomas)
1627 other code cleanup in date/time routines(Thomas)
1628 psql's \d now case-insensitive(Bruce)
1629 psql's backslash commands can now have trailing semicolon(Bruce)
1630 fix memory leak in psql when using \g(Bruce)
1631 major fix for endian handling of communication to server(Thomas, Tatsuo)
1632 Fix for Solaris assembler and include files(Yoshihiko Ichikawa)
1633 allow underscores in usernames(Bruce)
1634 pg_dumpall now returns proper status, portability fix(Bruce)
1636 ------------------------------------------------------------------------
1637 Release 6.2 Release 6.1
1639 ------------------------------------------------------------------------
1643 The regression tests have been adapted and extensively modified for the v6.1
1644 release of Postgres.
1646 Three new data types (datetime, timespan, and circle) have been added to the
1647 native set of Postgres types. Points, boxes, paths, and polygons have had
1648 their output formats made consistant across the data types. The polygon
1649 output in misc.out has only been spot-checked for correctness relative to
1650 the original regression output.
1652 Postgres v6.1 introduces a new, alternate optimizer which uses genetic
1653 algorithms. These algorithms introduce a random behavior in the ordering of
1654 query results when the query contains multiple qualifiers or multiple tables
1655 (giving the optimizer a choice on order of evaluation). Several regression
1656 tests have been modified to explicitly order the results, and hence are
1657 insensitive to optimizer choices. A few regression tests are for data types
1658 which are inherently unordered (e.g. points and time intervals) and tests
1659 involving those types are explicitly bracketed with set geqo to 'off' and
1662 The interpretation of array specifiers (the curly braces around atomic
1663 values) appears to have changed sometime after the original regression tests
1664 were generated. The current ./expected/*.out files reflect this new
1665 interpretation, which may not be correct!
1667 The float8 regression test fails on at least some platforms. This is due to
1668 differences in implementations of pow() and exp() and the signaling
1669 mechanisms used for overflow and underflow conditions.
1671 The "random" results in the random test should cause the "random" test to be
1672 "failed", since the regression tests are evaluated using a simple diff.
1673 However, "random" does not seem to produce random results on my test machine
1678 This migration requires a complete dump of the 6.0 database and a restore of
1679 the database in 6.1.
1681 Those migrating from earlier 1.* releases should first upgrade to 1.09
1682 because the COPY output format was improved from the 1.02 release.
1684 Detailed Change List
1688 packet length checking in library routines
1689 lock manager priority patch
1690 check for under/over flow of float8(Bruce)
1691 multi-table join fix(Vadim)
1692 SIGPIPE crash fix(Darren)
1693 large object fixes(Sven)
1694 allow btree indexes to handle NULLs(Vadim)
1695 timezone fixes(D'Arcy)
1696 select SUM(x) can return NULL on no rows(Thomas)
1697 internal optimizer, executor bug fixes(Vadim)
1698 fix problem where inner loop in < or <= has no rows(Vadim)
1699 prevent re-commuting join index clauses(Vadim)
1700 fix join clauses for multiple tables(Vadim)
1701 fix hash, hashjoin for arrays(Vadim)
1702 fix btree for abstime type(Vadim)
1703 large object fixes(Raymond)
1704 fix buffer leak in hash indices (Vadim)
1705 fix rtree for use in inner scan (Vadim)
1706 fix gist for use in inner scan, cleanups (Vadim, Andrea)
1707 avoid unnecessary local buffers allocation (Vadim, Massimo)
1708 fix local buffers leak in transaction aborts (Vadim)
1709 fix file manager memmory leaks, cleanups (Vadim, Massimo)
1710 fix storage manager memmory leaks (Vadim)
1711 fix btree duplicates handling (Vadim)
1712 fix deleted tuples re-incarnation caused by vacuum (Vadim)
1713 fix SELECT varchar()/char() INTO TABLE made zero-length fields(Bruce)
1714 many psql, pg_dump, and libpq memory leaks fixed using Purify (Igor)
1718 attribute optimization statistics(Bruce)
1719 much faster new btree bulk load code(Paul)
1720 BTREE UNIQUE added to bulk load code(Vadim)
1721 new lock debug code(Massimo)
1722 massive changes to libpg++(Leo)
1723 new GEQO optimizer speeds table multi-table optimization(Martin)
1724 new WARN message for non-unique insert into unique key(Marc)
1725 update x=-3, no spaces, now valid(Bruce)
1726 remove case-sensitive identifier handling(Bruce,Thomas,Dan)
1727 debug backend now pretty-prints tree(Darren)
1728 new Oracle character functions(Edmund)
1729 new plaintext password functions(Dan)
1730 no such class or insufficient privilege changed to distinct messages(Dan)
1731 new ANSI timestamp function(Dan)
1732 new ANSI Time and Date types (Thomas)
1733 move large chunks of data in backend(Martin)
1734 multi-column btree indexes(Vadim)
1735 new SET var TO value command(Martin)
1736 update transaction status on reads(Dan)
1737 new locale settings for character types(Oleg)
1738 new SEQUENCE serial number generator(Vadim)
1739 GROUP BY function now possible(Vadim)
1740 re-organize regression test(Thomas,Marc)
1741 new optimizer operation weights(Vadim)
1742 new psql \z grant/permit option(Marc)
1743 new MONEY data type(D'Arcy,Thomas)
1744 tcp socket communication speed improved(Vadim)
1745 new VACUUM option for attribute statistics, and for certain columns (Vadim)
1746 many geometric type improvements(Thomas,Keith)
1747 additional regression tests(Thomas)
1748 new datestyle variable(Thomas,Vadim,Martin)
1749 more comparison operators for sorting types(Thomas)
1750 new conversion functions(Thomas)
1751 new more compact btree format(Vadim)
1752 allow pg_dumpall to preserve database ownership(Bruce)
1753 new SET GEQO=# and R_PLANS variable(Vadim)
1754 old (!GEQO) optimizer can use right-sided plans (Vadim)
1755 typechecking improvement in SQL parser(Bruce)
1756 new SET, SHOW, RESET commands(Thomas,Vadim)
1757 new \connect database USER option
1758 new destroydb -i option (Igor)
1759 new \dt and \di psql commands (Darren)
1760 SELECT "\n" now escapes newline (A. Duursma)
1761 new geometry conversion functions from old format (Thomas)
1765 new configuration script(Marc)
1766 readline configuration option added(Marc)
1767 OS-specific configuration options removed(Marc)
1768 new OS-specific template files(Marc)
1769 no more need to edit Makefile.global(Marc)
1770 re-arrange include files(Marc)
1771 nextstep patches (Gregor Hoffleit)
1772 removed WIN32-specific code(Bruce)
1773 removed postmaster -e option, now only postgres -e option (Bruce)
1774 merge duplicate library code in front/backends(Martin)
1775 now works with eBones, international Kerberos(Jun)
1776 more shared library support
1777 c++ include file cleanup(Bruce)
1778 warn about buggy flex(Bruce)
1779 DG-UX, Ultrix, Irix, AIX portability fixes
1781 ------------------------------------------------------------------------
1782 Release 6.1.1 Release v6.0
1784 ------------------------------------------------------------------------
1788 A dump/restore is required for those wishing to migrate data from previous
1789 releases of Postgres.
1791 Migration from v1.09 to v6.0
1793 This migration requires a complete dump of the 1.09 database and a restore
1794 of the database in 6.0.
1796 Migration from pre-v1.09 to v6.0
1798 Those migrating from earlier 1.* releases should first upgrade to 1.09
1799 because the COPY output format was improved from the 1.02 release.
1801 Detailed Change List
1805 ALTER TABLE bug - running postgress process needs to re-read table definition
1806 Allow vacuum to be run on one table or entire database(Bruce)
1808 Fix array over-runs of memory writes(Kurt)
1809 Fix elusive btree range/non-range bug(Dan)
1810 Fix for hash indexes on some types like time and date
1811 Fix for pg_log size explosion
1812 Fix permissions on lo_export()(Bruce)
1813 Fix unitialized reads of memory(Kurt)
1814 Fixed ALTER TABLE ... char(3) bug(Bruce)
1815 Fixed a few small memory leaks
1816 Fixed EXPLAIN handling of options and changed full_path option name
1817 Fixed output of group acl permissions
1818 Memory leaks (hunt and destroy with tools like Purify(Kurt)
1819 Minor improvements to rules system
1821 New asserts for run-checking
1822 Overhauled parser/analyze code to properly report errors and increase speed
1823 Pg_dump -d now handles NULL's properly(Bruce)
1824 Prevent SELECT NULL from crashing server (Bruce)
1825 Properly report errors when INSERT ... SELECT columns did not match
1826 Properly report errors when insert column names were not correct
1827 Psql \g filename now works(Bruce)
1828 Psql fixed problem with multiple statements on one line with multiple outputs
1829 Removed duplicate system oid's
1830 SELECT * INTO TABLE . GROUP/ORDER BY gives unlink error if table exists(Bruce)
1831 Several fixes for queries that crashed the backend
1832 Starting quote in insert string errors(Bruce)
1833 Submitting an empty query now returns empty status, not just " " query(Bruce)
1837 Add EXPLAIN manual page(Bruce)
1838 Add UNIQUE index capability(Dan)
1839 Add hostname/user level access control rather than just hostname and user
1840 Add synonym of != for (Bruce)
1841 Allow "select oid,* from table"
1842 Allow BY,ORDER BY to specify columns by number, or by non-alias table.column(Bruce)
1843 Allow COPY from the frontend(Bryan)
1844 Allow GROUP BY to use alias column name(Bruce)
1845 Allow actual compression, not just reuse on the same page(Vadim)
1846 Allow installation-configuration option to auto-add all local users(Bryan)
1847 Allow libpq to distinguish between text value '' and null(Bruce)
1848 Allow non-postgres users with createdb privs to destroydb's
1849 Allow restriction on who can create C functions(Bryan)
1850 Allow restriction on who can do backend COPY(Bryan)
1851 Can shrink tables, pg_time and pg_log(Vadim & Erich)
1852 Change debug level 2 to print queries only, changed debug heading layout(Bruce)
1853 Change default decimal constant representation from float4 to float8(Bruce)
1854 European date format now set when postmaster is started
1855 Execute lowercase function names if not found with exact case
1856 Fixes for aggregate/GROUP processing, allow 'select sum(func(x),sum(x+y) from z'
1857 Gist now included in the distrubution(Marc)
1858 Idend authentication of local users(Bryan)
1859 Implement BETWEEN qualifier(Bruce)
1860 Implement IN qualifier(Bruce)
1861 Libpq has PQgetisnull()(Bruce)
1862 Libpq++ improvements
1863 New options to initdb(Bryan)
1864 Pg_dump allow dump of oid's(Bruce)
1865 Pg_dump create indexes after tables are loaded for speed(Bruce)
1866 Pg_dumpall dumps all databases, and the user table
1867 Pginterface additions for NULL values(Bruce)
1868 Prevent postmaster from being run as root
1869 Psql \h and \? is now readable(Bruce)
1870 Psql allow backslashed, semicolons anywhere on the line(Bruce)
1871 Psql changed command prompt for lines in query or in quotes(Bruce)
1872 Psql char(3) now displays as (bp)char in \d output(Bruce)
1873 Psql return code now more accurate(Bryan?)
1874 Psql updated help syntax(Bruce)
1875 Re-visit and fix vacuum(Vadim)
1876 Reduce size of regression diffs, remove timezone name difference(Bruce)
1877 Remove compile-time parameters to enable binary distributions(Bryan)
1878 Reverse meaning of HBA masks(Bryan)
1879 Secure Authentication of local users(Bryan)
1880 Speed up vacuum(Vadim)
1881 Vacuum now had VERBOSE option(Bruce)
1885 All functions now have prototypes that are compared against the calls
1886 Allow asserts to be disabled easly from Makefile.global(Bruce)
1887 Change oid constants used in code to #define names
1888 Decoupled sparc and solaris defines(Kurt)
1889 Gcc -Wall compiles cleanly with warnings only from unfixable constructs
1890 Major include file reorganization/reduction(Marc)
1891 Make now stops on compile failure(Bryan)
1892 Makefile restructuring(Bryan, Marc)
1893 Merge bsdi_2_1 to bsdi(Bruce)
1894 Monitor program removed
1895 Name change from Postgres95 to PostgreSQL
1896 New config.h file(Marc, Bryan)
1897 PG_VERSION now set to 6.0 and used by postmaster
1898 Portability additions, including Ultrix, DG/UX, AIX, and Solaris
1899 Reduced the number of #define's, centeralized #define's
1900 Remove duplicate OIDS in system tables(Dan)
1901 Remove duplicate system catalog info or report mismatches(Dan)
1902 Removed many os-specific #define's
1903 Restructured object file generation/location(Bryan, Marc)
1904 Restructured port-specific file locations(Bryan, Marc)
1905 Unused/uninialized variables corrected
1907 ------------------------------------------------------------------------
1908 Release 6.1 Release v1.09
1910 ------------------------------------------------------------------------
1914 Sorry, we stopped keeping track of changes from 1.02 to 1.09. Some of the
1915 changes listed in 6.0 were actually included in the 1.02.1 to 1.09 releases.
1917 ------------------------------------------------------------------------
1918 Release v6.0 Release v1.02
1920 ------------------------------------------------------------------------
1924 Migration from v1.02 to v1.02.1
1926 Here is a new migration file for 1.02.1. It includes the 'copy' change and a
1927 script to convert old ascii files.
1929 Note: The following notes are for the benefit of users who want to
1930 migrate databases from postgres95 1.01 and 1.02 to postgres95
1933 If you are starting afresh with postgres95 1.02.1 and do not need
1934 to migrate old databases, you do not need to read any further.
1936 In order to upgrade older postgres95 version 1.01 or 1.02 databases to
1937 version 1.02.1, the following steps are required:
1939 1. Start up a new 1.02.1 postmaster
1941 2. Add the new built-in functions and operators of 1.02.1 to 1.01 or 1.02
1942 databases. This is done by running the new 1.02.1 server against your
1943 own 1.01 or 1.02 database and applying the queries attached at the end
1944 of thie file. This can be done easily through psql. If your 1.01 or
1945 1.02 database is named "testdb" and you have cut the commands from the
1946 end of this file and saved them in addfunc.sql:
1948 % psql testdb -f addfunc.sql
1950 Those upgrading 1.02 databases will get a warning when executing the
1951 last two statements in the file because they are already present in
1952 1.02. This is not a cause for concern.
1954 Dump/Reload Procedure
1956 If you are trying to reload a pg_dump or text-mode 'copy tablename to
1957 stdout' generated with a previous version, you will need to run the attached
1958 sed script on the ASCII file before loading it into the database. The old
1959 format used '.' as end-of-data, while '\.' is now the end-of-data marker.
1960 Also, empty strings are now loaded in as '' rather than NULL. See the copy
1961 manual page for full details.
1963 sed 's/^\.$/\\./g' in_file out_file
1965 If you are loading an older binary copy or non-stdout copy, there is no
1966 end-of-data character, and hence no conversion necessary.
1968 -- following lines added by agc to reflect the case-insensitive
1969 -- regexp searching for varchar (in 1.02), and bpchar (in 1.02.1)
1970 create operator ~* (leftarg = bpchar, rightarg = text, procedure = texticregexeq);
1971 create operator !~* (leftarg = bpchar, rightarg = text, procedure = texticregexne);
1972 create operator ~* (leftarg = varchar, rightarg = text, procedure = texticregexeq);
1973 create operator !~* (leftarg = varchar, rightarg = text, procedure = texticregexne);
1975 Detailed Change List
1977 Source code maintenance and development
1978 * worldwide team of volunteers
1979 * the source tree now in CVS at ftp.ki.net
1982 * psql (and underlying libpq library) now has many more options for
1983 formatting output, including HTML
1984 * pg_dump now output the schema and/or the data, with many fixes to
1985 enhance completeness.
1986 * psql used in place of monitor in administration shell scripts.
1987 monitor to be depreciated in next release.
1988 * date/time functions enhanced
1989 * NULL insert/update/comparison fixed/enhanced
1990 * TCL/TK lib and shell fixed to work with both tck7.4/tk4.0 and tcl7.5/tk4.1
1992 Bug Fixes (almost too numerous to mention)
1994 * storage management
1995 * check for NULL pointer before dereferencing
1999 * added SolarisX86 port
2000 * added BSDI 2.1 port
2003 ------------------------------------------------------------------------
2004 Release v1.09 Release v1.01
2006 ------------------------------------------------------------------------
2010 Migration from v1.0 to v1.01
2012 The following notes are for the benefit of users who want to migrate
2013 databases from postgres95 1.0 to postgres95 1.01.
2015 If you are starting afresh with postgres95 1.01 and do not need to migrate
2016 old databases, you do not need to read any further.
2018 In order to postgres95 version 1.01 with databases created with postgres95
2019 version 1.0, the following steps are required:
2021 1. Set the definition of NAMEDATALEN in src/Makefile.global to 16 and
2024 2. Decide whether you want to use Host based authentication.
2026 a. If you do, you must create a file name "pg_hba" in your top-level
2027 data directory (typically the value of your $PGDATA).
2028 src/libpq/pg_hba shows an example syntax.
2030 b. If you do not want host-based authentication, you can comment out
2035 in src/Makefile.global
2037 Note that host-based authentication is turned on by default, and
2038 if you do not take steps A or B above, the out-of-the-box 1.01
2039 will not allow you to connect to 1.0 databases.
2041 3. Compile and install 1.01, but DO NOT do the initdb step.
2043 4. Before doing anything else, terminate your 1.0 postmaster, and backup
2044 your existing $PGDATA directory.
2046 5. Set your PGDATA environment variable to your 1.0 databases, but set up
2047 path up so that 1.01 binaries are being used.
2049 6. Modify the file $PGDATA/PG_VERSION from 5.0 to 5.1
2051 7. Start up a new 1.01 postmaster
2053 8. Add the new built-in functions and operators of 1.01 to 1.0 databases.
2054 This is done by running the new 1.01 server against your own 1.0
2055 database and applying the queries attached and saving in the file
2056 1.0_to_1.01.sql. This can be done easily through psql. If your 1.0
2057 database is name "testdb":
2059 % psql testdb -f 1.0_to_1.01.sql
2061 and then execute the following commands (cut and paste from here):
2063 -- add builtin functions that are new to 1.01
2065 create function int4eqoid (int4, oid) returns bool as 'foo'
2066 language 'internal';
2067 create function oideqint4 (oid, int4) returns bool as 'foo'
2068 language 'internal';
2069 create function char2icregexeq (char2, text) returns bool as 'foo'
2070 language 'internal';
2071 create function char2icregexne (char2, text) returns bool as 'foo'
2072 language 'internal';
2073 create function char4icregexeq (char4, text) returns bool as 'foo'
2074 language 'internal';
2075 create function char4icregexne (char4, text) returns bool as 'foo'
2076 language 'internal';
2077 create function char8icregexeq (char8, text) returns bool as 'foo'
2078 language 'internal';
2079 create function char8icregexne (char8, text) returns bool as 'foo'
2080 language 'internal';
2081 create function char16icregexeq (char16, text) returns bool as 'foo'
2082 language 'internal';
2083 create function char16icregexne (char16, text) returns bool as 'foo'
2084 language 'internal';
2085 create function texticregexeq (text, text) returns bool as 'foo'
2086 language 'internal';
2087 create function texticregexne (text, text) returns bool as 'foo'
2088 language 'internal';
2090 -- add builtin functions that are new to 1.01
2092 create operator = (leftarg = int4, rightarg = oid, procedure = int4eqoid);
2093 create operator = (leftarg = oid, rightarg = int4, procedure = oideqint4);
2094 create operator ~* (leftarg = char2, rightarg = text, procedure = char2icregexeq);
2095 create operator !~* (leftarg = char2, rightarg = text, procedure = char2icregexne);
2096 create operator ~* (leftarg = char4, rightarg = text, procedure = char4icregexeq);
2097 create operator !~* (leftarg = char4, rightarg = text, procedure = char4icregexne);
2098 create operator ~* (leftarg = char8, rightarg = text, procedure = char8icregexeq);
2099 create operator !~* (leftarg = char8, rightarg = text, procedure = char8icregexne);
2100 create operator ~* (leftarg = char16, rightarg = text, procedure = char16icregexeq);
2101 create operator !~* (leftarg = char16, rightarg = text, procedure = char16icregexne);
2102 create operator ~* (leftarg = text, rightarg = text, procedure = texticregexeq);
2103 create operator !~* (leftarg = text, rightarg = text, procedure = texticregexne);
2105 Detailed Change List
2108 * 1.01 is backwards compatible with 1.0 database provided the user
2109 follow the steps outlined in the MIGRATION_from_1.0_to_1.01 file.
2110 If those steps are not taken, 1.01 is not compatible with 1.0 database.
2113 * added PQdisplayTuples() to libpq and changed monitor and psql to use it
2114 * added NeXT port (requires SysVIPC implementation)
2115 * added CAST .. AS ... syntax
2116 * added ASC and DESC keywords
2117 * added 'internal' as a possible language for CREATE FUNCTION
2118 internal functions are C functions which have been statically linked
2119 into the postgres backend.
2120 * a new type "name" has been added for system identifiers (table names,
2121 attribute names, etc.) This replaces the old char16 type. The
2122 of name is set by the NAMEDATALEN #define in src/Makefile.global
2123 * a readable reference manual that describes the query language.
2124 * added host-based access control. A configuration file ($PGDATA/pg_hba)
2125 is used to hold the configuration data. If host-based access control
2126 is not desired, comment out HBA=1 in src/Makefile.global.
2127 * changed regex handling to be uniform use of Henry Spencer's regex code
2128 regardless of platform. The regex code is included in the distribution
2129 * added functions and operators for case-insensitive regular expressions.
2130 The operators are ~* and !~*.
2131 * pg_dump uses COPY instead of SELECT loop for better performance
2134 * fixed an optimizer bug that was causing core dumps when
2135 functions calls were used in comparisons in the WHERE clause
2136 * changed all uses of getuid to geteuid so that effective uids are used
2137 * psql now returns non-zero status on errors when using -c
2138 * applied public patches 1-14
2140 ------------------------------------------------------------------------
2141 Release v1.02 Release v1.0
2143 ------------------------------------------------------------------------
2147 Detailed Change List
2150 * The copyright of Postgres 1.0 has been loosened to be freely modifiable
2151 and modifiable for any purpose. Please read the COPYRIGHT file.
2152 Thanks to Professor Michael Stonebraker for making this possible.
2155 * date formats have to be MM-DD-YYYY (or DD-MM-YYYY if you're using
2156 EUROPEAN STYLE). This follows SQL-92 specs.
2157 * "delimiters" is now a keyword
2160 * sql LIKE syntax has been added
2161 * copy command now takes an optional USING DELIMITER specification.
2162 delimiters can be any single-character string.
2163 * IRIX 5.3 port has been added.
2164 Thanks to Paul Walmsley and others.
2165 * updated pg_dump to work with new libpq
2166 * \d has been added psql
2167 Thanks to Keith Parks
2168 * regexp performance for architectures that use POSIX regex has been
2169 improved due to caching of precompiled patterns.
2170 Thanks to Alistair Crooks
2171 * a new version of libpq++
2172 Thanks to William Wanders
2175 * arbitrary userids can be specified in the createuser script
2176 * \c to connect to other databases in psql now works.
2177 * bad pg_proc entry for float4inc() is fixed
2178 * users with usecreatedb field set can now create databases without
2179 having to be usesuper
2180 * remove access control entries when the entry no longer has any
2182 * fixed non-portable datetimes implementation
2183 * added kerberos flags to the src/backend/Makefile
2184 * libpq now works with kerberos
2185 * typographic errors in the user manual have been corrected.
2186 * btrees with multiple index never worked, now we tell you they don't
2187 work when you try to use them
2189 ------------------------------------------------------------------------
2190 Release v1.01 Postgres95 Beta 0.03
2192 ------------------------------------------------------------------------
2194 Postgres95 Beta 0.03
2196 Detailed Change List
2198 Incompatible changes:
2199 * BETA-0.3 IS INCOMPATIBLE WITH DATABASES CREATED WITH PREVIOUS VERSIONS
2200 (due to system catalog changes and indexing structure changes).
2201 * double-quote (") is deprecated as a quoting character for string literals;
2202 you need to convert them to single quotes (').
2203 * name of aggregates (eg. int4sum) are renamed in accordance with the
2204 SQL standard (eg. sum).
2205 * CHANGE ACL syntax is replaced by GRANT/REVOKE syntax.
2206 * float literals (eg. 3.14) are now of type float4 (instead of float8 in
2207 previous releases); you might have to do typecasting if you depend on it
2208 being of type float8. If you neglect to do the typecasting and you assign
2209 a float literal to a field of type float8, you may get incorrect values
2211 * LIBPQ has been totally revamped so that frontend applications
2212 can connect to multiple backends
2213 * the usesysid field in pg_user has been changed from int2 to int4 to
2214 allow wider range of Unix user ids.
2215 * the netbsd/freebsd/bsd o/s ports have been consolidated into a
2216 single BSD44_derived port. (thanks to Alistair Crooks)
2218 SQL standard-compliance (the following details changes that makes postgres95
2219 more compliant to the SQL-92 standard):
2220 * the following SQL types are now built-in: smallint, int(eger), float, real,
2221 char(N), varchar(N), date and time.
2223 The following are aliases to existing postgres types:
2225 integer, int -> int4
2226 float, real -> float4
2227 char(N) and varchar(N) are implemented as truncated text types. In
2228 addition, char(N) does blank-padding.
2229 * single-quote (') is used for quoting string literals; '' (in addition to
2230 \') is supported as means of inserting a single quote in a string
2231 * SQL standard aggregate names (MAX, MIN, AVG, SUM, COUNT) are used
2232 (Also, aggregates can now be overloaded, i.e. you can define your
2233 own MAX aggregate to take in a user-defined type.)
2234 * CHANGE ACL removed. GRANT/REVOKE syntax added.
2235 - Privileges can be given to a group using the "GROUP" keyword.
2237 GRANT SELECT ON foobar TO GROUP my_group;
2238 The keyword 'PUBLIC' is also supported to mean all users.
2240 Privileges can only be granted or revoked to one user or group
2243 "WITH GRANT OPTION" is not supported. Only class owners can change
2245 - The default access control is to to grant users readonly access.
2246 You must explicitly grant insert/update access to users. To change
2247 this, modify the line in
2248 src/backend/utils/acl.h
2249 that defines ACL_WORLD_DEFAULT
2252 * the bug where aggregates of empty tables were not run has been fixed. Now,
2253 aggregates run on empty tables will return the initial conditions of the
2254 aggregates. Thus, COUNT of an empty table will now properly return 0.
2255 MAX/MIN of an empty table will return a tuple of value NULL.
2256 * allow the use of \; inside the monitor
2257 * the LISTEN/NOTIFY asynchronous notification mechanism now work
2258 * NOTIFY in rule action bodies now work
2259 * hash indices work, and access methods in general should perform better.
2260 creation of large btree indices should be much faster. (thanks to Paul
2263 Other changes and enhancements:
2264 * addition of an EXPLAIN statement used for explaining the query execution
2265 plan (eg. "EXPLAIN SELECT * FROM EMP" prints out the execution plan for
2267 * WARN and NOTICE messages no longer have timestamps on them. To turn on
2268 timestamps of error messages, uncomment the line in
2269 src/backend/utils/elog.h:
2270 /* define ELOG_TIMESTAMPS */
2271 * On an access control violation, the message
2272 "Either no such class or insufficient privilege"
2273 will be given. This is the same message that is returned when
2274 a class is not found. This dissuades non-privileged users from
2275 guessing the existence of privileged classes.
2276 * some additional system catalog changes have been made that are not
2277 visible to the user.
2280 * The -oid option has been added to the "pg_result" tcl command.
2281 pg_result -oid returns oid of the last tuple inserted. If the
2282 last command was not an INSERT, then pg_result -oid returns "".
2283 * the large object interface is available as pg_lo* tcl commands:
2284 pg_lo_open, pg_lo_close, pg_lo_creat, etc.
2286 Portability enhancements and New Ports:
2287 * flex/lex problems have been cleared up. Now, you should be able to use
2288 flex instead of lex on any platforms. We no longer make assumptions of
2289 what lexer you use based on the platform you use.
2290 * The Linux-ELF port is now supported. Various configuration have been
2291 tested: The following configuration is known to work:
2292 kernel 1.2.10, gcc 2.6.3, libc 4.7.2, flex 2.5.2, bison 1.24
2293 with everything in ELF format,
2296 * ipcclean added to the distribution
2297 ipcclean usually does not need to be run, but if your backend crashes
2298 and leaves shared memory segments hanging around, ipcclean will
2299 clean them up for you.
2302 * the user manual has been revised and libpq documentation added.
2304 ------------------------------------------------------------------------
2305 Release v1.0 Postgres95 Beta 0.02
2307 ------------------------------------------------------------------------
2309 Postgres95 Beta 0.02
2311 Detailed Change List
2313 Incompatible changes:
2314 * The SQL statement for creating a database is 'CREATE DATABASE' instead
2315 of 'CREATEDB'. Similarly, dropping a database is 'DROP DATABASE' instead
2316 of 'DESTROYDB'. However, the names of the executables 'createdb' and
2317 'destroydb' remain the same.
2320 * pgperl - a Perl (4.036) interface to Postgres95
2321 * pg_dump - a utility for dumping out a postgres database into a
2322 script file containing query commands. The script files are in a ASCII
2323 format and can be used to reconstruct the database, even on other
2324 machines and other architectures. (Also good for converting
2325 a Postgres 4.2 database to Postgres95 database.)
2327 The following ports have been incorporated into postgres95-beta-0.02:
2328 * the NetBSD port by Alistair Crooks
2329 * the AIX port by Mike Tung
2330 * the Windows NT port by Jon Forrest (more stuff but not done yet)
2331 * the Linux ELF port by Brian Gallew
2333 The following bugs have been fixed in postgres95-beta-0.02:
2334 * new lines not escaped in COPY OUT and problem with COPY OUT when first
2336 * cannot type return to use the default user id in createuser
2337 * SELECT DISTINCT on big tables crashes
2338 * Linux installation problems
2339 * monitor doesn't allow use of 'localhost' as PGHOST
2340 * psql core dumps when doing \c or \l
2341 * the "pgtclsh" target missing from src/bin/pgtclsh/Makefile
2342 * libpgtcl has a hard-wired default port number
2343 * SELECT DISTINCT INTO TABLE hangs
2344 * CREATE TYPE doesn't accept 'variable' as the internallength
2345 * wrong result using more than 1 aggregate in a SELECT
2347 ------------------------------------------------------------------------
2348 Postgres95 Beta 0.03 Postgres95 Beta 0.01
2350 ------------------------------------------------------------------------
2352 Postgres95 Beta 0.01
2356 ------------------------------------------------------------------------
2357 Postgres95 Beta 0.02 Timing Results
2360 ------------------------------------------------------------------------
2364 These timing results are from running the regression test with the commands
2366 % cd src/test/regress
2371 Timing under Linux 2.0.27 seems to have a roughly 5% variation from run to
2372 run, presumably due to the scheduling vagaries of multitasking systems.
2376 As has been the case for previous releases, timing between releases is not
2377 directly comparable since new regression tests have been added. In general,
2378 v6.5 is faster than previous releases.
2380 Timing with fsync() disabled:
2383 02:00 Dual Pentium Pro 180, 224MB, UW-SCSI, Linux 2.0.36, gcc 2.7.2.3 -O2 -m486
2384 04:38 Sparc Ultra 1 143MHz, 64MB, Solaris 2.6
2387 Timing with fsync() enabled:
2390 04:21 Dual Pentium Pro 180, 224MB, UW-SCSI, Linux 2.0.36, gcc 2.7.2.3 -O2 -m486
2393 For the linux system above, using UW-SCSI disks rather than (older) IDE
2394 disks leads to a 50% improvement in speed on the regression test.
2398 The times for this release are not directly comparable to those for previous
2399 releases since some additional regression tests have been included. In
2400 general, however, v6.4 should be slightly faster than the previous release
2404 02:26 Dual Pentium Pro 180, 96MB, UW-SCSI, Linux 2.0.30, gcc 2.7.2.1 -O2 -m486
2408 The times for this release are not directly comparable to those for previous
2409 releases since some additional regression tests have been included and some
2410 obsolete tests involving time travel have been removed. In general, however,
2411 v6.3 is substantially faster than previous releases (thanks, Bruce!).
2414 02:30 Dual Pentium Pro 180, 96MB, UW-SCSI, Linux 2.0.30, gcc 2.7.2.1 -O2 -m486
2415 04:12 Dual Pentium Pro 180, 96MB, EIDE, Linux 2.0.30, gcc 2.7.2.1 -O2 -m486
2420 06:12 Pentium Pro 180, 32MB, EIDE, Linux 2.0.30, gcc 2.7.2 -O2 -m486
2421 12:06 P-100, 48MB, Linux 2.0.29, gcc
2422 39:58 Sparc IPC 32MB, Solaris 2.5, gcc 2.7.2.1 -O -g
2424 ------------------------------------------------------------------------
2426 Postgres95 Beta 0.01