Todo
From PostgreSQL Wiki
This list contains all known PostgreSQL bugs and feature requests. If you would like to work on an item, please read the Developer FAQ first. There is also a development information page.
-
- marks ordinary, incomplete items.
- [E] - marks items that are easier to implement.
-
[D] - marks changes that are done, and will appear in the next release.
For help on editing this list, please see Talk:Todo.
Administration
- [D]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
- 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.
- [D]Add function to report the time of the most recent server reload
- 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.
- 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 variables to appear in pg_settings()
- Allow custom variable classes that can restrict who can set the values
- Implement the SQL standard mechanism whereby REVOKE ROLE revokes only the privilege granted by the invoking role, and not those granted by other roles
- Allow SSL authentication/encryption over unix domain sockets
- Allow SSL key file permission checks to be optionally disabled when sharing SSL keys with other applications
- Allow SSL 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.
- Add 'hostgss' pg_hba.conf option to allow GSS link-level encryption
- Improve server security options
- Prevent query cancel packets from being replayed by an attacker, especially when using SSL
Configuration files
- 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.
- Allow postgresql.conf file values to be changed via an SQL API, perhaps using SET GLOBAL
- Allow the server to be stopped/restarted via an SQL API
- [D]Issue a warning if a change-on-restart-only postgresql.conf value is modified and the server config files are reloaded
- Consider normalizing fractions in postgresql.conf, perhaps using '%'
- Allow Kerberos to disable stripping of realms so we can check the username@realm against multiple realms
- Add functions to check correctness of configuration files before they are loaded "live"
- Improve LDAP authentication configuration options
- Add external tool to auto-tune some postgresql.conf parameters
Tablespaces
- 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.
- 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.
- Allow WAL replay of CREATE TABLESPACE to work when the directory structure on the recovery computer is different from the original
- Allow per-tablespace quotas
Statistics Collector
- Reduce the frequency that the statistics file is written
- [D]Allow statistics file location to be user-configured
- 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?
- Allow statistics last vacuum/analyze execution times to be displayed without requiring stats_row_level to be enabled
- Clear table counters on TRUNCATE
Point-In-Time Recovery (PITR)
- Allow a warm standby system to also allow read-only statements
- [E]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.
- Allow recovery.conf to support the same syntax as postgresql.conf, including quoting
- [D]Fix server restart problem when the server was shutdown during a PITR backup
- Recreate pg_xlog/archive_status/ if it doesn't exist after restoring from a PITR backup
- Reduce PITR WAL file size by removing full page writes and by removing trailing bytes to improve compression
Data Types
- Change NUMERIC to enforce the maximum precision
- Reduce storage space for small NUMERICs
- Fix data types where equality comparison isn't intuitive, e.g. box
- Add support for public SYNONYMs
- Fix CREATE CAST on DOMAINs
- Allow domains to be cast
- Add support for SQL-standard GENERATED/IDENTITY columns
- Improve XML support
- Consider placing all sequences in a single table, or create a system view
- Allow the UUID type to accept non-standard formats
- Consider a special data type for regular expressions
- Reduce BIT data type overhead using short varlena headers
- Allow xml arrays to be cast to other data types
- [D]Simplify integer cross-data-type operators
- Allow adding/renaming/removing enumerated values to an existing enumerated data type
Dates and Times
- Allow infinite dates and intervals just like infinite timestamps
- Allow TIMESTAMP WITH TIME ZONE to store the original timezone information, either zone name or offset from UTC
- If the TIMESTAMP value is stored with a time zone name, interval computations should adjust based on the time zone rules.
- Fix SELECT '0.01 years'::interval, '0.01 months'::interval
- Add a GUC variable to allow output of interval values in ISO8601 format
- Have timestamp subtraction not call justify_hours()?
- 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.
- Fix interval display to support values exceeding 2^31 hours
- Add overflow checking to timestamp and interval arithmetic
- [D]Extend timezone code to allow 64-bit values so we can represent years beyond 2038
- [D]Use LC_TIME for localized weekday/month names, rather than LC_MESSAGES
- Add ISO INTERVAL handling
- [D]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.
- [D]Add support for year-month syntax, INTERVAL '50-6' YEAR TO MONTH
- Round or truncate values to the requested precision, e.g. INTERVAL '11 months' AS YEAR should return one or zero
- [E]Revise the src/timezone/tznames abbreviation files:
-
- to add missing abbreviations
- to find abbreviations that can be safely promoted to the Default list
- BUG #4377: casting result of timeofday() to timestamp fails in some timezones
Arrays
- Delay resolution of array expression's data type so assignment coercion can be performed on empty array expressions
- Add support for arrays of domains
- Allow single-byte header storage for array elements
Binary Data
- Improve vacuum of large objects, like contrib/vacuumlo?
- Add security checking for large objects
- Auto-delete large objects when referencing row is deleted
- contrib/lo offers this functionality.
- Allow read/write into TOAST values like large objects
- This requires the TOAST column to be stored EXTERNAL.
- Add API for 64-bit large object access
MONEY data type
- Add locale-aware MONEY type, and support multiple currencies
- 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
Text Search
- Allow dictionaries to change the token that is passed on to later dictionaries
- Consider a function-based API for '@@' searches
- Improve text search error messages
- Consider changing error to warning for strings larger than one megabyte
Functions
- Allow INET subnet tests using non-constants to be indexed
- Allow to_date() and to_timestamp() accept localized month names
- [D]Fix to_date()-related functions to consistently issue errors
- Add missing parameter handling in to_char()
- 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.
- to_char(INTERVAL '1 hour 5 minutes', 'MI') => 65
- to_char(INTERVAL '43 hours 20 minutes', 'MI' ) => 2600
- to_char(INTERVAL '43 hours 20 minutes', 'WK:DD:HR:MI') => 0:1:19:20
- to_char(INTERVAL '3 years 5 months','MM') => 41
- [D]Implement inlining of set-returning functions defined in SQL
- Allow SQL-language functions to return results from RETURNING queries
- 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
- Allow holdable cursors in SPI
- Tighten function permission checks
- Fix IS OF so it matches the ISO specification, and add documentation
- Add missing operators for geometric data types
- Some geometric types do not have the full suite of geometric operators, e.g. box @> point
- [D]Implement Boyer-Moore searching in strpos()
- Implement Boyer-Moore searching in LIKE queries
- 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.
- Reduce memory usage of aggregates in set returning functions
- [D]Add temporal versions of generate_series()
- Add array_accum() and array_to_set() functions for arrays
- The standards specify array_agg() and UNNEST.
- Fix /contrib/ltree operator
- Fix inconsistent precedence of =, >, and < compared to <>, >=, and <=
- Fix regular expression bug when using complex back-references
- Have /contrib/dblink reuse unnamed connections
- Add SQL-standard array_agg() and unnest() array functions
- Allow calling of a procedure outside a SELECT that can control the transaction state
- [D]Fix all set-returning system functions so they support a wildcard target list
- SELECT * FROM pg_get_keywords() works but SELECT * FROM pg_show_all_settings() does not.
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; Add CREATE COLLATE
-
Right now only one encoding is allowed per database.
- Re: Patch for collation using ICU
- FW: Win32 unicode vs ICU
- Re: FW: Win32 unicode vs ICU
- Proof of concept COLLATE support with patch
- For review: Initial support for COLLATE
- Proposed COLLATE implementation
- TODO item: locale per database patch (new iteration)
- Re: FW: Win32 unicode vs ICU
- Re: Fixed length data types issue
- http://archives.postgresql.org/pgsql-hackers/2008-07/msg00557.php
- Todo:Collate
- Todo:ICU
- Support multiple simultaneous character sets, per SQL:2008
- 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
- Fix ILIKE and regular expressions to handle case insensitivity properly in multibyte encodings
- Set client encoding based on the client operating system encoding
- Currently client_encoding is set in postgresql.conf, which defaults to the server encoding.
- 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 SQL:2008
- We can only auto-create rules for simple views. For more complex cases users will still have to write rules manually.
- 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
- 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.
- Improve ability to modify views via ALTER TABLE
- Enable creation of RETURNING rules on inherited tables that have dropped columns
SQL Commands
- Add CORRESPONDING BY to UNION/INTERSECT/EXCEPT
- Add ROLLUP, CUBE, GROUPING SETS options to GROUP BY
- [E]Allow SET CONSTRAINTS to be qualified by schema/table name
- [D]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.
- [E]Fix TRUNCATE ... RESTART IDENTITY so its effect on sequences is rolled back on transaction abort
- 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
- [D]Improve failure message when DROP DATABASE is used on a database that has prepared transactions
- Allow prepared transactions with temporary tables created and dropped in the same transaction, and when an ON COMMIT DELETE ROWS temporary table is accessed
- Add a GUC variable to warn about non-standard SQL usage in queries
- Add SQL-standard MERGE/REPLACE/UPSERT command
- MERGE is typically used to merge two tables. REPLACE or UPSERT command does UPDATE, or on failure, INSERT. This is similar to UPDATE, then for unmatched rows, INSERT. Whether concurrent access allows modifications which could cause row loss is implementation independent. 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.
- 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
- [D]Add SQL:2008 Common Table Expression (WITH [RECURSIVE]) clause to SELECT
- 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:2008 window functions
- Allow INSERT/UPDATE ... RETURNING inside a SELECT 'FROM' clause or target list
- [D]Increase locking when DROPing objects so dependent objects cannot get dropped while the DROP operation is happening
- [D]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
- Add comments on system tables/columns using the information in catalogs.sgml
- Ideally the information would be pulled from the SGML file automatically.
- [D]Improve reporting of UNION type mismatches
CREATE
- Allow CREATE TABLE AS to determine column lengths for complex expressions like SELECT col1
- Have WITH CONSTRAINTS also create constraint indexes
- 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
- Prevent concurrent CREATE TABLE table1 from sometimes returning a cryptic error message
- Add CREATE SCHEMA ... LIKE that copies a schema
- Add CREATE TABLE LIKE ... INCLUDING COMMENTS
- CREATE OR REPLACE FUNCTION might leave dependent objects depending on the function in inconsistent state