Newer
Older
7.4 Development Branch
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Server Operation
Allow IPv6 server connections (Nigel Kukard, Johan Jordaan, Bruce, Tom, Kurt
Roeckx, Andrew Dunstan)
Fix SSL to handle errors cleanly (Nathan Mueller)
SSL protocol security and performance improvements (Sean Chittenden)
Print lock information when a deadlock is detected (Tom)
Update /tmp socket files regularly to avoid their removal (Tom)
Enable PAM for MAC OS X (Aaron Hillegass)
Make btree indexes fully WAL-safe (Tom)
Allow btree index compaction and empty page reuse (Tom)
Fix inconsistent index lookups during split of first root page (Tom)
Improve free space map allocation logic (Tom)
Preserve free space information between postmaster restarts (Tom)
Set proper schema permissions in initdb (Peter)
Add start time to pg_stat_activity (Neil)
New code to detect corrupt disk pages; erase with zero_damaged_pages (Tom)
New client/server protocol: faster, no username length limit, allow
clean exit
Add transaction status, tableid, columnid to backend protocol (Tom)
Add new binary I/O protocol (Tom)
Remove autocommit server setting; move to client applications (Tom)
New error message wording, error codes, and three levels of error detail (Tom)
_________________________________________________________________
Performance
Add hashing for GROUP BY aggregates (Tom)
Allow nested loops to be smarter about multicolumn indexes (Tom)
Allow multi-key hash joins (Tom)
Improve constant folding (Tom)
Add ability to inline simple SQL functions (Tom)
Reduce memory usage for queries using complex functions (Tom)
Improve GEQO optimizer performance (Tom)
Allow IN/NOT IN to be handled via hash tables (Tom)
Improve NOT IN (subquery) performance (Tom)
Allow most IN subqueries to be processed as joins (Tom)
Allow the postmaster to preload libraries using preload_libraries (Joe)
Improve reverse index scan performance (Tom)
Improve optimizer cost computations, particularly for subqueries (Tom)
Assume WHERE a.x = b.y and b.y = 42 also means a.x = 42 (Tom)
Allow hash/merge joins on complex joins (Tom)
Allow hash joins for more data types (Tom)
Allow join optimization of ANSI joins, disable with join_collapse_limit (Tom)
Add from_collapse_limit to control conversion of subqueries to joins (Tom)
Use faster regex code from TCL (Henry Spencer, Tom)
Use bit-mapped relation sets in the optimizer (Tom)
Improve backend startup time (Tom)
Improve trigger/constraint performance (Stephan)
_________________________________________________________________
Server Configuration
Rename server parameter server_min_messages to log_min_messages (Bruce)
Rename show_*_stats to log_*_stats (Bruce)
Rename show_source_port to log_source_port (Bruce)
Rename hostname_lookup to log_hostname (Bruce)
Add checkpoint_warning to warn of excessive checkpointing (Bruce)
New read-only server parameters for localization (Tom)
Change debug server log messages to output as DEBUG rather than LOG (Bruce)
Prevent server log variables from being turned off by non-super users (Bruce)
log_min_messages/client_min_messages now controls debug_* output (Bruce)
Add Rendezvous server support (Chris Campbell)
Add ability to print only slow statements using log_min_duration_statement
(Christopher)
Allow pg_hba.conf to accept netmasks in CIDR format (Andrew Dunstan)
New is_superuser read-only variable (Tom)
New server-side parameter log_error_verbosity to control error detail (Tom)
postgres --help-config now dumps server config variables (Tom)
Make default shared_buffers 1000 and max_connections 100, if possible (Tom)
Add new columns in pg_settings: context, type, source , min_val, max_val (Joe)
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
Prevent assign_session_authorization() from being confused by
all-numeric user names (Tom)
_________________________________________________________________
Queries
New SQL-standard information schema (Peter)
Add read-only transactions (Peter)
Add server variable regex_flavor to control regular expression
processing (Tom)
Allow ORDER BY in FROM subqueries to be honored by outer queries (Tom)
Print key name in foreign-key violation messages (Dmitry Tkach)
Allow users to see their own queries in pg_stat_activity (Kevin Brown)
Allow subquery aggregates to reference upper query columns (?) (Tom)
Add option to prevent auto-addition of tables referenced in query (Nigel J.
Andrews)
Allow dollar signs in identifiers, except as first character (Tom)
Allow UPDATE ... SET col = DEFAULT (Rod)
Allow expressions to be used in LIMIT/OFFSET (Tom)
_________________________________________________________________
Object Manipulation
Make CREATE SEQUENCE grammar more SQL1999 standards compliant (Neil)
Add FOR EACH STATEMENT statement-level triggers (Neil Conway)
Add DOMAIN CHECK constraints (Rod)
Add ALTER DOMAIN .. SET / DROP NOT NULL, SET / DROP DEFAULT, ADD / DROP
CONSTRAINT (Rod)
Allow zero-column tables (Tom)
Have ALTER TABLE ... ADD PRIMARY KEY add NOT NULL constraint (Rod)
Add ALTER DOMAIN OWNER (Rod)
Add pg_trigger.tgenabled to disable triggers? (Neil)
Add ALTER TABLE ... WITHOUT OIDS? (Rod)
Have parser honor foreign-key constraints if created via ALTER TABLE ADD
COLUMN? (Tom)
Add ALTER SEQUENCE to modify min/max/increment/cache/cycle values (Rod)
Add ALTER TABLE ... CLUSTER ON (Alvaro Herrera)
Improve DOMAIN automatic type casting (Tom)
Disallow dollar signs in operator names (Tom)
Allow SQL200X inheritance syntax LIKE <subtable>, INCLUDING DEFAULTS? (Rod)
Object owners can allow grantees to grant privilege to others?
_________________________________________________________________
Utility Commands
Add ON COMMIT PRESERVE ROWS for temp tables (Gavin)
Allow cursors outside transactions using WITH HOLD (Neil)
Make MOVE/FETCH 0 actually move/fetch 0 (Bruce)
Cause FETCH 1 to return the current cursor row, or zero if at
beginning/end of cursor, per SQL spec (Bruce)
Have MOVE return 0 or 1 depending on cursor position (Bruce)
Properly handle SCROLL with cursors, or report an error (Tom)
Implement SQL92-compatible FIRST, LAST, ABSOLUTE n, RELATIVE n options
for FETCH and MOVE (Tom)
Allow EXPLAIN on DECLARE CURSOR (Tom)
Allow CLUSTER to use index marked as pre-clustered by default (Alvaro Herrera)
Allow CLUSTER without tablename clusters all tables (Alvaro Herrera)
Prevent CLUSTER on partial indexes (Tom)
Allow \r and \r\n termination for COPY files (Bruce)
Disallow literal carriage return as a data value, backslash-carriage-return
and \r still allowed (Bruce)
COPY changes (binary, \.)? (Tom)
Recover from COPY IN/OUT failure cleanly (Tom)
Reduce memory used by COPY (Tom)
Make TRUNCATE transaction-safe (Rod)
Multiple pg_dump fixes, including tar format and large objects
Allow pg_dump to dump specific schemas (Neil)
Allow pg_dump to preserve column storage characteristics (Christopher)
Allow pg_dump to preserve CLUSTER characteristics (Christopher)
Have pg_dumpall use GRANT/REVOKE to dump database-level permissions (Tom)
Allow pg_dumpall to support the -a, -s, -x options of pg_dump (Tom)
Prevent pg_dump from lowercasing identifiers specified on the command line (Tom)
Allow EXPLAIN EXECUTE (Neil)
Allow pg_get_constraintdef() to support UNIQUE, PRIMARY KEY and
CHECK constraints (Christopher)
Improve VACUUM performance on indexes by reducing WAL traffic (Tom)
Allow pg_ctl to better handle non-standard ports (Greg)
Functional indexes now support indexes on column expressions (Tom)
Syntax errors now reported as 'syntax error' rather than 'parse error' (Tom)
Have SHOW TRANSACTION_ISOLATION match input to SET TRANSACTION_ISOLATION (Tom)
Have COMMENT ON DATABASE on non-local database generate a warning (Tom)
_________________________________________________________________
Data Types and Functions
New extra_float_digits server parameter to control float precision display
(Pedro Ferreira, Tom)
Allow +1300 as a numeric timezone specifier, for FJST (Tom)
Remove rarely used oidrand(), oidsrand(), and userfntest() functions (Neil)
Add md5() function to main server, already in /contrib/pgcrypto (Joe)
Increase date range of timestamp (John Cochran)
Change EXTRACT(EPOCH FROM timestamp) so timestamp without time zone
is assumed to be in local time, not GMT (Tom)
Trap division by zero in case the operating system doesn't prevent it (Tom)
Change the NUMERIC data type internally to base 10000 (Tom)
New hostmast() function (Greg Wickham)
Fixes for to_char() (Karel)
Allow functions that can take any argument data type and return
any data type, using ANYELEMENT and ANYARRAY (Joe)
Arrays may now be specified as ARRAY[1,2,3], ARRAY[['a','b'],['c','d']],
or ARRAY[ARRAY[ARRAY[2]]] (Joe)
Allow proper comparisons for arrays (Joe)
Allow indexes on array columns, and used in ORDER BY and DISTINCT (Joe)
Allow WHERE qualification 'expr <oper> ANY/SOME/ALL (array-expr)' (Tom, Joe)
Allow polymorphic SQL functions (Tom, Joe)
Allow array concatenation with '||' and normal array comparisons (Joe)
New array functions array_append(), array_cat(), array_lower(),
array_prepend(), array_to_string(), array_upper(), string_to_array() (Joe)
Allow user defined aggregates to use polymorphic functions (Tom, Joe)
Allow polymorphic user defined aggregates (Tom, Joe)
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
Allow assignments to empty arrays (Joe)
Allow 60 in seconds fields of timestamp, time, interval input values (Tom)
Allow PREPARE/bind of utility commands like FETCH and EXPLAIN (Tom)
Allow CIDR data type to be cast to text (Tom)
Allow the creation of special LIKE indexes for non-C locales (Peter)
Disallow invalid timezone names (Tom)
Trim trailing spaces when CHAR() data is cast to VARCHAR or TEXT (Tom)
Make FLOAT(p) measure the precision p in bits, not decimal digits (Tom)
Add IPv6 support to the inet and cidr data types (Michael Graff)
Add family() function to report whether address is IPv4 or IPv6 (Michael Graff)
Have SHOW DATESTYLE generate output similar to that used by SET DATESTYLE (Tom)
Change DATESTYLE to output its value in a more common format (Tom)
Make EXTRACT(TIMEZONE) and SET/SHOW TIMEZONE follow the SQL convention
for the sign of timezone offsets, ie, positive is east from UTC (Tom)
Fix date_trunc('quarter',...) (B?jthe Zolt?n)
Make initcap() more compatible with Oracle (Mike Nolan)
Honor only DateStyle setting for date entry without a four-digit year (Greg)
Add new DateStyle values MDY, DMY, and YMD, honor US and European for
backward compatibility (Tom)
'now' will no longer work as a default, use now() (change required for prepared
statements) (Tom)
Assume NaN value to be larger than any other value in comparisons (Tom)
_________________________________________________________________
Server-side Languages
Prevent PL/pgSQL crash when RETURN NEXT is used on a zero-row record (Tom)
Make PL/python's spi_execute interface handle NULLs properly (Andrew Bosma)
Allow PL/pgSQL to declare variables of composite types without %ROWTYPE (Tom)
Fix PL/python _quote() function to handle big integers (?)
Make PL/python an untrusted language, now called plpythonu (Kevin Jacobs, Tom)
Change PL/pgSQL EXECUTE INTO to CREATE TABLE AS EXECUTE? (Peter)
Allow polymorphic PL/pgSQL functions (Tom, Joe)
Improved compiled function caching mechanism in PL/pgSQL with full
support for polymorphism (Tom, Joe)
Add new $0 parameter in PL/pgSQL representing the function's actual
return type (Tom, Joe)
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
_________________________________________________________________
Psql
Add "\pset pager always" to always use pager (Greg)
Improve tab completion (Rod, Ross Reedstrom, Ian Barwick)
Reorder \? help into groupings (Harald Armin Massa, Bruce)
Add schema, cast, and conversion backslash commands (Christopher)
\encoding now tracks client_encoding server variable (Tom)
Save edit history into readline history (Ross)
Improve \d display (Christopher)
Enhance HTML mode to be more standards-compliant (Greg)
New '\set autocommit off' capability (Tom)
New '\set verbosity' to control error detail (Tom)
New %T prompt string to show transaction status (Tom)
_________________________________________________________________
Libpq
Allow PQcmdTuples() to return row counts for MOVE and FETCH (Neil)
Add PQfreemem() for freeing memory on Win32, suggest for NOTIFY (Bruce)
Document service capability, and add sample file (Bruce)
Make PQsetdbLogin() have the same defaults as PQconnectdb() (Tom)
Allow libpq to cleanly fail when result sets are too large (Tom)
Improve performance of PGunescapeBytea() (Ben Lamb)
Allow threaded with --enable-thread-safety (Lee Kindness, Philip Yarra)
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
Allow pqInternalNotice() to accept a format string and args instead of
just a preformatted message (Tom, Sean Chittenden)
Allow control SSL negotiation with sslmode values "disable", "allow",
"Prefer", and "require" (Jon Jensen)
_________________________________________________________________
JDBC
Allow setNull on updateable resultsets
Allow executeBatch on a prepared statement (Barry)
Support SSL connections (Barry)
Handle schema names in result sets (Paul Sorenson)
Add refcursor support (Nic Ferrier)
_________________________________________________________________
Miscellaneous Interfaces
Prevent possible memory leak or core dump during libpgtcl shutdown (Tom)
Add ecpg Informix compatibility (Michael)
Add ecpg DECIMAL type that is fixed length, for Informix (Michael)
Allow threaded ecpg with --enable-thread-safety (Lee Kindness, Bruce)
Allow client interfaces to compile under MinGW/win32 (Bruce)
Move python client interface to http://www.pygresql.org (Marc)
_________________________________________________________________
Source Code
Prevent need for separate platform geometry regression result files (Tom)
Improved PPC locking primitive (Reinhard Max)
Embed LD_LIBRARY_PATH used for build process into binaries (Billy)
New palloc0 to allocate and clear memory (Bruce)
Fix locking code for s390x CPU (64-bit) (Tom)
Allow OpenBSD to use local indent credentials (William Ahern)
Allow read-only query plans (Tom)
Add Darwin startup scripts (David Wheeler)
Allow libpq to compile with Borland C++ compiler (Lester Godwin, Karl Waclawek)
Use our own version of getopt_long() if needed (Peter)
Convert administration scripts to C (Peter)
Bison >=1.85 is now required for grammar changes
Merge documentation into one book (Peter)
Add Win32 compatibility functions (Bruce)
New ereport() function for error reporting (Tom)
Support Intel Linux compiler (Peter)
Improve Linux startup scripts (Slawomir Sudnik, Darko Prenosil)
Add support for AMD Opteron and Itanium (Jeffrey W. Baker, Bruce)
_________________________________________________________________
Contrib
Change dbmirror license to BSD
Improve earthdistance (Bruno Wolff III)
Portability improvements to pgcrypto (Marko Kreen)
Prevent xml crash (John Gray, Michael Richards)
Upgrade oracle
Upgrade mysql
Update cube (Bruno Wolff III)
Update earthdistance to use cube (Bruno Wolff III)
Update btree_gist (Oleg)
Add hashed based crosstab function to tablefuncs (Joe)
Add serial column to order connectby() siblings in tablefuncs (Nabil Sayegh,Joe)
Add named persistent connections to dblink (Shridhar Daithanka)
New pg_autovacuum allows automatic VACUUM (Matthew T. O'Connor)
Allow pgbench to honor PGHOST, PGPORT, PGUSER env. variables (Tatsuo)
Improve intarray (Teodor Sigaev)
Improve pgstattuple (Rod)
Fix bug in metaphone() in fuzzystrmatch
Improve adddepend (Rod)
Update spi/timetravel (B?jthe Zolt?n)
Fix dbase -s option (Thomas Behr)
_________________________________________________________________
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
Release 7.3.3
Release date: 2003-05-22
This has a variety of fixes from 7.3.2.
_________________________________________________________________
Migration to version 7.3.3
A dump/restore is *not* required for those running 7.3.*.
_________________________________________________________________
Changes
Repair sometimes-incorrect computation of StartUpID after a crash
Avoid slowness with lots of deferred triggers in one transaction (Stephan)
Don't lock referenced row when UPDATE doesn't change foreign key's value
(Jan)
Use -fPIC not -fpic on Sparc (Tom Callaway)
Repair lack of schema-awareness in contrib/reindexdb
Fix contrib/intarray error for zero-element result array (Teodor)
Ensure createuser script will exit on control-C (Oliver)
Fix errors when the type of a dropped column has itself been dropped
CHECKPOINT does not cause database panic on failure in noncritical steps
Accept 60 in seconds fields of timestamp, time, interval input values
Issue notice, not error, if TIMESTAMP, TIME, or INTERVAL precision too
large
Fix abstime-to-time cast function (fix is not applied unless you initdb)
Fix pg_proc entry for timestamptz_izone (fix is not applied unless you
initdb)
Make EXTRACT(EPOCH FROM timestamp without time zone) treat input as
local time
'now'::timestamptz gave wrong answer if timezone changed earlier in
transaction
HAVE_INT64_TIMESTAMP code for time with timezone overwrote its input
Accept GLOBAL TEMP/TEMPORARY as a synonym for TEMPORARY
Avoid improper schema-permissions-check failure in foreign-key triggers
Fix bugs in foreign-key triggers for SET DEFAULT action
Fix incorrect time-qual check in row fetch for UPDATE and DELETE triggers
Foreign-key clauses were parsed but ignored in ALTER TABLE ADD COLUMN
Fix createlang script breakage for case where handler function already
exists
Fix misbehavior on zero-column tables in pg_dump, COPY, ANALYZE, other
places
Fix misbehavior of func_error() on type names containing '%'
Fix misbehavior of replace() on strings containing '%'
Regular-expression patterns containing certain multibyte characters failed
Account correctly for NULLs in more cases in join size estimation
Avoid conflict with system definition of isblank() function or macro
Fix failure to convert large code point values in EUC_TW conversions
(Tatsuo)
Fix error recovery for SSL_read/SSL_write calls
Don't do early constant-folding of type coercion expressions
Validate page header fields immediately after reading in any page
Repair incorrect check for ungrouped variables in unnamed joins
Fix buffer overrun in to_ascii (Guido Notari)
contrib/ltree fixes (Teodor)
Fix core dump in deadlock detection on machines where char is unsigned
Avoid running out of buffers in many-way indexscan (bug introduced in 7.3)
Fix planner's selectivity estimation functions to handle domains properly
Fix dbmirror memory-allocation bug (Steven Singer)
Prevent infinite loop in ln(numeric) due to roundoff error.
GROUP BY got confused if there were multiple equal GROUP BY items
Fix bad plan when inherited UPDATE/DELETE references another inherited
table
Prevent clustering on incomplete (partial or non-NULL-storing) indexes
Service shutdown request at proper time if it arrives while still
starting up
Fix left-links in temporary indexes (could make backwards scans miss
entries)
Fix incorrect handling of client_encoding setting in postgresql.conf
(Tatsuo)
Fix failure to respond to 'pg_ctl stop -m fast' after Async_NotifyHandler
runs
Fix SPI for case where rule contains multiple statements of the same type
Fix problem with checking for wrong type of access permission in rule query
Fix problem with EXCEPT in CREATE RULE
Prevent problem with dropping temp tables having serial columns
Fix replace_vars_with_subplan_refs failure in complex views
Fix regexp slowness in single-byte encodings (Tatsuo)
Allow qualified type names in CREATE CAST and DROP CAST
Accept 'SETOF type[]', which formerly had to be written 'SETOF _type'
Fix pg_dump core dump in some cases with procedural languages
Force ISO datestyle in pg_dump output, for portability (Oliver)
pg_dump failed to handle error return from lo_read (Oleg Drokin)
pg_dumpall failed with groups having no members (Nick Eskelinen)
pg_dumpall failed to recognize --globals-only switch
pg_restore failed to restore blobs if -X disable-triggers is specified
Repair intrafunction memory leak in plpgsql
pltcl's elog command dumped core if given wrong parameters (Ian Harding)
plpython used wrong value of atttypmod (Brad McLean)
Fix improper quoting of boolean values in Python interface (D'Arcy)
Added addDataType() method to PGConnection interface for JDBC
Fixed various problems with updateable ResultSets for JDBC (Shawn Green)
Fixed various problems with DatabaseMetaData for JDBC (Kris Jurka,
Peter Royal)
Fixed problem with parsing table ACLs in JDBC
Better error message for character set conversion problems in JDBC
_________________________________________________________________
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
Release 7.3.2
Release date: 2003-02-04
This has a variety of fixes from 7.3.1.
_________________________________________________________________
Migration to version 7.3.2
A dump/restore is *not* required for those running 7.3.*.
_________________________________________________________________
Changes
Restore creation of OID column in CREATE TABLE AS / SELECT INTO
Fix pg_dump core dump when dumping views having comments
Dump DEFERRABLE/INITIALLY DEFERRED constraints properly
Fix UPDATE when child table's column numbering differs from parent
Increase default value of max_fsm_relations
Fix problem when fetching backwards in a cursor for a single-row query
Make backward fetch work properly with cursor on SELECT DISTINCT query
Fix problems with loading pg_dump files containing contrib/lo usage
Fix problem with all-numeric user names
Fix possible memory leak and core dump during disconnect in libpgtcl
Make plpython's spi_execute command handle nulls properly (Andrew Bosma)
Adjust plpython error reporting so that its regression test passes again
Work with bison 1.875
Handle mixed-case names properly in plpgsql's %type (Neil)
Fix core dump in pltcl when executing a query rewritten by a rule
Repair array subscript overruns (per report from Yichen Xie)
Reduce MAX_TIME_PRECISION from 13 to 10 in floating-point case
Correctly case-fold variable names in per-database and per-user settings
Fix coredump in plpgsql's RETURN NEXT when SELECT into record returns
no rows
Fix outdated use of pg_type.typprtlen in python client interface
Correctly handle fractional seconds in timestamps in JDBC driver
Improve performance of getImportedKeys() in JDBC
Make shared-library symlinks work standardly on HPUX (Giles)
Repair inconsistent rounding behavior for timestamp, time, interval
SSL negotiation fixes (Nathan Mueller)
Make libpq's ~/.pgpass feature work when connecting with PQconnectDB
Update my2pg, ora2pg
Translation updates
Add casts between types lo and oid in contrib/lo
fastpath code now checks for privilege to call function
_________________________________________________________________
Release 7.3.1
Release date: 2002-12-18
This has a variety of fixes from 7.3.
_________________________________________________________________
Migration to version 7.3.1
A dump/restore is *not* required for those running 7.3. However, it
should be noted that the main PostgreSQL interface library, libpq, has
a new major version number for this release, which may require
recompilation of client code in certain cases.
_________________________________________________________________
Changes
Fix a core dump of COPY TO when client/server encodings don't match (Tom)
Allow pg_dump to work with pre-7.2 servers (Philip)
Fix problem with deletion of per-user/per-database config settings (Tom)
/contrib/vacuumlo fix (Tom)
Allow 'password' encryption even when pg_shadow contains MD5 passwords
(Bruce)
/contrib/dbmirror fix (Steven Singer)
Optimizer fixes (Tom)
/contrib/tsearch fixes (Teodor Sigaev, Magnus)
Allow locale names to be mixed case (Nicolai Tufar)
Increment libpq library's major version number (Bruce)
pg_hba.conf error reporting fixes (Bruce, Neil)
Add SCO Openserver 5.0.4 as a supported platform (Bruce)
Prevent EXPLAIN from crashing server (Tom)
SSL fixes (Nathan Mueller)
Prevent composite column creation via ALTER TABLE (Tom)
_________________________________________________________________
Release date: 2002-11-27
_________________________________________________________________
Overview
Schemas allow users to create objects in separate namespaces,
so two people or applications can have tables with the same
name. There is also a public schema for shared tables.
Table/index creation can be restricted by removing permissions
on the public schema.
PostgreSQL now supports the ALTER TABLE ... DROP COLUMN
functionality.
Functions returning multiple rows and/or multiple columns are
now much easier to use than before. You can call such a "table
function" in the SELECT FROM clause, treating its output like a
table. Also, PL/pgSQL functions can now return sets.
PostgreSQL now supports prepared queries, for improved
performance.
PostgreSQL now records object dependencies, which allows
improvements in many areas. "DROP" statements now take either
CASCADE or RESTRICT to control whether dependent objects are
also dropped.
Functions and procedural languages now have privileges, and
functions can be defined to run with the privileges of their
creator.
Internationalization
Both multibyte and locale support are now always enabled.
A variety of logging options have been enhanced.
A large number of interfaces have been moved to
http://gborg.postgresql.org where they can be developed and
released independently.
By default, functions can now take up to 32 parameters, and
identifiers can be up to 63 bytes long. Also, OPAQUE is now
deprecated: there are specific "pseudo-datatypes" to represent
each of the former meanings of OPAQUE in function argument and
result types.
_________________________________________________________________
Migration to version 7.3
A dump/restore using pg_dump is required for those wishing to migrate
data from any previous release. If your application examines the
system catalogs, additional changes will be required due to the
introduction of schemas in 7.3; for more information, see:
http://www.ca.postgresql.org/docs/momjian/upgrade_tips_7.3.
Observe the following incompatibilities:
* Pre-6.3 clients are no longer supported.
* "pg_hba.conf" now has a column for the user name and additional
features. Existing files need to be adjusted.
* Several "postgresql.conf" logging parameters have been renamed.
* LIMIT #,# has been disabled; use LIMIT # OFFSET #.
* "INSERT" statements with column lists must specify a value for
each specified column. For example, INSERT INTO tab (col1, col2)
VALUES ('val1') is now invalid. It's still allowed to supply fewer
columns than expected if the "INSERT" does not have a column list.
* serial columns are no longer automatically UNIQUE; thus, an index
will not automatically be created.
* A "SET" command inside an aborted transaction is now rolled back.
* "COPY" no longer considers missing trailing columns to be null.
All columns need to be specified. (However, one may achieve a
similar effect by specifying a column list in the "COPY" command.)
* The data type timestamp is now equivalent to timestamp without
time zone, instead of timestamp with time zone.
* Pre-7.3 databases loaded into 7.3 will not have the new object
dependencies for serial columns, unique constraints, and foreign
keys. See the directory "contrib/adddepend/" for a detailed
description and a script that will add such dependencies.
* An empty string ('') is no longer allowed as the input into an
integer field. Formerly, it was silently interpreted as 0.
_________________________________________________________________
Changes
Add pg_locks view to show locks (Neil)
Security fixes for password negotiation memory allocation (Neil)
Remove support for version 0 FE/BE protocol (PostgreSQL 6.2 and
earlier) (Tom)
Reserve the last few backend slots for superusers, add parameter
superuser_reserved_connections to control this (Nigel J. Andrews)
_________________________________________________________________
Improve startup by calling localtime() only once (Tom)
Cache system catalog information in flat files for faster startup
(Tom)
Improve caching of index information (Tom)
Optimizer improvements (Tom, Fernando Nasser)
Catalog caches now store failed lookups (Tom)
Hash function improvements (Neil)
Improve performance of query tokenization and network handling (Peter)
Speed improvement for large object restore (Mario Weilguni)
Mark expired index entries on first lookup, saving later heap fetches
(Tom)
Avoid excessive NULL bitmap padding (Manfred Koizar)
Add BSD-licensed qsort() for Solaris, for performance (Bruce)
Reduce per-row overhead by four bytes (Manfred Koizar)
Fix GEQO optimizer bug (Neil Conway)
Make WITHOUT OID actually save four bytes per row (Manfred Koizar)
Add default_statistics_target variable to specify ANALYZE buckets
(Neil)
Use local buffer cache for temporary tables so no WAL overhead (Tom)
Improve free space map performance on large tables (Stephen Marshall,
Tom)
Improved WAL write concurrency (Tom)
_________________________________________________________________
Add privileges on functions and procedural languages (Peter)
Add OWNER to CREATE DATABASE so superusers can create databases
on behalf of unprivileged users. (Gavin Sherry, Tom)
Add new object permission bits EXECUTE and USAGE (Tom)
Add SET SESSION AUTHORIZATION DEFAULT and RESET SESSION AUTHORIZATION
(Tom)
Allow functions to be executed with the privilege of the function
owner (Peter)
_________________________________________________________________
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
Server log messages now tagged with LOG, not DEBUG (Bruce)
Add user column to pg_hba.conf (Bruce)
Have log_connections output two lines in log file (Tom)
Remove debug_level from postgresql.conf, now server_min_messages
(Bruce)
New ALTER DATABASE/USER ... SET command for per-user/database
initialization (Peter)
New parameters server_min_messages and client_min_messages to
control which messages are sent to the server logs or client
applications (Bruce)
Allow pg_hba.conf to specify lists of users/databases separated by
commas, group names prepended with +, and file names prepended
with @ (Bruce)
Remove secondary password file capability and pg_password utility
(Bruce)
Add variable db_user_namespace for database-local user names (Bruce)
SSL improvements (Bear Giles)
Make encryption of stored passwords the default (Bruce)
Allow pg_statistics to be reset by calling pg_stat_reset()
(Christopher)
Add log_duration parameter (Bruce)
Rename debug_print_query to log_statement (Bruce)
Rename show_query_stats to show_statement_stats (Bruce)
Add param log_min_error_statement to print commands to logs on error
(Gavin)
_________________________________________________________________
Make cursors insensitive, meaning their contents do not change (Tom)
Disable LIMIT #,# syntax; now only LIMIT # OFFSET # supported (Bruce)
Increase identifier length to 63 (Neil, Bruce)
UNION fixes for merging >= 3 columns of different lengths (Tom)
Add DEFAULT keyword to INSERT, e.g., INSERT ... (..., DEFAULT, ...)
(Rod)
Allow views to have default values using ALTER COLUMN ... SET DEFAULT
(Neil)
Fail on INSERTs with column lists that don't supply all column
values, e.g., INSERT INTO tab (col1, col2) VALUES ('val1'); (Rod)
Fix for join aliases (Tom)
Fix for FULL OUTER JOINs (Tom)
Improve reporting of invalid identifier and location (Tom, Gavin)
Fix OPEN cursor(args) (Tom)
Allow 'ctid' to be used in a view and currtid(viewname) (Hiroshi)
Fix for CREATE TABLE AS with UNION (Tom)
SQL99 syntax improvements (Thomas)
Add statement_timeout variable to cancel queries (Bruce)
Allow prepared queries with PREPARE/EXECUTE (Neil)
Allow FOR UPDATE to appear after LIMIT/OFFSET (Bruce)
Add variable autocommit (Tom, David Van Wie)
_________________________________________________________________
Make equals signs optional in CREATE DATABASE (Gavin Sherry)
Make ALTER TABLE OWNER change index ownership too (Neil)
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
New ALTER TABLE tabname ALTER COLUMN colname SET STORAGE controls
TOAST storage, compression (John Gray)
Add schema support, CREATE/DROP SCHEMA (Tom)
Create schema for temporary tables (Tom)
Add variable search_path for schema search (Tom)
Add ALTER TABLE SET/DROP NOT NULL (Christopher)
New CREATE FUNCTION volatility levels (Tom)
Make rule names unique only per table (Tom)
Add 'ON tablename' clause to DROP RULE and COMMENT ON RULE (Tom)
Add ALTER TRIGGER RENAME (Joe)
New current_schema() and current_schemas() inquiry functions (Tom)
Allow functions to return multiple rows (table functions) (Joe)
Make WITH optional in CREATE DATABASE, for consistency (Bruce)
Add object dependency tracking (Rod, Tom)
Add RESTRICT/CASCADE to DROP commands (Rod)
Add ALTER TABLE DROP for non-CHECK CONSTRAINT (Rod)
Autodestroy sequence on DROP of table with SERIAL (Rod)
Prevent column dropping if column is used by foreign key (Rod)
Automatically drop constraints/functions when object is dropped (Rod)
Add CREATE/DROP OPERATOR CLASS (Bill Studenmund, Tom)
Add ALTER TABLE DROP COLUMN (Christopher, Tom, Hiroshi)
Prevent inherited columns from being removed or renamed (Alvaro
Herrera)
Fix foreign key constraints to not error on intermediate database
states (Stephan)
Propagate column or table renaming to foreign key constraints
Add CREATE OR REPLACE VIEW (Gavin, Neil, Tom)
Add CREATE OR REPLACE RULE (Gavin, Neil, Tom)
Have rules execute alphabetically, returning more predictable values
(Tom)
Triggers are now fired in alphabetical order (Tom)
Add /contrib/adddepend to handle pre-7.3 object dependencies (Rod)
Allow better casting when inserting/updating values (Tom)
_________________________________________________________________
Have COPY TO output embedded carriage returns and newlines as \r and
\n (Tom)
Allow DELIMITER in COPY FROM to be 8-bit clean (Tatsuo)
Make pg_dump use ALTER TABLE ADD PRIMARY KEY, for performance (Neil)
Disable brackets in multistatement rules (Bruce)
Disable VACUUM from being called inside a function (Bruce)
Allow dropdb and other scripts to use identifiers with spaces (Bruce)
Restrict database comment changes to the current database
Allow comments on operators, independent of the underlying function
(Rod)
Rollback SET commands in aborted transactions (Tom)
EXPLAIN now outputs as a query (Tom)
Display condition expressions and sort keys in EXPLAIN (Tom)
Add 'SET LOCAL var = value' to set configuration variables for a
single transaction (Tom)
Allow ANALYZE to run in a transaction (Bruce)
Improve COPY syntax using new WITH clauses, keep backward
compatibility (Bruce)
Fix pg_dump to consistently output tags in non-ASCII dumps (Bruce)
Make foreign key constraints clearer in dump file (Rod)
Add COMMENT ON CONSTRAINT (Rod)
Allow COPY TO/FROM to specify column names (Brent Verner)
Dump UNIQUE and PRIMARY KEY constraints as ALTER TABLE (Rod)
Have SHOW output a query result (Joe)
Generate failure on short COPY lines rather than pad NULLs (Neil)
Fix CLUSTER to preserve all table attributes (Alvaro Herrera)
New pg_settings table to view/modify GUC settings (Joe)
Add smart quoting, portability improvements to pg_dump output (Peter)
Dump serial columns out as SERIAL (Tom)
Enable large file support, >2G for pg_dump (Peter, Philip Warner,
Bruce)
Disallow TRUNCATE on tables that are involved in referential
constraints (Rod)
Have TRUNCATE also auto-truncate the toast table of the relation (Tom)
Add clusterdb utility that will auto-cluster an entire database
based on previous CLUSTER operations (Alvaro Herrera)
Overhaul pg_dumpall (Peter)
Allow REINDEX of TOAST tables (Tom)
Implemented START TRANSACTION, per SQL99 (Neil)
Fix rare index corruption when a page split affects bulk delete (Tom)
Fix ALTER TABLE ... ADD COLUMN for inheritance (Alvaro Herrera)
_________________________________________________________________
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
Fix factorial(0) to return 1 (Bruce)
Date/time/timezone improvements (Thomas)
Fix for array slice extraction (Tom)
Fix extract/date_part to report proper microseconds for timestamp
(Tatsuo)
Allow text_substr() and bytea_substr() to read TOAST values more
efficiently (John Gray)
Add domain support (Rod)
Make WITHOUT TIME ZONE the default for TIMESTAMP and TIME data types
(Thomas)
Allow alternate storage scheme of 64-bit integers for date/time types
using --enable-integer-datetimes in configure (Thomas)
Make timezone(timestamptz) return timestamp rather than a string
(Thomas)
Allow fractional seconds in date/time types for dates prior to 1BC
(Thomas)
Limit timestamp data types to 6 decimal places of precision (Thomas)
Change timezone conversion functions from timetz() to timezone()
(Thomas)
Add configuration variables datestyle and timezone (Tom)
Add OVERLAY(), which allows substitution of a substring in a string
(Thomas)
Add SIMILAR TO (Thomas, Tom)
Add regular expression SUBSTRING(string FROM pat FOR escape) (Thomas)
Add LOCALTIME and LOCALTIMESTAMP functions (Thomas)
Add named composite types using CREATE TYPE typename AS (column)
(Joe)
Allow composite type definition in the table alias clause (Joe)
Add new API to simplify creation of C language table functions (Joe)
Remove ODBC-compatible empty parentheses from calls to SQL99
functions for which these parentheses do not match the standard
(Thomas)
Allow macaddr data type to accept 12 hex digits with no separators
(Mike Wyer)
Add CREATE/DROP CAST (Peter)
Add IS DISTINCT FROM operator (Thomas)
Add SQL99 TREAT() function, synonym for CAST() (Thomas)
Add pg_backend_pid() to output backend pid (Bruce)
Add IS OF / IS NOT OF type predicate (Thomas)
Allow bit string constants without fully-specified length (Thomas)
Allow conversion between 8-byte integers and bit strings (Thomas)
Implement hex literal conversion to bit string literal (Thomas)
Allow table functions to appear in the FROM clause (Joe)
Increase maximum number of function parameters to 32 (Bruce)
No longer automatically create index for SERIAL column (Tom)
Add current_database() (Rod)
Fix cash_words() to not overflow buffer (Tom)
Add functions replace(), split_part(), to_hex() (Joe)
Fix LIKE for bytea as a right-hand argument (Joe)
Prevent crashes caused by SELECT cash_out(2) (Tom)
Fix to_char(1,'FM999.99') to return a period (Karel)
Fix trigger/type/language functions returning OPAQUE to return
proper type (Tom)
_________________________________________________________________
Add additional encodings: Korean (JOHAB), Thai (WIN874), Vietnamese
(TCVN), Arabic (WIN1256), Simplified Chinese (GBK), Korean (UHC)
(Eiji Tokuya)
Enable locale support by default (Peter)
Add locale variables (Peter)
Escape byes >= 0x7f for multibyte in PQescapeBytea/PQunescapeBytea
(Tatsuo)
Add locale awareness to regular expression character classes
Enable multibyte support by default (Tatso)
Add GB18030 multibyte support (Bill Huang)
Add CREATE/DROP CONVERSION, allowing loadable encodings (Tatsuo,
Kaori)
Add pg_conversion table (Tatsuo)
Add SQL99 CONVERT() function (Tatsuo)
pg_dumpall, pg_controldata, and pg_resetxlog now national-language
aware (Peter)
New and updated translations
_________________________________________________________________
Allow recursive SQL function (Peter)
Change PL/Tcl build to use configured compiler and Makefile.shlib
(Peter)
Overhaul the PL/pgSQL FOUND variable to be more Oracle-compatible
(Neil, Tom)
Allow PL/pgSQL to handle quoted identifiers (Tom)
Allow set-returning PL/pgSQL functions (Neil)
Make PL/pgSQL schema-aware (Joe)
Remove some memory leaks (Nigel J. Andrews, Tom)
_________________________________________________________________
Don't lowercase psql \connect database name for 7.2.0 compatibility
(Tom)
Add psql \timing to time user queries (Greg Sabino Mullane)
Have psql \d show index information (Greg Sabino Mullane)
New psql \dD shows domains (Jonathan Eisler)
Allow psql to show rules on views (Paul ?)
Fix for psql variable substitution (Tom)
Allow psql \d to show temporary table structure (Tom)
Allow psql \d to show foreign keys (Rod)
Fix \? to honor \pset pager (Bruce)
Have psql reports its version number on startup (Tom)
Allow \copy to specify column names (Tom)
_________________________________________________________________
Add $HOME/.pgpass to store host/user password combinations (Alvaro
Herrera)
Add PQunescapeBytea() function to libpq (Patrick Welche)
Fix for sending large queries over non-blocking connections
(Bernhard Herzog)
Fix for libpq using timers on Win9X (David Ford)
Allow libpq notify to handle servers with different-length
identifiers (Tom)
Add libpq PQescapeString() and PQescapeBytea() to Win32 (Bruce)
Fix for SSL with non-blocking connections (Jack Bates)
Add libpq connection timeout parameter (Denis A Ustimenko)
_________________________________________________________________
Allow JDBC to compile with JDK 1.4 (Dave)
Add JDBC 3 support (Barry)
Allows JDBC to set loglevel by adding ?loglevel=X to the connection
URL (Barry)
Add Driver.info() message that prints out the version number (Barry)
Add updateable result sets (Raghu Nidagal, Dave)
Add support for callable statements (Paul Bethe)
Add query cancel capability
Add refresh row (Dave)
Fix MD5 encryption handling for multibyte servers (Jun Kawai)
Add support for prepared statements (Barry)
_________________________________________________________________
Fixed ECPG bug concerning octal numbers in single quotes (Michael)
Move src/interfaces/libpgeasy to http://gborg.postgresql.org (Marc,
Bruce)
Improve Python interface (Elliot Lee, Andrew Johnson, Greg Copeland)
Add libpgtcl connection close event (Gerhard Hintermayer)
Move src/interfaces/libpq++ to http://gborg.postgresql.org (Marc,
Bruce)
Move src/interfaces/odbc to http://gborg.postgresql.org (Marc)
Move src/interfaces/libpgeasy to http://gborg.postgresql.org (Marc,
Bruce)
Move src/interfaces/perl5 to http://gborg.postgresql.org (Marc,
Bruce)
Remove src/bin/pgaccess from main tree, now at
http://www.pgaccess.org (Bruce)
Add pg_on_connection_loss command to libpgtcl (Gerhard Hintermayer,
Tom)
_________________________________________________________________
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
Fix for parallel make (Peter)
AIX fixes for linking Tcl (Andreas Zeugswetter)
Allow PL/Perl to build under Cygwin (Jason Tishler)
Improve MIPS compiles (Peter, Oliver Elphick)
Require Autoconf version 2.53 (Peter)
Require readline and zlib by default in configure (Peter)
Allow Solaris to use Intimate Shared Memory (ISM), for performance
(Scott Brunza, P.J. Josh Rovero)
Always enable syslog in compile, remove --enable-syslog option
(Tatsuo)
Always enable multibyte in compile, remove --enable-multibyte option
(Tatsuo)
Always enable locale in compile, remove --enable-locale option
(Peter)
Fix for Win9x DLL creation (Magnus Naeslund)
Fix for link() usage by WAL code on Win32, BeOS (Jason Tishler)
Add sys/types.h to c.h, remove from main files (Peter, Bruce)
Fix AIX hang on SMP machines (Tomoyuki Niijima)
AIX SMP hang fix (Tomoyuki Niijima)
Fix pre-1970 date handling on newer glibc libraries (Tom)
Fix PowerPC SMP locking (Tom)
Prevent gcc -ffast-math from being used (Peter, Tom)
Bison >= 1.50 now required for developer builds
Kerberos 5 support now builds with Heimdal (Peter)
Add appendix in the User's Guide which lists SQL features (Thomas)
Improve loadable module linking to use RTLD_NOW (Tom)
New error levels WARNING, INFO, LOG, DEBUG[1-5] (Bruce)
New src/port directory holds replaced libc functions (Peter, Bruce)
New pg_namespace system catalog for schemas (Tom)
Add pg_class.relnamespace for schemas (Tom)
Add pg_type.typnamespace for schemas (Tom)
Add pg_proc.pronamespace for schemas (Tom)
Restructure aggregates to have pg_proc entries (Tom)
System relations now have their own namespace, pg_* test not required
(Fernando Nasser)
Rename TOAST index names to be *_index rather than *_idx (Neil)
Add namespaces for operators, opclasses (Tom)
Add additional checks to server control file (Thomas)