1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
2 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
5 <title>PostgreSQL TODO List</title>
6 <meta name="generator" content="HTML::TextToHTML v2.25"/>
8 <body bgcolor="#FFFFFF" text="#000000" link="#FF0000" vlink="#A00000" alink="#0000FF">
9 <h1><a name="section_1">PostgreSQL TODO List</a></h1>
10 <p>Current maintainer: Bruce Momjian (<a href="mailto:bruce@momjian.us">bruce@momjian.us</a>)<br/>
11 Last updated: Mon Jan 14 17:13:36 EST 2008
13 <p>The most recent version of this document can be viewed at<br/>
14 <a href="http://www.postgresql.org/docs/faqs.TODO.html">http://www.postgresql.org/docs/faqs.TODO.html</a>.
16 <p><strong>A hyphen, "-", marks changes that will appear in the upcoming 8.3 release.</strong><br/>
17 <strong>A percent sign, "%", marks items that are easier to implement.</strong>
19 <p>Bracketed items, "[<a href="http://momjian.postgresql.org/cgi-bin/pgtodo?"></a>]", have more detail.
21 <p>This list contains all known PostgreSQL bugs and feature requests. If<br/>
22 you would like to work on an item, please read the Developer's FAQ<br/>
23 first. There is also a developer's wiki at<br/>
24 <a href="http://developer.postgresql.org">http://developer.postgresql.org</a>.
26 <h1><a name="section_2">Administration</a></h1>
29 <li>Allow administrators to safely terminate individual sessions either
30 via an SQL function or SIGTERM
31 <p> Lock table corruption following SIGTERM of an individual backend
32 has been reported in 8.0. A possible cause was fixed in 8.1, but
33 it is unknown whether other problems exist. This item mostly
34 requires additional testing rather than of writing any new code.
36 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-08/msg00174.php">http://archives.postgresql.org/pgsql-hackers/2006-08/msg00174.php</a>
38 </li><li>Check for unreferenced table files created by transactions that were
39 in-progress when the server terminated abruptly
40 <p> <a href="http://archives.postgresql.org/pgsql-patches/2006-06/msg00096.php">http://archives.postgresql.org/pgsql-patches/2006-06/msg00096.php</a>
42 </li><li>Set proper permissions on non-system schemas during db creation
43 <p> Currently all schemas are owned by the super-user because they are copied
44 from the template1 database. However, since all objects are inherited
45 from the template database, it is not clear that setting schemas to the db
48 </li><li>Add function to report the time of the most recent server reload
49 </li><li>Allow statistics collector information to be pulled from the collector
50 process directly, rather than requiring the collector to write a
51 filesystem file twice a second?
52 </li><li>Allow log_min_messages to be specified on a per-module basis
53 <p> This would allow administrators to see more detailed information from
54 specific sections of the backend, e.g. checkpoints, autovacuum, etc.
55 Another idea is to allow separate configuration files for each module,
56 or allow arbitrary SET commands to be passed to them.
58 </li><li>Simplify ability to create partitioned tables
59 <p> This would allow creation of partitioned tables without requiring
60 creation of triggers or rules for INSERT/UPDATE/DELETE, and constraints
61 for rapid partition selection. Options could include range and hash
64 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-03/msg00375.php">http://archives.postgresql.org/pgsql-hackers/2007-03/msg00375.php</a>
65 <a href="http://archives.postgresql.org/pgsql-hackers/2007-04/msg00151.php">http://archives.postgresql.org/pgsql-hackers/2007-04/msg00151.php</a>
67 </li><li>Allow auto-selection of partitioned tables for min/max() operations
68 </li><li>Allow more complex user/database default GUC settings
69 <p> Currently ALTER USER and ALTER DATABASE support per-user and
70 per-database defaults. Consider adding per-user-and-database
71 defaults so things like search_path can be defaulted for a
72 specific user connecting to a specific database.
74 </li><li>Allow custom variable classes that can restrict who can set the values
75 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-11/msg00911.php">http://archives.postgresql.org/pgsql-hackers/2006-11/msg00911.php</a>
77 </li><li>Implement the SQL standard mechanism whereby REVOKE ROLE revokes only
78 the privilege granted by the invoking role, and not those granted
80 <p> <a href="http://archives.postgresql.org/pgsql-bugs/2007-05/msg00010.php">http://archives.postgresql.org/pgsql-bugs/2007-05/msg00010.php</a>
82 </li><li>Allow SSL authentication/encryption over unix domain sockets
83 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-12/msg00924.php">http://archives.postgresql.org/pgsql-hackers/2007-12/msg00924.php</a>
85 </li><li>Configuration files
87 <li>Allow pg_hba.conf to specify host names along with IP addresses
88 <p> Host name lookup could occur when the postmaster reads the
89 pg_hba.conf file, or when the backend starts. Another
90 solution would be to reverse lookup the connection IP and
91 check that hostname against the host names in pg_hba.conf.
92 We could also then check that the host name maps to the IP
95 </li><li>%Allow postgresql.conf file values to be changed via an SQL
96 API, perhaps using SET GLOBAL
97 </li><li>Allow the server to be stopped/restarted via an SQL API
98 </li><li>Issue a warning if a change-on-restart-only postgresql.conf value
99 is modified and the server config files are reloaded
103 <li>Allow a database in tablespace t1 with tables created in
104 tablespace t2 to be used as a template for a new database created
105 with default tablespace t2
106 <p> Currently all objects in the default database tablespace must
107 have default tablespace specifications. This is because new
108 databases are created by copying directories. If you mix default
109 tablespace tables and tablespace-specified tables in the same
110 directory, creating a new database from such a mixed directory
111 would create a new database with tables that had incorrect
112 explicit tablespaces. To fix this would require modifying
113 pg_class in the newly copied database, which we don't currently
116 </li><li>Allow reporting of which objects are in which tablespaces
117 <p> This item is difficult because a tablespace can contain objects
118 from multiple databases. There is a server-side function that
119 returns the databases which use a specific tablespace, so this
120 requires a tool that will call that function and connect to each
121 database to find the objects in each database for that tablespace.
123 </li><li>Allow WAL replay of CREATE TABLESPACE to work when the directory
124 structure on the recovery computer is different from the original
125 </li><li>Allow per-tablespace quotas
127 </li><li>Point-In-Time Recovery (PITR)
129 <li>Allow a warm standby system to also allow read-only statements
130 [<a href="http://momjian.postgresql.org/cgi-bin/pgtodo?pitr">pitr</a>]
131 <p> This is useful for checking PITR recovery.
132 <a href="http://archives.postgresql.org/pgsql-hackers/2007-03/msg00050.php">http://archives.postgresql.org/pgsql-hackers/2007-03/msg00050.php</a>
134 </li><li>%Create dump tool for write-ahead logs for use in determining
135 transaction id for point-in-time recovery
136 </li><li>Allow the PITR process to be debugged and data examined
137 </li><li>Allow recovery.conf to support the same syntax as
138 postgresql.conf, including quoting
139 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-12/msg00497.php">http://archives.postgresql.org/pgsql-hackers/2006-12/msg00497.php</a>
143 <h1><a name="section_3">Data Types</a></h1>
146 <li>Change NUMERIC to enforce the maximum precision
147 </li><li>Reduce storage space for small NUMERICs
148 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-02/msg01331.php">http://archives.postgresql.org/pgsql-hackers/2007-02/msg01331.php</a>
149 <a href="http://archives.postgresql.org/pgsql-patches/2007-02/msg00505.php">http://archives.postgresql.org/pgsql-patches/2007-02/msg00505.php</a>
151 </li><li>Fix data types where equality comparison isn't intuitive, e.g. box
152 </li><li>Add support for public SYNONYMs
153 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-03/msg00519.php">http://archives.postgresql.org/pgsql-hackers/2006-03/msg00519.php</a>
155 </li><li>Fix CREATE CAST on DOMAINs
156 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-05/msg00072.php">http://archives.postgresql.org/pgsql-hackers/2006-05/msg00072.php</a>
157 <a href="http://archives.postgresql.org/pgsql-hackers/2006-09/msg01681.php">http://archives.postgresql.org/pgsql-hackers/2006-09/msg01681.php</a>
159 </li><li>Add support for SQL-standard GENERATED/IDENTITY columns
160 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-07/msg00543.php">http://archives.postgresql.org/pgsql-hackers/2006-07/msg00543.php</a>
161 <a href="http://archives.postgresql.org/pgsql-hackers/2006-08/msg00038.php">http://archives.postgresql.org/pgsql-hackers/2006-08/msg00038.php</a>
162 <a href="http://archives.postgresql.org/pgsql-hackers/2007-05/msg00344.php">http://archives.postgresql.org/pgsql-hackers/2007-05/msg00344.php</a>
163 <a href="http://archives.postgresql.org/pgsql-patches/2007-05/msg00076.php">http://archives.postgresql.org/pgsql-patches/2007-05/msg00076.php</a>
165 </li><li>Improve XML support
166 <p> <a href="http://developer.postgresql.org/index.php/XML_Support">http://developer.postgresql.org/index.php/XML_Support</a>
168 </li><li>Consider placing all sequences in a single table, or create a system
170 </li><li>Dates and Times
172 <li>Allow infinite dates and intervals just like infinite timestamps
173 </li><li>Merge hardwired timezone names with the TZ database; allow either
174 kind everywhere a TZ name is currently taken
175 </li><li>Allow TIMESTAMP WITH TIME ZONE to store the original timezone
176 information, either zone name or offset from UTC [<a href="http://momjian.postgresql.org/cgi-bin/pgtodo?timezone">timezone</a>]
177 <p> If the TIMESTAMP value is stored with a time zone name, interval
178 computations should adjust based on the time zone rules.
180 </li><li>Fix SELECT '0.01 years'::interval, '0.01 months'::interval
181 </li><li>Add a GUC variable to allow output of interval values in ISO8601
183 </li><li>Have timestamp subtraction not call justify_hours()?
184 <p> <a href="http://archives.postgresql.org/pgsql-sql/2006-10/msg00059.php">http://archives.postgresql.org/pgsql-sql/2006-10/msg00059.php</a>
186 </li><li>Improve timestamptz subtraction to be DST-aware
187 <p> Currently subtracting one date from another that crosses a
188 daylight savings time adjustment can return '1 day 1 hour', but
189 adding that back to the first date returns a time one hour in
190 the future. This is caused by the adjustment of '25 hours' to
191 '1 day 1 hour', and '1 day' is the same time the next day, even
192 if daylight savings adjustments are involved.
194 </li><li>Fix interval display to support values exceeding 2^31 hours
195 </li><li>Add overflow checking to timestamp and interval arithmetic
196 </li><li>Extend timezone code to allow 64-bit values so we can
197 represent years beyond 2038
198 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-09/msg01363.php">http://archives.postgresql.org/pgsql-hackers/2006-09/msg01363.php</a>
200 </li><li>Use LC_TIME for localized weekday/month names, rather than
202 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-11/msg00390.php">http://archives.postgresql.org/pgsql-hackers/2006-11/msg00390.php</a>
204 </li><li>Add ISO INTERVAL handling
207 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-01/msg00250.php">http://archives.postgresql.org/pgsql-hackers/2006-01/msg00250.php</a><br/>
208 <a href="http://archives.postgresql.org/pgsql-bugs/2006-04/msg00248.php">http://archives.postgresql.org/pgsql-bugs/2006-04/msg00248.php</a>
211 <li>Support ISO INTERVAL syntax if units cannot be determined from
212 the string, and are supplied after the string
213 <p> The SQL standard states that the units after the string
214 specify the units of the string, e.g. INTERVAL '2' MINUTE
215 should return '00:02:00'. The current behavior has the units
216 restrict the interval value to the specified unit or unit
217 range, INTERVAL '70' SECOND returns '00:00:10'.
219 <p> For syntax that isn't uniquely ISO or PG syntax, like '1' or
220 '1:30', treat as ISO if there is a range specification clause,
221 and as PG if there no clause is present, e.g. interpret '1:30'
222 MINUTE TO SECOND as '1 minute 30 seconds', and interpret
223 '1:30' as '1 hour, 30 minutes'.
225 <p> This makes common cases like SELECT INTERVAL '1' MONTH
226 SQL-standard results. The SQL standard supports a limited
227 number of unit combinations and doesn't support unit names in
228 the string. The PostgreSQL syntax is more flexible in the
229 range of units supported, e.g. PostgreSQL supports '1 year 1
230 hour', while the SQL standard does not.
232 </li><li>Add support for year-month syntax, INTERVAL '50-6' YEAR
234 </li><li>Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1
235 year' AS INTERVAL MONTH), and this should return '12 months'
236 </li><li>Round or truncate values to the requested precision, e.g.
237 INTERVAL '11 months' AS YEAR should return one or zero
238 </li><li>Support precision, CREATE TABLE foo (a INTERVAL MONTH(3))
242 <li>Delay resolution of array expression's data type so assignment
243 coercion can be performed on empty array expressions
244 </li><li>Add support for arrays of domains
245 <p> <a href="http://archives.postgresql.org/pgsql-patches/2007-05/msg00114.php">http://archives.postgresql.org/pgsql-patches/2007-05/msg00114.php</a>
247 </li><li>Allow single-byte header storage for array elements
251 <li>Improve vacuum of large objects, like contrib/vacuumlo?
252 </li><li>Add security checking for large objects
253 </li><li>Auto-delete large objects when referencing row is deleted
254 <p> contrib/lo offers this functionality.
256 </li><li>Allow read/write into TOAST values like large objects
257 <p> This requires the TOAST column to be stored EXTERNAL.
259 </li><li>Add API for 64-bit large object access
260 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2005-09/msg00781.php">http://archives.postgresql.org/pgsql-hackers/2005-09/msg00781.php</a>
263 </li><li>MONEY data type
265 <li>Add locale-aware MONEY type, and support multiple currencies
266 <p> <a href="http://archives.postgresql.org/pgsql-general/2005-08/msg01432.php">http://archives.postgresql.org/pgsql-general/2005-08/msg01432.php</a>
267 <a href="http://archives.postgresql.org/pgsql-hackers/2007-03/msg01181.php">http://archives.postgresql.org/pgsql-hackers/2007-03/msg01181.php</a>
269 </li><li>MONEY dumps in a locale-specific format making it difficult to
270 restore to a system with a different locale
271 </li><li>Allow MONEY to be easily cast to/from other numeric data types
275 <h1><a name="section_4">Functions</a></h1>
278 <li>Allow INET subnet tests using non-constants to be indexed
279 </li><li>Allow to_date() and to_timestamp() accept localized month names
280 </li><li>Fix to_date()-related functions to consistently issue errors
281 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-02/msg00915.php">http://archives.postgresql.org/pgsql-hackers/2007-02/msg00915.php</a>
283 </li><li>Add missing parameter handling in to_char()
284 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2005-12/msg00948.php">http://archives.postgresql.org/pgsql-hackers/2005-12/msg00948.php</a>
286 </li><li>Allow substring/replace() to get/set bit values
287 </li><li>Allow to_char() on interval values to accumulate the highest unit
289 <p> Some special format flag would be required to request such
290 accumulation. Such functionality could also be added to EXTRACT.
291 Prevent accumulation that crosses the month/day boundary because of
292 the uneven number of days in a month.
295 <li>to_char(INTERVAL '1 hour 5 minutes', 'MI') => 65
296 </li><li>to_char(INTERVAL '43 hours 20 minutes', 'MI' ) => 2600
297 </li><li>to_char(INTERVAL '43 hours 20 minutes', 'WK:DD:HR:MI') => 0:1:19:20
298 </li><li>to_char(INTERVAL '3 years 5 months','MM') => 41
300 </li><li>Implement inlining of set-returning functions defined in SQL
301 </li><li>Allow SQL-language functions to return results from RETURNING queries
302 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-10/msg00665.php">http://archives.postgresql.org/pgsql-hackers/2006-10/msg00665.php</a>
304 </li><li>Allow SQL-language functions to reference parameters by parameter name
305 <p> Currently SQL-language functions can only refer to dollar parameters,
308 </li><li>Add SPI_gettypmod() to return the typemod for a TupleDesc
309 </li><li>Enforce typmod for function inputs, function results and parameters for
310 spi_prepare'd statements called from PLs
311 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-01/msg01403.php">http://archives.postgresql.org/pgsql-hackers/2007-01/msg01403.php</a>
313 </li><li>Allow holdable cursors in SPI
314 </li><li>Tighten function permission checks
315 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-12/msg00568.php">http://archives.postgresql.org/pgsql-hackers/2006-12/msg00568.php</a>
317 </li><li>Fix IS OF so it matches the ISO specification, and add documentation
318 <p> <a href="http://archives.postgresql.org/pgsql-patches/2003-08/msg00060.php">http://archives.postgresql.org/pgsql-patches/2003-08/msg00060.php</a>
319 <a href="http://archives.postgresql.org/pgsql-hackers/2007-02/msg00060.php">http://archives.postgresql.org/pgsql-hackers/2007-02/msg00060.php</a>
321 </li><li>Add missing operators for geometric data types
322 <p> Some geometric types do not have the full suite of geometric operators,
325 </li><li>Implement Boyer-Moore searching in strpos()
326 <p> <a href="http://archives.postgresql.org/pgsql-patches/2007-08/msg00012.php">http://archives.postgresql.org/pgsql-patches/2007-08/msg00012.php</a>
329 <h1><a name="section_5">Multi-Language Support</a></h1>
332 <li>Add NCHAR (as distinguished from ordinary varchar),
333 </li><li>Allow locale to be set at database creation
334 <p> Currently locale can only be set during initdb. No global tables have
335 locale-aware columns. However, the database template used during
336 database creation might have locale-aware indexes. The indexes would
337 need to be reindexed to match the new locale.
339 </li><li>Allow encoding on a per-column basis optionally using the ICU library:
340 <p> Right now only one encoding is allowed per database. [<a href="http://momjian.postgresql.org/cgi-bin/pgtodo?locale">locale</a>]
342 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2005-03/msg00932.php">http://archives.postgresql.org/pgsql-hackers/2005-03/msg00932.php</a>
343 <a href="http://archives.postgresql.org/pgsql-patches/2005-08/msg00309.php">http://archives.postgresql.org/pgsql-patches/2005-08/msg00309.php</a>
344 <a href="http://archives.postgresql.org/pgsql-patches/2006-03/msg00233.php">http://archives.postgresql.org/pgsql-patches/2006-03/msg00233.php</a>
345 <a href="http://archives.postgresql.org/pgsql-hackers/2006-09/msg00662.php">http://archives.postgresql.org/pgsql-hackers/2006-09/msg00662.php</a>
347 </li><li>Add CREATE COLLATE? [<a href="http://momjian.postgresql.org/cgi-bin/pgtodo?locale">locale</a>]
348 </li><li>Support multiple simultaneous character sets, per SQL92
349 </li><li>Improve UTF8 combined character handling?
350 </li><li>Add octet_length_server() and octet_length_client()
351 </li><li>Make octet_length_client() the same as octet_length()?
352 </li><li>Fix problems with wrong runtime encoding conversion for NLS message files
353 </li><li>Add URL to more complete multi-byte regression tests
354 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2005-07/msg00272.php">http://archives.postgresql.org/pgsql-hackers/2005-07/msg00272.php</a>
356 </li><li>Fix ILIKE and regular expressions to handle case insensitivity
357 properly in multibyte encodings
358 <p> <a href="http://archives.postgresql.org/pgsql-bugs/2005-10/msg00001.php">http://archives.postgresql.org/pgsql-bugs/2005-10/msg00001.php</a>
359 <a href="http://archives.postgresql.org/pgsql-patches/2005-11/msg00173.php">http://archives.postgresql.org/pgsql-patches/2005-11/msg00173.php</a>
361 </li><li>Set client encoding based on the client operating system encoding
362 <p> Currently client_encoding is set in postgresql.conf, which
363 defaults to the server encoding.
365 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-08/msg01696.php">http://archives.postgresql.org/pgsql-hackers/2006-08/msg01696.php</a>
368 <h1><a name="section_6">Views / Rules</a></h1>
371 <li>Automatically create rules on views so they are updateable, per SQL99
372 <p> We can only auto-create rules for simple views. For more complex
373 cases users will still have to write rules manually.
375 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-03/msg00586.php">http://archives.postgresql.org/pgsql-hackers/2006-03/msg00586.php</a>
376 <a href="http://archives.postgresql.org/pgsql-patches/2006-08/msg00255.php">http://archives.postgresql.org/pgsql-patches/2006-08/msg00255.php</a>
378 </li><li>Add the functionality for WITH CHECK OPTION clause of CREATE VIEW
379 </li><li>Allow NOTIFY in rules involving conditionals
380 </li><li>Allow VIEW/RULE recompilation when the underlying tables change
381 <p> Another issue is whether underlying table changes should be reflected
382 in the view, e.g. should SELECT * show additional columns if they
383 are added after the view is created.
385 </li><li>Make it possible to use RETURNING together with conditional DO INSTEAD
386 rules, such as for partitioning setups
387 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-09/msg00577.php">http://archives.postgresql.org/pgsql-hackers/2007-09/msg00577.php</a>
389 </li><li>Add the ability to automatically create materialized views
390 <p> Right now materialized views require the user to create triggers on the
391 main table to keep the summary table current. SQL syntax should be able
392 to manager the triggers and summary table automatically. A more
393 sophisticated implementation would automatically retrieve from the
394 summary table when the main table is referenced, if possible.
397 <h1><a name="section_7">SQL Commands</a></h1>
400 <li>Add CORRESPONDING BY to UNION/INTERSECT/EXCEPT
401 </li><li>Add ROLLUP, CUBE, GROUPING SETS options to GROUP BY
402 </li><li>%Allow SET CONSTRAINTS to be qualified by schema/table name
403 </li><li>%Add a separate TRUNCATE permission
404 <p> Currently only the owner can TRUNCATE a table because triggers are not
405 called, and the table is locked in exclusive mode.
407 </li><li>Allow PREPARE of cursors
408 </li><li>Allow finer control over the caching of prepared query plans
409 <p> Currently queries prepared via the libpq API are planned on first
410 execute using the supplied parameters --- allow SQL PREPARE to do the
411 same. Also, allow control over replanning prepared queries either
412 manually or automatically when statistics for execute parameters
413 differ dramatically from those used during planning.
415 </li><li>Improve logging of prepared transactions recovered during startup
416 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-11/msg00092.php">http://archives.postgresql.org/pgsql-hackers/2006-11/msg00092.php</a>
418 </li><li>Allow LISTEN/NOTIFY to store info in memory rather than tables?
419 <p> Currently LISTEN/NOTIFY information is stored in pg_listener. Storing
420 such information in memory would improve performance.
422 </li><li>Add optional textual message to NOTIFY
423 <p> This would allow an informational message to be added to the notify
424 message, perhaps indicating the row modified or other custom
427 </li><li>Allow multiple identical NOTIFY events to always be communicated to the
428 client, rather than sent as a single notification to the listener
429 </li><li>Add a GUC variable to warn about non-standard SQL usage in queries
430 </li><li>Add SQL-standard MERGE command, typically used to merge two tables
431 [<a href="http://momjian.postgresql.org/cgi-bin/pgtodo?merge">merge</a>]
432 <p> This is similar to UPDATE, then for unmatched rows, INSERT.
433 Whether concurrent access allows modifications which could cause
434 row loss is implementation independent.
436 </li><li>Add REPLACE or UPSERT command that does UPDATE, or on failure, INSERT
437 [<a href="http://momjian.postgresql.org/cgi-bin/pgtodo?merge">merge</a>]
438 <p> To implement this cleanly requires that the table have a unique index
439 so duplicate checking can be easily performed. It is possible to
440 do it without a unique index if we require the user to LOCK the table
443 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2005-11/msg00501.php">http://archives.postgresql.org/pgsql-hackers/2005-11/msg00501.php</a>
444 <a href="http://archives.postgresql.org/pgsql-hackers/2005-11/msg00536.php">http://archives.postgresql.org/pgsql-hackers/2005-11/msg00536.php</a>
446 </li><li>Add NOVICE output level for helpful messages like automatic sequence/index
448 </li><li>Add GUC to issue notice about statements that use unjoined tables
449 </li><li>Allow EXPLAIN to identify tables that were skipped because of
451 </li><li>Allow EXPLAIN output to be more easily processed by scripts, perhaps XML
452 </li><li>Enable standard_conforming_strings
453 </li><li>Make standard_conforming_strings the default in 8.5?
454 <p> When this is done, backslash-quote should be prohibited in non-E''
455 strings because of possible confusion over how such strings treat
456 backslashes. Basically, '' is always safe for a literal single
457 quote, while \' might or might not be based on the backslash
460 </li><li>Simplify dropping roles that have objects in several databases
461 </li><li>Allow COMMENT ON to accept an expression rather than just a string
462 </li><li>Allow the count returned by SELECT, etc to be represented as an int64
463 to allow a higher range of values
464 </li><li>Add SQL99 WITH clause to SELECT
465 </li><li>Add SQL:2003 WITH RECURSIVE (hierarchical) queries to SELECT
466 </li><li>Add DEFAULT .. AS OWNER so permission checks are done as the table
468 <p> This would be useful for SERIAL nextval() calls and CHECK constraints.
470 </li><li>Allow DISTINCT to work in multiple-argument aggregate calls
471 </li><li>Add column to pg_stat_activity that shows the progress of long-running
472 commands like CREATE INDEX and VACUUM
473 </li><li>Implement SQL:2003 window functions
474 </li><li>Improve failure message when DROP DATABASE is used on a database that
475 has prepared transactions
476 </li><li>Allow INSERT/UPDATE ... RETURNING inside a SELECT 'FROM' clause
477 <p> <a href="http://archives.postgresql.org/pgsql-general/2006-09/msg00803.php">http://archives.postgresql.org/pgsql-general/2006-09/msg00803.php</a>
478 <a href="http://archives.postgresql.org/pgsql-hackers/2006-10/msg00693.php">http://archives.postgresql.org/pgsql-hackers/2006-10/msg00693.php</a>
480 </li><li>Increase locking when DROPing objects so dependent objects cannot
481 get dropped while the DROP operation is happening
482 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-01/msg00937.php">http://archives.postgresql.org/pgsql-hackers/2007-01/msg00937.php</a>
486 <li>Allow CREATE TABLE AS to determine column lengths for complex
487 expressions like SELECT col1 || col2
488 </li><li>Have WITH CONSTRAINTS also create constraint indexes
489 <p> <a href="http://archives.postgresql.org/pgsql-patches/2007-04/msg00149.php">http://archives.postgresql.org/pgsql-patches/2007-04/msg00149.php</a>
494 <li>Allow UPDATE tab SET ROW (col, ...) = (SELECT...)
495 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-07/msg01306.php">http://archives.postgresql.org/pgsql-hackers/2006-07/msg01306.php</a>
496 <a href="http://archives.postgresql.org/pgsql-hackers/2007-03/msg00865.php">http://archives.postgresql.org/pgsql-hackers/2007-03/msg00865.php</a>
498 </li><li>Research self-referential UPDATEs that see inconsistent row versions
499 in read-committed mode
500 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-05/msg00507.php">http://archives.postgresql.org/pgsql-hackers/2007-05/msg00507.php</a>
501 <a href="http://archives.postgresql.org/pgsql-hackers/2007-06/msg00016.php">http://archives.postgresql.org/pgsql-hackers/2007-06/msg00016.php</a>
503 </li><li>Allow GLOBAL temporary tables to exist as empty by default in
505 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-07/msg00006.php">http://archives.postgresql.org/pgsql-hackers/2007-07/msg00006.php</a>
510 <li>%Have ALTER TABLE RENAME rename SERIAL sequence names
511 </li><li>Have ALTER SEQUENCE RENAME rename the sequence name stored
512 in the sequence table
513 <p> <a href="http://archives.postgresql.org/pgsql-bugs/2007-09/msg00092.php">http://archives.postgresql.org/pgsql-bugs/2007-09/msg00092.php</a>
514 <a href="http://archives.postgresql.org/pgsql-bugs/2007-10/msg00007.php">http://archives.postgresql.org/pgsql-bugs/2007-10/msg00007.php</a>
516 </li><li>Add ALTER DOMAIN to modify the underlying data type
517 </li><li>%Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME
518 <p> <a href="http://archives.postgresql.org/pgsql-patches/2006-02/msg00168.php">http://archives.postgresql.org/pgsql-patches/2006-02/msg00168.php</a>
520 </li><li>%Allow ALTER TABLE to change constraint deferrability and actions
521 </li><li>Add missing object types for ALTER ... SET SCHEMA
522 </li><li>Allow ALTER TABLESPACE to move to different directories
523 </li><li>Allow databases to be moved to different tablespaces
524 </li><li>Allow moving system tables to other tablespaces, where possible
525 <p> Currently non-global system tables must be in the default database
526 tablespace. Global system tables can never be moved.
528 </li><li>Prevent parent tables from altering or dropping constraints
529 like CHECK that are inherited by child tables unless CASCADE
531 </li><li>%Prevent child tables from altering or dropping constraints
532 like CHECK that were inherited from the parent table
533 </li><li>Have ALTER INDEX update the name of a constraint using that index
534 </li><li>Add ALTER TABLE RENAME CONSTRAINT, update index name also
535 </li><li>Allow column display reordering by recording a display,
536 storage, and permanent id for every column?
537 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-12/msg00782.php">http://archives.postgresql.org/pgsql-hackers/2006-12/msg00782.php</a>
542 <li>Automatically maintain clustering on a table
543 <p> This might require some background daemon to maintain clustering
544 during periods of low usage. It might also require tables to be only
545 partially filled for easier reorganization. Another idea would
546 be to create a merged heap/index data file so an index lookup would
547 automatically access the heap data too. A third idea would be to
548 store heap rows in hashed groups, perhaps using a user-supplied
550 <a href="http://archives.postgresql.org/pgsql-performance/2004-08/msg00349.php">http://archives.postgresql.org/pgsql-performance/2004-08/msg00349.php</a>
552 </li><li>%Add default clustering to system tables
553 <p> To do this, determine the ideal cluster index for each system
554 table and set the cluster setting during initdb.
556 </li><li>%Add VERBOSE option to report tables as they are processed,
561 <li>Allow COPY to report error lines and continue
562 <p> This requires the use of a savepoint before each COPY line is
563 processed, with ROLLBACK on COPY failure.
565 </li><li>Allow COPY on a newly-created table to skip WAL logging
566 <p> On crash recovery, the table involved in the COPY would
567 be removed or have its heap and index files truncated. One
568 issue is that no other backend should be able to add to
569 the table at the same time, which is something that is
570 currently allowed. This currently is done if the table is
571 created inside the same transaction block as the COPY because
572 no other backends can see the table.
575 </li><li>GRANT/REVOKE
577 <li>Allow column-level privileges
578 </li><li>%Allow GRANT/REVOKE permissions to be applied to all schema objects
580 <p> The proposed syntax is:
581 </p><p> GRANT SELECT ON ALL TABLES IN public TO phpuser;
582 GRANT SELECT ON NEW TABLES IN public TO phpuser;
584 </li><li>Allow GRANT/REVOKE permissions to be inherited by objects based on
586 </li><li>Allow SERIAL sequences to inherit permissions from the base table?
590 <li>Prevent DROP TABLE from dropping a row referenced by its own open
595 <li>Allow INSERT/UPDATE of the system-generated oid value for a row
596 </li><li>In rules, allow VALUES() to contain a mixture of 'old' and 'new'
601 <li>Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM
604 </li><li>Referential Integrity
606 <li>Add MATCH PARTIAL referential integrity
607 </li><li>Change foreign key constraint for array -> element to mean element
609 </li><li>Fix problem when cascading referential triggers make changes on
610 cascaded tables, seeing the tables in an intermediate state
611 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2005-09/msg00174.php">http://archives.postgresql.org/pgsql-hackers/2005-09/msg00174.php</a>
612 <a href="http://archives.postgresql.org/pgsql-hackers/2005-09/msg00174.php">http://archives.postgresql.org/pgsql-hackers/2005-09/msg00174.php</a>
614 </li><li>Allow DEFERRABLE and end-of-statement UNIQUE constraints?
615 <p> This would allow UPDATE tab SET col = col + 1 to work if col has
616 a unique index. Currently, uniqueness checks are done while the
617 command is being executed, rather than at the end of the statement
620 <p> <a href="http://people.planetpostgresql.org/greg/index.php?/archives/2006/06/10.html">http://people.planetpostgresql.org/greg/index.php?/archives/2006/06/10.html</a>
621 <a href="http://archives.postgresql.org/pgsql-hackers/2006-09/msg01458.php">http://archives.postgresql.org/pgsql-hackers/2006-09/msg01458.php</a>
624 </li><li>Server-Side Languages
628 <li>Fix RENAME to work on variables other than OLD/NEW
629 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2002-03/msg00591.php">http://archives.postgresql.org/pgsql-hackers/2002-03/msg00591.php</a>
630 <a href="http://archives.postgresql.org/pgsql-hackers/2007-01/msg01615.php">http://archives.postgresql.org/pgsql-hackers/2007-01/msg01615.php</a>
631 <a href="http://archives.postgresql.org/pgsql-hackers/2007-01/msg01587.php">http://archives.postgresql.org/pgsql-hackers/2007-01/msg01587.php</a>
633 </li><li>Allow function parameters to be passed by name,
634 get_employee_salary(12345 AS emp_id, 2001 AS tax_year)
635 </li><li>Allow handling of %TYPE arrays, e.g. tab.col%TYPE[<a href="http://momjian.postgresql.org/cgi-bin/pgtodo?"></a>]
636 </li><li>Allow listing of record column names, and access to
637 record columns via variables, e.g. columns := r.(*),
639 <p> <a href="http://archives.postgresql.org/pgsql-patches/2005-07/msg00458.php">http://archives.postgresql.org/pgsql-patches/2005-07/msg00458.php</a>
640 <a href="http://archives.postgresql.org/pgsql-patches/2006-05/msg00302.php">http://archives.postgresql.org/pgsql-patches/2006-05/msg00302.php</a>
641 <a href="http://archives.postgresql.org/pgsql-patches/2006-06/msg00031.php">http://archives.postgresql.org/pgsql-patches/2006-06/msg00031.php</a>
643 </li><li>Add support for SCROLL cursors
644 </li><li>Add support for WITH HOLD cursors
645 </li><li>Allow row and record variables to be set to NULL constants,
646 and allow NULL tests on such variables
647 <p> Because a row is not scalar, do not allow assignment
648 from NULL-valued scalars.
650 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-10/msg00070.php">http://archives.postgresql.org/pgsql-hackers/2006-10/msg00070.php</a>
655 <li>Add table function support to pltcl, plpythonu
656 </li><li>Add support for polymorphic arguments and return types to
657 languages other than PL/PgSQL
658 </li><li>Add capability to create and call PROCEDURES
659 </li><li>Add support for OUT and INOUT parameters to languages other
661 </li><li>Add PL/PythonU tracebacks
662 <p> <a href="http://archives.postgresql.org/pgsql-patches/2006-02/msg00288.php">http://archives.postgresql.org/pgsql-patches/2006-02/msg00288.php</a>
664 </li><li>Allow data to be passed in native language formats, rather
666 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-05/msg00289.php">http://archives.postgresql.org/pgsql-hackers/2007-05/msg00289.php</a>
671 <h1><a name="section_8">Clients</a></h1>
674 <li>Have pg_ctl look at PGHOST in case it is a socket directory?
675 </li><li>Allow pg_ctl to work properly with configuration files located outside
677 <p> pg_ctl can not read the pid file because it isn't located in the
678 config directory but in the PGDATA directory. The solution is to
679 allow pg_ctl to read and understand postgresql.conf to find the
680 data_directory value.
684 <li>Have psql show current values for a sequence
685 </li><li>Move psql backslash database information into the backend, use
686 mnemonic commands? [<a href="http://momjian.postgresql.org/cgi-bin/pgtodo?psql">psql</a>]
687 <p> This would allow non-psql clients to pull the same information out
688 of the database as psql.
690 </li><li>Fix psql's \d commands more consistent
691 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2004-11/msg00014.php">http://archives.postgresql.org/pgsql-hackers/2004-11/msg00014.php</a>
692 <a href="http://archives.postgresql.org/pgsql-hackers/2004-11/msg00014.php">http://archives.postgresql.org/pgsql-hackers/2004-11/msg00014.php</a>
694 </li><li>Consistently display privilege information for all objects in psql
695 </li><li>Add auto-expanded mode so expanded output is used if the row
696 length is wider than the screen width.
697 <p> Consider using auto-expanded mode for backslash commands like \df+.
699 </li><li>Prevent tab completion of SET TRANSACTION from querying the
700 database and therefore preventing the transaction isolation
701 level from being set.
702 <p> Currently SET <tab> causes a database lookup to check all
703 supported session variables. This query causes problems
704 because setting the transaction isolation level must be the
705 first statement of a transaction.
707 </li><li>Add a \set variable to control whether \s displays line numbers
708 <p> Another option is to add \# which lists line numbers, and
709 allows command execution.
711 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php">http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php</a>
716 <li>%Add dumping of comments on index columns and composite type columns
717 </li><li>%Add full object name to the tag field. eg. for operators we need
718 '=(integer, integer)', instead of just '='.
719 </li><li>Add pg_dumpall custom format dumps?
720 </li><li>Allow selection of individual object(s) of all types, not just
722 </li><li>In a selective dump, allow dumping of an object and all its
724 </li><li>Add options like pg_restore -l and -L to pg_dump
725 </li><li>Stop dumping CASCADE on DROP TYPE commands in clean mode
726 </li><li>Allow pg_dump --clean to drop roles that own objects or have
728 </li><li>Change pg_dump so that a comment on the dumped database is
729 applied to the loaded database, even if the database has a
730 different name. This will require new backend syntax, perhaps
731 COMMENT ON CURRENT DATABASE.
732 </li><li>Remove unnecessary function pointer abstractions in pg_dump source
738 <p> Document differences between ecpg and the SQL standard and
739 information about the Informix-compatibility module.
741 </li><li>Solve cardinality > 1 for input descriptors / variables?
742 </li><li>Add a semantic check level, e.g. check if a table really exists
743 </li><li>fix handling of DB attributes that are arrays
744 </li><li>Use backend PREPARE/EXECUTE facility for ecpg where possible
745 </li><li>Implement SQLDA
746 </li><li>Fix nested C comments
747 </li><li>%sqlwarn[<a href="http://momjian.postgresql.org/cgi-bin/pgtodo?6">6</a>] should be 'W' if the PRECISION or SCALE value specified
748 </li><li>Make SET CONNECTION thread-aware, non-standard?
749 </li><li>Allow multidimensional arrays
750 </li><li>Add internationalized message strings
751 </li><li>Implement COPY FROM STDIN
755 <li>Add PQescapeIdentifierConn()
756 </li><li>Prevent PQfnumber() from lowercasing unquoted the column name
757 <p> PQfnumber() should never have been doing lowercasing, but
758 historically it has so we need a way to prevent it
760 </li><li>Allow statement results to be automatically batched to the client
761 <p> Currently all statement results are transferred to the libpq
762 client before libpq makes the results available to the
763 application. This feature would allow the application to make
764 use of the first result rows while the rest are transferred, or
765 held on the server waiting for them to be requested by libpq.
766 One complexity is that a statement like SELECT 1/col could error
767 out mid-way through the result set.
769 </li><li>Consider disallowing multiple queries in PQexec() as an
770 additional barrier to SQL injection attacks
771 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-01/msg00184.php">http://archives.postgresql.org/pgsql-hackers/2007-01/msg00184.php</a>
773 </li><li>Add PQexecf() that allows complex parameter substitution
774 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-03/msg01803.php">http://archives.postgresql.org/pgsql-hackers/2007-03/msg01803.php</a>
778 <h1><a name="section_9">Triggers</a></h1>
781 <li>Add deferred trigger queue file
782 <p> Right now all deferred trigger information is stored in backend
783 memory. This could exhaust memory for very large trigger queues.
784 This item involves dumping large queues into files.
786 </li><li>Allow triggers to be disabled in only the current session.
787 <p> This is currently possible by starting a multi-statement transaction,
788 modifying the system tables, performing the desired SQL, restoring the
789 system tables, and committing the transaction. ALTER TABLE ...
790 TRIGGER requires a table lock so it is not ideal for this usage.
792 </li><li>With disabled triggers, allow pg_dump to use ALTER TABLE ADD FOREIGN KEY
793 <p> If the dump is known to be valid, allow foreign keys to be added
794 without revalidating the data.
796 </li><li>Allow statement-level triggers to access modified rows
797 </li><li>Support triggers on columns
798 <p> <a href="http://archives.postgresql.org/pgsql-patches/2005-07/msg00107.php">http://archives.postgresql.org/pgsql-patches/2005-07/msg00107.php</a>
800 </li><li>Allow AFTER triggers on system tables
801 <p> System tables are modified in many places in the backend without going
802 through the executor and therefore not causing triggers to fire. To
803 complete this item, the functions that modify system tables will have
806 </li><li>Tighten trigger permission checks
807 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-12/msg00564.php">http://archives.postgresql.org/pgsql-hackers/2006-12/msg00564.php</a>
809 </li><li>Allow BEFORE INSERT triggers on views
810 <p> <a href="http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php">http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php</a>
812 </li><li>Add ability to trigger on TRUNCATE
813 <p> <a href="http://archives.postgresql.org/pgsql-sql/2008-01/msg00050.php">http://archives.postgresql.org/pgsql-sql/2008-01/msg00050.php</a>
816 <h1><a name="section_10">Indexes</a></h1>
819 <li>Add UNIQUE capability to non-btree indexes
820 </li><li>Prevent index uniqueness checks when UPDATE does not modify the column
821 <p> Uniqueness (index) checks are done when updating a column even if the
822 column is not modified by the UPDATE.
824 </li><li>Allow the creation of on-disk bitmap indexes which can be quickly
825 combined with other bitmap indexes
826 <p> Such indexes could be more compact if there are only a few distinct values.
827 Such indexes can also be compressed. Keeping such indexes updated can be
830 <p> <a href="http://archives.postgresql.org/pgsql-patches/2005-07/msg00512.php">http://archives.postgresql.org/pgsql-patches/2005-07/msg00512.php</a>
831 <a href="http://archives.postgresql.org/pgsql-hackers/2006-12/msg01107.php">http://archives.postgresql.org/pgsql-hackers/2006-12/msg01107.php</a>
833 </li><li>Allow accurate statistics to be collected on indexes with more than
834 one column or expression indexes, perhaps using per-index statistics
835 <p> <a href="http://archives.postgresql.org/pgsql-performance/2006-10/msg00222.php">http://archives.postgresql.org/pgsql-performance/2006-10/msg00222.php</a>
836 <a href="http://archives.postgresql.org/pgsql-hackers/2007-03/msg01131.php">http://archives.postgresql.org/pgsql-hackers/2007-03/msg01131.php</a>
838 </li><li>Consider compressing indexes by storing key values duplicated in
839 several rows as a single index entry
840 </li><li>Add REINDEX CONCURRENTLY, like CREATE INDEX CONCURRENTLY
841 <p> This is difficult because you must upgrade to an exclusive table lock
842 to replace the existing index file. CREATE INDEX CONCURRENTLY does not
843 have this complication. This would allow index compaction without
846 </li><li>Allow multiple indexes to be created concurrently, ideally via a
847 single heap scan, and have a restore of a pg_dump somehow use it
848 <p> <a href="http://archives.postgresql.org/pgsql-general/2007-05/msg01274.php">http://archives.postgresql.org/pgsql-general/2007-05/msg01274.php</a>
852 <li>Allow inherited tables to inherit indexes, UNIQUE constraints,
853 and primary/foreign keys
854 </li><li>Honor UNIQUE INDEX on base column in INSERTs/UPDATEs
855 on inherited table, e.g. INSERT INTO inherit_table
856 (unique_index_col) VALUES (dup) should fail
857 <p> The main difficulty with this item is the problem of
858 creating an index that can span multiple tables.
860 </li><li>Allow SELECT ... FOR UPDATE on inherited tables
864 <li>Add more GIST index support for geometric data types
865 </li><li>Allow GIST indexes to create certain complex index types, like
866 digital trees (see Aoki)
869 <a href="http://archives.postgresql.org/pgsql-hackers/2007-09/msg00051.php">http://archives.postgresql.org/pgsql-hackers/2007-09/msg00051.php</a>
871 <li>Pack hash index buckets onto disk pages more efficiently
872 <p> Currently only one hash bucket can be stored on a page. Ideally
873 several hash buckets could be stored on a single page and greater
874 granularity used for the hash algorithm.
876 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2004-06/msg00168.php">http://archives.postgresql.org/pgsql-hackers/2004-06/msg00168.php</a>
878 </li><li>Consider sorting hash buckets so entries can be found using a
879 binary search, rather than a linear scan
880 </li><li>In hash indexes, consider storing the hash value with or instead
882 </li><li>Add WAL logging for crash recovery
883 </li><li>Allow multi-column hash indexes
884 </li><li>During index creation, pre-sort the tuples to improve build speed
885 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-03/msg01199.php">http://archives.postgresql.org/pgsql-hackers/2007-03/msg01199.php</a>
889 <h1><a name="section_11">Fsync</a></h1>
892 <li>Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
893 <p> Ideally this requires a separate test program that can be run
894 at initdb time or optionally later. Consider O_SYNC when
897 </li><li>Add program to test if fsync has a delay compared to non-fsync
899 <h1><a name="section_12">Cache Usage</a></h1>
902 <li>Speed up COUNT(*)
903 <p> We could use a fixed row count and a +/- count to follow MVCC
904 visibility rules, or a single cached value could be used and
905 invalidated if anyone modifies the table. Another idea is to
906 get a count directly from a unique index, but for this to be
907 faster than a sequential scan it must avoid access to the heap
908 to obtain tuple visibility information.
910 </li><li>Provide a way to calculate an "estimated COUNT(*)"
911 <p> Perhaps by using the optimizer's cardinality estimates or random
914 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2005-11/msg00943.php">http://archives.postgresql.org/pgsql-hackers/2005-11/msg00943.php</a>
916 </li><li>Allow data to be pulled directly from indexes
917 <p> Currently indexes do not have enough tuple visibility information
918 to allow data to be pulled from the index without also accessing
919 the heap. One way to allow this is to set a bit on index tuples
920 to indicate if a tuple is currently visible to all transactions
921 when the first valid heap lookup happens. This bit would have to
922 be cleared when a heap tuple is expired.
924 <p> Another idea is to maintain a bitmap of heap pages where all rows
925 are visible to all backends, and allow index lookups to reference
926 that bitmap to avoid heap lookups, perhaps the same bitmap we might
927 add someday to determine which heap pages need vacuuming. Frequently
928 accessed bitmaps would have to be stored in shared memory. One 8k
929 page of bitmaps could track 512MB of heap pages.
931 <p> A third idea would be for a heap scan to check if all rows are visible
932 and if so set a per-table flag which can be checked by index scans.
933 Any change to the table would have to clear the flag. To detect
934 changes during the heap scan a counter could be set at the start and
935 checked at the end --- if it is the same, the table has not been
936 modified --- any table change would increment the counter.
938 </li><li>Consider automatic caching of statements at various levels:
940 <li>Parsed query tree
941 </li><li>Query execute plan
942 </li><li>Query results
944 </li><li>Consider increasing internal areas when shared buffers is increased
945 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2005-10/msg01419.php">http://archives.postgresql.org/pgsql-hackers/2005-10/msg01419.php</a>
947 </li><li>Consider decreasing the amount of memory used by PrivateRefCount
948 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-11/msg00797.php">http://archives.postgresql.org/pgsql-hackers/2006-11/msg00797.php</a>
949 <a href="http://archives.postgresql.org/pgsql-hackers/2007-01/msg00752.php">http://archives.postgresql.org/pgsql-hackers/2007-01/msg00752.php</a>
952 <h1><a name="section_13">Vacuum</a></h1>
955 <li>Improve speed with indexes
956 <p> For large table adjustments during VACUUM FULL, it is faster to cluster
957 or reindex rather than update the index. Also, index updates can bloat
960 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-03/msg00024.php">http://archives.postgresql.org/pgsql-hackers/2007-03/msg00024.php</a>
961 <a href="http://archives.postgresql.org/pgsql-performance/2007-05/msg00296.php">http://archives.postgresql.org/pgsql-performance/2007-05/msg00296.php</a>
963 </li><li>Auto-fill the free space map by scanning the buffer cache or by
964 checking pages written by the background writer
965 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-02/msg01125.php">http://archives.postgresql.org/pgsql-hackers/2006-02/msg01125.php</a>
966 <a href="http://archives.postgresql.org/pgsql-hackers/2006-03/msg00011.php">http://archives.postgresql.org/pgsql-hackers/2006-03/msg00011.php</a>
968 </li><li>Create a bitmap of pages that need vacuuming
969 <p> Instead of sequentially scanning the entire table, have the background
970 writer or some other process record pages that have expired rows, then
971 VACUUM can look at just those pages rather than the entire table. In
972 the event of a system crash, the bitmap would probably be invalidated.
973 One complexity is that index entries still have to be vacuumed, and
974 doing this without an index scan (by using the heap values to find the
975 index entry) might be slow and unreliable, especially for user-defined
978 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-12/msg01188.php">http://archives.postgresql.org/pgsql-hackers/2006-12/msg01188.php</a>
979 <a href="http://archives.postgresql.org/pgsql-hackers/2007-01/msg00121.php">http://archives.postgresql.org/pgsql-hackers/2007-01/msg00121.php</a>
981 </li><li>Allow FSM to return free space toward the beginning of the heap file,
982 in hopes that empty pages at the end can be truncated by VACUUM
983 </li><li>Allow FSM page return free space based on table clustering, to assist
984 in maintaining clustering?
985 </li><li>Improve dead row detection during multi-statement transactions usage
986 <p> <a href="http://archives.postgresql.org/pgsql-patches/2007-03/msg00358.php">http://archives.postgresql.org/pgsql-patches/2007-03/msg00358.php</a>
990 <li>%Issue log message to suggest VACUUM FULL if a table is nearly
992 </li><li>Improve control of auto-vacuum
993 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-12/msg00876.php">http://archives.postgresql.org/pgsql-hackers/2006-12/msg00876.php</a>
995 </li><li>Prevent long-lived temporary tables from causing frozen-xid
996 advancement starvation
997 <p> The problem is that autovacuum cannot vacuum them to set frozen xids;
998 only the session that created them can do that.
999 <a href="http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php">http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php</a>
1003 <h1><a name="section_14">Locking</a></h1>
1006 <li>Fix priority ordering of read and write light-weight locks (Neil)
1007 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2004-11/msg00893.php">http://archives.postgresql.org/pgsql-hackers/2004-11/msg00893.php</a>
1008 <a href="http://archives.postgresql.org/pgsql-hackers/2004-11/msg00905.php">http://archives.postgresql.org/pgsql-hackers/2004-11/msg00905.php</a>
1010 </li><li>Fix problem when multiple subtransactions of the same outer transaction
1011 hold different types of locks, and one subtransaction aborts
1012 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-11/msg01011.php">http://archives.postgresql.org/pgsql-hackers/2006-11/msg01011.php</a>
1013 <a href="http://archives.postgresql.org/pgsql-hackers/2006-12/msg00001.php">http://archives.postgresql.org/pgsql-hackers/2006-12/msg00001.php</a>
1014 <a href="http://archives.postgresql.org/pgsql-hackers/2007-02/msg00435.php">http://archives.postgresql.org/pgsql-hackers/2007-02/msg00435.php</a>
1016 </li><li>Allow UPDATEs on only non-referential integrity columns not to conflict
1017 with referential integrity locks
1018 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-02/msg00073.php">http://archives.postgresql.org/pgsql-hackers/2007-02/msg00073.php</a>
1020 </li><li>Add idle_in_transaction_timeout GUC so locks are not held for long
1023 <h1><a name="section_15">Startup Time Improvements</a></h1>
1026 <li>Experiment with multi-threaded backend for backend creation [<a href="http://momjian.postgresql.org/cgi-bin/pgtodo?thread">thread</a>]
1027 <p> This would prevent the overhead associated with process creation. Most
1028 operating systems have trivial process creation time compared to
1029 database startup overhead, but a few operating systems (Win32,
1030 Solaris) might benefit from threading. Also explore the idea of
1031 a single session using multiple threads to execute a statement faster.
1034 <h1><a name="section_16">Write-Ahead Log</a></h1>
1037 <li>Eliminate need to write full pages to WAL before page modification [<a href="http://momjian.postgresql.org/cgi-bin/pgtodo?wal">wal</a>]
1038 <p> Currently, to protect against partial disk page writes, we write
1039 full page images to WAL before they are modified so we can correct any
1040 partial page writes during recovery. These pages can also be
1041 eliminated from point-in-time archive files.
1044 <li>When off, write CRC to WAL and check file system blocks
1046 <p> If CRC check fails during recovery, remember the page in case
1047 a later CRC for that page properly matches.
1049 </li><li>Write full pages during file system write and not when
1050 the page is modified in the buffer cache
1051 <p> This allows most full page writes to happen in the background
1052 writer. It might cause problems for applying WAL on recovery
1053 into a partially-written page, but later the full page will be
1057 </li><li>Allow WAL traffic to be streamed to another server for stand-by
1059 </li><li>Reduce WAL traffic so only modified values are written rather than
1061 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-03/msg01589.php">http://archives.postgresql.org/pgsql-hackers/2007-03/msg01589.php</a>
1063 </li><li>Allow WAL information to recover corrupted pg_controldata
1064 <p> <a href="http://archives.postgresql.org/pgsql-patches/2006-06/msg00025.php">http://archives.postgresql.org/pgsql-patches/2006-06/msg00025.php</a>
1066 </li><li>Find a way to reduce rotational delay when repeatedly writing
1068 <p> Currently fsync of WAL requires the disk platter to perform a full
1069 rotation to fsync again. One idea is to write the WAL to different
1070 offsets that might reduce the rotational delay.
1072 </li><li>Allow WAL logging to be turned off for a table, but the table
1073 might be dropped or truncated during crash recovery [<a href="http://momjian.postgresql.org/cgi-bin/pgtodo?walcontrol">walcontrol</a>]
1074 <p> Allow tables to bypass WAL writes and just fsync() dirty pages on
1075 commit. This should be implemented using ALTER TABLE, e.g. ALTER
1076 TABLE PERSISTENCE [<a href="http://momjian.postgresql.org/cgi-bin/pgtodo? DROP | TRUNCATE | DEFAULT "> DROP | TRUNCATE | DEFAULT </a>]. Tables using
1077 non-default logging should not use referential integrity with
1078 default-logging tables. A table without dirty buffers during a
1079 crash could perhaps avoid the drop/truncate.
1081 </li><li>Allow WAL logging to be turned off for a table, but the table would
1082 avoid being truncated/dropped [<a href="http://momjian.postgresql.org/cgi-bin/pgtodo?walcontrol">walcontrol</a>]
1083 <p> To do this, only a single writer can modify the table, and writes
1084 must happen only on new pages so the new pages can be removed during
1085 crash recovery. Readers can continue accessing the table. Such
1086 tables probably cannot have indexes. One complexity is the handling
1087 of indexes on TOAST tables.
1090 <h1><a name="section_17">Optimizer / Executor</a></h1>
1093 <li>Improve selectivity functions for geometric operators
1094 </li><li>Precompile SQL functions to avoid overhead
1095 </li><li>Create utility to compute accurate random_page_cost value
1096 </li><li>Improve ability to display optimizer analysis using OPTIMIZER_DEBUG
1097 </li><li>Have EXPLAIN ANALYZE issue NOTICE messages when the estimated and
1098 actual row counts differ by a specified percentage
1099 </li><li>Consider using hash buckets to do DISTINCT, rather than sorting
1100 <p> This would be beneficial when there are few distinct values. This is
1101 already used by GROUP BY.
1103 </li><li>Log statements where the optimizer row estimates were dramatically
1104 different from the number of rows actually found?
1105 </li><li>Consider compressed annealing to search for query plans
1106 <p> This might replace GEQO, <a href="http://sixdemonbag.org/Djinni">http://sixdemonbag.org/Djinni</a>.
1108 </li><li>Improve merge join performance by allowing mark/restore of
1110 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-01/msg00096.php">http://archives.postgresql.org/pgsql-hackers/2007-01/msg00096.php</a>
1113 <h1><a name="section_18">Miscellaneous Performance</a></h1>
1116 <li>Do async I/O for faster random read-ahead of data
1117 <p> Async I/O allows multiple I/O requests to be sent to the disk with
1118 results coming back asynchronously.
1120 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-10/msg00820.php">http://archives.postgresql.org/pgsql-hackers/2006-10/msg00820.php</a>
1121 <a href="http://archives.postgresql.org/pgsql-hackers/2007-12/msg00027.php">http://archives.postgresql.org/pgsql-hackers/2007-12/msg00027.php</a>
1123 </li><li>Use mmap() rather than SYSV shared memory or to write WAL files?
1124 <p> This would remove the requirement for SYSV SHM but would introduce
1125 portability issues. Anonymous mmap (or mmap to /dev/zero) is required
1126 to prevent I/O overhead.
1128 </li><li>Consider mmap()'ing files into a backend?
1129 <p> Doing I/O to large tables would consume a lot of address space or
1130 require frequent mapping/unmapping. Extending the file also causes
1131 mapping problems that might require mapping only individual pages,
1132 leading to thousands of mappings. Another problem is that there is no
1133 way to <u>prevent</u> I/O to disk from the dirty shared buffers so changes
1134 could hit disk before WAL is written.
1136 </li><li>Add a script to ask system configuration questions and tune postgresql.conf
1137 </li><li>Consider ways of storing rows more compactly on disk
1139 <li>Reduce the row header size?
1140 </li><li>Consider reducing on-disk varlena length from four bytes to
1141 two because a heap row cannot be more than 64k in length
1143 </li><li>Consider increasing NUM_CLOG_BUFFERS
1144 </li><li>Consider having the background writer update the transaction status
1145 hint bits before writing out the page
1146 <p> Implementing this requires the background writer to have access to system
1147 catalogs and the transaction status log.
1149 </li><li>Allow user configuration of TOAST thresholds
1150 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2007-02/msg00213.php">http://archives.postgresql.org/pgsql-hackers/2007-02/msg00213.php</a>
1152 </li><li>Allow configuration of backend priorities via the operating system
1153 <p> Though backend priorities make priority inversion during lock
1154 waits possible, research shows that this is not a huge problem.
1156 <p> <a href="http://archives.postgresql.org/pgsql-general/2007-02/msg00493.php">http://archives.postgresql.org/pgsql-general/2007-02/msg00493.php</a>
1158 </li><li>Experiment with multi-threaded backend better resource utilization
1159 <p> This would allow a single query to make use of multiple CPU's or
1160 multiple I/O channels simultaneously. One idea is to create a
1161 background reader that can pre-fetch sequential and index scan
1162 pages needed by other backends. This could be expanded to allow
1163 concurrent reads from multiple devices in a partitioned table.
1166 <h1><a name="section_19">Source Code</a></h1>
1169 <li>Add use of 'const' for variables in source tree
1170 </li><li>Move some things from contrib into main tree
1171 </li><li>%Remove warnings created by -Wcast-align
1172 </li><li>Move platform-specific ps status display info from ps_status.c to ports
1173 </li><li>Add optional CRC checksum to heap and index pages
1174 </li><li>Improve documentation to build only interfaces (Marc)
1175 </li><li>Remove or relicense modules that are not under the BSD license, if possible
1176 </li><li>Acquire lock on a relation before building a relcache entry for it
1177 </li><li>Allow cross-compiling by generating the zic database on the target system
1178 </li><li>Improve NLS maintenance of libpgport messages linked onto applications
1179 </li><li>Clean up casting in contrib/isn
1180 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-11/msg00245.php">http://archives.postgresql.org/pgsql-hackers/2006-11/msg00245.php</a>
1182 </li><li>Use UTF8 encoding for NLS messages so all server encodings can
1184 </li><li>Update Bonjour to work with newer cross-platform SDK
1185 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-09/msg02238.php">http://archives.postgresql.org/pgsql-hackers/2006-09/msg02238.php</a>
1186 <a href="http://archives.postgresql.org/pgsql-patches/2006-10/msg00048.php">http://archives.postgresql.org/pgsql-patches/2006-10/msg00048.php</a>
1188 </li><li>Consider detoasting keys before sorting
1189 </li><li>Consider GnuTLS if OpenSSL license becomes a problem
1190 <p> <a href="http://archives.postgresql.org/pgsql-patches/2006-05/msg00040.php">http://archives.postgresql.org/pgsql-patches/2006-05/msg00040.php</a>
1191 <a href="http://archives.postgresql.org/pgsql-hackers/2006-12/msg01213.php">http://archives.postgresql.org/pgsql-hackers/2006-12/msg01213.php</a>
1193 </li><li>Consider changing documentation format from SGML to XML
1194 <p> <a href="http://archives.postgresql.org/pgsql-docs/2006-12/msg00152.php">http://archives.postgresql.org/pgsql-docs/2006-12/msg00152.php</a>
1196 </li><li>Consider making NAMEDATALEN more configurable in future releases
1197 </li><li>Update our code to handle 64-bit timezone files to match the zic
1198 source code, which now uses them
1199 </li><li>Have configure choose integer datetimes by default
1200 <p> <a href="http://archives.postgresql.org/pgsql-patches/2007-05/msg00046.php">http://archives.postgresql.org/pgsql-patches/2007-05/msg00046.php</a>
1202 </li><li>Support scoped IPv6 addresses
1203 <p> <a href="http://archives.postgresql.org/pgsql-bugs/2007-05/msg00111.php">http://archives.postgresql.org/pgsql-bugs/2007-05/msg00111.php</a>
1205 </li><li>Consider allowing 64-bit integers and floats to be passed by value on
1207 <p> Also change 32-bit floats (float4) to be passed by value at the same
1212 <li>Remove configure.in check for link failure when cause is found
1213 </li><li>Remove readdir() errno patch when runtime/mingwex/dirent.c rev
1215 </li><li>Remove psql newline patch when we find out why mingw outputs an
1217 </li><li>Allow psql to use readline once non-US code pages work with
1219 </li><li>Fix problem with shared memory on the Win32 Terminal Server
1220 </li><li>Improve signal handling
1221 <p> <a href="http://archives.postgresql.org/pgsql-patches/2005-06/msg00027.php">http://archives.postgresql.org/pgsql-patches/2005-06/msg00027.php</a>
1224 </li><li>Wire Protocol Changes
1226 <li>Allow dynamic character set handling
1227 </li><li>Add decoded type, length, precision
1228 </li><li>Use compression?
1229 </li><li>Update clients to use data types, typmod, schema.table.column names
1230 of result sets using new statement protocol
1233 <h1><a name="section_20">Exotic Features</a></h1>
1236 <li>Add pre-parsing phase that converts non-ISO syntax to supported
1238 <p> This could allow SQL written for other databases to run without
1241 </li><li>Allow plug-in modules to emulate features from other databases
1242 </li><li>Add Oracle-style packages (Pavel)
1244 <p> A package would be a schema with session-local variables,<br/>
1245 public/private functions, and initialization functions. It<br/>
1246 is also possible to implement these capabilities<br/>
1247 in any schema and not use a separate "packages"<br/>
1250 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-08/msg00384.php">http://archives.postgresql.org/pgsql-hackers/2006-08/msg00384.php</a>
1253 <li>Consider allowing control of upper/lower case folding of unquoted
1255 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2004-04/msg00818.php">http://archives.postgresql.org/pgsql-hackers/2004-04/msg00818.php</a>
1256 <a href="http://archives.postgresql.org/pgsql-hackers/2006-10/msg01527.php">http://archives.postgresql.org/pgsql-hackers/2006-10/msg01527.php</a>
1259 <h1><a name="section_21">Features We Do <u>Not</u> Want</a></h1>
1262 <li>All backends running as threads in a single process (not wanted)
1263 <p> This eliminates the process protection we get from the current setup.
1264 Thread creation is usually the same overhead as process creation on
1265 modern systems, so it seems unwise to use a pure threaded model.
1267 </li><li>Optimizer hints (not wanted)
1268 <p> Optimizer hints are used to work around problems in the optimizer. We
1269 would rather have the problems reported and fixed.
1271 <p> <a href="http://archives.postgresql.org/pgsql-hackers/2006-08/msg00506.php">http://archives.postgresql.org/pgsql-hackers/2006-08/msg00506.php</a>
1272 <a href="http://archives.postgresql.org/pgsql-hackers/2006-10/msg00517.php">http://archives.postgresql.org/pgsql-hackers/2006-10/msg00517.php</a>
1273 <a href="http://archives.postgresql.org/pgsql-hackers/2006-10/msg00663.php">http://archives.postgresql.org/pgsql-hackers/2006-10/msg00663.php</a>
1275 </li><li>Allow AS in "SELECT col AS label" to be optional (not wanted)
1276 <p> Because we support postfix operators, it isn't possible to make AS
1277 optional and continue to use bison.
1278 <a href="http://archives.postgresql.org/pgsql-hackers/2003-04/msg00436.php">http://archives.postgresql.org/pgsql-hackers/2003-04/msg00436.php</a>
1280 <p> <a href="http://archives.postgresql.org/pgsql-sql/2006-08/msg00164.php">http://archives.postgresql.org/pgsql-sql/2006-08/msg00164.php</a>
1282 </li><li>Embedded server (not wanted)
1283 <p> While PostgreSQL clients runs fine in limited-resource environments, the
1284 server requires multiple processes and a stable pool of resources to
1285 run reliabily and efficiently. Stripping down the PostgreSQL server
1286 to run in the same process address space as the client application
1287 would add too much complexity and failure cases.</p>