1
2
3
4
5
6
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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
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
189
190
191
192
193
194
195
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
229
230
231
232
233
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
261
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
324
325
326
327
328
329
330
331
332
333
334
335
336
337
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
TODO list for PostgreSQL
========================
Last updated: Wed Oct 31 10:10:58 EST 2001
Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us)
The most recent version of this document can be viewed at
the PostgreSQL web site, http://www.PostgreSQL.org.
A dash (-) marks changes that will appear in the upcoming 7.2 release.
Bracketed items "[]" have more detailed.
RELIABILITY
-----------
* -SELECT pg_class FROM pg_class generates strange error (Bruce)
* PL/PgSQL does not handle quoted mixed-case identifiers
ENHANCEMENTS
------------
URGENT
* Add replication of distributed databases [replication]
o automatic failover
o load balancing
o master/slave replication
o multi-master replication
o partition data across servers
o sample implementation in contrib/rserv
o queries across databases or servers (two-phase commit)
* Point-in-time data recovery using backup and write-ahead log
* Allow row re-use without vacuum (Tom)
ADMIN
* Improve control over user privileges, including table creation and
lock use [privileges] (Karel, others)
* -Permission to DELETE table also allows UPDATE (Peter E)
* Allow elog() to return error codes, module name, file name, line
number, not just messages (Peter E) [elog]
* -Allow international error message support and add error codes[elog](Peter E)
* -Remove unused sort files on postmaster startup (Bruce)
* Remove unreferenced table files and temp tables during database vacuum
or postmaster startup
* -Remove unreferenced sort files during postmaster startup (Bruce)
* Add table name mapping for numeric file names
* -Better document pg_hba.conf host-based authentication (Bruce)
* -Encrpyt passwords in pg_shadow table using MD5 (Bruce, Vince)
* Incremental backups
* Make it easier to create a database owned by someone who can't createdb
* Remove behavior of postmaster -o after making postmaster/postgres
flags unique
TYPES
* Add domain capability [domain]
* Add IPv6 capability to INET/CIDR types
* -Add conversion function from text to inet
* Store binary-compatible type information in the system
* Allow better handling of numeric constants, type conversion [typeconv]
* Support construction of array result values in expressions
* Remove Money type, add money formatting for decimal type
* SELECT cash_out(2) crashes because of opaque
* Declare typein/out functions in pg_proc with a special "C string" data type
* Functions returning sets do not totally work
* SELECT col FROM tab WHERE numeric_col = 10.1 fails, requires quotes
* Missing optimizer selectivities for date, r-tree, etc. [optimizer]
* -Add SQL standard function bit_length() (Peter E)
* -Make oid use unsigned int more reliably (Tom)
* ARRAYS
o Allow nulls in arrays
o Allow arrays to be ORDER'ed
o fix array handling in ECPG
* BINARY DATA
o -Add non-large-object binary field (already exists -- bytea)
o -Make binary interface for TOAST columns (base64)
o Improve vacuum of large objects, like /contrib/vacuumlo
o Add security checking for large objects
o Make file in/out interface for TOAST columns, similar to large object
interface (force out-of-line storage and no compression)
o Auto-delete large objects when referencing row is deleted
MULTILANGUAGE SUPPORT
* Add NCHAR (as distinguished from ordinary varchar),
* Allow LOCALE on a per-column basis, default to ASCII
* Support multiple simultaneous character sets, per SQL92
* -Reject character sequences those are not valid in their charset (Tatsuo)
* -Make functions more multi-byte aware, e.g. trim() (Tatsuo)
* -Make n of CHAR(n)/VARCHAR(n) the number of letters, not bytes (Tatsuo)
* Allow setting database character set without multibyte enabled
* Improve Unicode combined character handling
* Optimize locale to have minimal performance impact when not used (Peter E)
VIEWS
* Automatically create rules on views so they are updateable, per SQL92 [view]
* Add the functionality for WITH CHECK OPTION clause of CREATE VIEW
* Allow NOTIFY in rules involving conditionals
* -Evaluate INSERT rules at end of query, rather than beginning (Jan)
* Allow temporary views
* Move psql backslash information into views
INDEXES
* Allow CREATE INDEX zman_index ON test (date_trunc( 'day', zman ) datetime_ops)
fails index can't store constant parameters
* Add FILLFACTOR to index creation
* Order duplicate index entries by tid for faster heap lookups
* -Re-enable partial indexes
* -Prevent pg_attribute from having duplicate oids for indexes (Tom)
* Allow inherited tables to inherit index, UNIQUE constraint, and primary
key, foreign key [inheritance]
* UNIQUE INDEX on base column not honored on inserts from inherited table
INSERT INTO inherit_table (unique_index_col) VALUES (dup) should fail
[inheritance]
* Allow DELETE/UPDATE on inherited table
* Add UNIQUE capability to non-btree indexes
* Certain indexes will not shrink, e.g. oid indexes with many inserts
* Have UPDATE/DELETE clean out indexes
* Add btree index support for reltime, tinterval, regproc
* Add rtree index support for line, lseg, path, point
* Use indexes for min() and max() or convert to SELECT col FROM tab ORDER
BY col DESC LIMIT 1 if appropriate index exists and WHERE clause acceptible
* Use index to restrict rows returned by multi-key index when used with
non-consecutive keys or OR clauses, so fewer heap accesses
* Allow SELECT * FROM tab WHERE int2col = 4 to use int2col index, int8,
float4, numeric/decimal too [optimizer]
* -Use indexes with CIDR '<<' (contains) operator
* Allow LIKE indexing optimization for non-ASCII locales
* Be smarter about insertion of already-ordered data into btree index
* -Gather more accurate dispersion statistics using indexes (Tom)
* Add deleted bit to index tuples to reduce heap access
* Prevent index uniqueness checks when UPDATE does not modifying column
* Add bitmap indexes [performance]
* Improve handling of index scans for NULL
* Improve concurrency in GIST
SYSTEM TABLES
* -Add unique indexes to pg_shadow.usename and pg_shadow.usesysid or
switch to pg_shadow.oid as user id (Tom)
* -Add unique indexes on pg_database (Tom)
* -Check all system tables and add unique indexes as needed (Tom)
* -Remove pg_listener index (Tom)
* -Remove unused pg_variable, pg_inheritproc, pg_ipl tables (Bruce)
COMMANDS
* Add SIMILAR TO to allow character classes, 'pg_[a-c]%'
* -Allow LOCK TABLE tab1, tab2, tab3 so all tables locked in unison [lock]
* Allow RULE recompilation
* Add BETWEEN ASYMMETRIC/SYMMETRIC
* Remove LIMIT #,# and force use LIMIT and OFFSET clauses in 7.3 (Bruce)
* Allow LIMIT/OFFSET to use expressions
* Allow PL/PgSQL's RAISE function to take expressions
* Change PL/PgSQL to use palloc() instead of malloc()
* -Allow GRANT/REVOKE to handle multiple user/group names
* -Allow CREATEUSER/CREATEDB ordering in CREATE/ALTER USER (Vince)
* Disallow TRUNCATE on tables that are involved in referential constraints
* Add OR REPLACE clauses to non-FUNCTION object creation
* ALTER
o ALTER TABLE ADD COLUMN does not honor DEFAULT and non-CHECK CONSTRAINT
o ALTER TABLE ADD COLUMN to inherited table put column in wrong place
[inheritance]
o Add ALTER TABLE DROP COLUMN feature [drop]
o Add ALTER FUNCTION
o Add ALTER TABLE DROP non-CHECK CONSTRAINT
o -Add ALTER TABLE DROP CHECK CONSTRAINT (Christopher Kings-Lynne)
o ALTER TABLE ADD PRIMARY KEY (Christopher Kings-Lynne)
o ALTER TABLE ADD UNIQUE (Christopher Kings-Lynne)
o ALTER TABLE table ADD COLUMN column SERIAL doesn't create sequence
* CLUSTER
o cluster all tables at once
o prevent lose of indexes, permissions, inheritance
o Automatically keep clustering on a table
o -Keep statistics about clustering (Tom) [optimizer]
* COPY
o Allow specification of column names
o Allow dump/load of CSV format
* CURSOR
o Allow BINARY option to SELECT, like we do with DECLARE
o MOVE 0 should not move to end of cursor
o Allow cursors to be DECLAREd/OPENed/CLOSEed outside transactions
o Allow DELETE WHERE CURRENT OF cursor
* INSERT
o Allow INSERT/UPDATE of system-generated oid value for a row
o Allow INSERT INTO tab (col1, ..) VALUES (val1, ..), (val2, ..)
o Allow INSERT INTO my_table VALUES (a, b, c, DEFAULT, x, y, z, ...)
o Disallow missing columns in INSERT ... VALUES, per ANSI
o Allow INSERT/UPDATE ... RETURNING new.col or old.col, handle
RULE cases (Philip)
* SHOW/SET
o Add SHOW command to display locks
o -Add SHOW command to show all settings
o -Add a global RESET command for use with connection pooling
o Add SET or BEGIN timeout parameter to cancel query
o Add SET REAL_FORMAT and SET DOUBLE_PRECISION_FORMAT using printf args
o Remove SET KSQO option now that OR processing is improved (Tom)
o Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM
ANALYZE, and CLUSTER
o Add SHOW command to see locale
CLIENTS
* -Make NULL's come out at the beginning or end depending on the
ORDER BY direction (Tom)
* Have pg_dump use LEFT OUTER JOIN in multi-table SELECTs
or multiple SELECTS to avoid bad system catalog entries
* Have pg_dump -C dump database location and encoding information
* Allow psql \d to show foreign keys
* Allow psql \d to show temporary table structure
* Add XML interface: psql, pg_dump, COPY, separate server (?)
* -Fix libpq to properly handle socket failures under native MS Win32
* Add MD5 to ODBC
* Fix ecpg variable handling in EXEC SQL AT statement
* JDBC
o Comprehensive test suite. This may be available already.
o Updateable resultSet (must be done in backend code)
o -Improved DatabaseMetaData [java]
o JDBC-standard BLOB support
o Error Codes (pending backend implementation)
o -Support for binary data/bytea
o Move to using 'make' rather than 'ant'(?)
* ECPG
o implement set descriptor, using descriptor
o make casts work in variable initializations
o allow variable to specify the connection name
o implement SQLDA
o allow SELECT of array of strings into a auto-sized variable
o Use cursors implicitly to avoid large results (see setCursorName())
o Solve cardinality > 1 for input descriptors / variables
REFERENTIAL INTEGRITY
* Add MATCH PARTIAL referential integrity
* -Check that primary key exists at foreign key definition time
* Add deferred trigger queue file (Jan)
* Allow oid to act as a foreign key
* Implement dirty reads and use them in RI triggers
* Enforce referential integrity for system tables
* INSERT & UPDATE/DELETE in transaction of primary key fails with
deferredTriggerGetPreviousEvent or "change violation" [foreign]
* Allow user to control trigger firing order
* Change foreign key constraint for array -> element to mean element
in array
DEPENDENCY CHECKING
* Add pg_depend table for dependency recording; use sysrelid, oid,
depend_sysrelid, depend_oid, name
* Auto-destroy sequence on DROP of table with SERIAL; perhaps a separate
SERIAL type
* Prevent column dropping if column is used by foreign key
* Propagate column or table renaming to foreign key constraints
* Automatically drop constraints/functions when object is dropped
* Make constraints clearer in dump file
* Make foreign keys easier to identify
TRANSACTIONS
* Allow autocommit so always in a transaction block
* Overhaul bufmgr/lockmgr/transaction manager
* Allow nested transactions / savepoints [transactions]
* -Handle transaction rollover (Tom) [transactions]
EXOTIC FEATURES
* Add sql3 recursive unions
* Add the concept of dataspaces/tablespaces [tablespaces]
* Allow SQL92 schemas [schema]
* Allow queries across multiple databases
* Add pre-parsing phase that converts non-ANSI features to supported features
* Allow plug-in modules to emulate features from other databases
* SQL*Net listener that makes PostgreSQL appear as an Oracle database
to clients
* Allow Java server-side programming [java]
MISCELLANEOUS
* Increase identifier length (NAMEDATALEN) if small performance hit
* -Populate backend status area and write program to dump status data (Jan)
* -Put sort files in their own directory (Bruce)
* Show location of syntax error in query [yacc]
* Change representation of whole-tuple parameters to functions
PERFORMANCE
-----------
FSYNC
* Delay fsync() when other backends are about to commit too [fsync]
o Determine optimal commit_delay value
* Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
o Allow multiple blocks to be written to WAL with one write()
CACHE
* Cache most recent query plan(s) (Karel) [prepare]
* Shared catalog cache, reduce lseek()'s by caching table size in shared area
* Add free-behind capability for large sequential scans
VACUUM
* Improve speed with indexes (perhaps recreate index instead) [vacuum]
* Reduce lock time by moving tuples with read lock, then write
lock and truncate table [vacuum]
* -Make ANALYZE a separate command (Tom)
* -Allow ANALYZE to ESTIMATE based on certain random precentage of rows (Tom)
* Add LAZY VACUUM (Vadim)
MISCELLANEOUS
* -Allow compression of log and meta data (Tom)
* Do async I/O to do better read-ahead of data
* Experiment with multi-threaded backend [thread]
* Get faster regex() code from Henry Spencer <henry@zoo.utoronto.ca>
when it is available
* Use mmap() rather than SYSV shared memory(?) [mmap]
* Use mmap() to write WAL files(?) [mmap]
* Improve Subplan list handling
* Allow Subplans to use efficient joins(hash, merge) with upper variable
* Improve dynamic memory allocation by introducing tuple-context memory
allocation (Tom)
* -Allow GUC configuration of maximum number of open files (Tom)
* -Improve statistics storage in pg_class [performance] (Tom)
* Add connection pooling [pool]
* Allow persistent backends [persistent]
* Create a transaction processor to aid in persistent connections and
connection pooling
* Make blind writes go through the file descriptor cache
* Allow logging of query durations
* Add hash for evaluating GROUP BY aggregates
* -Read pg_hba.conf only on postmaster startup or SIGHUP (Bruce)
* Make secondary WAL page write()'s write only modified data to kernel
* -Improve spinlock code [performance] (Tom)
* Do listen() in postmaster and accept() in pre-forked backend
* Have pre-forked backend pre-connect to last requested database or pass
file descriptor to backend pre-forked for matching database
SOURCE CODE
-----------
* Add use of 'const' for variables in source tree
* Does Mariposa source contain any other bug fixes?
* -Convert remaining fprintf(stderr,...)/perror() to elog() (Peter E)
* Fix problems with libpq non-blocking/async code [async]
* -Merge global and template BKI files (Tom)
* Fix username/password length limits in all areas, e.g. pg_passwd
* -Remove compile-time upper limit on number of backends (MAXBACKENDS) (Tom)
* Make sure all block numbers are unsigned to increase maximum table size
* Use BlockNumber rather than int where appropriate
* Merge LockMethodCtl and LockMethodTable into one shared structure (Bruce)
* HOLDER/HOLDERTAB rename to PROCLOCK/PROCLOCKTAG (Bruce)
* Remove LockMethodTable.prio field, not used (Bruce)
* Add version file format stamp to heap and other table types
* -Make elog(LOG) in WAL its own output type, distinct from DEBUG (Peter E)
* Rename some /contrib modules from pg* to pg_*
* Move some things from /contrib into main tree, like fuzzystrmatch
* Remove warnings created by -Wcast-align
* Move platform-specific ps status display info from ps_status.c to ports
* Allow ps status display to work on Solaris/SVr4-based systems
* -Decide on spelling of indexes/indices (Peter E)
* -Add mention of VACUUM, log rotation to Administrator's Guide (Tom, Bruce)
* Make one version of simple_prompt() in code (Bruce, Tom)
---------------------------------------------------------------------------
Developers who have claimed items are:
--------------------------------------
* Billy is Billy G. Allie <Bill.Allie@mug.org>
* Bruce is Bruce Momjian<pgman@candle.pha.pa.us>
* D'Arcy is D'Arcy J.M. Cain <darcy@druid.net>
* Edmund is Edmund Mergl <E.Mergl@bawue.de>
* Hiroshi is Hiroshi Inoue <Inoue@tpf.co.jp>
* Karel is Karel Zak <zakkr@zf.jcu.cz>
* Jan is Jan Wieck <wieck@sapserv.debis.de>
* Liam is Liam Stewart <liams@redhat.com>
* Marc is Marc Fournier <scrappy@hub.org>
* Mark is Mark Hollomon <mhh@mindspring.com>
* Marko is Marko Kreen <marko@l-t.ee>
* Michael is Michael Meskes <meskes@postgresql.org>
* Oleg is Oleg Bartunov <oleg@sai.msu.su>
* Peter M is Peter T Mount <peter@retep.org.uk>
* Peter E is Peter Eisentraut<peter_e@gmx.net>
* Philip is Philip Warner <pjw@rhyme.com.au>
* Ross is Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
* Ryan is Ryan Bradetich <rbrad@hpb50023.boi.hp.com>
* Stephan is Stephan Szabo <sszabo@megazone23.bigpanda.com>
* Tatsuo is Tatsuo Ishii <t-ishii@sra.co.jp>
* Thomas is Thomas Lockhart <lockhart@alumni.caltech.edu>
* Tom is Tom Lane <tgl@sss.pgh.pa.us>
* TomH is Tom I Helbekkmo <tih@Hamartun.Priv.no>
* Vadim is Vadim B. Mikheev <vadim4o@email.com>