-PostgreSQL TODO List
-====================
-Current maintainer: Bruce Momjian (bruce@momjian.us)
-Last updated: Tue Apr 15 11:08:50 EDT 2008
+The TODO list is now maintained at:
-The most recent version of this document can be viewed at
-http://www.postgresql.org/docs/faqs.TODO.html.
-
-#A hyphen, "-", marks changes that will appear in the upcoming 8.3 release.#
-#A percent sign, "%", marks items that are easier to implement.#
-
-Bracketed items, "[]", have more detail.
-
-This list contains all known PostgreSQL bugs and feature requests. If
-you would like to work on an item, please read the Developer's FAQ
-first. There is also a developer's wiki at
-http://developer.postgresql.org.
-
-
-Administration
-==============
-
-* -Allow administrators to safely terminate individual sessions either
- via an SQL function or SIGTERM
-* Check for unreferenced table files created by transactions that were
- in-progress when the server terminated abruptly
-
- http://archives.postgresql.org/pgsql-patches/2006-06/msg00096.php
-
-* Set proper permissions on non-system schemas during db creation
-
- Currently all schemas are owned by the super-user because they are copied
- from the template1 database. However, since all objects are inherited
- from the template database, it is not clear that setting schemas to the db
- owner is correct.
-
-* Add function to report the time of the most recent server reload
-* Allow statistics collector information to be pulled from the collector
- process directly, rather than requiring the collector to write a
- filesystem file twice a second?
-* Reduce file system activity overhead of statistics file pgstat.stat
-
- http://archives.postgresql.org/pgsql-general/2007-12/msg00106.php
-
-* Allow statistics last vacuum/analyze execution times to be displayed
- without requiring stats_row_level to be enabled
-
- http://archives.postgresql.org/pgsql-docs/2007-04/msg00028.php
-
-* Allow log_min_messages to be specified on a per-module basis
-
- This would allow administrators to see more detailed information from
- specific sections of the backend, e.g. checkpoints, autovacuum, etc.
- Another idea is to allow separate configuration files for each module,
- or allow arbitrary SET commands to be passed to them.
-
-* Simplify ability to create partitioned tables
-
- This would allow creation of partitioned tables without requiring
- creation of triggers or rules for INSERT/UPDATE/DELETE, and constraints
- for rapid partition selection. Options could include range and hash
- partition selection.
-
- http://archives.postgresql.org/pgsql-hackers/2007-03/msg00375.php
- http://archives.postgresql.org/pgsql-hackers/2007-04/msg00151.php
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg00028.php
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg00248.php
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg00387.php
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg00413.php
-
-* Allow auto-selection of partitioned tables for min/max() operations
-* Allow more complex user/database default GUC settings
-
- Currently ALTER USER and ALTER DATABASE support per-user and
- per-database defaults. Consider adding per-user-and-database
- defaults so things like search_path can be defaulted for a
- specific user connecting to a specific database.
-
-* Allow custom variable classes that can restrict who can set the values
-
- http://archives.postgresql.org/pgsql-hackers/2006-11/msg00911.php
-
-* Implement the SQL standard mechanism whereby REVOKE ROLE revokes only
- the privilege granted by the invoking role, and not those granted
- by other roles
-
- http://archives.postgresql.org/pgsql-bugs/2007-05/msg00010.php
-
-* Allow SSL authentication/encryption over unix domain sockets
-
- http://archives.postgresql.org/pgsql-hackers/2007-12/msg00924.php
-
-* Allow SSL key file permission checks to be optionally disabled when
- sharing SSL keys with other applications
-
- http://archives.postgresql.org/pgsql-bugs/2007-12/msg00069.php
-
-* Allow client certificate names to be checked against the client
- hostname
-
- This is already implemented in
- libpq/fe-secure.c::verify_peer_name_matches_certificate() but the code
- is commented out.
-
-* Configuration files
-
- o Allow pg_hba.conf to specify host names along with IP addresses
-
- Host name lookup could occur when the postmaster reads the
- pg_hba.conf file, or when the backend starts. Another
- solution would be to reverse lookup the connection IP and
- check that hostname against the host names in pg_hba.conf.
- We could also then check that the host name maps to the IP
- address.
-
- o %Allow postgresql.conf file values to be changed via an SQL
- API, perhaps using SET GLOBAL
- o Allow the server to be stopped/restarted via an SQL API
- o Issue a warning if a change-on-restart-only postgresql.conf value
- is modified and the server config files are reloaded
- o Consider normalizing fractions in postgresql.conf, perhaps
- using '%'
-
- http://archives.postgresql.org/pgsql-hackers/2007-06/msg00550.php
-
- o Allow Kerberos to disable stripping of realms so we can
- check the username@realm against multiple realms
-
- http://archives.postgresql.org/pgsql-hackers/2007-11/msg00009.php
-
-
-* Tablespaces
-
- o Allow a database in tablespace t1 with tables created in
- tablespace t2 to be used as a template for a new database created
- with default tablespace t2
-
- Currently all objects in the default database tablespace must
- have default tablespace specifications. This is because new
- databases are created by copying directories. If you mix default
- tablespace tables and tablespace-specified tables in the same
- directory, creating a new database from such a mixed directory
- would create a new database with tables that had incorrect
- explicit tablespaces. To fix this would require modifying
- pg_class in the newly copied database, which we don't currently
- do.
-
- o Allow reporting of which objects are in which tablespaces
-
- This item is difficult because a tablespace can contain objects
- from multiple databases. There is a server-side function that
- returns the databases which use a specific tablespace, so this
- requires a tool that will call that function and connect to each
- database to find the objects in each database for that tablespace.
-
- o Allow WAL replay of CREATE TABLESPACE to work when the directory
- structure on the recovery computer is different from the original
-
- o Allow per-tablespace quotas
-
-
-* Point-In-Time Recovery (PITR)
-
- o Allow a warm standby system to also allow read-only statements
- [pitr]
-
- http://archives.postgresql.org/pgsql-hackers/2007-03/msg00050.php
-
- o %Create dump tool for write-ahead logs for use in determining
- transaction id for point-in-time recovery
-
- This is useful for checking PITR recovery.
-
- o Allow recovery.conf to support the same syntax as
- postgresql.conf, including quoting
-
- http://archives.postgresql.org/pgsql-hackers/2006-12/msg00497.php
-
- o Fix server restart problem when the server was shutdown during
- a PITR backup
-
- http://archives.postgresql.org/pgsql-hackers/2007-11/msg00800.php
-
- o Recreate pg_xlog/archive_status/ if it doesn't exist after
- restoring from a PITR backup
-
- http://archives.postgresql.org/pgsql-hackers/2007-12/msg00487.php
-
-
-Data Types
-==========
-
-* Change NUMERIC to enforce the maximum precision
-* Reduce storage space for small NUMERICs
-
- http://archives.postgresql.org/pgsql-hackers/2007-02/msg01331.php
- http://archives.postgresql.org/pgsql-patches/2007-02/msg00505.php
- http://archives.postgresql.org/pgsql-hackers/2007-06/msg00715.php
-
-* Fix data types where equality comparison isn't intuitive, e.g. box
-* Add support for public SYNONYMs
-
- http://archives.postgresql.org/pgsql-hackers/2006-03/msg00519.php
-
-* Fix CREATE CAST on DOMAINs
-
- http://archives.postgresql.org/pgsql-hackers/2006-05/msg00072.php
- http://archives.postgresql.org/pgsql-hackers/2006-09/msg01681.php
-
-* Allow domains to be cast
-
- http://archives.postgresql.org/pgsql-hackers/2003-06/msg01206.php
- http://archives.postgresql.org/pgsql-hackers/2007-08/msg00289.php
-
-* Add support for SQL-standard GENERATED/IDENTITY columns
-
- http://archives.postgresql.org/pgsql-hackers/2006-07/msg00543.php
- http://archives.postgresql.org/pgsql-hackers/2006-08/msg00038.php
- http://archives.postgresql.org/pgsql-hackers/2007-05/msg00344.php
- http://archives.postgresql.org/pgsql-patches/2007-05/msg00076.php
- http://archives.postgresql.org/pgsql-hackers/2008-02/msg00604.php
-
-* Improve XML support
-
- http://developer.postgresql.org/index.php/XML_Support
-
-* Consider placing all sequences in a single table, or create a system
- view
-
- http://archives.postgresql.org/pgsql-hackers/2008-03/msg00008.php
-
-* Allow the UUID type to accept non-standard formats
-
- http://archives.postgresql.org/pgsql-hackers/2008-02/msg01214.php
-
-* Allow text search dictionary to filter out only stop words
-
- http://archives.postgresql.org/pgsql-patches/2007-11/msg00081.php
-
-* Consider a function-based API for '@@' full text searches
-
- http://archives.postgresql.org/pgsql-hackers/2007-11/msg00511.php
-
-* Improve text search error messages
-
- http://archives.postgresql.org/pgsql-hackers/2007-10/msg00966.php
- http://archives.postgresql.org/pgsql-hackers/2007-11/msg01146.php
-
-* Consider a special data type for regular expressions
-
- http://archives.postgresql.org/pgsql-hackers/2007-08/msg01067.php
-
-* Reduce BIT data type overhead using short varlena headers
-
- http://archives.postgresql.org/pgsql-general/2007-12/msg00273.php
-
-* Allow xml arrays to be cast to other data types
-
- http://archives.postgresql.org/pgsql-hackers/2007-09/msg00981.php
- http://archives.postgresql.org/pgsql-hackers/2007-10/msg00231.php
- http://archives.postgresql.org/pgsql-hackers/2007-11/msg00471.php
-
-* Simplify integer cross-data-type operators
-
- http://archives.postgresql.org/pgsql-bugs/2008-01/msg00189.php
-
-* Allow XML to accept more liberal DOCTYPE specifications
-
- http://archives.postgresql.org/pgsql-general/2008-02/msg00347.php
-
-
-* Dates and Times
-
- o Allow infinite dates and intervals just like infinite timestamps
- o Merge hardwired timezone names with the TZ database; allow either
- kind everywhere a TZ name is currently taken
- o Allow TIMESTAMP WITH TIME ZONE to store the original timezone
- information, either zone name or offset from UTC [timezone]
-
- If the TIMESTAMP value is stored with a time zone name, interval
- computations should adjust based on the time zone rules.
-
- o Fix SELECT '0.01 years'::interval, '0.01 months'::interval
- o Add a GUC variable to allow output of interval values in ISO8601
- format
- o Have timestamp subtraction not call justify_hours()?
-
- http://archives.postgresql.org/pgsql-sql/2006-10/msg00059.php
-
- o Improve timestamptz subtraction to be DST-aware
-
- Currently subtracting one date from another that crosses a
- daylight savings time adjustment can return '1 day 1 hour', but
- adding that back to the first date returns a time one hour in
- the future. This is caused by the adjustment of '25 hours' to
- '1 day 1 hour', and '1 day' is the same time the next day, even
- if daylight savings adjustments are involved.
-
- o Fix interval display to support values exceeding 2^31 hours
- o Add overflow checking to timestamp and interval arithmetic
- o Extend timezone code to allow 64-bit values so we can
- represent years beyond 2038
-
- http://archives.postgresql.org/pgsql-hackers/2006-09/msg01363.php
-
- o Use LC_TIME for localized weekday/month names, rather than
- LC_MESSAGES
-
- http://archives.postgresql.org/pgsql-hackers/2006-11/msg00390.php
-
- o Add ISO INTERVAL handling
-
- http://archives.postgresql.org/pgsql-hackers/2006-01/msg00250.php
- http://archives.postgresql.org/pgsql-bugs/2006-04/msg00248.php
-
- o Support ISO INTERVAL syntax if units cannot be determined from
- the string, and are supplied after the string
-
- The SQL standard states that the units after the string
- specify the units of the string, e.g. INTERVAL '2' MINUTE
- should return '00:02:00'. The current behavior has the units
- restrict the interval value to the specified unit or unit
- range, INTERVAL '70' SECOND returns '00:00:10'.
-
- For syntax that isn't uniquely ISO or PG syntax, like '1' or
- '1:30', treat as ISO if there is a range specification clause,
- and as PG if there no clause is present, e.g. interpret '1:30'
- MINUTE TO SECOND as '1 minute 30 seconds', and interpret
- '1:30' as '1 hour, 30 minutes'.
-
- This makes common cases like SELECT INTERVAL '1' MONTH
- SQL-standard results. The SQL standard supports a limited
- number of unit combinations and doesn't support unit names in
- the string. The PostgreSQL syntax is more flexible in the
- range of units supported, e.g. PostgreSQL supports '1 year 1
- hour', while the SQL standard does not.
-
- o Add support for year-month syntax, INTERVAL '50-6' YEAR
- TO MONTH
- o Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1
- year' AS INTERVAL MONTH), and this should return '12 months'
- o Round or truncate values to the requested precision, e.g.
- INTERVAL '11 months' AS YEAR should return one or zero
- o Support precision, CREATE TABLE foo (a INTERVAL MONTH(3))
-
-
-* Arrays
-
- o Delay resolution of array expression's data type so assignment
- coercion can be performed on empty array expressions
- o Add support for arrays of domains
-
- http://archives.postgresql.org/pgsql-patches/2007-05/msg00114.php
-
- o Allow single-byte header storage for array elements
-
-
-* Binary Data
-
- o Improve vacuum of large objects, like contrib/vacuumlo?
- o Add security checking for large objects
- o Auto-delete large objects when referencing row is deleted
-
- contrib/lo offers this functionality.
-
- o Allow read/write into TOAST values like large objects
-
- This requires the TOAST column to be stored EXTERNAL.
-
- o Add API for 64-bit large object access
-
- http://archives.postgresql.org/pgsql-hackers/2005-09/msg00781.php
-
-* MONEY data type
-
- * Add locale-aware MONEY type, and support multiple currencies
-
- http://archives.postgresql.org/pgsql-general/2005-08/msg01432.php
- http://archives.postgresql.org/pgsql-hackers/2007-03/msg01181.php
-
- * MONEY dumps in a locale-specific format making it difficult to
- restore to a system with a different locale
- * Allow MONEY to be easily cast to/from other numeric data types
-
-
-Functions
-=========
-
-* Allow INET subnet tests using non-constants to be indexed
-* Allow to_date() and to_timestamp() accept localized month names
-* Fix to_date()-related functions to consistently issue errors
-
- http://archives.postgresql.org/pgsql-hackers/2007-02/msg00915.php
-
-* Add missing parameter handling in to_char()
-
- http://archives.postgresql.org/pgsql-hackers/2005-12/msg00948.php
-
-* Allow substring/replace() to get/set bit values
-* Allow to_char() on interval values to accumulate the highest unit
- requested
-
- Some special format flag would be required to request such
- accumulation. Such functionality could also be added to EXTRACT.
- Prevent accumulation that crosses the month/day boundary because of
- the uneven number of days in a month.
-
- o to_char(INTERVAL '1 hour 5 minutes', 'MI') => 65
- o to_char(INTERVAL '43 hours 20 minutes', 'MI' ) => 2600
- o to_char(INTERVAL '43 hours 20 minutes', 'WK:DD:HR:MI') => 0:1:19:20
- o to_char(INTERVAL '3 years 5 months','MM') => 41
-
-* Implement inlining of set-returning functions defined in SQL
-* Allow SQL-language functions to return results from RETURNING queries
-
- http://archives.postgresql.org/pgsql-hackers/2006-10/msg00665.php
-
-* Allow SQL-language functions to reference parameters by parameter name
-
- Currently SQL-language functions can only refer to dollar parameters,
- e.g. $1
-
-* Add SPI_gettypmod() to return the typemod for a TupleDesc
-* Enforce typmod for function inputs, function results and parameters for
- spi_prepare'd statements called from PLs
-
- http://archives.postgresql.org/pgsql-hackers/2007-01/msg01403.php
-
-* Allow holdable cursors in SPI
-* Tighten function permission checks
-
- http://archives.postgresql.org/pgsql-hackers/2006-12/msg00568.php
-
-* Fix IS OF so it matches the ISO specification, and add documentation
-
- http://archives.postgresql.org/pgsql-patches/2003-08/msg00060.php
- http://archives.postgresql.org/pgsql-hackers/2007-02/msg00060.php
-
-* Add missing operators for geometric data types
-
- Some geometric types do not have the full suite of geometric operators,
- e.g. box @> point
-
-* Implement Boyer-Moore searching in strpos()
-
- http://archives.postgresql.org/pgsql-patches/2007-08/msg00012.php
-
-* Prevent malicious functions from being executed with the permissions
- of unsuspecting users
-
- Index functions are safe, so VACUUM and ANALYZE are safe too.
- Triggers, CHECK and DEFAULT expressions, and rules are still vulnerable.
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg00268.php
-
-* Reduce memory usage of aggregates in set returning functions
-
- http://archives.postgresql.org/pgsql-performance/2008-01/msg00031.php
-
-* Add temporal versions of generate_series()
-
- http://archives.postgresql.org/pgsql-hackers/2007-04/msg01180.php
-
-* Add array_accum() and array_to_set() functions for arrays
-
- The standards specify array_agg() and UNNEST.
- http://archives.postgresql.org/pgsql-hackers/2007-08/msg00464.php
-
-* Fix /contrib/ltree operator
-
- http://archives.postgresql.org/pgsql-bugs/2007-11/msg00044.php
-
-* Fix inconsistent precedence of =, >, and < compared to <>, >=, and <=
-
- http://archives.postgresql.org/pgsql-bugs/2007-12/msg00145.php
-
-* Fix regular expression bug when using complex back-references
-
- http://archives.postgresql.org/pgsql-bugs/2007-10/msg00000.php
-
-* Have /contrib/dblink reuse unnamed connections
-
- http://archives.postgresql.org/pgsql-hackers/2007-10/msg00895.php
-
-* Add SQL-standard array_agg() and unnest() array functions
-
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg01017.php
-
-* Allow calling of a procedure outside a SELECT that can control the
- transaction state
-
- http://archives.postgresql.org/pgsql-hackers/2007-10/msg01375.php
-
-
-
-Multi-Language Support
-======================
-
-* Add NCHAR (as distinguished from ordinary varchar),
-* Allow locale to be set at database creation
-
- Currently locale can only be set during initdb. No global tables have
- locale-aware columns. However, the database template used during
- database creation might have locale-aware indexes. The indexes would
- need to be reindexed to match the new locale.
-
-* Allow encoding on a per-column basis optionally using the ICU library
-
- Right now only one encoding is allowed per database. [locale]
-
- http://archives.postgresql.org/pgsql-hackers/2005-03/msg00932.php
- http://archives.postgresql.org/pgsql-patches/2005-08/msg00309.php
- http://archives.postgresql.org/pgsql-patches/2006-03/msg00233.php
- http://archives.postgresql.org/pgsql-hackers/2006-09/msg00662.php
- http://wiki.postgresql.org/wiki/Todo:Collate
- http://wiki.postgresql.org/wiki/Todo:ICU
-
-* Add CREATE COLLATE? [locale]
-* Support multiple simultaneous character sets, per SQL92
-* Improve UTF8 combined character handling?
-* Add octet_length_server() and octet_length_client()
-* Make octet_length_client() the same as octet_length()?
-* Fix problems with wrong runtime encoding conversion for NLS message files
-* Add URL to more complete multi-byte regression tests
-
- http://archives.postgresql.org/pgsql-hackers/2005-07/msg00272.php
-
-* Fix ILIKE and regular expressions to handle case insensitivity
- properly in multibyte encodings
-
- http://archives.postgresql.org/pgsql-bugs/2005-10/msg00001.php
- http://archives.postgresql.org/pgsql-patches/2005-11/msg00173.php
-
-* Set client encoding based on the client operating system encoding
-
- Currently client_encoding is set in postgresql.conf, which
- defaults to the server encoding.
- http://archives.postgresql.org/pgsql-hackers/2006-08/msg01696.php
-
-* Change memory allocation for multi-byte functions so memory is
- allocated inside conversion functions
-
- Currently we preallocate memory based on worst-case usage.
-
-
-
-Views / Rules
-=============
-
-* Automatically create rules on views so they are updateable, per SQL99
-
- We can only auto-create rules for simple views. For more complex
- cases users will still have to write rules manually.
-
- http://archives.postgresql.org/pgsql-hackers/2006-03/msg00586.php
- http://archives.postgresql.org/pgsql-patches/2006-08/msg00255.php
-
-* Add the functionality for WITH CHECK OPTION clause of CREATE VIEW
-* Allow VIEW/RULE recompilation when the underlying tables change
-
- Another issue is whether underlying table changes should be reflected
- in the view, e.g. should SELECT * show additional columns if they
- are added after the view is created.
-
-* Make it possible to use RETURNING together with conditional DO INSTEAD
- rules, such as for partitioning setups
-
- http://archives.postgresql.org/pgsql-hackers/2007-09/msg00577.php
-
-* Add the ability to automatically create materialized views
-
- Right now materialized views require the user to create triggers on the
- main table to keep the summary table current. SQL syntax should be able
- to manager the triggers and summary table automatically. A more
- sophisticated implementation would automatically retrieve from the
- summary table when the main table is referenced, if possible.
-
-
-
-SQL Commands
-============
-
-* Add CORRESPONDING BY to UNION/INTERSECT/EXCEPT
-* Add ROLLUP, CUBE, GROUPING SETS options to GROUP BY
-* %Allow SET CONSTRAINTS to be qualified by schema/table name
-* %Add a separate TRUNCATE permission
-
- Currently only the owner can TRUNCATE a table because triggers are not
- called, and the table is locked in exclusive mode.
-
-* Allow PREPARE of cursors
-* Allow finer control over the caching of prepared query plans
-
- Currently queries prepared via the libpq API are planned on first
- execute using the supplied parameters --- allow SQL PREPARE to do the
- same. Also, allow control over replanning prepared queries either
- manually or automatically when statistics for execute parameters
- differ dramatically from those used during planning.
-
-* Improve logging of prepared transactions recovered during startup
-
- http://archives.postgresql.org/pgsql-hackers/2006-11/msg00092.php
-
-* Add a GUC variable to warn about non-standard SQL usage in queries
-* Add SQL-standard MERGE command, typically used to merge two tables
- [merge]
-
- This is similar to UPDATE, then for unmatched rows, INSERT.
- Whether concurrent access allows modifications which could cause
- row loss is implementation independent.
-
-* Add REPLACE or UPSERT command that does UPDATE, or on failure, INSERT
- [merge]
-
- To implement this cleanly requires that the table have a unique index
- so duplicate checking can be easily performed. It is possible to
- do it without a unique index if we require the user to LOCK the table
- before the MERGE.
-
- http://archives.postgresql.org/pgsql-hackers/2005-11/msg00501.php
- http://archives.postgresql.org/pgsql-hackers/2005-11/msg00536.php
-
-* Add NOVICE output level for helpful messages like automatic sequence/index
- creation
-* Add GUC to issue notice about statements that use unjoined tables
-* Allow EXPLAIN to identify tables that were skipped because of
- constraint_exclusion
-* Allow EXPLAIN output to be more easily processed by scripts, perhaps XML
-* Enable standard_conforming_strings
-* Make standard_conforming_strings the default in 8.5?
-
- When this is done, backslash-quote should be prohibited in non-E''
- strings because of possible confusion over how such strings treat
- backslashes. Basically, '' is always safe for a literal single
- quote, while \' might or might not be based on the backslash
- handling rules.
-
-* Simplify dropping roles that have objects in several databases
-* Allow COMMENT ON to accept an expression rather than just a string
-* Allow the count returned by SELECT, etc to be represented as an int64
- to allow a higher range of values
-* Add SQL99 WITH clause to SELECT
-* Add SQL:2003 WITH RECURSIVE (hierarchical) queries to SELECT
-
- http://archives.postgresql.org/pgsql-hackers/2007-01/msg01375.php
- http://archives.postgresql.org/pgsql-hackers/2008-02/msg00642.php
- http://archives.postgresql.org/pgsql-patches/2007-03/msg00139.php
- http://archives.postgresql.org/pgsql-hackers/2007-11/msg01334.php
- http://archives.postgresql.org/pgsql-patches/2008-01/msg00105.php
- http://archives.postgresql.org/pgsql-patches/2008-03/msg00327.php
-
-* Add DEFAULT .. AS OWNER so permission checks are done as the table
- owner
-
- This would be useful for SERIAL nextval() calls and CHECK constraints.
-
-* Allow DISTINCT to work in multiple-argument aggregate calls
-* Add column to pg_stat_activity that shows the progress of long-running
- commands like CREATE INDEX and VACUUM
-* Implement SQL:2003 window functions
-* Improve failure message when DROP DATABASE is used on a database that
- has prepared transactions
-* Allow INSERT/UPDATE ... RETURNING inside a SELECT 'FROM' clause
-
- http://archives.postgresql.org/pgsql-general/2006-09/msg00803.php
- http://archives.postgresql.org/pgsql-hackers/2006-10/msg00693.php
-
-* Increase locking when DROPing objects so dependent objects cannot
- get dropped while the DROP operation is happening
-
- http://archives.postgresql.org/pgsql-hackers/2007-01/msg00937.php
-
-* -Allow AS in "SELECT col AS label" to be optional in certain cases
-
-* Allow INSERT ... DELETE ... RETURNING, namely allow the DELETE ...
- RETURNING to supply values to the INSERT
- http://archives.postgresql.org/pgsql-hackers/2008-02/thrd2.php#00979
-
-* Add comments on system tables/columns using the information in
- catalogs.sgml
-
- Ideally the information would be pulled from the SGML file
- automatically.
-
-* Improve reporting of UNION type mismatches
-
- http://archives.postgresql.org/pgsql-hackers/2007-04/msg00944.php
- http://archives.postgresql.org/pgsql-hackers/2008-03/msg00597.php
-
-
-* CREATE
-
- o Allow CREATE TABLE AS to determine column lengths for complex
- expressions like SELECT col1 || col2
-
- o Have WITH CONSTRAINTS also create constraint indexes
-
- http://archives.postgresql.org/pgsql-patches/2007-04/msg00149.php
-
- o Have CONSTRAINT cname NOT NULL record the contraint name
-
- Right now pg_attribute.attnotnull records the NOT NULL status
- of the column, but does not record the contraint name
-
- o Prevent concurrent CREATE TABLE table1 from sometimes returning
- a cryptic error message
-
- http://archives.postgresql.org/pgsql-bugs/2007-10/msg00169.php
-
-
-* UPDATE
- o Allow UPDATE tab SET ROW (col, ...) = (SELECT...)
-
- http://archives.postgresql.org/pgsql-hackers/2006-07/msg01306.php
- http://archives.postgresql.org/pgsql-hackers/2007-03/msg00865.php
- http://archives.postgresql.org/pgsql-patches/2007-04/msg00315.php
- http://archives.postgresql.org/pgsql-patches/2008-03/msg00237.php
-
- o Research self-referential UPDATEs that see inconsistent row versions
- in read-committed mode
-
- http://archives.postgresql.org/pgsql-hackers/2007-05/msg00507.php
- http://archives.postgresql.org/pgsql-hackers/2007-06/msg00016.php
-
- o Allow GLOBAL temporary tables to exist as empty by default in
- all sessions
-
- http://archives.postgresql.org/pgsql-hackers/2007-07/msg00006.php
-
-
-* ALTER
-
- o Have ALTER TABLE RENAME rename SERIAL sequence names
-
- http://archives.postgresql.org/pgsql-hackers/2008-03/msg00008.php
-
- o Have ALTER SEQUENCE RENAME rename the sequence name stored
- in the sequence table
-
- http://archives.postgresql.org/pgsql-bugs/2007-09/msg00092.php
- http://archives.postgresql.org/pgsql-bugs/2007-10/msg00007.php
- http://archives.postgresql.org/pgsql-hackers/2008-03/msg00008.php
-
- o Add ALTER DOMAIN to modify the underlying data type
- o %Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME
-
- http://archives.postgresql.org/pgsql-patches/2006-02/msg00168.php
-
- o %Allow ALTER TABLE to change constraint deferrability and actions
- o Add missing object types for ALTER ... SET SCHEMA
- o Allow ALTER TABLESPACE to move to different directories
- o Allow databases to be moved to different tablespaces
- o Allow moving system tables to other tablespaces, where possible
-
- Currently non-global system tables must be in the default database
- tablespace. Global system tables can never be moved.
-
- o Prevent parent tables from altering or dropping constraints
- like CHECK that are inherited by child tables unless CASCADE
- is used
- o %Prevent child tables from altering or dropping constraints
- like CHECK that were inherited from the parent table
- o Have ALTER INDEX update the name of a constraint using that index
- o Add ALTER TABLE RENAME CONSTRAINT, update index name also
- o Allow column display reordering by recording a display,
- storage, and permanent id for every column?
-
- http://archives.postgresql.org/pgsql-hackers/2006-12/msg00782.php
-
-
-
-* CLUSTER
-
- o Automatically maintain clustering on a table
-
- This might require some background daemon to maintain clustering
- during periods of low usage. It might also require tables to be only
- partially filled for easier reorganization. Another idea would
- be to create a merged heap/index data file so an index lookup would
- automatically access the heap data too. A third idea would be to
- store heap rows in hashed groups, perhaps using a user-supplied
- hash function.
- http://archives.postgresql.org/pgsql-performance/2004-08/msg00349.php
-
- o %Add default clustering to system tables
-
- To do this, determine the ideal cluster index for each system
- table and set the cluster setting during initdb.
-
- o %Add VERBOSE option to report tables as they are processed,
- like VACUUM VERBOSE
-
-
-* COPY
-
- o Allow COPY to report error lines and continue
-
- This requires the use of a savepoint before each COPY line is
- processed, with ROLLBACK on COPY failure.
- http://archives.postgresql.org/pgsql-hackers/2007-12/msg00572.php
-
- o Allow COPY on a newly-created table to skip WAL logging
-
- On crash recovery, the table involved in the COPY would
- be removed or have its heap and index files truncated. One
- issue is that no other backend should be able to add to
- the table at the same time, which is something that is
- currently allowed. This currently is done if the table is
- created inside the same transaction block as the COPY because
- no other backends can see the table.
-
- o Consider using a ring buffer for COPY FROM
-
- http://archives.postgresql.org/pgsql-patches/2008-02/msg00140.php
- http://archives.postgresql.org/pgsql-hackers/2008-02/msg01080.php
-
- o Allow COPY FROM to create index entries in bulk
-
- http://archives.postgresql.org/pgsql-hackers/2008-02/msg00811.php
-
- o Allow COPY in CSV mode to control whether a quoted zero-length
- string is treated as NULL
-
- Currently this is always treated as a zero-length string,
- which generates an error when loading into an integer column
- http://archives.postgresql.org/pgsql-hackers/2007-07/msg00905.php
-
-
-
-* GRANT/REVOKE
-
- o Allow column-level privileges
- o %Allow GRANT/REVOKE permissions to be applied to all schema objects
- with one command
-
- The proposed syntax is:
- GRANT SELECT ON ALL TABLES IN public TO phpuser;
- GRANT SELECT ON NEW TABLES IN public TO phpuser;
-
- o Allow GRANT/REVOKE permissions to be inherited by objects based on
- schema permissions
-
- o Allow SERIAL sequences to inherit permissions from the base table?
-
-
-* CURSOR
-
- o Prevent DROP TABLE from dropping a row referenced by its own open
- cursor?
-
-
-* INSERT
-
- o Allow INSERT/UPDATE of the system-generated oid value for a row
- o In rules, allow VALUES() to contain a mixture of 'old' and 'new'
- references
-
-
-* SHOW/SET
-
- o Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM
- ANALYZE, and CLUSTER
-
-
-* LISTEN/NOTIFY
-
- o Allow LISTEN/NOTIFY to store info in memory rather than tables?
-
- Currently LISTEN/NOTIFY information is stored in pg_listener.
- Storing such information in memory would improve performance.
-
- o Add optional textual message to NOTIFY
-
- This would allow an informational message to be added to the notify
- message, perhaps indicating the row modified or other custom
- information.
-
- o Allow multiple identical NOTIFY events to always be communicated
- to the client, rather than sent as a single notification to the
- listener
-
- http://archives.postgresql.org/pgsql-general/2008-01/msg00057.php
-
- o Allow NOTIFY in rules involving conditionals
- o Improve LISTEN concurrency
-
- http://archives.postgresql.org/pgsql-hackers/2008-02/msg01106.php
-
-
-
-Referential Integrity
-=====================
-
-* Add MATCH PARTIAL referential integrity
-* Change foreign key constraint for array -> element to mean element
- in array?
-* Fix problem when cascading referential triggers make changes on
- cascaded tables, seeing the tables in an intermediate state
-
- http://archives.postgresql.org/pgsql-hackers/2005-09/msg00174.php
- http://archives.postgresql.org/pgsql-hackers/2005-09/msg00174.php
-
-* Allow DEFERRABLE and end-of-statement UNIQUE constraints?
-
- This would allow UPDATE tab SET col = col + 1 to work if col has
- a unique index. Currently, uniqueness checks are done while the
- command is being executed, rather than at the end of the statement
- or transaction.
- http://people.planetpostgresql.org/greg/index.php?/archives/2006/06/10.html
- http://archives.postgresql.org/pgsql-hackers/2006-09/msg01458.php
-
-* Optimize referential integrity checks
-
- http://archives.postgresql.org/pgsql-performance/2005-10/msg00458.php
- http://archives.postgresql.org/pgsql-hackers/2007-04/msg00744.php
-
-
-Server-Side Languages
-=====================
-
-* PL/pgSQL
- o Fix RENAME to work on variables other than OLD/NEW
-
- http://archives.postgresql.org/pgsql-hackers/2002-03/msg00591.php
- http://archives.postgresql.org/pgsql-hackers/2007-01/msg01615.php
- http://archives.postgresql.org/pgsql-hackers/2007-01/msg01587.php
-
- o Allow function parameters to be passed by name,
- get_employee_salary(12345 AS emp_id, 2001 AS tax_year)
- o Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]
- o Allow listing of record column names, and access to
- record columns via variables, e.g. columns := r.(*),
- tval2 := r.(colname)
-
- http://archives.postgresql.org/pgsql-patches/2005-07/msg00458.php
- http://archives.postgresql.org/pgsql-patches/2006-05/msg00302.php
- http://archives.postgresql.org/pgsql-patches/2006-06/msg00031.php
-
- o Add support for SCROLL cursors
- o Add support for WITH HOLD cursors
- o Allow row and record variables to be set to NULL constants,
- and allow NULL tests on such variables
-
- Because a row is not scalar, do not allow assignment
- from NULL-valued scalars.
-
- http://archives.postgresql.org/pgsql-hackers/2006-10/msg00070.php
-
- o Review handling of MOVE and FETCH
-
- http://archives.postgresql.org/pgsql-patches/2007-04/msg00527.php
-
- o Improve logic of determining if an identifier is a a
- variable or column name
-
- http://archives.postgresql.org/pgsql-hackers/2007-07/msg00436.php
-
- o Consider keeping seperate cached copies when search_path changes
-
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg01009.php
-
- o Add CASE capability to language (already in SQL)
-
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg00696.php
-
-
-
-
-* Other
- o Add table function support to pltcl, plpythonu
- o Add support for polymorphic arguments and return types to
- languages other than PL/PgSQL
- o Add capability to create and call PROCEDURES
- o Add support for OUT and INOUT parameters to languages other
- than PL/PgSQL
- o Add PL/PythonU tracebacks
-
- http://archives.postgresql.org/pgsql-patches/2006-02/msg00288.php
-
- o Allow data to be passed in native language formats, rather
- than only text
-
- http://archives.postgresql.org/pgsql-hackers/2007-05/msg00289.php
-
- o Add ability to obfuscate function bodies
-
- http://archives.postgresql.org/pgsql-patches/2008-01/msg00125.php
-
-
-
-Clients
-=======
-
-* Have pg_ctl look at PGHOST in case it is a socket directory?
-* Allow pg_ctl to work properly with configuration files located outside
- the PGDATA directory
-
- pg_ctl can not read the pid file because it isn't located in the
- config directory but in the PGDATA directory. The solution is to
- allow pg_ctl to read and understand postgresql.conf to find the
- data_directory value.
-
-* Add a function like pg_get_indexdef() that report more detailed index
- information
-
- http://archives.postgresql.org/pgsql-bugs/2007-12/msg00166.php
-
-* psql
- o Have psql show current values for a sequence
- o Move psql backslash database information into the backend, use
- mnemonic commands? [psql]
-
- This would allow non-psql clients to pull the same information out
- of the database as psql.
-
- o Fix psql's \d commands more consistent
-
- http://archives.postgresql.org/pgsql-hackers/2004-11/msg00014.php
- http://archives.postgresql.org/pgsql-hackers/2004-11/msg00014.php
-
- o Consistently display privilege information for all objects in psql
- o Add auto-expanded mode so expanded output is used if the row
- length is wider than the screen width.
-
- Consider using auto-expanded mode for backslash commands like \df+.
-
- o Prevent tab completion of SET TRANSACTION from querying the
- database and therefore preventing the transaction isolation
- level from being set.
-
- Currently SET <tab> causes a database lookup to check all
- supported session variables. This query causes problems
- because setting the transaction isolation level must be the
- first statement of a transaction.
-
- o Add a \set variable to control whether \s displays line numbers
-
- Another option is to add \# which lists line numbers, and
- allows command execution.
-
- http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php
-
- o Prevent escape string warnings when object names have
- backslashes
-
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg00227.php
-
- o Have \d show foreign keys that reference a table's primary key
-
- http://archives.postgresql.org/pgsql-hackers/2007-04/msg00424.php
-
- o Have \d show child tables that inherit from the specified parent
- o Have \l+ show database size, if permissions allow
-
- Ideally it will not generate an error for invalid permissions
-
- o Include the symbolic SQLSTATE name in verbose error reports
-
- http://archives.postgresql.org/pgsql-general/2007-09/msg00438.php
-
- o Improve display if enums
-
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg00826.php
-
-
-
-
-* pg_dump / pg_restore
- o %Add dumping of comments on index columns and composite type columns
- o %Add full object name to the tag field. eg. for operators we need
- '=(integer, integer)', instead of just '='.
- o Add pg_dumpall custom format dumps?
- o Allow selection of individual object(s) of all types, not just
- tables
- o In a selective dump, allow dumping of an object and all its
- dependencies
- o Add options like pg_restore -l and -L to pg_dump
- o Stop dumping CASCADE on DROP TYPE commands in clean mode
- o Allow pg_dump --clean to drop roles that own objects or have
- privileges
- o Change pg_dump so that a comment on the dumped database is
- applied to the loaded database, even if the database has a
- different name. This will require new backend syntax, perhaps
- COMMENT ON CURRENT DATABASE.
- o Remove unnecessary function pointer abstractions in pg_dump source
- code
- o Allow pg_dump to utilize multiple CPUs and I/O channels by dumping
- multiple objects simultaneously
-
- The difficulty with this is getting multiple dump processes to
- produce a single dump output file. It also would require
- several sessions to share the same snapshot.
- http://archives.postgresql.org/pgsql-hackers/2008-02/msg00205.php
-
- o Allow pg_restore to utilize multiple CPUs and I/O channels by
- restoring multiple objects simultaneously
-
- This might require a pg_restore flag to indicate how many
- simultaneous operations should be performed. Only pg_dump's
- -Fc format has the necessary dependency information.
- http://archives.postgresql.org/pgsql-hackers/2008-02/msg00963.php
-
- o To better utilize resources, allow pg_restore to check foreign
- keys simultaneously, where possible
- o Allow pg_restore to create all indexes of a table
- concurrently, via a single heap scan
-
- This requires a pg_dump -Fc file because that format contains
- the required dependency information.
- http://archives.postgresql.org/pgsql-general/2007-05/msg01274.php
-
- o Allow pg_restore to load different parts of the COPY data
- simultaneously
- o Prevent pg_dump/pg_restore from being affected by
- statement_timeout
-
- Using psql to restore a pg_dump dump is also affected.
-
- o Remove pre-7.3 pg_dump code that assumes pg_depend does not exit
- o Allow pre/data/post files when schema and data are dumped
- separately, for performance reasons
-
- http://archives.postgresql.org/pgsql-hackers/2008-02/msg00205.php
-
-
-
-* ecpg
- o Docs
-
- Document differences between ecpg and the SQL standard and
- information about the Informix-compatibility module.
-
- o Solve cardinality > 1 for input descriptors / variables?
- o Add a semantic check level, e.g. check if a table really exists
- o fix handling of DB attributes that are arrays
- o Use backend PREPARE/EXECUTE facility for ecpg where possible
- o Implement SQLDA
- o Fix nested C comments
- o %sqlwarn[6] should be 'W' if the PRECISION or SCALE value specified
- o Make SET CONNECTION thread-aware, non-standard?
- o Allow multidimensional arrays
- o Add internationalized message strings
- o Implement COPY FROM STDIN
-
-
-* libpq
- o Add PQescapeIdentifierConn()
- o Prevent PQfnumber() from lowercasing unquoted the column name
-
- PQfnumber() should never have been doing lowercasing, but
- historically it has so we need a way to prevent it
-
- o Allow statement results to be automatically batched to the client
-
- Currently all statement results are transferred to the libpq
- client before libpq makes the results available to the
- application. This feature would allow the application to make
- use of the first result rows while the rest are transferred, or
- held on the server waiting for them to be requested by libpq.
- One complexity is that a statement like SELECT 1/col could error
- out mid-way through the result set.
-
- o Consider disallowing multiple queries in PQexec() as an
- additional barrier to SQL injection attacks
-
- http://archives.postgresql.org/pgsql-hackers/2007-01/msg00184.php
-
- o Add PQexecf() that allows complex parameter substitution
-
- http://archives.postgresql.org/pgsql-hackers/2007-03/msg01803.php
-
- o Add SQLSTATE severity to PGconn return status
-
- http://archives.postgresql.org/pgsql-interfaces/2007-11/msg00015.php
-
-
-Triggers
-========
-
-* Add deferred trigger queue file
-
- Right now all deferred trigger information is stored in backend
- memory. This could exhaust memory for very large trigger queues.
- This item involves dumping large queues into files.
-
-* Allow triggers to be disabled in only the current session.
-
- This is currently possible by starting a multi-statement transaction,
- modifying the system tables, performing the desired SQL, restoring the
- system tables, and committing the transaction. ALTER TABLE ...
- TRIGGER requires a table lock so it is not ideal for this usage.
-
-* With disabled triggers, allow pg_dump to use ALTER TABLE ADD FOREIGN KEY
-
- If the dump is known to be valid, allow foreign keys to be added
- without revalidating the data.
-
-* Allow statement-level triggers to access modified rows
-* Support triggers on columns
-
- http://archives.postgresql.org/pgsql-patches/2005-07/msg00107.php
-
-* Allow AFTER triggers on system tables
-
- System tables are modified in many places in the backend without going
- through the executor and therefore not causing triggers to fire. To
- complete this item, the functions that modify system tables will have
- to fire triggers.
-
-* Tighten trigger permission checks
-
- http://archives.postgresql.org/pgsql-hackers/2006-12/msg00564.php
-
-* Allow BEFORE INSERT triggers on views
-
- http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php
-
-* -Add ability to trigger on TRUNCATE
-
-
-
-Indexes
-=======
-
-* Add UNIQUE capability to non-btree indexes
-* Prevent index uniqueness checks when UPDATE does not modify the column
-
- Uniqueness (index) checks are done when updating a column even if the
- column is not modified by the UPDATE.
-
-* Allow the creation of on-disk bitmap indexes which can be quickly
- combined with other bitmap indexes
-
- Such indexes could be more compact if there are only a few distinct values.
- Such indexes can also be compressed. Keeping such indexes updated can be
- costly.
-
- http://archives.postgresql.org/pgsql-patches/2005-07/msg00512.php
- http://archives.postgresql.org/pgsql-hackers/2006-12/msg01107.php
- http://archives.postgresql.org/pgsql-hackers/2007-03/msg00265.php
- http://archives.postgresql.org/pgsql-hackers/2007-03/msg01214.php
- http://archives.postgresql.org/pgsql-patches/2007-05/msg00013.php
- http://archives.postgresql.org/pgsql-hackers/2007-07/msg00741.php
-
-* Allow accurate statistics to be collected on indexes with more than
- one column or expression indexes, perhaps using per-index statistics
-
- http://archives.postgresql.org/pgsql-performance/2006-10/msg00222.php
- http://archives.postgresql.org/pgsql-hackers/2007-03/msg01131.php
-
-* Consider increasing the number of default statistics target, and
- reduce statistics target overhead
-
- Also consider having a larger statistics target for indexed columns
- and expression indexes
- http://archives.postgresql.org/pgsql-general/2007-05/msg01228.php
- http://archives.postgresql.org/pgsql-general/2007-06/msg00542.php
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg01066.php
-
-* Consider compressing indexes by storing key values duplicated in
- several rows as a single index entry
-
- http://archives.postgresql.org/pgsql-hackers/2006-12/msg00341.php
- http://archives.postgresql.org/pgsql-hackers/2007-02/msg01264.php
- http://archives.postgresql.org/pgsql-hackers/2007-03/msg00465.php
- http://archives.postgresql.org/pgsql-patches/2007-03/msg00163.php
- http://archives.postgresql.org/pgsql-hackers/2007-08/msg00014.php
- http://archives.postgresql.org/pgsql-hackers/2007-08/msg00487.php
-
-* Add REINDEX CONCURRENTLY, like CREATE INDEX CONCURRENTLY
-
- This is difficult because you must upgrade to an exclusive table lock
- to replace the existing index file. CREATE INDEX CONCURRENTLY does not
- have this complication. This would allow index compaction without
- downtime.
- http://archives.postgresql.org/pgsql-performance/2007-08/msg00289.php
-
-* Allow multiple indexes to be created concurrently, ideally via a
- single heap scan, and have pg_restore use it
-
-* Consider sorting entries before inserting into btree index
-
- http://archives.postgresql.org/pgsql-general/2008-01/msg01010.php
-
-* Allow index scans to return matching index keys
-
- http://archives.postgresql.org/pgsql-hackers/2007-03/msg01079.php
-
-
-* Inheritance
-
- o Allow inherited tables to inherit indexes, UNIQUE constraints,
- and primary/foreign keys
- o Honor UNIQUE INDEX on base column in INSERTs/UPDATEs
- on inherited table, e.g. INSERT INTO inherit_table
- (unique_index_col) VALUES (dup) should fail
-
- The main difficulty with this item is the problem of
- creating an index that can span multiple tables.
-
- o Allow SELECT ... FOR UPDATE on inherited tables
- o Require all CHECK constraints to be inherited
-
- http://archives.postgresql.org/pgsql-bugs/2007-04/msg00026.php
-
- o Add checks to prevent a CREATE RULE views on inherited tables
-
- http://archives.postgresql.org/pgsql-general/2008-02/msg01420.php
- http://archives.postgresql.org/pgsql-general/2008-03/msg00077.php
-
-
-* GIST
-
- o Add more GIST index support for geometric data types
- o Allow GIST indexes to create certain complex index types, like
- digital trees (see Aoki)
-
-
-* Hash
-
- http://archives.postgresql.org/pgsql-hackers/2007-09/msg00051.php
-
- o Pack hash index buckets onto disk pages more efficiently
-
- Currently only one hash bucket can be stored on a page. Ideally
- several hash buckets could be stored on a single page and greater
- granularity used for the hash algorithm.
-
- http://archives.postgresql.org/pgsql-hackers/2004-06/msg00168.php
-
- o Consider sorting hash buckets so entries can be found using a
- binary search, rather than a linear scan
- o In hash indexes, consider storing the hash value with or instead
- of the key itself
- o Add WAL logging for crash recovery
- o Allow multi-column hash indexes
- o -During index creation, pre-sort the tuples to improve build speed
-
-
-
-Fsync
-=====
-
-* Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
-
- Ideally this requires a separate test program that can be run
- at initdb time or optionally later. Consider O_SYNC when
- O_DIRECT exists.
-
-* Add program to test if fsync has a delay compared to non-fsync
-* Consider sorting writes during checkpoint
-
- http://archives.postgresql.org/pgsql-hackers/2007-06/msg00541.php
-
-
-
-Cache Usage
-===========
-
-* Speed up COUNT(*)
-
- We could use a fixed row count and a +/- count to follow MVCC
- visibility rules, or a single cached value could be used and
- invalidated if anyone modifies the table. Another idea is to
- get a count directly from a unique index, but for this to be
- faster than a sequential scan it must avoid access to the heap
- to obtain tuple visibility information.
-
-* Provide a way to calculate an "estimated COUNT(*)"
-
- Perhaps by using the optimizer's cardinality estimates or random
- sampling.
-
- http://archives.postgresql.org/pgsql-hackers/2005-11/msg00943.php
-
-* Allow data to be pulled directly from indexes
-
- Currently indexes do not have enough tuple visibility information
- to allow data to be pulled from the index without also accessing
- the heap. One way to allow this is to set a bit on index tuples
- to indicate if a tuple is currently visible to all transactions
- when the first valid heap lookup happens. This bit would have to
- be cleared when a heap tuple is expired.
-
- Another idea is to maintain a bitmap of heap pages where all rows
- are visible to all backends, and allow index lookups to reference
- that bitmap to avoid heap lookups, perhaps the same bitmap we might
- add someday to determine which heap pages need vacuuming. Frequently
- accessed bitmaps would have to be stored in shared memory. One 8k
- page of bitmaps could track 512MB of heap pages.
-
- A third idea would be for a heap scan to check if all rows are visible
- and if so set a per-table flag which can be checked by index scans.
- Any change to the table would have to clear the flag. To detect
- changes during the heap scan a counter could be set at the start and
- checked at the end --- if it is the same, the table has not been
- modified --- any table change would increment the counter.
-
- http://archives.postgresql.org/pgsql-patches/2007-10/msg00166.php
- http://archives.postgresql.org/pgsql-patches/2008-01/msg00049.php
-
-* Consider automatic caching of statements at various levels:
-
- o Parsed query tree
- o Query execute plan
- o Query results
- http://archives.postgresql.org/pgsql-hackers/2008-04/msg00823.php
-
-* Consider increasing internal areas when shared buffers is increased
-
- http://archives.postgresql.org/pgsql-hackers/2005-10/msg01419.php
-
-* Consider decreasing the amount of memory used by PrivateRefCount
-
- http://archives.postgresql.org/pgsql-hackers/2006-11/msg00797.php
- http://archives.postgresql.org/pgsql-hackers/2007-01/msg00752.php
-
-* Consider allowing higher priority queries to have referenced buffer
- cache pages stay in memory longer
-
- http://archives.postgresql.org/pgsql-hackers/2007-11/msg00562.php
-
-
-
-Vacuum
-======
-
-* Improve speed with indexes
-
- For large table adjustments during VACUUM FULL, it is faster to cluster
- or reindex rather than update the index. Also, index updates can bloat
- the index.
-
- http://archives.postgresql.org/pgsql-hackers/2007-03/msg00024.php
- http://archives.postgresql.org/pgsql-performance/2007-05/msg00296.php
- http://archives.postgresql.org/pgsql-hackers/2007-08/msg00307.php
-
-* Auto-fill the free space map by scanning the buffer cache or by
- checking pages written by the background writer
-
- http://archives.postgresql.org/pgsql-hackers/2006-02/msg01125.php
- http://archives.postgresql.org/pgsql-hackers/2006-03/msg00011.php
-
-* Create a bitmap of pages that need vacuuming
-
- Instead of sequentially scanning the entire table, have the background
- writer or some other process record pages that have expired rows, then
- VACUUM can look at just those pages rather than the entire table. In
- the event of a system crash, the bitmap would probably be invalidated.
- One complexity is that index entries still have to be vacuumed, and
- doing this without an index scan (by using the heap values to find the
- index entry) might be slow and unreliable, especially for user-defined
- index functions.
-
- http://archives.postgresql.org/pgsql-hackers/2006-12/msg01188.php
- http://archives.postgresql.org/pgsql-hackers/2007-01/msg00121.php
- http://archives.postgresql.org/pgsql-patches/2007-03/msg00508.php
- http://archives.postgresql.org/pgsql-patches/2007-04/msg00347.php
- http://archives.postgresql.org/pgsql-hackers/2007-11/msg00156.php
- http://archives.postgresql.org/pgsql-hackers/2008-03/msg00546.php
- http://archives.postgresql.org/pgsql-hackers/2008-04/msg00416.php
-
-* Allow FSM to return free space toward the beginning of the heap file,
- in hopes that empty pages at the end can be truncated by VACUUM
-* Allow FSM page return free space based on table clustering, to assist
- in maintaining clustering?
-* Improve dead row detection during multi-statement transactions usage
-
- http://archives.postgresql.org/pgsql-patches/2007-03/msg00358.php
-
-* Consider a more compact data representation for dead tuples
-
- http://archives.postgresql.org/pgsql-patches/2007-05/msg00143.php
-
-
-* Auto-vacuum
-
- o %Issue log message to suggest VACUUM FULL if a table is nearly
- empty?
- o Improve control of auto-vacuum
-
- http://archives.postgresql.org/pgsql-hackers/2006-12/msg00876.php
-
- o Prevent long-lived temporary tables from causing frozen-xid
- advancement starvation
-
- The problem is that autovacuum cannot vacuum them to set frozen xids;
- only the session that created them can do that.
- http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php
-
- o Store per-table autovacuum settings in pg_class.reloptions.
-
- http://archives.postgresql.org/pgsql-hackers/2007-02/msg01440.php
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg00724.php
-
- o Prevent autovacuum from running if an old transaction is still
- running from the last vacuum
-
- http://archives.postgresql.org/pgsql-hackers/2007-11/msg00899.php
-
-
-
-Locking
-=======
-
-* Fix priority ordering of read and write light-weight locks (Neil)
-
- http://archives.postgresql.org/pgsql-hackers/2004-11/msg00893.php
- http://archives.postgresql.org/pgsql-hackers/2004-11/msg00905.php
-
-* Fix problem when multiple subtransactions of the same outer transaction
- hold different types of locks, and one subtransaction aborts
-
- http://archives.postgresql.org/pgsql-hackers/2006-11/msg01011.php
- http://archives.postgresql.org/pgsql-hackers/2006-12/msg00001.php
- http://archives.postgresql.org/pgsql-hackers/2007-02/msg00435.php
- http://archives.postgresql.org/pgsql-hackers/2007-05/msg00773.php
-
-* Allow UPDATEs on only non-referential integrity columns not to conflict
- with referential integrity locks
-
- http://archives.postgresql.org/pgsql-hackers/2007-02/msg00073.php
-
-* Add idle_in_transaction_timeout GUC so locks are not held for long
- periods of time
-
-* Improve deadlock detection when a page cleaning lock conflicts
- with a shared buffer that is pinned
-
- http://archives.postgresql.org/pgsql-bugs/2008-01/msg00138.php
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg00873.php
- http://archives.postgresql.org/pgsql-committers/2008-01/msg00365.php
-
-* Detect deadlocks involving LockBufferForCleanup()
-
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg00873.php
-
-
-
-Startup Time Improvements
-=========================
-
-* Experiment with multi-threaded backend for backend creation [thread]
-
- This would prevent the overhead associated with process creation. Most
- operating systems have trivial process creation time compared to
- database startup overhead, but a few operating systems (Win32,
- Solaris) might benefit from threading. Also explore the idea of
- a single session using multiple threads to execute a statement faster.
-
-
-
-Write-Ahead Log
-===============
-
-* Eliminate need to write full pages to WAL before page modification [wal]
-
- Currently, to protect against partial disk page writes, we write
- full page images to WAL before they are modified so we can correct any
- partial page writes during recovery. These pages can also be
- eliminated from point-in-time archive files.
-
- o When off, write CRC to WAL and check file system blocks
- on recovery
-
- If CRC check fails during recovery, remember the page in case
- a later CRC for that page properly matches.
-
- o Write full pages during file system write and not when
- the page is modified in the buffer cache
-
- This allows most full page writes to happen in the background
- writer. It might cause problems for applying WAL on recovery
- into a partially-written page, but later the full page will be
- replaced from WAL.
-
-* Allow WAL traffic to be streamed to another server for stand-by
- replication
-* Reduce WAL traffic so only modified values are written rather than
- entire rows
-
- http://archives.postgresql.org/pgsql-hackers/2007-03/msg01589.php
-
-* Allow WAL information to recover corrupted pg_controldata
-
- http://archives.postgresql.org/pgsql-patches/2006-06/msg00025.php
-
-* Find a way to reduce rotational delay when repeatedly writing
- last WAL page
-
- Currently fsync of WAL requires the disk platter to perform a full
- rotation to fsync again. One idea is to write the WAL to different
- offsets that might reduce the rotational delay.
-
-* Allow WAL logging to be turned off for a table, but the table
- might be dropped or truncated during crash recovery [walcontrol]
-
- Allow tables to bypass WAL writes and just fsync() dirty pages on
- commit. This should be implemented using ALTER TABLE, e.g. ALTER
- TABLE PERSISTENCE [ DROP | TRUNCATE | DEFAULT ]. Tables using
- non-default logging should not use referential integrity with
- default-logging tables. A table without dirty buffers during a
- crash could perhaps avoid the drop/truncate.
-
-* Allow WAL logging to be turned off for a table, but the table would
- avoid being truncated/dropped [walcontrol]
-
- To do this, only a single writer can modify the table, and writes
- must happen only on new pages so the new pages can be removed during
- crash recovery. Readers can continue accessing the table. Such
- tables probably cannot have indexes. One complexity is the handling
- of indexes on TOAST tables.
-
-* Speed WAL recovery by allowing more than one page to be prefetched
-
- This should be done utilizing the same infrastructure used for
- prefetching in general to avoid introducing complex error-prone code
- in WAL replay.
- http://archives.postgresql.org/pgsql-general/2007-12/msg00683.php
- http://archives.postgresql.org/pgsql-hackers/2007-12/msg00497.php
- http://archives.postgresql.org/pgsql-hackers/2008-02/msg01279.php
-
-* Improve WAL concurrency by increasing lock granularity
-
- http://archives.postgresql.org/pgsql-hackers/2008-02/msg00556.php
-
-* Be more aggressive about creating WAL files
-
- http://archives.postgresql.org/pgsql-hackers/2007-10/msg01325.php
- http://archives.postgresql.org/pgsql-hackers/2004-07/msg01075.php
- http://archives.postgresql.org/pgsql-hackers/2005-04/msg00556.php
-
-* Have resource managers report the duration of their status changes
-
- http://archives.postgresql.org/pgsql-hackers/2007-10/msg01468.php
-
-* Move pgfoundry's xlogdump to /contrib and have it rely more closely
- on the WAL backend code
-
- http://archives.postgresql.org/pgsql-hackers/2007-11/msg00035.php
-
-
-
-
-Optimizer / Executor
-====================
-
-* Improve selectivity functions for geometric operators
-* Precompile SQL functions to avoid overhead
-* Create utility to compute accurate random_page_cost value
-* Improve ability to display optimizer analysis using OPTIMIZER_DEBUG
-* Have EXPLAIN ANALYZE issue NOTICE messages when the estimated and
- actual row counts differ by a specified percentage
-* Improve how ANALYZE computes in-doubt tuples
-
- http://archives.postgresql.org/pgsql-hackers/2007-11/msg00771.php
-
-* Consider using hash buckets to do DISTINCT, rather than sorting
-
- This would be beneficial when there are few distinct values. This is
- already used by GROUP BY.
-
-* Log statements where the optimizer row estimates were dramatically
- different from the number of rows actually found?
-* Consider compressed annealing to search for query plans
-
- This might replace GEQO, http://sixdemonbag.org/Djinni.
-
-* Improve merge join performance by allowing mark/restore of
- tuple sources
-
- http://archives.postgresql.org/pgsql-hackers/2007-01/msg00096.php
-
-* Consider using a hash for joining to a large IN (VALUES ...) list
-
- http://archives.postgresql.org/pgsql-hackers/2007-05/msg00450.php
-
-
-
-Background Writer
-=================
-
-* Consider having the background writer update the transaction status
- hint bits before writing out the page
-
- Implementing this requires the background writer to have access to system
- catalogs and the transaction status log.
-
-* Consider adding buffers the background writer finds reusable to the
- free list
-
- http://archives.postgresql.org/pgsql-hackers/2007-04/msg00781.php
-
-* Automatically tune bgwriter_delay based on activity rather then using a
- fixed interval
-
- http://archives.postgresql.org/pgsql-hackers/2007-04/msg00781.php
-
-* Consider wither increasing BM_MAX_USAGE_COUNT improves performance
-
- http://archives.postgresql.org/pgsql-hackers/2007-06/msg01007.php
-
-* Test to see if calling PreallocXlogFiles() from the background writer
- will help with WAL segment creation latency
-
- http://archives.postgresql.org/pgsql-patches/2007-06/msg00340.php
-
-
-
-Miscellaneous Performance
-=========================
-
-* Do async I/O for faster random read-ahead of data
-
- Async I/O allows multiple I/O requests to be sent to the disk with
- results coming back asynchronously.
-
- http://archives.postgresql.org/pgsql-hackers/2006-10/msg00820.php
- http://archives.postgresql.org/pgsql-performance/2007-09/msg00255.php
- http://archives.postgresql.org/pgsql-hackers/2007-12/msg00027.php
- http://archives.postgresql.org/pgsql-patches/2008-01/msg00170.php
-
-* Use mmap() rather than SYSV shared memory or to write WAL files?
-
- This would remove the requirement for SYSV SHM but would introduce
- portability issues. Anonymous mmap (or mmap to /dev/zero) is required
- to prevent I/O overhead.
-
-* Consider mmap()'ing files into a backend?
-
- Doing I/O to large tables would consume a lot of address space or
- require frequent mapping/unmapping. Extending the file also causes
- mapping problems that might require mapping only individual pages,
- leading to thousands of mappings. Another problem is that there is no
- way to _prevent_ I/O to disk from the dirty shared buffers so changes
- could hit disk before WAL is written.
-
-* Add a script to ask system configuration questions and tune postgresql.conf
-* Consider ways of storing rows more compactly on disk
-
- o Reduce the row header size?
- o Consider reducing on-disk varlena length from four bytes to
- two because a heap row cannot be more than 64k in length
-
-* Consider increasing NUM_CLOG_BUFFERS
-
- http://archives.postgresql.org/pgsql-hackers/2007-08/msg00030.php
- http://archives.postgresql.org/pgsql-performance/2007-08/msg00024.php
-
-* Consider transaction start/end performance improvements
-
- http://archives.postgresql.org/pgsql-hackers/2007-07/msg00948.php
- http://archives.postgresql.org/pgsql-hackers/2008-03/msg00361.php
-
-* Allow user configuration of TOAST thresholds
-
- http://archives.postgresql.org/pgsql-hackers/2007-02/msg00213.php
- http://archives.postgresql.org/pgsql-hackers/2007-08/msg00082.php
-
-* Allow configuration of backend priorities via the operating system
-
- Though backend priorities make priority inversion during lock
- waits possible, research shows that this is not a huge problem.
-
- http://archives.postgresql.org/pgsql-general/2007-02/msg00493.php
-
-* Experiment with multi-threaded backend better I/O utilization
-
- This would allow a single query to make use of multiple I/O channels
- simultaneously. One idea is to create a background reader that can
- pre-fetch sequential and index scan pages needed by other backends.
- This could be expanded to allow concurrent reads from multiple devices
- in a partitioned table.
-
-* Experiment with multi-threaded backend better CPU utilization
-
- This would allow several CPUs to be used for a single query, such as
- for sorting or query execution.
-
-* Consider increasing the minimum allowed number of shared buffers
-
- http://archives.postgresql.org/pgsql-bugs/2008-02/msg00157.php
-
-* Expire published xmin for read-only and idle transactions
-
- http://archives.postgresql.org/pgsql-hackers/2007-09/msg00343.php
-
-* Consider if CommandCounterIncrement() can avoid its
- AcceptInvalidationMessages() call
-
- http://archives.postgresql.org/pgsql-committers/2007-11/msg00585.php
-
-* Improve performance of shared invalidation queue for multiple CPUs
-
- http://archives.postgresql.org/pgsql-performance/2008-01/msg00023.php
-
-* Consider Cartesian joins when both relations are needed to form an
- indexscan qualification for a third relation
-
- http://archives.postgresql.org/pgsql-performance/2007-12/msg00090.php
-
-* Consider not storing a NULL bitmap on disk if all the NULLs are
- trailing
-
- http://archives.postgresql.org/pgsql-hackers/2007-12/msg00624.php
- http://archives.postgresql.org/pgsql-patches/2007-12/msg00109.php
-
-* Sort large UPDATE/DELETEs so it is done in heap order
-
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg01119.php
-
-* -Avoid tuple some tuple copying in sort routines
-* SMP scalability improvements
-
- http://archives.postgresql.org/pgsql-hackers/2007-07/msg00439.php
- http://archives.postgresql.org/pgsql-hackers/2007-09/msg00206.php
- http://archives.postgresql.org/pgsql-hackers/2008-03/msg00361.php
-
-* Research reducing deTOASTing in more places
-
- http://archives.postgresql.org/pgsql-hackers/2007-09/msg00895.php
-
-* Consider being smarter about memory and external files used during
- sorts
-
- http://archives.postgresql.org/pgsql-hackers/2007-11/msg01101.php
- http://archives.postgresql.org/pgsql-hackers/2007-12/msg00045.php
-
-* Allow one transaction to see tuples using the snapshot of another
- transaction
-
- This would assist multiple backends in working together.
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg00400.php
-
-
-Source Code
-===========
-
-* Add use of 'const' for variables in source tree
-* Move some things from contrib into main tree
-* %Remove warnings created by -Wcast-align
-* Move platform-specific ps status display info from ps_status.c to ports
-* Add optional CRC checksum to heap and index pages
-* Improve documentation to build only interfaces (Marc)
-* Remove or relicense modules that are not under the BSD license, if possible
-* Acquire lock on a relation before building a relcache entry for it
-* Allow cross-compiling by generating the zic database on the target system
-* Improve NLS maintenance of libpgport messages linked onto applications
-* Clean up casting in contrib/isn
-
- http://archives.postgresql.org/pgsql-hackers/2006-11/msg00245.php
-
-* Use UTF8 encoding for NLS messages so all server encodings can
- read them properly
-* Update Bonjour to work with newer cross-platform SDK
-
- http://archives.postgresql.org/pgsql-hackers/2006-09/msg02238.php
- http://archives.postgresql.org/pgsql-patches/2006-10/msg00048.php
-
-* Consider detoasting keys before sorting
-* Consider GnuTLS if OpenSSL license becomes a problem
-
- http://archives.postgresql.org/pgsql-patches/2006-05/msg00040.php
- http://archives.postgresql.org/pgsql-hackers/2006-12/msg01213.php
-
-* Consider changing documentation format from SGML to XML
-
- http://archives.postgresql.org/pgsql-docs/2006-12/msg00152.php
-
-* Consider making NAMEDATALEN more configurable in future releases
-* Update our code to handle 64-bit timezone files to match the zic
- source code, which now uses them
-* Have configure choose integer datetimes by default
-
- http://archives.postgresql.org/pgsql-patches/2007-05/msg00046.php
-
-* Support scoped IPv6 addresses
-
- http://archives.postgresql.org/pgsql-bugs/2007-05/msg00111.php
-
-* Consider allowing 64-bit integers and floats to be passed by value on
- 64-bit platforms
-
- Also change 32-bit floats (float4) to be passed by value at the same
- time.
-
-* Research use of signals and sleep wake ups
-
- http://archives.postgresql.org/pgsql-hackers/2007-07/msg00003.php
-
-* Add automated check for invalid C++ source code constructs
-
- http://archives.postgresql.org/pgsql-patches/2007-07/msg00056.php
-
-* Consider simplifying how memory context resets handle child contexts
-
- http://archives.postgresql.org/pgsql-patches/2007-08/msg00067.php
-
-* Remove use of MAKE_PTR and MAKE_OFFSET macros
-
- http://archives.postgresql.org/pgsql-general/2007-08/msg01510.php
-
-* Convert single quotes to apostrophes in the PDF documentation
-
- http://archives.postgresql.org/pgsql-docs/2007-12/msg00059.php
-
-* Create three versions of libpgport to simplify client code
-
- http://archives.postgresql.org/pgsql-hackers/2007-10/msg00154.php
-
-* Remove old-style routines for manipulating tuples
-
- http://archives.postgresql.org/pgsql-hackers/2007-10/msg00851.php
-
-* Improve detection of shared memory segments being used by other
- FreeBSD jails
-
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg00656.php
-
-
-
-* Win32
-
- o Remove configure.in check for link failure when cause is found
- o Remove readdir() errno patch when runtime/mingwex/dirent.c rev
- 1.4 is released
- o Remove psql newline patch when we find out why mingw outputs an
- extra newline
- o Allow psql to use readline once non-US code pages work with
- backslashes
- o Fix problem with shared memory on the Win32 Terminal Server
- o Diagnose problem where shared memory can sometimes not be
- attached by postmaster children
-
- http://archives.postgresql.org/pgsql-general/2007-08/msg01377.php
-
- o Improve signal handling
-
- http://archives.postgresql.org/pgsql-patches/2005-06/msg00027.php
-
- o Convert MSVC build system to remove most batch files
-
- http://archives.postgresql.org/pgsql-hackers/2007-08/msg00961.php
-
- o Prevent SSL from sending network packets to avoid interference
- with Win32 signal emulation
-
- http://archives.postgresql.org/pgsql-hackers/2007-12/msg00455.php
-
- o Support pgxs when using MSVC
-
- o Fix MSVC NLS support, like for to_char()
-
- http://archives.postgresql.org/pgsql-hackers/2008-02/msg00485.php
- http://archives.postgresql.org/pgsql-patches/2008-02/msg00038.php
-
- o Find a correct rint() substitute on Windows
-
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg00808.php
-
-
-
-* Wire Protocol Changes
-
- o Allow dynamic character set handling
- o Add decoded type, length, precision
- o Use compression?
- o Update clients to use data types, typmod, schema.table.column names
- of result sets using new statement protocol
-
-
-Exotic Features
-===============
-
-* Add pre-parsing phase that converts non-ISO syntax to supported
- syntax
-
- This could allow SQL written for other databases to run without
- modification.
-
-* Allow plug-in modules to emulate features from other databases
-* Add features of Oracle-style packages (Pavel)
-
- A package would be a schema with session-local variables,
- public/private functions, and initialization functions. It
- is also possible to implement these capabilities
- in any schema and not use a separate "packages"
- syntax at all.
-
- http://archives.postgresql.org/pgsql-hackers/2006-08/msg00384.php
-
-* Consider allowing control of upper/lower case folding of unquoted
- identifiers
-
- http://archives.postgresql.org/pgsql-hackers/2004-04/msg00818.php
- http://archives.postgresql.org/pgsql-hackers/2006-10/msg01527.php
- http://archives.postgresql.org/pgsql-hackers/2008-03/msg00849.php
-
-* Add autonomous transactions
-
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php
-
-
-
-Features We Do _Not_ Want
-=========================
-
-* All backends running as threads in a single process (not wanted)
-
- This eliminates the process protection we get from the current setup.
- Thread creation is usually the same overhead as process creation on
- modern systems, so it seems unwise to use a pure threaded model.
-
-* Optimizer hints (not wanted)
-
- Optimizer hints are used to work around problems in the optimizer. We
- would rather have the problems reported and fixed.
-
- http://archives.postgresql.org/pgsql-hackers/2006-08/msg00506.php
- http://archives.postgresql.org/pgsql-hackers/2006-10/msg00517.php
- http://archives.postgresql.org/pgsql-hackers/2006-10/msg00663.php
-
-
- Because we support postfix operators, it isn't possible to make AS
- optional and continue to use bison.
- http://archives.postgresql.org/pgsql-hackers/2003-04/msg00436.php
-
- http://archives.postgresql.org/pgsql-sql/2006-08/msg00164.php
-
-* Embedded server (not wanted)
-
- While PostgreSQL clients runs fine in limited-resource environments, the
- server requires multiple processes and a stable pool of resources to
- run reliabily and efficiently. Stripping down the PostgreSQL server
- to run in the same process address space as the client application
- would add too much complexity and failure cases.
+ http://wiki.postgresql.org/wiki/Todo