PostgreSQL TODO List

====================

Current maintainer:Bruce Momjian (pgman@candle.pha.pa.us)

Last updated:Wed Aug24 21:24:51 EDT 2005

Last updated:Fri Aug26 14:52:30 EDT 2005

The most recent version of this document can be viewed at

http://www.postgresql.org/docs/faqs.TODO.html.

* Allow administrators to safely terminate individual sessions either

via an SQL function or SIGTERM

Currently SIGTERM of a backend can lead to lock table corruption.

Lock table corruption following SIGTERM of an individual backend

has been reported in 8.0. A possible cause was fixed in 8.1, but

it is unknown whether other problems exist. This item mostly

requires additional testing rather than of writing any new code.

* -Prevent dropping user that still owns objects, or auto-drop the objects

* %Set proper permissions on non-system schemas during db creation

* Configuration files

o %Add "include file" functionality in postgresql.conf

o Allow postgresql.conf values to be set so they can not be changed

by the user

o %Allow commenting of variables in postgresql.conf to restore them

to defaults

Data Types

==========

* %Remove Money type, add money formatting for decimal type

* Improve the MONEY data type

Change the MONEY data type to use DECIMAL internally, with special

locale-aware output formatting.

* Change NUMERIC to enforce the maximum precision, and increase it

* Add NUMERIC division operator that doesn't round?

* Arrays

o Allow NULLs in arrays

o%Allow MIN()/MAX() on arrays

o-Allow MIN()/MAX() on arrays

o Delay resolution of array expression's data type so assignment

coercion can be performed on empty array expressions

o Modify array literal representation to handle array index lower bound

o-Modify array literal representation to handle array index lower bound

of other than one

* 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

Currently large objects entries do not have owners. Permissions can

only be set at the pg_largeobject table level.

/contrib/lo offers this functionality.

o Auto-delete large objects when referencing row is deleted

o Allow read/write into TOAST values like large objects

This requires the TOAST column to be stored EXTERNAL.

* Add the functionality for WITH CHECK OPTION clause of CREATE VIEW

* Allow NOTIFY in rules involving conditionals

*%Have views on temporary tables exist in the temporary namespace

* Allow temporary views on non-temporary tables

*%Allow RULE recompilation

*-Have views on temporary tables exist in the temporary namespace

*-Allow temporary views on non-temporary tables

* AllowVIEW/RULE recompilation when the underlying tables change

SQL Commands

* Add ROLLUP, CUBE, GROUPING SETS options to GROUP BY

* %Allow SET CONSTRAINTS to be qualified by schema/table name

* %Allow TRUNCATE ... CASCADE/RESTRICT

This is like DELETE CASCADE, but truncates.

* %Add a separate TRUNCATE permission

Currently only the owner can TRUNCATE a table because triggers are not

When enabled, this would allow errors in multi-statement transactions

to be automatically ignored.

* Make row-wise comparisons work per SQL spec

*%Make row-wise comparisons work per SQL spec

* Add RESET CONNECTION command to reset all session state

This would include resetting of all variables (RESET ALL), dropping of

o Use more reliable method for CREATE DATABASE to get a consistent

copy of db?

o Currently the system uses the operating system COPY command to

create a new database. Add ON COMMIT capability to CREATE TABLE AS

SELECT

o Add ON COMMIT capability to CREATE TABLE AS ... SELECT

* UPDATE

* ALTER

o %Have ALTER TABLE RENAME rename SERIAL sequence names

o%Add ALTER DOMAINTYPE

o Add ALTER DOMAINto modify the underlying data type

o %Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME

o %Allow ALTER TABLE to change constraint deferrability and actions

o %Disallow dropping of an inherited constraint

o -Allow objects to be moved to different schemas

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 child tables from altering constraints like CHECK that were

inherited from the parent table

o %Disallow dropping of an inherited constraint

o %Prevent child tables from altering or dropping constraints

like CHECK that were inherited from the parent table

* CLUSTER

Currently only constants are supported.

o -Change PL/PgSQL to use palloc() instead of malloc()

o Handle references to temporary tables that are created, destroyed,

then recreated during a session, and EXECUTE is not used

This requires the cached PL/PgSQL byte code to be invalidated when

an object referenced in the function is changed.

o Fix PL/pgSQL RENAME to work on variables other than OLD/NEW

o Allow function parameters to be passed by name,

get_employee_salary(emp_id => 12345, tax_year => 2001)

o Add Oracle-style packages

o Add table function support to pltcl, plperl, plpython?

o Allow PL/pgSQL to name columns by ordinal position, e.g. rec.(3)

o Add table function support to pltcl, plpython

o -Allow PL/pgSQL EXECUTE query_var INTO record_var;

o Add capability to create and call PROCEDURES

o Allow PL/pgSQL to handle %TYPE arrays, e.g. tab.col%TYPE[]

o Allow function argument names to be queries from PL/PgSQL

o Add MOVE to PL/pgSQL

o Pass arrays natively instead of as text between plperl and postgres

o Add support for polymorphic arguments and return types to plperl

o -Pass arrays natively instead of as text between plperl and postgres

o Add support for polymorphic arguments and return types to

languages other than PL/PgSQL

o Add support for OUT and INOUT parameters to languages other

than PL/PgSQL

Clients

=======

* Add a libpq function to support Parse/DescribeStatement capability

* Prevent libpq's PQfnumber() from lowercasing the column name?

* Allow libpq to access SQLSTATE so pg_ctl can test for connection failure

This would be used for checking if the server is up.

* Add PQescapeIdentifier() to libpq

* Have initdb set DateStyle based on locale?

* Have initdb setthe inputDateStyle (MDY or DMY) based on locale?

* Have pg_ctl look at PGHOST in case it is a socket directory?

* Add a schema option to createlang

* Allow pg_ctl to work properly with configuration files located outside

the PGDATA directory

o Stop dumping CASCADE on DROP TYPE commands in clean mode

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.

This is probably best done by combining pg_dump and pg_dumpall

into a single binary.

o Add pg_dumpall custom format dumps?

o %Add CSV output format

o Update pg_dump and psql to use the new COPY libpq API (Christopher)

o Remove unnecessary abstractions in pg_dump source code

o Remove unnecessary function pointer abstractions in pg_dump source

code

* ecpg

* Allow statement-level triggers to access modified rows

* Support triggers on columns (Greg Sabino Mullane)

* %Remove CREATE CONSTRAINT TRIGGER

This was used in older releases to dump referential integrity

constraints.

* Enforce referential integrity for system tables

* Allow AFTER triggers on system tables

* Flush cached query plans when the dependent objects change

* Track dependencies in function bodies and recompile/invalidate

This is particularly important for references to temporary tables

in PL/PgSQL because PL/PgSQL caches query plans. The only workaround

in PL/PgSQL is to use EXECUTE.

Exotic Features

===============

Uniqueness (index) checks are done when updating a column even if the

column is not modified by the UPDATE.

* Fetch heap pages matching index entries in sequential order

*-Fetch heap pages matching index entries in sequential order

Rather than randomly accessing heap pages based on index entries, mark

heap pages needing access in a bitmap and do the lookups in sequential

o Pack hash index buckets onto disk pages more efficiently

Currentlynoonly one hash bucket can be stored on a page. Ideally

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.

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

Fsync

=====

* Improve commit_delay handling to reduce 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.

* -Allow multiple blocks to be written to WAL with one write()

* %Add an option to sync() before fsync()'ing checkpoint files

* Add program to test if fsync has a delay compared to non-fsync

Larger local buffer cache sizes requires more efficient handling of

local cache lookups.

* Improve the background writer

*-Improve the background writer

Allow the background writer to more efficiently write dirty buffers

from the end of the LRU cache and use a clock sweep algorithm to

* Improve speed with indexes

For large table adjustements duringvacuum, it is faster toreindex

rather than update the index.

For large table adjustements duringVACUUM FULL, it is faster to

reindexrather than update the index.

* Reduce lock time by moving tuples with read lock, then write

lock and truncate table

* Reduce lock timeduring VACUUM FULLby moving tuples with read lock,

then writelock and truncate table

Moved tuples are invisible to other backends so they don't require a

write lock. However, the read lock promotion to write lock could lead

o -Move into the backend code

o Use free-space map information to guide refilling

o %Suggest VACUUM FULL if a table is nearly empty

o %Issue log message to suggest VACUUM FULL if a table is nearly

empty?

o Improve xid wraparound detection by recording per-table rather

than per-database

* Reduce WAL traffic so only modified values are written rather than

entire rows?

* Add WAL index reliability improvement to non-btree indexes

* Allow the pg_xlog directory location to be specified during initdb

with a symlink back to the /data location

* Allow WAL information to recover corrupted pg_controldata

This would be beneficial when there are few distinct values.

* ANALYZE should record a pg_statistic entry for an all-NULL column

*-ANALYZE should record a pg_statistic entry for an all-NULL column

* Log queries where the optimizer row estimates were dramatically

different from the number of rows actually found?

* Allow constraint_elimination to be automatically performed

This requires additional code to reduce the performance loss caused by

constraint elimination.

Miscellaneous Performance

* 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

* Remove memory/file descriptor freeing before ereport(ERROR)

*%Remove memory/file descriptor freeing before ereport(ERROR)

* Acquire lock on a relation before building a relcache entry for it

* Promote debug_query_string into a server-side function current_query()

* Allow the identifier length to be increased via a configure option

*%Promote debug_query_string into a server-side function current_query()

*%Allow the identifier length to be increased via a configure option

* Remove Win32 rename/unlink looping if unnecessary

* -Remove kerberos4 from source tree

* Allow cross-compiling by generating the zic database on the target system

is already possible to install into a directory that doesn't contain

spaces and then copy the install to a directory with spaces.

* Fix cross-compiling of time zone database via 'zic'

* Fix sgmltools so PDFs can be generated with bookmarks

* -Add C code on Unix to copy directories for use in creating new databases

* %Clean up compiler warnings (especially with gcc version 4)

backslashes

o Re-enable timezone output on log_line_prefix '%t' when a

shorter timezone string is available

o Improve dlerror() reporting string

o-Improve dlerror() reporting string

o Fix problem with shared memory on the Win32 Terminal Server

o Add support for Unicode

o%Add support for Unicode

To fix this, the data needs to be converted to/from UTF16/UTF8

so the Win32 wcscoll() can be used, and perhaps other functions