https://wiki.postgresql.org/api.php?action=feedcontributions&user=Robins&feedformat=atomPostgreSQL wiki - User contributions [en]2024-03-29T09:40:09ZUser contributionsMediaWiki 1.35.13https://wiki.postgresql.org/index.php?title=Visibility_Map_Problems&diff=36128Visibility Map Problems2021-06-04T05:48:51Z<p>Robins: Functions allows checking for TOAST tables too.</p>
<hr />
<div>= Introduction =<br />
<br />
PostgreSQL tables contain visibility maps (VM), which provide information about which blocks in the table are known to contain only tuples that are visible to all transactions. For such a block, index-only scans need not physically visit the block to confirm that a TID obtained from the index is valid. In PostgreSQL 9.6 and up, the visibility map file also contains data about which blocks are known to contain only frozen tuples.<br />
<br />
Corruption of a visibility map can lead to false results from index-only scans, that is returning rows that should not be returned because they have been deleted. In 9.6 and up, corruption can also lead to VACUUM bypassing blocks that need maintenance, which will ultimately result in data corruption. This page discusses ways to detect and fix such problems.<br />
<br />
= Known bugs causing VM corruption =<br />
<br />
PostgreSQL versions 9.6 (before 9.6.1), 9.5 (before 9.5.5), 9.4 (before 9.4.10), and 9.3 (before 9.3.15) contain a bug that causes failure to make adequate WAL log entries when a VM is truncated as part of truncating its table. A database crash-and-restart shortly after such an event can lead to corrupted VMs. In a replication environment, standby servers will receive incorrect WAL data causing them to create corrupted VMs locally, meaning that standbys are likely to have corrupted data even if the master is valid. However, the effects of this bug are limited to causing incorrect checksums to be generated for VM pages, so that no ill effects will be observed unless the database has checksum generation and checking enabled. If you see checksum complaints about pages in VM files, suspect this bug as the cause.<br />
<br />
In PostgreSQL version 9.6 (before 9.6.1), pg_upgrade contains a bug that causes it to generate incorrect VM data if it is running on big-endian hardware. (That excludes Intel-based machines, but many non-Intel architectures such as PPC and Sparc can run big-endian.) If you have used 9.6.0's pg_upgrade on such hardware, you should assume that all VM files in the resulting database are corrupt.<br />
<br />
In addition to these known bugs in PostgreSQL itself, operating-system bugs or hardware problems could result in corrupted VM files.<br />
<br />
= Detection of broken VM files =<br />
<br />
First it is necessary to detect which tables contain corrupt VMs. This analysis method makes use of the extension pg_visibility, so it needs to be enabled first:<br />
<br />
<source lang="sql"><br />
CREATE EXTENSION pg_visibility;<br />
</source><br />
<br />
Then run the following query, as superuser, to look for corrupted VMs within the current database:<br />
<br />
<source lang="sql"><br />
SELECT oid::regclass AS relname<br />
FROM pg_class<br />
WHERE relkind IN ('r', 'm', 't') AND (<br />
EXISTS (SELECT * FROM pg_check_visible(oid))<br />
OR EXISTS (SELECT * FROM pg_check_frozen(oid)));<br />
</source><br />
<br />
If this returns an empty list, the current database contains no problems. If not, the listed tables contain corrupt VM data that needs to be repaired. See below for more details. <br />
<br />
Be sure to repeat this test in each database of the cluster. In a replicated configuration, you'll need to check each standby individually, along with the master.<br />
<br />
In PostgreSQL releases before 9.6, the pg_visibility extension does not exist, so this direct test for bad data is not possible. Suspect VM corruption if a query that uses an index-only scan (as shown by EXPLAIN) gives wrong results, but turning off '''enable_indexonlyscan''' causes it to give correct results. Also, a checksum complaint about a block in a VM file is of course evidence of corruption.<br />
<br />
= Cleanup of broken VM files =<br />
<br />
When using the pg_visibility extension, it's sufficient to apply the pg_truncate_visibility_map() function to each table that is identified as having a problem. You can automate that by just calling it as part of the query given above:<br />
<br />
<source lang="sql"><br />
SELECT oid::regclass AS relname, pg_truncate_visibility_map(oid)<br />
FROM pg_class<br />
WHERE relkind IN ('r', 'm', 't') AND (<br />
EXISTS (SELECT * FROM pg_check_visible(oid))<br />
OR EXISTS (SELECT * FROM pg_check_frozen(oid)));<br />
</source><br />
<br />
Remember to do this in each database. Afterwards, you may wish to VACUUM each repaired table to rebuild up-to-date VM data. However, that is not urgent unless you are very reliant on high performance of index-only scans. Autovacuum can be expected to rebuild the VMs eventually, except perhaps those for nearly-read-only tables.<br />
<br />
In a replicated configuration, if you have found that any standby servers have corrupted VM files that are not also corrupt on the master, you will need to do pg_truncate_visibility_map() on those tables on the master, so that the truncation and subsequent rebuild propagate to the standby.<br />
<br />
If you are not using the pg_visibility extension, perhaps because you are on a pre-9.6 release, you can fix broken VM files simply by deleting them by hand while the server is stopped. The file name to delete can be determined by <br />
<br />
<source lang="sql"><br />
SELECT pg_relation_filepath('name of damaged table') || '_vm'<br />
</source><br />
<br />
See [[Free Space Map Problems]] for additional discussion --- this is much like cleanup of a broken FSM, except that the name of the file to delete ends in "vm" not "fsm".</div>Robinshttps://wiki.postgresql.org/index.php?title=Performance_Optimization&diff=30286Performance Optimization2017-06-02T03:13:04Z<p>Robins: /* General Setup and Optimization */ Old URL had stopped working. Got this via Wayback</p>
<hr />
<div>{{Languages}}<br />
<br />
== General Setup and Optimization ==<br />
* [[Tuning Your PostgreSQL Server]] by Greg Smith, Robert Treat, and Christopher Browne<br />
* [http://www.revsys.com/writings/postgresql-performance.html Performance Tuning PostgreSQL] by Frank Wiles<br />
* [http://www.pgcon.org/2008/schedule/events/104.en.html GUCs: A Three Hour Tour] by Josh Berkus. Also useful here is his [http://pgfoundry.org/docman/view.php/1000106/84/calcfactors.sxc tuning OpenOffice spreadsheet], which suggests tuning values for 5 different types of workloads. <br />
* [http://linuxfinances.info/info/quickstart.html QuickStart Guide to Tuning PostgreSQL] by Christopher Browne<br />
* [http://www.varlena.com/GeneralBits/Tidbits/annotated_conf_e.html Annotated postgresql.conf] by Josh Berkus and Shridhar Daithankar (older V7.4 targeted version of material covered in the GUC tour referenced above)<br />
* [http://www.varlena.com/GeneralBits/Tidbits/perf.html Performance Tuning] by Josh Berkus and Shridhar Daithankar<br />
* [http://old.zope.org/Members/pupq/pg_in_aggregates/howto_view Replacing Slow Loops in PostgreSQL] by Joel Burton<br />
* [http://momjian.us/main/writings/pgsql/hw_performance/ PostgreSQL Hardware Performance Tuning] by Bruce Momjian<br />
* [http://www.targeted.org/articles/databases/fragmentation.html The effects of data fragmentation in a mixed load database] by Dmitry Dvoinikov<br />
* [https://wiki.postgresql.org/images/a/a8/Postgres_8_3_Performance.pdf PostgreSQL Performance Features in 8.3] by Simon Riggs<br />
* [http://2ndquadrant.com/media/pdfs/talks/Postgres_Performance_Update84.pdf PostgreSQL Performance Features in 8.4] by Simon Riggs<br />
* [http://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/ Understanding Postgres Performance] by Craig Kerstiens<br />
* [http://www.craigkerstiens.com/2013/01/10/more-on-postgres-performance/ More on Postgres Performance] by Craig Kerstiens<br />
* [https://www.citusdata.com/blog/2016/10/12/count-performance/ Faster PostgreSQL counting]<br />
<br />
<br />
Performance courses are available from a number of companies. Check [http://www.postgresql.org/about/eventarchive events and trainings] for further details.<br />
<br />
==Critical maintenance for performance==<br />
*[[Introduction to VACUUM, ANALYZE, EXPLAIN, and COUNT]] by Jim Nasby.<br />
*[[VACUUM FULL]] and why you should avoid it<br />
*[[Planner Statistics]]<br />
*[[Using EXPLAIN]]<br />
*[[Logging Difficult Queries]]<br />
*[[Logging Checkpoints]]<br />
*[http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm Checkpoints and the Background Writer: PostgreSQL 8.3 Improvements and Migration] by Greg Smith<br />
*[[Bulk Loading and Restores]]<br />
*[[Performance Analysis Tools]] by Craig Ringer<br />
<br />
== Database architecture ==<br />
* [[Priorities|Limiting and prioritizing user/query/database resource usage]] by Craig Ringer<br />
* [[Prioritizing databases by separating into multiple clusters]] by Craig Ringer<br />
* [[Clustering]]<br />
* [[Shared Storage]]<br />
<br />
==Database Hardware Selection and Setup==<br />
* [[Database Hardware]]<br />
* [[Reliable Writes]]<br />
<br />
==Benchmark Workloads== <br />
* [[:Category:Benchmarking]]<br />
<br />
[[Category:Administration]][[Category:Performance]][[Category:Benchmarking]]<br />
[[Category:General articles and guides]]</div>Robinshttps://wiki.postgresql.org/index.php?title=Pgbenchtesting&diff=27233Pgbenchtesting2016-03-15T18:52:33Z<p>Robins: The -M option needs to be capital M (and not small m)</p>
<hr />
<div>= Testing for Performance Regression with pgBench 9.0 =<br />
<br />
This page will eventually be merged into [[Regression Testing with pgbench]]<br />
<br />
* [http://developer.postgresql.org/pgdocs/postgres/pgbench.html pgBench 9.0 Docs]<br />
<br />
In order to test for performance regressions (or improvements) it's necessary to install two versions of PostgreSQL on the same machine. Otherwise, you have no comparable statistics. For example, you might install 8.4.3 and 9.0alpha5, or you might install 9.0alpha4 and 9.0alpha5. You also might run against the same test version in two modes: with HS/SR and without, for example.<br />
<br />
Since pgbench is such a simple test, you'll need to run several different runs to see different aspects of performance. It's also a good idea to run each at least 3 times, since pgbench has some randomness to it.<br />
<br />
Always run the same version of pgBench against both databases, probably the newer version.<br />
<br />
Some factors:<br />
<br />
* '''Where to run pgBench''': Ideally, you want to run it from a separate machine from the one holding the database. That way, you don't have pgBench taking CPU away from the database.<br />
* '''Number of Threads and Clients to Use''': This depends on the number of cores on the machine(s) you're testing. For each core available to the database, I suggest 1 thread and 2 clients. Note: do not use multi-threaded pgBench on non-threadsafe systems; you will get unreliable results.<br />
* '''PostgreSQL Configuration''': use what you'd consider a normal performance configuration for the machine being tested. Use (as much as possible) the same configuration for both.<br />
* '''Time vs. Transactions''': results which run pgbench for a specific amount of time are easier to compare. You also know how long they'll take you.<br />
* '''Initializing Databases''': if you are going to use the same database for several test runs in a row, it's important that you "prime" it by running pgbench against it for at least 20 minutes first, or the first couple of tests will be misleadingly fast. Alternately, you can initialize a new database for each test run.<br />
* '''Time to Run''' Ideally, you'd do each pgbench run for at least an hour for useful results. However, this interferes with running a lot of different tests for people who don't do this full-time or have a dedicated testing server. Make sure to run it for at least 10 minutes, though, to get results you can even measure. Possibly run the most interesting results in a 1-hour test. All tests below run for 10 minutes.<br />
<br />
What follows are some examples of tests. The command line given would be appropriate for a machine with 2 cores available to the database and thread-safe.<br />
<br />
== Memory vs. Disk Performance ==<br />
<br />
You want to test pgbench at the 3 levels of performance related to disk: in buffer, mostly in cache, and all on disk. You manipulate this by changing the scale factor, following these two formulas, assuming a dedicated database server.<br />
<br />
''scale / 75 = 1GB database''<br />
<br />
* In Buffer Test: 0.1 X RAM<br />
* Mostly Cached: 0.9 X RAM<br />
* Mostly on Disk: 4.0 X RAM<br />
<br />
Note that the mostly-on-disk test may require you to have a considerable amount of disk space available for your database.<br />
<br />
Examples: the following assume a 2-core machine with 2GB of RAM, running for 10 minutes:<br />
<br />
Buffer test:<br />
* pgbench -i -s 15 bench1<br />
* pgbench -c 4 -j 2 -T 600 bench1<br />
<br />
Mostly Cache Test:<br />
* pgbench -i -s 70 bench2<br />
* pgbench -c 4 -j 2 -T 600 bench2<br />
<br />
On-Disk Test:<br />
* pgbench -i -s 600 bench3<br />
* pgbench -c 4 -j 2 -T 600 bench3<br />
<br />
Measuring the amount of time required to initialize the three databases will also provide interesting results.<br />
<br />
== Read vs. Write Performance ==<br />
<br />
It is also interesting to test relative speed of different write patterns. For this set of tests, use either the Mostly Cache or On-Disk size database, or something in-between.<br />
<br />
The tests below assume the same machine above. All start with:<br />
<br />
* pgbench -i -s 70 bench2<br />
<br />
Read-Write Test<br />
* pgbench -c 4 -j 2 -T 600 bench2<br />
<br />
Read-Only Test<br />
* pgbench -c 4 -j 2 -T 600 -S bench2<br />
<br />
Simple Write Test<br />
* pgbench -c 4 -j 2 -T 600 -N bench2<br />
<br />
== Connections and Contention ==<br />
<br />
For this series of tests, we want to test how PostgreSQL behaves with different levels of connection activity. In this case, it's very relative to how many cores you have. Again, we're assuming the same 2-core, 2GB machine.<br />
<br />
Unfortunately, you can only do this test effectively from another machine which has at least as many cores as the database server.<br />
<br />
All tests start with:<br />
* pgbench -i -s 30 bench<br />
<br />
Single-Threaded<br />
* pgbench -c 1 -T 600 bench<br />
<br />
Normal Load<br />
* pgbench -c 8 -j 2 -T 600 bench<br />
<br />
Heavy Contention<br />
* pgbench -c 64 -j 4 -T 600 bench<br />
<br />
Heavy Connections without Contention<br />
* pgbench -c 64 -j 4 -T 600 -N bench<br />
<br />
Heavy Re-connection (simulates no connection pooling)<br />
* pgbench -c 8 -j 2 -T 600 -C bench<br />
<br />
== Prepared vs. Ah-hoc Queries ==<br />
<br />
pgBench 9.0 also allows you to test the effect of prepared queries on performance. Assumes the same database server as above.<br />
<br />
Initialize with:<br />
* pgbench -i -s 70 bench<br />
<br />
Unprepared, Read-Write:<br />
* pgbench -c 4 -j 2 -T 600 bench<br />
<br />
Prepared, Read-Write:<br />
* pgbench -c 4 -j 2 -T 600 -M prepared bench<br />
<br />
Unprepared, Read-Only:<br />
* pgbench -c 4 -j 2 -T 600 -S bench<br />
<br />
Prepared, Read-Only:<br />
* pgbench -c 4 -j 2 -T 600 -M prepared -S bench<br />
<br />
[[Category:Benchmarking]]</div>Robinshttps://wiki.postgresql.org/index.php?title=Disk_Usage&diff=27232Disk Usage2016-03-15T04:57:38Z<p>Robins: Found an alternate version (of recently removed dead URL) somewhere else. Quoting that instead.</p>
<hr />
<div>__NOTOC__<br />
== Finding the size of various object in your database ==<br />
* [http://andreas.scherbaum.la/blog/archives/282-table-size,-database-size.html table size, database size]<br />
<br />
== Finding the largest databases in your cluster ==<br />
<br />
{{SnippetInfo|Disk usage|lang=SQL|version=>=8.2|category=Performance}}<br />
<br />
Databases to which the user cannot connect are sorted as if they were infinite size.<br />
<br />
<source lang="sql"><br />
SELECT d.datname as Name, pg_catalog.pg_get_userbyid(d.datdba) as Owner,<br />
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')<br />
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))<br />
ELSE 'No Access'<br />
END as Size<br />
FROM pg_catalog.pg_database d<br />
order by<br />
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')<br />
THEN pg_catalog.pg_database_size(d.datname)<br />
ELSE NULL<br />
END desc -- nulls first<br />
LIMIT 20<br />
</source><br />
<br />
== Finding the size of your biggest relations ==<br />
<br />
{{SnippetInfo|Disk usage|lang=SQL|version=>=8.1|category=Performance}}<br />
<br />
Relations are objects in the database such as tables and indexes, and this query shows the size of all the individual parts. Tables which have both regular and [http://www.postgresql.org/docs/current/static/storage-toast.html TOAST] pieces will be broken out into separate components; an example showing how you might include those into the main total is available in the [http://www.postgresql.org/docs/current/static/disk-usage.html documentation], and as of PostgreSQL 9.0 it's possible to include it automatically by using pg_table_size here instead of pg_relation_size:<br />
<br />
Note that all of the queries below this point on this page show you the sizes for only those objects which are in the database you are currently connected to.<br />
<br />
<source lang="sql"><br />
SELECT nspname || '.' || relname AS "relation",<br />
pg_size_pretty(pg_relation_size(C.oid)) AS "size"<br />
FROM pg_class C<br />
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)<br />
WHERE nspname NOT IN ('pg_catalog', 'information_schema')<br />
ORDER BY pg_relation_size(C.oid) DESC<br />
LIMIT 20;<br />
</source><br />
<br />
Example output (from a database created with pgbench, scale=25):<br />
<br />
<code><pre><br />
relation | size <br />
------------------------+------------<br />
public.accounts | 326 MB<br />
public.accounts_pkey | 44 MB<br />
public.history | 592 kB<br />
public.tellers_pkey | 16 kB<br />
public.branches_pkey | 16 kB<br />
public.tellers | 16 kB<br />
public.branches | 8192 bytes<br />
</pre></code><br />
<br />
== Finding the total size of your biggest tables ==<br />
<br />
This version of the query uses [http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE pg_total_relation_size], which sums total disk space used by the table including indexes and toasted data rather than breaking out the individual pieces:<br />
<br />
<source lang="sql"><br />
SELECT nspname || '.' || relname AS "relation",<br />
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"<br />
FROM pg_class C<br />
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)<br />
WHERE nspname NOT IN ('pg_catalog', 'information_schema')<br />
AND C.relkind <> 'i'<br />
AND nspname !~ '^pg_toast'<br />
ORDER BY pg_total_relation_size(C.oid) DESC<br />
LIMIT 20;<br />
</source><br />
<br />
== Sizes before 8.1 ==<br />
<br />
The pg_relation_size functions were introduced in PostgreSQL 8.1. In earlier versions, the following query can be used instead, returning the size in megabytes:<br />
<br />
<source lang="sql"><br />
SELECT <br />
relname, (relpages * 8) / 1024 as size_mb<br />
FROM pg_class ORDER by relpages DESC LIMIT 20;<br />
</source><br />
<br />
You'll need to account for TOAST yourself here. Bear in mind also that relpages is only up-to-date as of the last VACUUM or ANALYZE on the particular table.<br />
<br />
== Sizes in 8.4 and later ==<br />
<br />
In 8.4, pg_relation_size was changed to use the regclass type, which means that pg_relation_size(data_type_name) no longer works.<br />
<br />
== Easy access to these queries ==<br />
<br />
[https://github.com/datachomp/dotfiles/blob/master/.psqlrc#L53 ~/.psqlrc tricks: table sizes] shows how to make it easy to run size related queries like this in psql.<br />
<br />
[[Category:SQL]]<br />
[[Category:Administration]][[Category:Performance]]</div>Robinshttps://wiki.postgresql.org/index.php?title=Disk_Usage&diff=27231Disk Usage2016-03-15T04:53:45Z<p>Robins: Removed a dead URL. Looks like page / site re-owned by someone else and no trace of the original page</p>
<hr />
<div>__NOTOC__<br />
== Finding the size of various object in your database ==<br />
* [http://andreas.scherbaum.la/blog/archives/282-table-size,-database-size.html table size, database size]<br />
<br />
== Finding the largest databases in your cluster ==<br />
<br />
{{SnippetInfo|Disk usage|lang=SQL|version=>=8.2|category=Performance}}<br />
<br />
Databases to which the user cannot connect are sorted as if they were infinite size.<br />
<br />
<source lang="sql"><br />
SELECT d.datname as Name, pg_catalog.pg_get_userbyid(d.datdba) as Owner,<br />
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')<br />
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))<br />
ELSE 'No Access'<br />
END as Size<br />
FROM pg_catalog.pg_database d<br />
order by<br />
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')<br />
THEN pg_catalog.pg_database_size(d.datname)<br />
ELSE NULL<br />
END desc -- nulls first<br />
LIMIT 20<br />
</source><br />
<br />
== Finding the size of your biggest relations ==<br />
<br />
{{SnippetInfo|Disk usage|lang=SQL|version=>=8.1|category=Performance}}<br />
<br />
Relations are objects in the database such as tables and indexes, and this query shows the size of all the individual parts. Tables which have both regular and [http://www.postgresql.org/docs/current/static/storage-toast.html TOAST] pieces will be broken out into separate components; an example showing how you might include those into the main total is available in the [http://www.postgresql.org/docs/current/static/disk-usage.html documentation], and as of PostgreSQL 9.0 it's possible to include it automatically by using pg_table_size here instead of pg_relation_size:<br />
<br />
Note that all of the queries below this point on this page show you the sizes for only those objects which are in the database you are currently connected to.<br />
<br />
<source lang="sql"><br />
SELECT nspname || '.' || relname AS "relation",<br />
pg_size_pretty(pg_relation_size(C.oid)) AS "size"<br />
FROM pg_class C<br />
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)<br />
WHERE nspname NOT IN ('pg_catalog', 'information_schema')<br />
ORDER BY pg_relation_size(C.oid) DESC<br />
LIMIT 20;<br />
</source><br />
<br />
Example output (from a database created with pgbench, scale=25):<br />
<br />
<code><pre><br />
relation | size <br />
------------------------+------------<br />
public.accounts | 326 MB<br />
public.accounts_pkey | 44 MB<br />
public.history | 592 kB<br />
public.tellers_pkey | 16 kB<br />
public.branches_pkey | 16 kB<br />
public.tellers | 16 kB<br />
public.branches | 8192 bytes<br />
</pre></code><br />
<br />
== Finding the total size of your biggest tables ==<br />
<br />
This version of the query uses [http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE pg_total_relation_size], which sums total disk space used by the table including indexes and toasted data rather than breaking out the individual pieces:<br />
<br />
<source lang="sql"><br />
SELECT nspname || '.' || relname AS "relation",<br />
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"<br />
FROM pg_class C<br />
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)<br />
WHERE nspname NOT IN ('pg_catalog', 'information_schema')<br />
AND C.relkind <> 'i'<br />
AND nspname !~ '^pg_toast'<br />
ORDER BY pg_total_relation_size(C.oid) DESC<br />
LIMIT 20;<br />
</source><br />
<br />
== Sizes before 8.1 ==<br />
<br />
The pg_relation_size functions were introduced in PostgreSQL 8.1. In earlier versions, the following query can be used instead, returning the size in megabytes:<br />
<br />
<source lang="sql"><br />
SELECT <br />
relname, (relpages * 8) / 1024 as size_mb<br />
FROM pg_class ORDER by relpages DESC LIMIT 20;<br />
</source><br />
<br />
You'll need to account for TOAST yourself here. Bear in mind also that relpages is only up-to-date as of the last VACUUM or ANALYZE on the particular table.<br />
<br />
== Sizes in 8.4 and later ==<br />
<br />
In 8.4, pg_relation_size was changed to use the regclass type, which means that pg_relation_size(data_type_name) no longer works.<br />
<br />
[[Category:SQL]]<br />
[[Category:Administration]][[Category:Performance]]</div>Robinshttps://wiki.postgresql.org/index.php?title=Using_EXPLAIN&diff=25586Using EXPLAIN2015-08-11T14:29:02Z<p>Robins: Replacing Dead Link with a working one</p>
<hr />
<div>{{Languages}}<br />
Figuring out why a statement is taking so long to execute is done with the [http://www.postgresql.org/docs/current/static/sql-explain.html EXPLAIN] command. You can run this two ways; if you use EXPLAIN ANALYZE, it will actually run the statement and let you compare what the planner thought was going to happen with what actually did. Note that if the statement changes data, that will also happen when you run with EXPLAIN ANALYZE; if you just use EXPLAIN the statement doesn't do anything to the database.<br />
<br />
There are some tools available to help interpret EXPLAIN output:<br />
* pgadmin includes a visual EXPLAIN tool that helps map out what's actually happening. See [http://www.postgresonline.com/journal/index.php?/archives/27-Reading-PgAdmin-Graphical-Explain-Plans.html Reading PgAdmin Graphical Explain Plans].<br />
* Visual Explain, originally a [http://sources.redhat.com/rhdb/visualexplain.html RedHat component] that has been kept current and improved by EnterpriseDB, comes bundled with the EnterpriseDB Advanced Server package. It can be built to run against other PostgreSQL installations using the source code to their [http://www.enterprisedb.com/products/download.do Developer Studio] package.<br />
* [http://explain.depesz.com/ explain.depesz.com] shows explain plan with extracted summarized times, and highlights based on chosen criteria.<br />
<br />
And there are a few tutorials on this subject, many of which are titled "Explaining Explain" (sigh):<br />
* [http://www.postgresql.org/docs/current/interactive/performance-tips.html Performance Tips]<br />
* [http://www.gtsm.com/oscon2003/toc.html Efficient SQL] by Greg Sabino Mullane (2003)<br />
* Explaining Explain: [http://www.xzilla.net/assets/OSCON_Explaining_Explain.pdf pdf] [http://www.postgresql.org/communityfiles/13.sxi OpenOffice Presentation] by Robert Treat (2005)<br />
* [http://pooteeweet.org/files/phpworkso6/exlaining_explain.pdf Explaining Explain] by Lukas Smith (2006)<br />
* [http://wiki.postgresql.org/images/4/45/Explaining_EXPLAIN.pdf Explaining Explain] by Greg Stark (2008)<br />
* [http://neilconway.org/talks/executor.pdf Query Execution Techniques in PostgreSQL] by Neil Conway<br />
* [[Introduction to VACUUM, ANALYZE, EXPLAIN, and COUNT]] by Jim Nasby<br />
* [https://sites.google.com/site/robertmhaas/presentations The PostgreSQL Query Planner] by Robert Haas (2010)<br />
* [http://www.2ndquadrant.com/books/postgresql-9-0-high-performance/ PostgreSQL 9.0 High Performance] (2010) is a book with a long discussion of how to use EXPLAIN, read the resulting query plans, and make changes to get different plans.<br />
<br />
A common problem that causes the planner to make bad decisions is not keeping [[Planner Statistics]] updated. Another is leaving the tuning parameters that let the server know how memory is available at the very small defaults. For example, in the stock configuration, sorts that take more than 1MB are swapped to disk as being too big to process in memory. [[Tuning Your PostgreSQL Server]] covers good practices for sizing the memory and other tuning parameters that most impact query planning.<br />
<br />
One thing you do when stumped by a plan is to submit the full EXPLAIN ANALYZE output, along with the schema of the involved queries, to the [http://archives.postgresql.org/pgsql-performance/ pgsql-performance] mailing list. To get a useful reply more quickly, please read [[SlowQueryQuestions]] before posting. Note that if you're not running a relatively current version of PostgreSQL, it's quite possible the answer you'll get is that the problem is resolved in a later one. It may save you some time to try at least the most current update to the version you're using (i.e. upgrade to 8.2.6 if that's the current latest rev and you're using 8.2.3) and see if that improves the plan you get. <br />
<br />
An advanced technique here is to save your explain plans over time and see how they change as the amount of data in the table grows. This can give you an idea if you're collecting statistics aggressively enough. A simple pl/pgsql example is at [http://archives.postgresql.org/message-id/9359.1243290777@sss.pgh.pa.us generic options for explain].<br />
<br />
[[Category:FAQ]]<br />
[[Category:Performance]]<br />
[[Category:PGAdmin]]</div>Robinshttps://wiki.postgresql.org/index.php?title=Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding&diff=24088Microsoft SQL Server to PostgreSQL Migration by Ian Harding2015-01-08T10:52:29Z<p>Robins: /* Tables */ PostgreSQL supports multiple ALTER TABLE changes since 8.0 and this paragraph isn't an issue any more.</p>
<hr />
<div>by Ian A. Harding <ianh@tpchd.org><br />
v1.00, Last updated 17th September 2001<br />
<br />
How to move a database from a popular proprietary database to the world's most powerful open source database.<br />
<br />
== Disclaimer ==<br />
The following document is offered in good faith as comprising only safe programming and procedures. No responsibility is accepted by the author for any loss or damage caused in any way to any person or equipment, as a direct or indirect consequence of following these instructions.<br />
<br />
== Introduction ==<br />
Microsoft SQL Server is very popular relational database management systems (RDBMS) with highly restrictive licensing and high cost of ownership if the database is of significant size, or is used by a significant number of clients. It does, however, provide a very user-friendly interface, is easy to learn and use, and has low cost entry level configurations. This has resulted in a very large installed user base.<br />
<br />
PostgreSQL now challenges MS SQL Server in basic feature set, reliability and performance, has a much less restrictive license, and is open source. As a matter of course, users are migrating to PostgreSQL from MS SQL Server as the cost of ownership becomes an issue, and as their knowledge of relational database systems increases.<br />
<br />
This HOW-TO is intended for the MS SQL Server user who is now ready to migrate databases to PostgreSQL.<br />
<br />
== Considerations ==<br />
<br />
RDBMS features are implemented differently and to different degrees by programmers. Some applications rely heavily on so-called middleware, or on the client application to handle business logic. Others attempt to put as much logic as possible in the database. Your migration will be far more difficult if your application is in the latter group. While it is a sound design choice to put logic in the database server, it will require programming in a vendor specific Structured Query Language (SQL) extension such as Microsoft's Transact SQL (T-SQL). This is also the case with PostgreSQL. There is no easy way to migrate stored procedures, triggers, or rules. On the bright side, PostgreSQL provides several language options, all of which are more graceful than T-SQL.<br />
<br />
RDBMS all provide built-in functions. However, like procedural extensions to SQL, they are not portable. Fortunately, there is some overlap, and the simple syntax makes migration relatively easy.<br />
<br />
Finally, the programmer's choice of SQL syntax can affect this process. Most RDBMS are approaching the evolving SQL standards. That is, they are leaning away from vendor specific syntax such as the '*=' syntax for a left outer join. This syntax is still supported in MS SQL Server as of version 7.0, but was never supported in PostgreSQL.<br />
<br />
This process will require either a mind-numbing amount of hand editing of script and data files, or use of a scripting language to programmatically modify these files, followed by a somewhat less enormous amount of editing. I am not smart enough to identify every possible option for the migration, or to accomodate them in a script. I have done this migration on a relatively complex database application in a reasonable amount of time. This, rather than a technically flawless script, should be your goal.<br />
<br />
I use Tool Command Language (TCL) for almost everything, so I use it here. You can use whatever language you like.<br />
<br />
== Tables ==<br />
<br />
Dump the table defininitions with the MS SQL Server scripting tool. From the Enterprise Manager, right click on your database and select 'All Tasks', then 'Generate SQL Scripts' from the context menu. Uncheck 'Script All Objects', and select 'All Tables'. On the 'Formatting' tab, de-select 'Generate DROP...'. On the 'Options' tab, select 'Script indexes' and Script PRIMARY KEYS...'. Select the 'MS-DOS' file format, and make sure 'Create one file' is checked. Click OK, give it a name, and put it somewhere you can find it.<br />
<br />
A brief look at this file will show you what we are up against. MS uses square brackets around all identifiers, to protect you from poor design choices such as using reserved keywords so crazy things like:<br />
<br />
CREATE TABLE [dbo].[Select] ([Union] [int])<br />
<br />
are possible. PostgreSQL uses double quotes instead. MS uses the object owner qualification for all objects, 'dbo' in this case. PostgreSQL has no such qualifications in object names.<br />
<br />
Another thing to note is that MS SQL identifiers are case preserved, but in practice most installations are installed as case insensitive. PostgreSQL is case agnostic in the case of SQL keywords and unquoted identifiers, forcing all queries to lower case. It is not the same as being case insensitive, in that you can create tables using the double quote protection mentioned above, such that they can only be accessed using the same double quoting method. I find it is best to abandon case in object identifiers when migrating to PostgreSQL. Also, it is safest to avoid any identifiers that require quoting to avoid problems down the road.<br />
<br />
It is worth noting that for data comparisons, PostgreSQL is case sensitive and there is no option to change this behaviour. You will have to force data to upper or lower on both sides of text comparisons if case is not important to the operation and there is a chance of it being different. This conversion might be a good time to force data used in joins and comparisons to all upper or lower case. You will also need to look at the application for code that does comparisons of user-entered information taking advantage of MS SQL Server's typical case insensitivity.<br />
<br />
Indexes are a bright spot, mostly. The CLUSTER keyword in PostgreSQL is not the same as the CLUSTERED keyword in a MS SQL Server index creation. PostgreSQL will allow you to 'cluster' a table, that is, rearranging the tuples in a table in order for that field. This sounds good, except that the cluster is not maintained for updates and inserts, and the fact that it will break all your other indexes whenever you generate the clustering.<br />
<br />
Having said all that, here is a partial list of things to correct:<br />
<br />
# Force to lower case.<br />
# Remove all square brackets.<br />
# Remove all object owner prefixes (i.e. "dbo.")<br />
# Remove all reference to filegroup (i.e. "ON PRIMARY")<br />
# Remove all non-supported optional keywords (i.e. "WITH NOCHECK", "CLUSTERED")<br />
# Update all non-supported data types (i.e. "DATETIME" becomes "TIMESTAMP") Also, this is a good time to get away from MONEY.It is supported in PostgreSQL, but is on its way out.Use NUMERIC(19,4).<br />
# Replace the T-SQL batch terminator "GO" with the PostgreSQL batch terminator ";"<br />
<br />
Put this file somewhere safe, and now let's get the data.<br />
<br />
== Data ==<br />
<br />
Data is data. It is brought over in text form and cast into it's proper form by the database according to the datatypes you used in creating your tables. If you have binary data, I am the wrong guy to ask.<br />
<br />
There are a couple gotchas here too, of course. Since we use the COPY command, and it interprets a newline as the end of a tuple, you need to clean out all those newlines lurking in your text fields in MS SQL Server. This is easy enough to do. Also, the data dump from MS SQL Server will use the standard cr/lf line terminator, which needs to be changed to lf or it will cause havoc in comparisons of strings, among other problems. I did this the easy way, downloading the dumps to my machine running my favorite Unix-like operating system via ftp, which does this translation for you.<br />
<br />
The first step in dumping the data out of MS SQL Server is to type all the names of your fields into a text file on the Win32 machine. You can cheat and issue:<br />
<br />
select name from sysobjects where type = 'U'<br />
<br />
in Query Analyzer (ISQL-W) to get the list, then save the results to a file. Then, write a handy little script to call bcp, the Bulk Copy Program. Mine looks like this:<br />
<br />
set file [open "C:\\inetpub\\ftproot\\tablelist.txt" r] while {![eof $file]} {<br />
set table [gets $file]<br />
exec bcp ..$table out $table -c -k -S192.168.100.1 -Usa -Ppassword -r ~<br />
}<br />
close $file<br />
<br />
This will dump all the listed tables into files of the same name in the current directory. The -c flag means to use plain character format. The -k flag tells bcp to "keep nulls". This is important later when we import the data. The -r is the "row terminator". To make cleaning up the carriage returns easier, I use this to signal the end of a row. I put this script in the C:\InetPub\ftproot directory, so I can go right to the next step.<br />
<br />
From the Unix-like machine, start ftp and get the file listing you created earlier. Put it in a work directory. Change to the new work directory and get the files:<br />
<br />
ftp> lcd /home/homer/workdir Local directory now /home/homer/workdir<br />
ftp> fget tablelist.txt<br />
<br />
This should download all of the data files to the work directory, magically converting line terminators to Unix compatible format. If you can't use FTP, there are other ways to get files from here to there. Just be advised that you may need a little sed script to fix the cr/lf problem.<br />
<br />
Now, let's fix the embedded line feed issue.<br />
<br />
#!/usr/pkg/bin/tclsh<br />
set file [open tblnames r]<br />
set flist [read -nonewline $file]<br />
close $file<br />
set flist [split $flist \n]<br />
foreach f $flist {<br />
set file [open $f r]<br />
set data [read -nonewline $file]<br />
close $file<br />
regsub -all {\000} $data {} data<br />
regsub -all {\n} $data \\\n data<br />
regsub -all {~} $data \n data<br />
set file [open $f w]<br />
puts -nonewline $file $data<br />
close $file<br />
}<br />
<br />
The regsub lines are where the work gets done. They replace all nulls (\000) with an empty string, then all linefeeds with a literal "\n" which will tell COPY what to do when we import the file, then my line terminators get replaced with a linefeed, which is what COPY is expecting. There are cleaner and easier ways to do this, but you get the point.<br />
<br />
Now, go back to the sql file you edited to create your database objects. I assume it is on the Unix-like box at this point. It should have a series of CREATE TABLE statements, followed by ALTER TABLE and CREATE INDEX, etc statements. What we need to do now is tell it we want to load data after the tables are created, but before anything else.<br />
<br />
For each CREATE TABLE statement, follow it with a COPY statment. Something like :<br />
<br />
COPY tablename FROM '/home/homer/workdir/tablename' with null as '';<br />
<br />
Once you have this done, execute it against your PostgreSQL database, something like<br />
<br />
$ psql newdb < modifiedscript.sql &> outfile<br />
<br />
should work. The output file is good to have for looking for problems. It gets messy so :<br />
<br />
$ grep ERROR outfile<br />
<br />
can give you an idea how things went. I guarantee you have some troubleshooting to do.<br />
<br />
== Views ==<br />
<br />
Views are pretty easy, as long as you didn't use too many functions in them. A favorite of mine is isnull(). Like most functions, it has a PostgreSQL counterpart, coalesce(). A surprising number of functions will work just fine. For example, round() is exactly the same. datepart() becomes date_part(), but the arguments are the same, althought PostgreSQL may be more particular about format strings. For example, SQL Server will accept datepart(yyyy, mydatefield) as well as datepart(year, mydatefield). PostgreSQL wants to see date_part('year', mydatefield) (note single quotes).<br />
<br />
Generating sql for views is pretty much the same as for tables. From the Enterprise Manager, right click on your database and select 'All Tasks', then 'Generate SQL Scripts' from the context menu. Uncheck 'Script All Objects', and select 'All Views'. On the 'Formatting' tab, de-select 'Generate DROP...'. On the 'Options' tab, Select the 'MS-DOS' file format, and make sure 'Create one file' is checked. Click OK, give it a name, and put it somewhere you can find it.<br />
<br />
Run this file through the same script you created to clean the sql for your tables, and see if it will work on PostgreSQL. If not, you will have to do some fixing of functions.<br />
<br />
== Summary ==<br />
<br />
Converting a database from MS SQL Server is not always easy. It is, however, always worth it. You will find PostgreSQL to be an extremely powerful and flexible product, with the best tech support in the world, the actual developers and users of the product. If you spent days trying to get xp_sendmail to work on SQL Server version 7.0, or wondered what was in those enormous "Service Packs" then you will appreciate this.<br />
<br />
== Extension Links ==<br />
<br />
* [http://www.easyfrom.net/ ESF Database Migration Toolkit] Enables you to transfer data across various databases in 3 simple steps without writing any scripts. Supporting PostgreSQL, MySQL, Oracle, SQL Server, IBM DB2, Informix, Microsoft Access, Microsoft Excel, dBase, Foxpro, Firbird, SQLite etc.<br />
* [http://dbconvert.com/index.php DBConvert database conversion / synchronization tools] Migrate and sync your data between PostgreSQL, SQL Server, SQL Azure, MySQL, MS Access<br />
* [https://github.com/morepaolo/db-migration-assistant DB Migration Assistant] Open source database migration app, written in php. Currently supports migration from mssqlnative to postgres9<br />
<br />
[[Category:Migration to PostgreSQL]]</div>Robinshttps://wiki.postgresql.org/index.php?title=GUI_Database_Design_Tools&diff=22262GUI Database Design Tools2014-05-05T02:03:57Z<p>Robins: TCM Home-page is down, replacing with GNU Manual page (Probably should remove link since it seems unmaintained for a decade)</p>
<hr />
<div>{{Languages}}<br />
If you're building a complicated data model, support from tools that provide for the creation of Entity-Relationship diagrams and similar techniques are extremely useful. Some tools that have been suggested as useful by the PostgreSQL community are:<br />
<br />
* [http://www.aquafold.com Datastudio]<br />
* [http://sourceforge.net/projects/dbdesigner-fork/ DbDesigner fork]<br />
* [http://www.dbschema.com/ DbSchema]<br />
* [http://www.dbwrench.com dbwrench]<br />
* [http://www.datanamic.com/dezign/index.html DeZign for Database]<br />
* [http://druid.sourceforge.net/ Druid III]<br />
<br />
* [http://www.modelright.com/ ModelRight]<br />
* [http://www.moskitt.org/eng/moskitt0/ Moskitt] and its [http://www.pgmodeler.com.br/ spatial plugin for PostGIS users]<br />
<br />
* [http://www.codebydesign.com Open System Architect]<br />
<br />
* [http://www.pgmodeler.com.br/ PGmodeler]<br />
* [http://www.sqlmaestro.com/products/postgresql/maestro PostgreSQL Maestro]<br />
* [http://www.sqlpower.ca/page/architect Power*Architect]<br />
<br />
* [http://doc.gnu-darwin.org/usersguide/ TCM - Toolkit for Conceptual Modelling]<br />
* [http://www.quest.com/Toad-Data-Modeler/ Toad Data Modeler]<br />
<br />
* [http://www.valentina-db.com/valentina-studio-overview Valentina Studio]<br />
<br />
* [http://www.vertabelo.com Vertabelo (designing db online)]<br />
<br />
* [http://www.wavemaker.com/ WaveMaker Visual Ajax Studio]<br />
<br />
* [http://xml2ddl.berlios.de/ xml to DDL]<br />
<br />
Also, [http://www.pgadmin.org/ pgadmin3] isn't a serious design tool, but can be helpful for browsing and simple additions<br />
<br />
[[Category:Tool]]</div>Robinshttps://wiki.postgresql.org/index.php?title=GUI_Database_Design_Tools&diff=22261GUI Database Design Tools2014-05-05T01:04:58Z<p>Robins: theKompany is essentially defunct (the website's down)</p>
<hr />
<div>{{Languages}}<br />
If you're building a complicated data model, support from tools that provide for the creation of Entity-Relationship diagrams and similar techniques are extremely useful. Some tools that have been suggested as useful by the PostgreSQL community are:<br />
<br />
* [http://www.aquafold.com Datastudio]<br />
* [http://sourceforge.net/projects/dbdesigner-fork/ DbDesigner fork]<br />
* [http://www.dbschema.com/ DbSchema]<br />
* [http://www.dbwrench.com dbwrench]<br />
* [http://www.datanamic.com/dezign/index.html DeZign for Database]<br />
* [http://druid.sourceforge.net/ Druid III]<br />
<br />
* [http://www.modelright.com/ ModelRight]<br />
* [http://www.moskitt.org/eng/moskitt0/ Moskitt] and its [http://www.pgmodeler.com.br/ spatial plugin for PostGIS users]<br />
<br />
* [http://www.codebydesign.com Open System Architect]<br />
<br />
* [http://www.pgmodeler.com.br/ PGmodeler]<br />
* [http://www.sqlmaestro.com/products/postgresql/maestro PostgreSQL Maestro]<br />
* [http://www.sqlpower.ca/page/architect Power*Architect]<br />
<br />
* [http://wwwhome.cs.utwente.nl/~tcm/ TCM - Toolkit for Conceptual Modelling]<br />
* [http://www.quest.com/Toad-Data-Modeler/ Toad Data Modeler]<br />
<br />
* [http://www.valentina-db.com/valentina-studio-overview Valentina Studio]<br />
<br />
* [http://www.vertabelo.com Vertabelo (designing db online)]<br />
<br />
* [http://www.wavemaker.com/ WaveMaker Visual Ajax Studio]<br />
<br />
* [http://xml2ddl.berlios.de/ xml to DDL]<br />
<br />
Also, [http://www.pgadmin.org/ pgadmin3] isn't a serious design tool, but can be helpful for browsing and simple additions<br />
<br />
[[Category:Tool]]</div>Robinshttps://wiki.postgresql.org/index.php?title=Todo&diff=21902Todo2014-03-02T23:10:48Z<p>Robins: It seems Bruce incorrectly used the wrong message-id when creating the ToDo. Probably it should be this one.</p>
<hr />
<div><div style="margin: 1ex 1em; float: right;"><br />
__TOC__<br />
</div><br />
<br />
This list contains '''known PostgreSQL bugs and feature requests''' and we hope it is complete. If you would like to work on an item, please read the [[Developer FAQ]] first. There is also a [[Development_information|development information page]].<br />
<br />
* {{TodoPending}} - marks ordinary, incomplete items<br />
* {{TodoEasy}} - marks items that are easier to implement<br />
* {{TodoDone}} - marks changes that are done, and will appear in the PostgreSQL 9.4 release.<br />
<br />
For help on editing this list, please see [[Talk:Todo]]. <b>Please do not add items here without discussion on the mailing list.</b><br />
<br />
<b>For Developers:</b> Unfortunately this list does not contain all the information necessary for someone to start coding a feature. Some of these items might have become unnecessary since they were added --- others might be desirable but the implementation might be unclear. When selecting items listed below, be prepared to first discuss the value of the feature. Do not assume that you can select one, code it and then expect it to be committed. Always discuss design on Hackers list before starting to code. The flow should be:<br />
<br />
Desirability -> Design -> Implement -> Test -> Review -> Commit<br />
<br />
<div style="padding: 1ex 4em;"><br />
== Administration ==<br />
<br />
{{TodoItem<br />
|Allow administrators to cancel multi-statement idle transactions<br />
|This allows locks to be released, but it is complex to report the cancellation back to the client.<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-12/msg01340.php <nowiki>Cancelling idle in transaction state</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-12/msg00441.php <nowiki>Re: Cancelling idle in transaction state</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Check for unreferenced table files created by transactions that were in-progress when the server terminated abruptly<br />
* [http://archives.postgresql.org/pgsql-patches/2006-06/msg00096.php <nowiki>Removing unreferenced files</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Set proper permissions on non-system schemas during db creation<br />
|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.}}<br />
<br />
{{TodoItem<br />
|Allow log_min_messages to be specified on a per-module basis<br />
|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. See also [[Logging Brainstorm]].}}<br />
<br />
{{TodoItem<br />
|Simplify creation of partitioned tables<br />
|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. See also [[Table partitioning]]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow custom variables to appear in pg_settings()<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-06/msg00850.php <nowiki>Re: count(*) performance improvement ideas</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Have custom variables be transaction-safe<br />
* {{MessageLink|4B577E9F.8000505@dunslane.net|Custom GUCs still a bit broken}}<br />
}}<br />
<br />
{{TodoItem<br />
|Implement the SQL-standard mechanism whereby REVOKE ROLE revokes only the privilege granted by the invoking role, and not those granted by other roles<br />
* [http://archives.postgresql.org/pgsql-bugs/2007-05/msg00010.php <nowiki>Re: Grantor name gets lost when grantor role dropped</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Prevent query cancel packets from being replayed by an attacker, especially when using SSL<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-08/msg00345.php <nowiki>Replay attack of query cancel</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Provide a way to query the log collector subprocess to determine the name of the currently active log file<br />
* [http://archives.postgresql.org/pgsql-general/2008-11/msg00418.php <nowiki>Current log files when rotating?</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow simpler reporting of the unix domain socket directory and allow easier configuration of its default location<br />
* http://archives.postgresql.org/pgsql-hackers/2010-10/msg01555.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-10/msg01482.php<br />
}}<br />
<br />
{{TodoItem<br />
|Allow custom daemons to be automatically stopped/started along with the postmaster<br />
|This allows easier administration of daemons like user job schedulers or replication-related daemons.<br />
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg01701.php <nowiki>Re: scheduler in core</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve logging of prepared transactions recovered during startup<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-11/msg00092.php <nowiki>&quot;recovering prepared transaction&quot; after server restart message</nowiki>]<br />
}}<br />
<br />
=== Configuration files ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItemDone<br />
|Allow postgresql.conf file values to be changed via an SQL API, perhaps using SET GLOBAL<br />
* http://archives.postgresql.org/pgsql-hackers/2010-10/msg00764.php<br />
* http://archives.postgresql.org/pgsql-hackers/2012-10/msg01509.php<br />
* http://archives.postgresql.org/pgsql-hackers/2012-11/msg00002.php<br />
}}<br />
<br />
{{TodoItem<br />
|Consider normalizing fractions in postgresql.conf, perhaps using '%'<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-06/msg00550.php <nowiki>Fractions in GUC variables</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow Kerberos to disable stripping of realms so we can check the username@realm against multiple realms<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-11/msg00009.php <nowiki>krb_match_realm patch</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve LDAP authentication configuration options<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg01745.php <nowiki>Proposed Patch - LDAPS support for servers on port 636 w/o TLS</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add external tool to auto-tune some postgresql.conf parameters<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-06/msg00000.php <nowiki>Re: Overhauling GUCS</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-11/msg00033.php <nowiki>Simple postgresql.conf wizard</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add 'hostgss' pg_hba.conf option to allow GSS link-level encryption<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg01454.php <nowiki>Re: Plans for 8.4</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Process pg_hba.conf keywords as case-insensitive<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-09/msg00432.php <nowiki>More robust pg_hba.conf parsing/error logging</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow pg_hba.conf to process include files<br />
* [http://www.postgresql.org/message-id/86fvnm5t44.fsf@jerry.enova.com HBA files w/include support]<br />
}}<br />
<br />
{{TodoItem<br />
|Create utility to compute accurate random_page_cost value<br />
* http://archives.postgresql.org/pgsql-performance/2011-04/msg00162.php<br />
* http://archives.postgresql.org/pgsql-performance/2011-04/msg00362.php<br />
}}<br />
<br />
{{TodoItem<br />
|Allow configuration files to be independently validated<br />
* http://archives.postgresql.org/pgsql-hackers/2011-03/msg01831.php<br />
* http://archives.postgresql.org/message-id/12666.1310774573@sss.pgh.pa.us<br />
}}<br />
<br />
{{TodoItem<br />
|Allow postgresql.conf settings to be accepted by backends even if some settings are invalid for those backends<br />
* http://archives.postgresql.org/pgsql-hackers/2011-04/msg00330.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-05/msg00375.php<br />
}}<br />
<br />
{{TodoItem<br />
|Allow all backends to receive postgresql.conf setting changes at the same time<br />
* http://archives.postgresql.org/pgsql-hackers/2011-04/msg00330.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-05/msg00375.php<br />
}}<br />
<br />
{{TodoItem<br />
|Allow synchronous_standby_names to be disabled after communication failure with all synchronous standby servers exceeds some timeout<br />
|This also requires successful execution of a synchronous notification command.<br />
* http://archives.postgresql.org/pgsql-hackers/2012-07/msg00409.php<br />
* [http://www.postgresql.org/message-id/BF2827DCCE55594C8D7A8F7FFD3AB7713DD9A622@SZXEML508-MBX.china.huawei.com Standalone synchronous master]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Tablespaces ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|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<br />
|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.}}<br />
<br />
{{TodoItem<br />
|Allow reporting of which objects are in which tablespaces<br />
|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.}}<br />
<br />
{{TodoItem<br />
|Allow WAL replay of CREATE TABLESPACE to work when the directory structure on the recovery computer is different from the original}}<br />
<br />
{{TodoItem<br />
|Allow per-tablespace quotas}}<br />
<br />
{{TodoItem<br />
|Allow tablespaces on RAM-based partitions for unlogged tables<br />
* http://archives.postgresql.org/pgsql-advocacy/2011-05/msg00033.php<br />
}}<br />
<br />
{{TodoItem<br />
|Allow toast tables to be moved to a different tablespace<br />
* [http://archives.postgresql.org/pgsql-hackers/2011-05/msg00980.php]<br />
* {{messageLink|CAFEQCbH756DyyAPQ1ykh3+b+kE1-EhWRww1WO_x5v38C-uLnUg@mail.gmail.com|patch : Allow toast tables to be moved to a different tablespace}} (issues remain)<br />
* [http://archives.postgresql.org/message-id/CAFEQCbEq07OopgE5xFYv2Q3eMq45hRSJkjCBO+kvpJq9NEVhow@mail.gmail.com Allow toast tables to be moved to a different tablespace]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Statistics Collector ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Allow statistics last vacuum/analyze execution times to be displayed without requiring track_counts to be enabled<br />
* [http://archives.postgresql.org/pgsql-docs/2007-04/msg00028.php <nowiki>row-level stats and last analyze time</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Clear table counters on TRUNCATE<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00169.php <nowiki>Small TRUNCATE glitch</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Track number of WAL files ready to be archived in pg_stat_archiver <br />
* [http://www.postgresql.org/message-id/CAB7nPqSCrcZGGy_SmpT7ubSzVGNMtphYU1JJZYyapHuN46E-Tw@mail.gmail.com <nowiki>pg_stat_archiver missing feature</nowiki>]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== SSL ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Allow SSL authentication/encryption over unix domain sockets<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-12/msg00924.php <nowiki>Re: Spoofing as the postmaster</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow SSL key file permission checks to be optionally disabled when sharing SSL keys with other applications<br />
* [http://archives.postgresql.org/pgsql-bugs/2007-12/msg00069.php <nowiki>BUG #3809: SSL &quot;unsafe&quot; private key permissions bug</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow SSL CRL files to be re-read during configuration file reload, rather than requiring a server restart<br />
|Unlike SSL CRT files, CRL (Certificate Revocation List) files are updated frequently<br />
* [http://archives.postgresql.org/pgsql-general/2008-12/msg00832.php <nowiki>Automatic CRL reload</nowiki>]<br />
Alternatively or additionally supporting OCSP (online certificate security protocol) would provide real-time revocation discovery without reloading<br />
}}<br />
<br />
{{TodoItem<br />
| Allow automatic selection of SSL client certificates from a certificate store<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-05/msg00406.php <nowiki>Allow multiple certificates or keys in the postgresql.crt/.key files</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
| Send the full certificate server chain to the client<br />
* [http://archives.postgresql.org/pgsql-bugs/2009-12/msg00145.php BUG #5245: Full Server Certificate Chain Not Sent to client]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Point-In-Time Recovery (PITR) ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Allow archive_mode to be changed without server restart?<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-10/msg01655.php <nowiki>Enabling archive_mode without restart</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider avoiding WAL switching via archive_timeout if there has been no database activity<br />
* [http://archives.postgresql.org/pgsql-hackers/2010-01/msg01469.php <nowiki>archive_timeout behavior for no activity</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg00395.php <nowiki>Re: archive_timeout behavior for no activity</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow base backup from standby to continue when the standby is promoted.<br />
* [http://archives.postgresql.org/pgsql-hackers/2012-10/msg00239.php <nowiki>Re: Promoting a standby during base backup</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add recovery target option to stop as soon as consistency is reached.<br />
* [http://archives.postgresql.org/message-id/5188F87D.1080908@vmware.com <nowiki>Re: Recovery target 'immediate'</nowiki>]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Standby server mode ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
| Allow pg_xlogfile_name() to be used in recovery mode<br />
* [http://archives.postgresql.org/message-id/3f0b79eb1001190135vd9f62f1sa7868abc1ea61d12@mail.gmail.com <nowiki>Streaming replication and pg_xlogfile_name()</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
| Prevent variables inherited from the server environment from begin used for making streaming replication connections.<br />
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg01011.php <nowiki>Re: Parameter name standby_mode</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
| Change walsender so that it applies per-role settings<br />
* http://archives.postgresql.org/pgsql-hackers/2010-09/msg00642.php<br />
}}<br />
<br />
{{TodoItem<br />
| Restructure configuration parameters for standby mode<br />
* http://archives.postgresql.org/pgsql-hackers/2010-09/msg01820.php<br />
}}<br />
<br />
{{TodoItemf<br />
| Allow time-delayed application of logs on the standby<br />
* http://archives.postgresql.org/pgsql-hackers/2011-04/msg00992.php<br />
}}<br />
<br />
{{TodoItem<br />
| Add -X parameter to pg_basebackup to specify a different directory for px_xlog, like initdb<br />
}}<br />
<br />
{{TodoItem<br />
| Add a new "eager" synchronous mode that starts out synchronous but reverts to asynchronous after a failure timeout period<br />
|This would require some type of command to be executed to alert administrators of this change.<br />
* http://archives.postgresql.org/pgsql-hackers/2011-12/msg01224.php<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
== Data Types ==<br />
<br />
{{TodoItem<br />
|Fix data types where equality comparison is not intuitive, e.g. box<br />
* http://archives.postgresql.org/pgsql-hackers/2011-10/msg01643.php<br />
}}<br />
<br />
{{TodoItem<br />
|Add support for public SYNONYMs<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-03/msg00519.php <nowiki>Proposal for SYNONYMS</nowiki>]<br />
* http://archives.postgresql.org/pgsql-hackers/2010-11/msg02043.php<br />
* http://archives.postgresql.org/pgsql-general/2010-12/msg00139.php<br />
}}<br />
<br />
{{TodoItem<br />
|Add support for SQL-standard GENERATED/IDENTITY columns<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-07/msg00543.php <nowiki>Re: Three weeks left until feature freeze</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-08/msg00038.php <nowiki>GENERATED ... AS IDENTITY, Was: Re: Feature Freeze</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-05/msg00344.php <nowiki>Behavior of GENERATED columns per SQL2003</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-patches/2007-05/msg00076.php <nowiki>Re: [HACKERS] Behavior of GENERATED columns per SQL2003</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-02/msg00604.php <nowiki>IDENTITY/GENERATED patch</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider placing all sequences in a single table, or create a system view<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-03/msg00008.php <nowiki>Re: newbie: renaming sequences task</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2012-02/msg00258.php Removing special case OID generation]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider a special data type for regular expressions<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-08/msg01067.php <nowiki>Why is there a tsquery data type?</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow renaming and deleting enumerated values from an existing enumerated data type<br />
}}<br />
<br />
{{TodoItem<br />
|Support scoped IPv6 addresses in the inet type<br />
* [http://archives.postgresql.org/pgsql-bugs/2007-05/msg00111.php <nowiki>strange problem with ip6</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Considering improving performance of computing CHAR() value lengths<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-06/msg00900.php <nowiki>char() overhead on read-only workloads not so insignifcant as the docs claim it is...</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg01787.php <nowiki>Re: [PATCH] backend: compare word-at-a-time in bcTruelen</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add overlaps geometric operators that ignore point overlaps<br />
* http://archives.postgresql.org/pgsql-hackers/2010-03/msg00861.php<br />
}}<br />
<br />
{{TodoItem<br />
|Remove or improve rounding in geometric comparison operators<br />
* http://archives.postgresql.org/message-id/9804.1346187849@sss.pgh.pa.us<br />
}}<br />
<br />
{{TodoItem<br />
| Add IMMUTABLE column attribute<br />
* http://archives.postgresql.org/pgsql-hackers/2011-11/msg00623.php<br />
}}<br />
<br />
=== Domains ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Allow functions defined as casts to domains to be called during casting<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-05/msg00072.php <nowiki>bug? non working casts for domain</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-09/msg01681.php <nowiki>TODO: Fix CREATE CAST on DOMAINs</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow values to be cast to domain types<br />
* [http://archives.postgresql.org/pgsql-hackers/2003-06/msg01206.php <nowiki>Domain casting still doesn't work right</nowiki>] <br />
* [http://archives.postgresql.org/pgsql-hackers/2007-08/msg00289.php <nowiki>domain casting?</nowiki>]<br />
* http://archives.postgresql.org/pgsql-hackers/2011-05/msg00812.php<br />
}}<br />
<br />
{{TodoItem<br />
|Make domains work better with polymorphic functions<br />
* [http://archives.postgresql.org/message-id/4887.1228700773@sss.pgh.pa.us Polymorphic types vs. domains]<br />
* [http://archives.postgresql.org/message-id/15535.1238774571@sss.pgh.pa.us some difficulties with fixing it]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Dates and Times ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Allow infinite intervals just like infinite timestamps<br />
* http://archives.postgresql.org/pgsql-hackers/2011-11/msg00076.php<br />
}}<br />
<br />
{{TodoItem<br />
|Determine how to represent date/time field extraction on infinite timestamps<br />
* [http://archives.postgresql.org/message-id/CA+mi_8bda-Fnev9iXeUbnqhVaCWzbYhHkWoxPQfBca9eDPpRMw@mail.gmail.com extract(epoch from infinity) is not 0]<br />
* [http://archives.postgresql.org/message-id/CADAkt-icuESH16uLOCXbR-dKpcvwtUJE4JWXnkdAjAAwP6j12g@mail.gmail.com converting between infinity timestamp and float8]<br />
}}<br />
<br />
<br />
{{TodoItem<br />
|Allow TIMESTAMP WITH TIME ZONE to store the original timezone information, either zone name or offset from UTC<br />
|If the TIMESTAMP value is stored with a time zone name, interval computations should adjust based on the time zone rules. <br />
* [http://archives.postgresql.org/pgsql-hackers/2004-10/msg00705.php <nowiki>timestamp with time zone a la sql99</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Have timestamp subtraction not call justify_hours()?<br />
* [http://archives.postgresql.org/pgsql-sql/2006-10/msg00059.php <nowiki>timestamp subtraction (was Re: formatting intervals with to_char)</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve TIMESTAMP WITH TIME ZONE subtraction to be DST-aware<br />
|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.}}<br />
<br />
{{TodoItem<br />
|Fix interval display to support values exceeding 2^31 hours}}<br />
<br />
{{TodoItem<br />
|Add overflow checking to timestamp and interval arithmetic}}<br />
<br />
{{TodoItem<br />
|Add function to allow the creation of timestamps using parameters<br />
* http://archives.postgresql.org/pgsql-performance/2010-06/msg00232.php<br />
}}<br />
<br />
{{TodoItem<br />
|Allow a comma to denote fractional seconds in ISO-8601-compliant times (and timestamps)<br />
* http://www.postgresql.org/message-id/7D5AC9AB-238D-4FE7-8857-18D98190A4D9@justatheory.com<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Arrays ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Add support for arrays of domains<br />
* [http://archives.postgresql.org/pgsql-patches/2007-05/msg00114.php <nowiki>Re: updated WIP: arrays of composites</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow single-byte header storage for array elements}}<br />
<br />
{{TodoItem<br />
|Add function to detect if an array is empty<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-11/msg00475.php <nowiki>Re: array_length()</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve handling of NULLs in arrays<br />
* [http://archives.postgresql.org/pgsql-bugs/2008-11/msg00009.php <nowiki>BUG #4509: array_cat's null behaviour is inconsistent</nowiki>]<br />
* http://archives.postgresql.org/pgsql-hackers/2010-11/msg01040.php<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Binary Data ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Improve vacuum of large objects, like contrib/vacuumlo?}}<br />
<br />
{{TodoItem<br />
|Auto-delete large objects when referencing row is deleted<br />
|contrib/lo offers this functionality.}}<br />
<br />
{{TodoItem<br />
|Allow read/write into TOAST values like large objects<br />
|Writing might require the TOAST column to be stored EXTERNAL.<br />
* http://archives.postgresql.org/pgsql-hackers/2011-06/msg00049.php<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== MONEY Data Type ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Add locale-aware MONEY type, and support multiple currencies<br />
* [http://archives.postgresql.org/pgsql-general/2005-08/msg01432.php <nowiki>A real currency type</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-03/msg01181.php <nowiki>Money type todos?</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|MONEY dumps in a locale-specific format making it difficult to restore to a system with a different locale}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Text Search ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Allow dictionaries to change the token that is passed on to later dictionaries<br />
* [http://archives.postgresql.org/pgsql-patches/2007-11/msg00081.php <nowiki>a tsearch2 (8.2.4) dictionary that only filters out stopwords</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Exact phrase search, <br />
* [http://www.sai.msu.su/~megera/wiki/2009-08-12 <nowiki>Algebra for full-text queries</nowiki>]<br />
* [http://www.sai.msu.su/~megera/postgres/talks/2009.pdf <nowiki>Some recent advances in<br />
full-text search</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider a function-based API for '@@' searches<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-11/msg00511.php <nowiki>Some recent advances in<br />
full-text search</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve text search error messages<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg00966.php <nowiki>Poorly designed tsearch NOTICEs</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-11/msg01146.php <nowiki>Re: Poorly designed tsearch NOTICEs</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider changing error to warning for strings larger than one megabyte<br />
* [http://archives.postgresql.org/pgsql-bugs/2008-02/msg00190.php <nowiki>BUG #3975: tsearch2 index should not bomb out of 1Mb limit</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-patches/2008-03/msg00062.php <nowiki>Re: [BUGS] BUG #3975: tsearch2 index should not bomb out of 1Mb limit</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|tsearch and tsdicts regression tests fail in Turkish locale on glibc<br />
* [http://archives.postgresql.org/message-id/49749645.5070801@gmx.net tsearch with Turkish locale]<br />
}}<br />
<br />
{{TodoItem<br />
|tsquery negator operator treated as part of lexeme<br />
* [http://archives.postgresql.org/pgsql-bugs/2009-06/msg00346.php BUG #4887: inclusion operator (@>) on tsqeries behaves not conforming to documentation]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve handling of dash and plus signs in email address user names, and perhaps improve URL parsing<br />
* http://archives.postgresql.org/pgsql-hackers/2010-10/msg00772.php<br />
* [http://archives.postgresql.org/message-id/E1Ri8il-0008Ct-9p@wrigleys.postgresql.org tsearch does not recognize all valid emails]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve default parser, to more easily allow adding new tokens<br />
* http://archives.postgresql.org/message-id/23485.1297727826@sss.pgh.pa.us<br />
}}<br />
<br />
{{TodoItem<br />
|Add additional support functions<br />
* http://archives.postgresql.org/pgsql-hackers/2011-06/msg00319.php<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== XML ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Allow XML arrays to be cast to other data types<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-09/msg00981.php <nowiki>proposal casting from XML[] to int[], numeric[], text[]</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg00231.php <nowiki>Re: proposal casting from XML[] to int[], numeric[], text[]</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-11/msg00471.php <nowiki>Re: proposal casting from XML[] to int[], numeric[], text[]</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add XML Schema validation and xmlvalidate functions (SQL:2008)}}<br />
<br />
{{TodoItem<br />
|Add xmlvalidatedtd variant to support validating against a DTD?}}<br />
<br />
{{TodoItem<br />
|Relax-NG validation; libxml2 supports this already}}<br />
<br />
{{TodoItem<br />
|Allow reliable XML operation non-UTF8 server encodings (xpath(), in particular, is known to not work)<br />
* [http://archives.postgresql.org/pgsql-bugs/2009-01/msg00135.php <nowiki>BUG #4622: xpath only work in utf-8 server encoding</nowiki>] <br />
* http://archives.postgresql.org/message-id/4110.1238973350@sss.pgh.pa.us}}<br />
<br />
{{TodoItem<br />
|Add functions from SQL:2006: XMLDOCUMENT, XMLCAST, XMLTEXT}}<br />
<br />
{{TodoItem<br />
|Add XMLNAMESPACES support in XMLELEMENT and elsewhere}}<br />
<br />
{{TodoItem<br />
|Move XSLT from contrib/xml2 to a more reasonable location<br />
* http://archives.postgresql.org/pgsql-hackers/2010-08/msg00539.php<br />
}}<br />
<br />
{{TodoItem<br />
|Report errors returned by the XSLT library<br />
* http://archives.postgresql.org/pgsql-hackers/2010-08/msg00562.php<br />
}}<br />
<br />
{{TodoItem<br />
|Improve the XSLT parameter passing API<br />
* http://archives.postgresql.org/pgsql-hackers/2010-08/msg00416.php<br />
}}<br />
<br />
{{TodoItem<br />
|XML Canonical: Convert XML documents to canonical form to compare them. libxml2 has support for this.}}<br />
<br />
{{TodoItem<br />
|Add pretty-printed XML output option<br />
|Parse a document and serialize it back in some indented form. libxml2 might support this.}}<br />
<br />
{{TodoItem<br />
|Add XMLQUERY (from the SQL/XML standard)}}<br />
<br />
{{TodoItem<br />
|Allow XML shredding<br />
|In some cases shredding could be better option (if there is no need to keep XML docs entirely, e.g. if we have already developed tools that understand only relational data. This would be a separate module that implements annotated schema decomposition technique, similar to DB2 and SQL Server functionality.}}<br />
<br />
{{TodoItem<br />
|Fix Nested or repeated xpath() that apparently mess up namespaces [http://archives.postgresql.org/pgsql-bugs/2008-03/msg00097.php] [http://archives.postgresql.org/pgsql-bugs/2008-03/msg00144.php] [http://archives.postgresql.org/pgsql-general/2008-03/msg00295.php] [http://archives.postgresql.org/pgsql-bugs/2008-07/msg00054.php] [http://archives.postgresql.org/message-id/004f01c90e91$138e9d10$3aabd730$@anstett@iaas.uni-stuttgart.de]}}<br />
<br />
{{TodoItem<br />
|XPath: Adding the <x> at the root causes problems [http://archives.postgresql.org/pgsql-bugs/2008-05/msg00184.php] [http://archives.postgresql.org/pgsql-bugs/2008-07/msg00054.php] [http://archives.postgresql.org/pgsql-general/2008-07/msg00613.php]}}<br />
<br />
{{TodoItem<br />
|xpath_table needs to be implemented/implementable to get rid of contrib/xml2 [http://archives.postgresql.org/pgsql-general/2008-05/msg00823.php]}}<br />
<br />
{{TodoItem<br />
|xpath_table is pretty broken anyway [http://archives.postgresql.org/pgsql-hackers/2010-02/msg02424.php]}}<br />
<br />
{{TodoItem<br />
|better handling of XPath data types [http://archives.postgresql.org/pgsql-hackers/2008-06/msg00616.php] [http://archives.postgresql.org/message-id/004a01c90e90$4b986d90$e2c948b0$@anstett@iaas.uni-stuttgart.de]}}<br />
<br />
{{TodoItem<br />
|Improve handling of PIs and DTDs in xmlconcat() [http://archives.postgresql.org/message-id/200904211211.n3LCB09p008988@wwwmaster.postgresql.org]}}<br />
<br />
{{TodoItem<br />
|Restructure XML and /contrib/xml2 functionality<br />
* http://archives.postgresql.org/pgsql-hackers/2011-02/msg02314.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-03/msg00017.php<br />
}}<br />
<br />
{{TodoItem<br />
|Verify Xpath escaping behavior<br />
* [http://www.postgresql.org/message-id/E1VOXZv-0008Q9-0Z@wrigleys.postgresql.org Xpath behaviour unintuitive / arguably wrong]<br />
* [http://www.postgresql.org/message-id/CAAY5AM1L83y79rtOZAUJioREO6n4%3DXAFKcGu6qO3hCZE1yJytg@mail.gmail.com xpath missing entity decoding - bug or feature]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
== Functions ==<br />
<br />
{{TodoItem<br />
|Allow INET subnet comparisons using non-constants to be indexed}}<br />
<br />
{{TodoItem<br />
|Add an INET overlaps operator, for use by exclusion constraints <br />
* http://archives.postgresql.org/pgsql-hackers/2010-03/msg00845.php<br />
}}<br />
<br />
{{TodoItem<br />
|Enforce typmod for function inputs, function results and parameters for spi_prepare'd statements called from PLs<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-01/msg01403.php <nowiki>Re: BUG #2917: spi_prepare doesn't accept typename aliases</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-11/msg01160.php <nowiki>RFC for adding typmods to functions</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Fix IS OF so it matches the ISO specification, and add documentation<br />
* [http://archives.postgresql.org/pgsql-patches/2003-08/msg00060.php <nowiki>Re: [HACKERS] IS OF</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-02/msg00060.php <nowiki>ToDo: add documentation for operator IS OF</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Implement Boyer-Moore searching in LIKE queries<br />
* {{messageLink|27645.1220635769@sss.pgh.pa.us|TODO item: Implement Boyer-Moore searching (First time hacker)}}<br />
}}<br />
<br />
{{TodoItem<br />
|Prevent malicious functions from being executed with the permissions of unsuspecting users<br />
|Index functions are safe, so VACUUM and ANALYZE are safe too. Triggers, CHECK and DEFAULT expressions, and rules are still vulnerable. <br />
* [http://archives.postgresql.org/pgsql-hackers/2008-01/msg00268.php <nowiki>Some notes about the index-functions security vulnerability</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Reduce memory usage of aggregates in set returning functions<br />
* [http://archives.postgresql.org/pgsql-performance/2008-01/msg00031.php <nowiki>Re: Performance of aggregates over set-returning functions</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Fix /contrib/ltree operator<br />
* [http://archives.postgresql.org/pgsql-bugs/2007-11/msg00044.php <nowiki>BUG #3720: wrong results at using ltree</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Fix /contrib/btree_gist's implementation of inet indexing<br />
* [http://archives.postgresql.org/pgsql-bugs/2010-10/msg00099.php <nowiki>BUG #5705: btree_gist: Index on inet changes query result</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|<nowiki>Fix inconsistent precedence of =, &gt;, and &lt; compared to &lt;&gt;, &gt;=, and &lt;=</nowiki><br />
* [http://archives.postgresql.org/pgsql-bugs/2007-12/msg00145.php <nowiki>BUG #3822: Nonstandard precedence for comparison operators</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Fix regular expression bug when using complex back-references<br />
* [http://archives.postgresql.org/pgsql-bugs/2007-10/msg00000.php <nowiki>BUG #3645: regular expression back references seem broken</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Have /contrib/dblink reuse unnamed connections<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg00895.php <nowiki>dblink un-named connection doesn't get re-used</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve formatting of pg_get_viewdef() output<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-01/msg01648.php <nowiki>pg_get_viewdef formattiing</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-08/msg01885.php <nowiki>Re: pretty print viewdefs</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2011-12/msg00906.php reprise: pretty print viewdefs]<br />
}}<br />
<br />
{{TodoItem<br />
|Add function to dump pg_depend information cleanly<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-09/msg00226.php <nowiki>Elementary dependency look-up</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add function to allow easier transaction id comparisons<br />
* http://archives.postgresql.org/pgsql-hackers/2011-11/msg00786.php<br />
}}<br />
<br />
=== Character Formatting ===<br />
<br />
{{TodoSubsection}}<br />
{{TodoItem<br />
|Allow to_date() and to_timestamp() to accept localized month names}}<br />
<br />
{{TodoItem<br />
|Add missing parameter handling in to_char()<br />
* [http://archives.postgresql.org/pgsql-hackers/2005-12/msg00948.php <nowiki>Re: to_char and i18n</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Throw an error from to_char() instead of printing a string of "#" when a number doesn't fit in the desired output format.<br />
* discussed in [http://archives.postgresql.org/message-id/37ed240d0907290836w42187222n18664dfcbcb445b1@mail.gmail.com "to_char, support for EEEE format"]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow to_char() on interval values to accumulate the highest unit requested<br />
|2= 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.<br />
* to_char(INTERVAL '1 hour 5 minutes', 'MI') =&gt; 65<br />
* to_char(INTERVAL '43 hours 20 minutes', 'MI' ) =&gt; 2600<br />
* to_char(INTERVAL '43 hours 20 minutes', 'WK:DD:HR:MI') =&gt; 0:1:19:20<br />
* to_char(INTERVAL '3 years 5 months','MM') =&gt; 41<br />
}}<br />
<br />
{{TodoItem<br />
|Fix to_number() handling for values not matching the format string<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-09/msg01447.php <nowiki>Re: numeric_to_number() function skipping some digits</nowiki>]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
== Multi-Language Support ==<br />
<br />
{{TodoItem<br />
|Add NCHAR (as distinguished from ordinary varchar)<br />
* [http://www.postgresql.org/message-id/A756FAD7EDC2E24F8CAB7E2F3B5375E918B12BC0@FALEX03.au.fjanz.com UTF8 national character data type support WIP patch and list of open issues.]<br />
}}<br />
<br />
{{TodoItem<br />
|Add a cares-about-collation column to pg_proc, so that unresolved-collation errors can be thrown at parse time<br />
* [http://archives.postgresql.org/pgsql-hackers/2011-03/msg01520.php <nowiki>Open issues for collations</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Integrate collations with text search configurations<br />
* [http://archives.postgresql.org/message-id/28887.1303579034@sss.pgh.pa.us <nowiki>Some TODO items for collations</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Integrate collations with to_char() and related functions<br />
* [http://archives.postgresql.org/message-id/28887.1303579034@sss.pgh.pa.us <nowiki>Some TODO items for collations</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Support collation-sensitive equality and hashing functions<br />
* [http://archives.postgresql.org/pgsql-hackers/2011-06/msg00472.php <nowiki> contrib/citext versus collations</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add a LOCALE option to CREATE DATABASE, as a shorthand<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-04/msg00119.php <nowiki> Re: 8.4 open items list</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Support multiple simultaneous character sets, per SQL:2008}}<br />
<br />
{{TodoItem<br />
|Improve UTF8 combined character handling?}}<br />
<br />
{{TodoItem<br />
|Add octet_length_server() and octet_length_client()}}<br />
<br />
{{TodoItem<br />
|Make octet_length_client() the same as octet_length()?}}<br />
<br />
{{TodoItem<br />
|Fix problems with wrong runtime encoding conversion for NLS message files}}<br />
<br />
{{TodoItem<br />
|Add URL to more complete multi-byte regression tests<br />
* [http://archives.postgresql.org/pgsql-hackers/2005-07/msg00272.php <nowiki>Multi-byte and client side character encoding tests for copy command..</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Fix contrib/fuzzystrmatch to work with multibyte encodings<br />
* [http://archives.postgresql.org/pgsql-bugs/2009-04/msg00047.php <nowiki> soundex function returns UTF-16 characters</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2010-04/msg00138.php <nowiki> dmetaphone woes</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Change memory allocation for multi-byte functions so memory is allocated inside conversion functions<br />
|Currently we preallocate memory based on worst-case usage.}}<br />
<br />
{{TodoItem<br />
|Add ability to use case-insensitive regular expressions on multi-byte characters<br />
|Currently it works for UTF-8, but not other multi-byte encodings<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-12/msg00433.php <nowiki>Regexps vs. locale</nowiki>]<br />
* {{MessageLink|20091201210024.B1393753FB7@cvs.postgresql.org|A partial solution for UTF-8}}<br />
}}<br />
<br />
{{TodoItem<br />
|Improve encoding of connection startup messages sent to the client<br />
|Currently some authentication error messages are sent in the server encoding<br />
* [http://archives.postgresql.org/pgsql-general/2008-12/msg00801.php <nowiki>encoding of PostgreSQL messages</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-general/2009-01/msg00005.php <nowiki>Re: encoding of PostgreSQL messages</nowiki>]<br />
* [http://www.postgresql.org/message-id/20131220030725.GA1411150@tornado.leadboat.com multibyte messages are displayed incorrectly on the client]<br />
}}<br />
<br />
{{TodoItem<br />
|More sensible support for Unicode combining characters, normal forms<br />
* http://archives.postgresql.org/message-id/200904141532.44618.peter_e@gmx.net<br />
}}<br />
<br />
== Views and Rules ==<br />
<br />
{{TodoItemDone<br />
|Add the functionality of the WITH CHECK OPTION clause to CREATE VIEW<br />
}}<br />
{{TodoItem<br />
|Allow VIEW/RULE recompilation when the underlying tables change<br />
|This is both difficult and controversial.<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-12/msg01723.php Re: About "Allow VIEW/RULE recompilation when the underlying tables change"]<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-12/msg01724.php Re: About "Allow VIEW/RULE recompilation when the underlying tables change2"]<br />
* [http://archives.postgresql.org/message-id/CACk%3DU9NFSzWrEba8G5dZ%3DTZLy3_hx3QXGyCcKVWT%3D4iA1FjMuA@mail.gmail.com VIEW still referring to old name of field]<br />
}}<br />
{{TodoItem<br />
|Make it possible to use RETURNING together with conditional DO INSTEAD rules, such as for partitioning setups<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-09/msg00577.php <nowiki>RETURNING and DO INSTEAD ... Intentional or not?</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve ability to modify views via ALTER TABLE<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-05/msg00691.php <nowiki>Re: idea: storing view source in system catalogs</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg01410.php <nowiki>modifying views</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-08/msg00300.php <nowiki>Re: patch: Add columns via CREATE OR REPLACE VIEW</nowiki>]<br />
}}<br />
<br />
== SQL Commands ==<br />
<br />
{{TodoItem<br />
|Add CORRESPONDING BY to UNION/INTERSECT/EXCEPT<br />
* [http://dissipatedheat.com/2011/11/10/how-not-to-write-a-patch-for-postgresql/ How not to write this patch.]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve type determination of unknown (NULL or quoted literal) result columns for UNION/INTERSECT/EXCEPT<br />
* [http://archives.postgresql.org/message-id/9799.1302719551@sss.pgh.pa.us <nowiki>UNION construct type cast gives poor error message</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add ROLLUP, CUBE, GROUPING SETS options to GROUP BY<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-10/msg00838.php <nowiki>WIP: grouping sets support</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-05/msg00466.php <nowiki>Implementation of GROUPING SETS (T431: Extended grouping capabilities)</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow prepared transactions with temporary tables created and dropped in the same transaction, and when an ON COMMIT DELETE ROWS temporary table is accessed<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-03/msg00047.php <nowiki>Re: &quot;could not open relation 1663/16384/16584: No such file or directory&quot; in a specific combination of transactions with temp tables</nowiki>]<br />
* [http://archives.postgresql.org/message-id/492543D5.9050904@enterprisedb.com A suggestion on how to implement this]<br />
}}<br />
<br />
{{TodoItem<br />
|Add a GUC variable to warn about non-standard SQL usage in queries}}<br />
<br />
{{TodoItem<br />
|Add SQL-standard MERGE/REPLACE/UPSERT command<br />
|MERGE is typically used to merge two tables. REPLACE or UPSERT command does UPDATE, or on failure, INSERT. See [[SQL MERGE]] for notes on the implementation details.<br />
}}<br />
<br />
{{TodoItem<br />
|Add NOVICE output level for helpful messages<br />
|For example, have it warn about unjoined tables. This could also control automatic sequence/index creation messages.<br />
}}<br />
<br />
{{TodoItem<br />
|Allow NOTIFY in rules involving conditionals}}<br />
<br />
{{TodoItem<br />
|Allow LISTEN on patterns<br />
* http://www.postgresql.org/message-id/52693FC5.7070507@gmail.com<br />
}}<br />
<br />
{{TodoItem<br />
|Allow EXPLAIN to identify tables that were skipped because of constraint_exclusion<br />
}}<br />
<br />
{{TodoItem<br />
|Simplify dropping roles that have objects in several databases}}<br />
<br />
{{TodoItem<br />
|Allow the count returned by SELECT, etc to be represented as an int64 to allow a higher range of values}}<br />
<br />
{{TodoItem<br />
|Add support for WITH RECURSIVE ... CYCLE<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-10/msg00291.php <nowiki>WITH RECURSIVE ... CYCLE in vanilla SQL: issues with arrays of rows</nowiki>]}}<br />
<br />
{{TodoItem<br />
|Add DEFAULT .. AS OWNER so permission checks are done as the table owner<br />
|This would be useful for SERIAL nextval() calls and CHECK constraints.}}<br />
<br />
{{TodoItem<br />
|Allow DISTINCT to work in multiple-argument aggregate calls}}<br />
<br />
{{TodoItem<br />
|Add comments on system tables/columns using the information in catalogs.sgml<br />
|Ideally the information would be pulled from the SGML file automatically.}}<br />
<br />
{{TodoItem<br />
|Prevent the specification of conflicting transaction read/write options<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-01/msg00684.php <nowiki>Re: SET TRANSACTION and SQL Standard</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow DELETE and UPDATE to be used with LIMIT and ORDER BY<br />
* http://archives.postgresql.org/pgadmin-hackers/2010-04/msg00078.php<br />
* http://archives.postgresql.org/pgsql-hackers/2010-11/msg01997.php<br />
* http://archives.postgresql.org/pgsql-hackers/2010-12/msg00021.php<br />
}}<br />
<br />
{{TodoItem<br />
|Allow PREPARE of cursors}}<br />
<br />
{{TodoItem<br />
|Have DISCARD PLANS discard plans cached by functions<br />
|DISCARD all should do the same.<br />
* http://archives.postgresql.org/pgsql-hackers/2011-01/msg00431.php<br />
}}<br />
<br />
{{TodoItem<br />
|Avoid multiple-evaluation of BETWEEN and IN arguments containing volatile expressions<br />
* http://archives.postgresql.org/message-id/4D95B605.2020709@enterprisedb.com<br />
}}<br />
<br />
{{TodoItem<br />
|Fix nested CASE-WHEN constructs<br />
* http://archives.postgresql.org/message-id/4DDCEEB8.50602@enterprisedb.com<br />
}}<br />
<br />
{{TodoItem<br />
|IS NULL testing of nested ROW() values is inconsistent<br />
* http://www.postgresql.org/message-id/50B3D11F.20408@2ndQuadrant.com<br />
}}<br />
<br />
=== CREATE ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Allow CREATE TABLE AS to determine column lengths for complex expressions like SELECT col1 || col2}}<br />
<br />
{{TodoItem<br />
|Have WITH CONSTRAINTS also create constraint indexes<br />
* [http://archives.postgresql.org/pgsql-patches/2007-04/msg00149.php <nowiki>Re: CREATE TABLE LIKE INCLUDING INDEXES support</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Move NOT NULL constraint information to pg_constraint<br />
|Currently NOT NULL constraints are stored in pg_attribute without any designation of their origins, e.g. primary keys. One manifest problem is that dropping a PRIMARY KEY constraint does not remove the NOT NULL constraint designation. Another issue is that we should probably force NOT NULL to be propagated from parent tables to children, just as CHECK constraints are. (But then does dropping PRIMARY KEY affect children?)<br />
* http://archives.postgresql.org/message-id/19768.1238680878@sss.pgh.pa.us<br />
* http://archives.postgresql.org/message-id/200909181005.n8IA5Ris061239@wwwmaster.postgresql.org<br />
* http://archives.postgresql.org/pgsql-hackers/2011-07/msg01223.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-07/msg00358.php<br />
}}<br />
<br />
{{TodoItem<br />
|Prevent concurrent CREATE TABLE from sometimes returning a cryptic error message<br />
* [http://archives.postgresql.org/pgsql-bugs/2007-10/msg00169.php <nowiki>BUG #3692: Conflicting create table statements throw unexpected error</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add CREATE SCHEMA ... LIKE that copies a schema}}<br />
<br />
{{TodoItem<br />
|Fix CREATE OR REPLACE FUNCTION to not leave objects depending on the function in inconsistent state<br />
* [http://archives.postgresql.org/pgsql-general/2008-08/msg00985.php indexes on functions and create or replace function]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow temporary tables to exist as empty by default in all sessions<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-07/msg00006.php <nowiki>what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-04/msg01329.php <nowiki>idea: global temp tables</nowiki>]<br />
* [http://archives.postgresql.org//pgsql-hackers/2009-05/msg00016.php <nowiki>Re: idea: global temp tables</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2010-04/msg01098.php <nowiki>global temporary tables</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2012-04/msg01148.php Temporary tables under hot standby]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow the creation of "distinct" types<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-10/msg01647.php <nowiki>Distinct types</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider analyzing temporary tables when they are first used in a query<br />
|Autovacuum cannot analyze or vacuum temporary tables.<br />
* [http://archives.postgresql.org/pgsql-hackers/2010-04/msg00416.php <nowiki>autovacuum and temp tables support</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow an unlogged table to be changed to logged<br />
* http://archives.postgresql.org/pgsql-hackers/2011-01/msg00315.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-04/msg00437.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-05/msg00323.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-06/msg00237.php<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== UPDATE ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|<nowiki>Allow UPDATE tab SET ROW (col, ...) = (SELECT...)</nowiki><br />
* [http://archives.postgresql.org/pgsql-hackers/2006-07/msg01308.php <nowiki>Re: [PATCHES] extension for sql update</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-03/msg00865.php <nowiki>UPDATE using sub selects</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-patches/2007-04/msg00315.php <nowiki>UPDATE using sub selects</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-patches/2008-03/msg00237.php <nowiki>Re: UPDATE using sub selects</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Research self-referential UPDATEs that see inconsistent row versions in read-committed mode<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-05/msg00507.php <nowiki>Concurrently updating an updatable view</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-06/msg00016.php <nowiki>Re: Do we need a TODO? (was Re: Concurrently updating anupdatable view)</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve performance of EvalPlanQual mechanism that rechecks already-updated rows<br />
|This is related to the previous item, which questions whether it even has the right semantics<br />
* [http://archives.postgresql.org/pgsql-bugs/2008-09/msg00045.php <nowiki>BUG #4401: concurrent updates to a table blocks one update indefinitely</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-bugs/2009-07/msg00302.php <nowiki>BUG #4945: Parallel update(s) gone wild</nowiki>]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== ALTER ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Have ALTER TABLE RENAME of a SERIAL column rename the sequence<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-03/msg00008.php <nowiki>Re: newbie: renaming sequences task</nowiki>]<br />
* [http://archives.postgresql.org/message-id/CADLWmXUV4LbLhMZL8rYMhCy72aZZLB5BSARPQVgoX0BrxA0FFg@mail.gmail.com renaming implicit sequences]<br />
}}<br />
<br />
{{TodoItem<br />
|Have ALTER SEQUENCE RENAME rename the sequence name stored in the sequence table<br />
* [http://archives.postgresql.org/pgsql-bugs/2007-09/msg00092.php <nowiki>BUG #3619: Renaming sequence does not update its 'sequence_name' field</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-bugs/2007-10/msg00007.php <nowiki>Re: BUG #3619: Renaming sequence does not update its 'sequence_name' field</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-03/msg00008.php <nowiki>Re: newbie: renaming sequences task</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add ALTER DOMAIN to modify the underlying data type}}<br />
<br />
{{TodoItem<br />
|Allow ALTER TABLESPACE to move the tablespace to different directories}}<br />
<br />
{{TodoItem<br />
|Allow moving system tables to other tablespaces, where possible<br />
|Currently non-global system tables must be in the default database tablespace. Global system tables can never be moved.}}<br />
<br />
{{TodoItem<br />
|Have ALTER INDEX update the name of a constraint using that index}}<br />
<br />
{{TodoItem<br />
|Allow column display reordering by recording a display, storage, and permanent id for every column?<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-12/msg00782.php <nowiki>Re: column ordering, was Re: [PATCHES] Enums patch v2</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-11/msg01029.php <nowiki>Column reordering in pg_dump</nowiki>]<br />
* http://archives.postgresql.org/message-id/1324412114-sup-9608@alvh.no-ip.org<br />
* [http://www.postgresql.org/message-id/CAApHDvqhnuznxd4xVMFDcGn+nHVYyUtJ-TvbRsOuR%3DPaVbbGqw@mail.gmail.com logical column order and physical column order]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow deactivating (and reactivating) indexes via ALTER TABLE<br />
* http://archives.postgresql.org/pgsql-hackers/2010-12/msg01191.php<br />
}}<br />
<br />
{{TodoItem<br />
|Add ALTER OPERATOR ... RENAME<br />
|needs to consider effects of changing operator precedence<br />
* [http://archives.postgresql.org/message-id/1322948781.26266.9.camel@vanquo.pezone.net Missing rename support]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== CLUSTER ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Automatically maintain clustering on a table<br />
|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.<br />
* [http://archives.postgresql.org/pgsql-performance/2004-08/msg00350.php <nowiki>Equivalent praxis to CLUSTERED INDEX?</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-03/msg00155.php <nowiki>Re: Grouped Index Tuples</nowiki>]<br />
* http://community.enterprisedb.com/git/<br />
* [http://archives.postgresql.org/pgsql-performance/2009-10/msg00346.php <nowiki>Re: maintain_cluster_order_v5.patch</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
| Allow CLUSTER to be used on a partial index<br />
* http://www.postgresql.org/message-id/CAMkU%3D1zYwoHHsqJ8wfK3GdG_t_a6t4RK-GFDSKymQ0EGP%3DtypA@mail.gmail.com<br />
}} <br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== COPY ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Allow COPY to report error lines and continue<br />
|This requires the use of a savepoint before each COPY line is processed, with ROLLBACK on COPY failure. <br />
* [http://archives.postgresql.org/pgsql-hackers/2007-12/msg00572.php <nowiki>Re: VLDB Features</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow COPY FROM to create index entries in bulk<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-02/msg00811.php <nowiki>Batch update of indexes on data loading</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow COPY in CSV mode to control whether a quoted zero-length string is treated as NULL<br />
|Currently this is always treated as a zero-length string, which generates an error when loading into an integer column <br />
* [http://archives.postgresql.org/pgsql-hackers/2007-07/msg00905.php <nowiki>Re: [PATCHES] allow CSV quote in NULL</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve COPY performance<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-02/msg00954.php <nowiki>Re: 8.3 / 8.2.6 restore comparison</nowiki>]<br />
* http://archives.postgresql.org/pgsql-hackers/2010-08/msg01882.php<br />
}}<br />
<br />
{{TodoItem<br />
|Allow COPY to report errors sooner<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg01169.php <nowiki>Timely reporting of COPY errors</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow COPY to handle other number formats<br />
|E.g. the German notation. Best would be something like WITH DECIMAL ','.<br />
}}<br />
<br />
{{TodoItem<br />
|Allow a stalled COPY to exit if the backend is terminated<br />
* [http://archives.postgresql.org/pgsql-bugs/2009-04/msg00067.php <nowiki>Re: possible bug not in open items</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow COPY "text" format to output a header<br />
* http://www.postgresql.org/message-id/CACfv+pJ31tesLvncJyP24quo8AE+M0GP6p6MEpwPv6yV8%3DsVHQ@mail.gmail.com<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== GRANT/REVOKE ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Allow SERIAL sequences to inherit permissions from the base table?}}<br />
<br />
{{TodoItem<br />
|Allow dropping of a role that has connection rights<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-05/msg00736.php <nowiki>DROP ROLE dependency tracking ...</nowiki>]<br />
}}<br />
{{TodoEndSubsection}}<br />
<br />
=== DECLARE CURSOR ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Prevent DROP TABLE from dropping a table referenced by its own open cursor?}}<br />
<br />
{{TodoItem<br />
|Provide some guarantees about the behavior of cursors that invoke volatile functions<br />
* [http://archives.postgresql.org/message-id/20997.1244563664@sss.pgh.pa.us Re: Cursor with hold emits the same row more than once across commits in 8.3.7]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== INSERT ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Allow INSERT/UPDATE of the system-generated oid value for a row}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== SHOW/SET ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM ANALYZE, and CLUSTER}}<br />
<br />
{{TodoItem<br />
|Rationalize the discrepancy between settings that use values in bytes and SHOW that returns the object count<br />
* [http://archives.postgresql.org/pgsql-docs/2008-07/msg00007.php <nowiki>Re: [ADMIN] shared_buffers and shmmax</nowiki>]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== ANALYZE ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Have EXPLAIN ANALYZE issue NOTICE messages when the estimated and actual row counts differ by a specified percentage}}<br />
<br />
{{TodoItem<br />
|Have EXPLAIN ANALYZE report rows as floating-point numbers<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-05/msg01363.php <nowiki>explain analyze rows=%.0f</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-06/msg00108.php <nowiki>Re: explain analyze rows=%.0f</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve how ANALYZE computes in-doubt tuples<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-11/msg00771.php <nowiki>VACUUM/ANALYZE counting of in-doubt tuples</nowiki>]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Window Functions ===<br />
See {{messageLink|357.1230492361@sss.pgh.pa.us|TODO items for window functions}}.<br />
{{TodoSubsection}}<br />
{{TodoItem<br />
|Support creation of user-defined window functions<br />
|We have the ability to create new window functions written in C. Is it<br />
worth the effort to create an API that would let them be written in PL/pgsql, etc?}}<br />
<br />
{{TodoItem<br />
|Implement full support for window framing clauses<br />
|In addition to done clauses described in the [http://developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS latest doc], these clauses are not implemented yet.<br />
* RANGE BETWEEN ... PRECEDING/FOLLOWING<br />
* EXCLUDE<br />
}}<br />
<br />
{{TodoItem<br />
|Investigate tuplestore performance issues<br />
|The tuplestore_in_memory() thing is just a band-aid, we ought to try to solve it properly. tuplestore_advance seems like a weak spot as well.<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-12/msg00152.php <nowiki>tuplestore potential performance problem</nowiki>]<br />
}}<br />
<br />
{{TodoItem|Do we really need so much duplicated code between Agg and WindowAgg?}}<br />
<br />
{{TodoItem<br />
|Teach planner to evaluate multiple windows in the optimal order<br />
|Currently windows are always evaluated in the query-specified order.<br />
* http://archives.postgresql.org/message-id/3CDAD71E9D70417290FCF66F0178D1E1@amd64<br />
}}<br />
<br />
{{TodoItem<br />
|Implement DISTINCT clause in window aggregates<br />
|Some proprietary RDBMSs have implemented it already, so it helps with porting from those.}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
== Integrity Constraints ==<br />
=== Keys ===<br />
<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Improve deferrable unique constraints for cases with many conflicts<br />
|The current implementation fires a trigger for each potentially conflicting row. This might not scale well for an update that changes many key values at once.<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Referential Integrity ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Add MATCH PARTIAL referential integrity}}<br />
<br />
{{TodoItem<br />
|Change foreign key constraint for array -&gt; element to mean element in array?<br />
* [http://archives.postgresql.org/pgsql-hackers/2010-10/msg01814.php <nowiki>foreign keys for array/period contains relationships</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Fix problem when cascading referential triggers make changes on cascaded tables, seeing the tables in an intermediate state<br />
* [http://archives.postgresql.org/pgsql-hackers/2005-09/msg00174.php <nowiki>Re: [PATCHES] Work-in-progress referential action trigger timing</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Are ri_KeysEqual checks in the RI enforcement triggers still necessary?<br />
* [http://archives.postgresql.org/pgsql-performance/2005-10/msg00458.php <nowiki>Re: Effects of cascading references in foreign keys</nowiki>]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Check Constraints ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Run check constraints only when affected columns are changed<br />
* http://archives.postgresql.org/message-id/1326055327.15293.13.camel@vanquo.pezone.net<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
== Server-Side Languages ==<br />
<br />
{{TodoItem<br />
|Add support for polymorphic arguments and return types to languages other than PL/PgSQL}}<br />
<br />
{{TodoItem<br />
|Add support for OUT and INOUT parameters to languages other than PL/PgSQL}}<br />
<br />
{{TodoItem<br />
|Add more fine-grained specification of functions taking arbitrary data types<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-09/msg00367.php <nowiki>RfD: more powerful &quot;any&quot; types</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Implement stored procedures<br />
|This might involve the control of transaction state and the return of multiple result sets<br />
* [http://archives.postgresql.org/pgsql-general/2008-10/msg00454.php <nowiki>PL/pgSQL stored procedure returning multiple result sets (SELECTs)?</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01375.php <nowiki>Proposal: real procedures again (8.4)</nowiki>]<br />
* http://archives.postgresql.org/pgsql-hackers/2010-09/msg00542.php<br />
* [http://archives.postgresql.org/pgsql-hackers/2011-04/msg01149.php <nowiki>Gathering specs and discussion on feature (post 9.1)</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow holdable cursors in SPI}}<br />
<br />
=== SQL-Language Functions ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Rethink query plan caching and timing of parse analysis within SQL-language functions<br />
|They should work more like plpgsql functions do ...<br />
* [http://archives.postgresql.org/pgsql-bugs/2011-05/msg00078.php <nowiki>Re: BUG #6019: invalid cached plan on inherited table</nowiki>]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== PL/pgSQL ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]}}<br />
<br />
{{TodoItem<br />
|<nowiki>Allow listing of record column names, and access to record columns via variables, e.g. columns := r.(*), tval2 := r.(colname)</nowiki><br />
* [http://archives.postgresql.org/pgsql-patches/2005-07/msg00458.php <nowiki>Re: PL/PGSQL: Dynamic Record Introspection</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-patches/2006-05/msg00302.php <nowiki>Re: PL/PGSQL: Dynamic Record Introspection</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-patches/2006-06/msg00031.php <nowiki>Re: PL/PGSQL: Dynamic Record Introspection</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow row and record variables to be set to NULL constants, and allow NULL tests on such variables<br />
|Because a row is not scalar, do not allow assignment from NULL-valued scalars.<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-10/msg00070.php <nowiki>NULL and plpgsql rows</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider keeping separate cached copies when search_path changes<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-01/msg01009.php <nowiki>pl/pgsql Plan Invalidation and search_path</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve handling of NULL row values vs. NULL rows<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-09/msg01758.php <nowiki>Null row vs. row of nulls in plpgsql</nowiki>]<br />
* http://archives.postgresql.org/pgsql-hackers/2010-10/msg01973.php<br />
}}<br />
<br />
{{TodoItem<br />
|Improve PERFORM handling of WITH queries or document limitation<br />
* http://archives.postgresql.org/pgsql-bugs/2011-03/msg00309.php<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== PL/Perl ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Allow regex operations in plperl using UTF8 characters in non-UTF8 encoded databases}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== PL/Python ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Develop a trusted variant of PL/Python.}}<br />
<br />
{{TodoItem<br />
|Create a new restricted execution class that will allow passing function arguments in as locals. Passing them as globals means functions cannot be called recursively.<br />
* [http://archives.postgresql.org/pgsql-hackers/2011-02/msg01468.php <nowiki>Re: pl/python do not delete function arguments</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add a DB-API compliant interface on top of the SPI interface<br />
* http://petereisentraut.blogspot.com/2011/11/plpydbapi-db-api-for-plpython.html<br />
}}<br />
<br />
{{TodoItem<br />
|For functions returning a setof record with a composite type, cache the I/O functions for the composite type<br />
* http://archives.postgresql.org/pgsql-hackers/2010-12/msg02007.php<br />
}}<br />
<br />
{{TodoItemDone<br />
|Fix loss of information during conversion of numeric type to Python float}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== PL/Tcl ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Add table function support}}<br />
<br />
{{TodoItem<br />
|Check encoding validity of values passed back to Postgres in function returns, trigger tuple changes, and SPI calls.}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
== Clients ==<br />
<br />
{{TodoItem<br />
|Add a function like pg_get_indexdef() that report more detailed index information<br />
* [http://archives.postgresql.org/pgsql-bugs/2007-12/msg00166.php <nowiki>BUG #3829: Wrong index reporting from pgAdmin III (v1.8.0 rev 6766-6767)</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Split out pg_resetxlog output into pre- and post-sections<br />
* http://archives.postgresql.org/pgsql-hackers/2010-08/msg02040.php<br />
}}<br />
<br />
=== pg_ctl ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Improve pg_ctl's detection of running postmasters<br />
* http://archives.postgresql.org/pgsql-hackers/2011-06/msg00000.php<br />
* http://archives.postgresql.org/pgsql-committers/2011-06/msg00001.php<br />
}}<br />
<br />
{{TodoItem<br />
|Add additional shutdown modes, and change the default?<br />
* http://archives.postgresql.org/pgsql-hackers/2012-04/msg01283.php<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== psql ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Have psql \ds show all sequences and their settings<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg00916.php <nowiki>Re: TODO item: Have psql show current values for a sequence</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-12/msg00401.php <nowiki>Quick patch: Display sequence owner</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Move psql backslash database information into the backend, use mnemonic commands?<br />
|This would allow non-psql clients to pull the same information out of the database as psql. <br />
* [http://archives.postgresql.org/pgsql-hackers/2004-01/msg00191.php <nowiki>Re: psql \d option list overloaded</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Make psql's \d commands more consistent in their handling of schemas<br />
* [http://archives.postgresql.org/pgsql-hackers/2004-11/msg00014.php <nowiki>Re: psql and schemas</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Make psql's \d commands distinguish default privileges from no privileges<br />
|ACL displays were visibly different for the two cases before we "improved" them by using array_to_string.<br />
* [http://archives.postgresql.org/pgsql-bugs/2011-05/msg00082.php <nowiki>BUG #6021: There is no difference between default and empty access privileges with \dp</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consistently display privilege information for all objects in psql}}<br />
<br />
{{TodoItemEasy<br />
|\s without arguments (display history) fails with libedit, doesn't use pager either<br />
* [http://archives.postgresql.org/pgsql-bugs/2011-06/msg00114.php <nowiki> psql \s not working - OS X</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add a \set variable to control whether \s displays line numbers<br />
|Another option is to add \# which lists line numbers, and allows command execution.<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php <nowiki>Re: psql possible TODO</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Include the symbolic SQLSTATE name in verbose error reports<br />
* [http://archives.postgresql.org/pgsql-general/2007-09/msg00438.php <nowiki>Re: Checking is TSearch2 query is valid</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add prompt escape to display the client and server versions<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-05/msg00310.php <nowiki>WIP patch for TODO Item: Add prompt escape to display the client and server versions</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add option to wrap column values at whitespace boundaries, rather than chopping them at a fixed width.<br />
|Currently, &quot;wrapped&quot; format chops values into fixed widths. Perhaps the word wrapping could use the same algorithm documented in the W3C specification. <br />
* [http://archives.postgresql.org/pgsql-hackers/2008-05/msg00404.php <nowiki>Re: psql wrapped format default for backslash-d commands</nowiki>]<br />
* http://www.w3.org/TR/CSS21/tables.html#auto-table-layout}}<br />
<br />
{{TodoItem<br />
|Support the ReST table output format<br />
|Details about the ReST format: http://docutils.sourceforge.net/rst.html#reference-documentation<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-08/msg01007.php <nowiki>Proposal: new border setting in psql</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-01/msg00518.php <nowiki>Re: Proposal: new border setting in psql</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-01/msg00609.php <nowiki>Re: Proposal: new border setting in psql</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add option to print advice for people familiar with other databases<br />
* [http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php <nowiki>MySQL-ism help patch for psql</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add ability to edit views with \ev<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-09/msg00023.php <nowiki>Adding \ev view editor?</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Fix FETCH_COUNT to handle SELECT ... INTO and WITH queries<br />
* http://archives.postgresql.org/pgsql-hackers/2010-05/msg01565.php<br />
* http://archives.postgresql.org/pgsql-bugs/2010-05/msg00192.php<br />
}}<br />
<br />
{{TodoItem<br />
|Prevent psql from sending remaining single-line multi-statement queries after reconnecting<br />
* http://archives.postgresql.org/pgsql-bugs/2010-05/msg00159.php<br />
* http://archives.postgresql.org/pgsql-hackers/2010-05/msg01283.php<br />
}}<br />
<br />
{{TodoItem<br />
|Consider having psql -c read .psqlrc, for consistency<br />
|psql -f already reads .psqlrc<br />
}}<br />
<br />
{{TodoItem<br />
|Allow processing of multiple -f (file) options<br />
* http://www.postgresql.org/message-id/AANLkTikFpzrTRl6392GhatQdwlCWQTXFdSMxh5CP51iv@mail.gmail.com<br />
}}<br />
<br />
{{TodoItem<br />
|Improve line drawing characters<br />
* http://archives.postgresql.org/pgsql-hackers/2011-04/msg00386.php<br />
}}<br />
<br />
{{TodoItem<br />
|Consider improving the continuation prompt<br />
* http://archives.postgresql.org/pgsql-hackers/2011-04/msg01772.php<br />
}}<br />
<br />
{{TodoItem<br />
|Improve speed of tab completion by using LIKE<br />
* http://www.postgresql.org/message-id/20120821174847.GL1267@tamriel.snowman.net<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== pg_dump / pg_restore ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItemEasy<br />
|<nowiki>Add full object name to the tag field. eg. for operators we need '=(integer, integer)', instead of just '='.</nowiki>}}<br />
<br />
{{TodoItemEasy<br />
|Modify pg_dump to create skeleton views for reload (which are then updated via CREATE OR REPLACE VIEW) when views have circular dependencies. This should eliminate the need for the CREATE RULE "_RETURN" hack currently used to address this issue. Thread and additional information here:<br />
* [http://www.postgresql.org/message-id/25554.1360895028@sss.pgh.pa.us Description of change]<br />
|}}<br />
<br />
{{TodoItem<br />
|Add pg_dumpall custom format dumps?<br />
* [http://archives.postgresql.org/pgsql-general/2010-05/msg00509.php pg_dumpall custom format]<br />
|}}<br />
<br />
{{TodoItem<br />
|Avoid using platform-dependent locale names in pg_dumpall output<br />
|Using native locale names puts roadblocks in the way of porting a dump to another platform. One possible solution is to get<br />
CREATE DATABASE to accept some agreed-on set of locale names and fix them up to meet the platform's requirements.<br />
* http://archives.postgresql.org/message-id/21396.1241716688@sss.pgh.pa.us<br />
}}<br />
<br />
{{TodoItem<br />
|In a selective dump, allow dumping of an object and all its dependencies}}<br />
<br />
{{TodoItem<br />
|Add options like pg_restore -l and -L to pg_dump}}<br />
<br />
{{TodoItem<br />
|Stop dumping CASCADE on DROP TYPE commands in clean mode}}<br />
<br />
{{TodoItem<br />
|Allow pg_restore to load different parts of the COPY data for a single table simultaneously}}<br />
<br />
{{TodoItem<br />
|Remove support for dumping from pre-7.3 servers<br />
|In 7.3 and later, we can get accurate dependency information from the server. pg_dump still contains a lot of crufty code<br />
to try to deal with the lack of dependency info in older servers, but the usefulness of maintaining that code grows small.}}<br />
<br />
{{TodoItem<br />
|Refactor handling of database attributes between pg_dump and pg_dumpall<br />
|Currently only pg_dumpall emits database attributes, such as ALTER DATABASE SET commands and database-level GRANTs.<br />
Many people wish that pg_dump would do that. One proposal is to let pg_dump issue such commands if the -C switch was used,<br />
but it's unclear whether that will satisfy the demand.<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-06/msg01031.php <nowiki>ALTER DATABASE vs pg_dump</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-bugs/2010-05/msg00010.php summary of the issues]<br />
}}<br />
<br />
{{TodoItem<br />
|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.<br />
|This will require new backend syntax, perhaps COMMENT ON CURRENT DATABASE. This is related to the previous item.}}<br />
<br />
{{TodoItem<br />
|Allow parallel restore of tar dumps<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-02/msg01154.php <nowiki>Re: parallel restore</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Preserve sparse storage of large objects over dump/restore<br />
* [http://archives.postgresql.org/message-id/18789.1349750451@sss.pgh.pa.us <nowiki>TODO item: teach pg_dump about sparsely-stored large objects</nowiki>]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== ecpg ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Docs<br />
|Document differences between ecpg and the SQL standard and information about the Informix-compatibility module.}}<br />
<br />
{{TodoItem<br />
|Solve cardinality &gt; 1 for input descriptors / variables?}}<br />
<br />
{{TodoItem<br />
|Add a semantic check level, e.g. check if a table really exists}}<br />
<br />
{{TodoItem<br />
|fix handling of DB attributes that are arrays}}<br />
<br />
{{TodoItem<br />
|Fix nested C comments}}<br />
<br />
{{TodoItemEasy<br />
|sqlwarn[6] should be 'W' if the PRECISION or SCALE value specified}}<br />
<br />
{{TodoItem<br />
|Make SET CONNECTION thread-aware, non-standard?}}<br />
<br />
{{TodoItem<br />
|Allow multidimensional arrays}}<br />
<br />
{{TodoItem<br />
|Implement COPY FROM STDIN}} <br />
<br />
{{TodoItem<br />
|Provide a way to specify size of a bytea parameter<br />
* [http://archives.postgresql.org/message-id/200906192131.n5JLVoMo044178@wwwmaster.postgresql.org <nowiki>BUG #4866: ECPG and BYTEA</nowiki>]<br />
}}<br />
<br />
{{TodoItemEasy<br />
|Fix small memory leaks in ecpg<br />
|Memory leaks in a short running application like ecpg are not really a problem, but make debugging more complicated}} <br />
<br />
{{TodoItem<br />
|Allow reuse of cursor name variables<br />
* [http://archives.postgresql.org/message-id/20100329113435.GA3430@feivel.credativ.lan <nowiki>Problems with variable cursorname in ecpg</nowiki>]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== libpq ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Prevent PQfnumber() from lowercasing unquoted column names<br />
|PQfnumber() should never have been doing lowercasing, but historically it has so we need a way to prevent it}}<br />
<br />
{{TodoItem<br />
|Consider disallowing multiple queries in PQexec() as an additional barrier to SQL injection attacks<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-01/msg00184.php <nowiki>Re: InitPostgres and flatfiles question</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add PQexecf() that allows complex parameter substitution<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-03/msg01803.php <nowiki>Last minute mini-proposal (I know, know) for PQexecf()</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add SQLSTATE and severity to errors generated within libpq itself<br />
* [http://archives.postgresql.org/pgsql-interfaces/2007-11/msg00015.php <nowiki>v8.1: Error severity on libpq PGconn*</nowiki>]<br />
* http://archives.postgresql.org/pgsql-hackers/2010-08/msg01425.php<br />
}}<br />
<br />
{{TodoItem<br />
|Add support for interface/ipaddress binding to libpq<br />
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg01811.php <nowiki>SR/libpq - outbound interface/ipaddress binding</nowiki>]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== HTTP===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Allow access to the database via HTTP<br />
|See [[HTTP_API]]}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
== Triggers ==<br />
<br />
{{TodoItem<br />
|Improve storage of deferred trigger queue<br />
|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, or doing some kind of join to process all the triggers, some bulk operation, or a bitmap. <br />
* [http://archives.postgresql.org/pgsql-hackers/2008-05/msg00876.php <nowiki>Re: BUG #4204: COPY to table with FK has memory leak</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-10/msg00464.php <nowiki>Scaling up deferred unique checks and the after trigger queue</nowiki>]<br />
* http://archives.postgresql.org/pgsql-hackers/2011-08/msg00023.php<br />
}}<br />
<br />
{{TodoItem<br />
|Allow triggers to be disabled in only the current session.<br />
|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.}}<br />
<br />
{{TodoItem<br />
|With disabled triggers, allow pg_dump to use ALTER TABLE ADD FOREIGN KEY<br />
|If the dump is known to be valid, allow foreign keys to be added without revalidating the data.}}<br />
<br />
{{TodoItem<br />
|Allow statement-level triggers to access modified rows}}<br />
<br />
{{TodoItem<br />
|When statement-level triggers are defined on a parent table, have them fire only on the parent table, and fire child table triggers only where appropriate<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-11/msg01883.php <nowiki>Statement-level triggers and inheritance</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Tighten trigger permission checks<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-12/msg00564.php <nowiki>Security leak with trigger functions?</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow BEFORE INSERT triggers on views<br />
* [http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php <nowiki>Re: Why can't I put a BEFORE EACH ROW trigger on a view?</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add database and transaction-level triggers<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-03/msg00451.php <nowiki>Proposal for db level triggers</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-05/msg00620.php <nowiki>triggers on prepare, commit, rollback... ?</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Reduce locking requirements for creating a trigger<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-06/msg00635.php <nowiki>Re: Change lock requirements for adding a trigger</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Avoid requirement for "AFTER" trigger functions to return a value<br />
* http://archives.postgresql.org/pgsql-hackers/2011-02/msg02384.php<br />
}}<br />
<br />
{{TodoItem<br />
|Allow creation of inline triggers<br />
* http://archives.postgresql.org/pgsql-hackers/2012-02/msg00708.php<br />
}}<br />
<br />
== Inheritance ==<br />
<br />
{{TodoItem<br />
|Allow inherited tables to inherit indexes, UNIQUE constraints, and primary/foreign keys<br />
* [http://archives.postgresql.org/pgsql-hackers/2010-05/msg00285.php <nowiki>Partitioning/inherited tables vs FKs</nowiki>]<br />
* http://archives.postgresql.org/pgsql-hackers/2010-12/msg00039.php<br />
* http://archives.postgresql.org/pgsql-hackers/2010-12/msg00305.php<br />
}}<br />
<br />
{{TodoItem<br />
|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<br />
|The main difficulty with this item is the problem of creating an index that can span multiple tables.}}<br />
<br />
{{TodoItem<br />
|Determine whether ALTER TABLE / SET SCHEMA should work on inheritance hierarchies (and thus support ONLY). If yes, implement it.}}<br />
<br />
{{TodoItem<br />
|ALTER TABLE variants sometimes support recursion and sometimes not, but this is poorly/not documented, and the ONLY marker would then be silently ignored. Clarify the documentation, and reject ONLY if it is not supported.}}<br />
<br />
== Indexes ==<br />
<br />
{{TodoItem<br />
|Prevent index uniqueness checks when UPDATE does not modify the column<br />
|Uniqueness (index) checks are done when updating a column even if the column is not modified by the UPDATE.<br />
However, HOT already short-circuits this in common cases, so more work might not be helpful.<br />
* http://www.postgresql.org/message-id/CA+TgmoZOyaTanfEvNUdiHBCuu9Zh0JVP1e_UTVbx6Rvj9vFC9Q@mail.gmail.com<br />
}}<br />
<br />
{{TodoItem<br />
|Allow the creation of on-disk bitmap indexes which can be quickly combined with other bitmap indexes<br />
|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.<br />
* [http://archives.postgresql.org/pgsql-patches/2005-07/msg00512.php <nowiki>Re: Bitmap index AM</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-12/msg01107.php <nowiki>Bitmap index thoughts</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-03/msg00265.php <nowiki>Stream bitmaps</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-03/msg01214.php <nowiki>Re: Bitmapscan changes - Requesting further feedback</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-patches/2007-05/msg00013.php <nowiki>Updated bitmap index patch</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-07/msg00741.php <nowiki>Reviewing new index types (was Re: [PATCHES] Updated bitmap indexpatch)</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-10/msg01023.php <nowiki>Bitmap Indexes: request for feedback</nowiki>]<br />
* http://archives.postgresql.org/message-id/800923.27831.qm@web29010.mail.ird.yahoo.com <br />
}}<br />
<br />
{{TodoItem<br />
|Allow accurate statistics to be collected on indexes with more than one column or expression indexes, perhaps using per-index statistics<br />
* [http://archives.postgresql.org/pgsql-performance/2006-10/msg00222.php <nowiki>Re: Simple join optimized badly?</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-03/msg01131.php <nowiki>Stats for multi-column indexes</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-10/msg00741.php <nowiki>Cross-column statistics revisited</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-06/msg01431.php <nowiki>Multi-Dimensional Histograms</nowiki>]<br />
* http://archives.postgresql.org/pgsql-hackers/2010-12/msg00913.php<br />
* http://archives.postgresql.org/pgsql-hackers/2010-12/msg02179.php <br />
* http://archives.postgresql.org/pgsql-hackers/2011-01/msg00459.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-02/msg02054.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-04/msg01731.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-03/msg00894.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-09/msg00679.php<br />
}}<br />
<br />
{{TodoItem<br />
|Consider having a larger statistics target for indexed columns and expression indexes. <br />
}}<br />
<br />
{{TodoItem<br />
|Consider smaller indexes that record a range of values per heap page, rather than having one index entry for every heap row<br />
|This is useful if the heap is clustered by the indexed values. <br />
* [http://archives.postgresql.org/pgsql-hackers/2006-12/msg00341.php <nowiki>Grouped Index Tuples</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-02/msg01264.php <nowiki>Grouped Index Tuples</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-03/msg00465.php <nowiki>Grouped Index Tuples / Clustered Indexes</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-patches/2007-03/msg00163.php <nowiki>Bitmapscan changes</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-08/msg00014.php <nowiki>Re: GIT patch</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-08/msg00487.php <nowiki>Re: Index Tuple Compression Approach?</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg01589.php <nowiki>Re: Index AM change proposals, redux</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add REINDEX CONCURRENTLY, like CREATE INDEX CONCURRENTLY<br />
|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. <br />
* [http://archives.postgresql.org/pgsql-performance/2007-08/msg00289.php <nowiki>Re: When/if to Reindex</nowiki>]<br />
* http://archives.postgresql.org/pgsql-hackers/2012-09/msg00911.php<br />
* http://archives.postgresql.org/pgsql-hackers/2012-10/msg00128.php<br />
* [http://www.postgresql.org/message-id/CAB7nPqTys6JUQDxUczbJb0BNW0kPrW8WdZuk11KaxQq6o98PJg@mail.gmail.com Support for REINDEX CONCURRENTLY]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow multiple indexes to be created concurrently, ideally via a single heap scan<br />
|pg_restore allows parallel index builds, but it is done via subprocesses, and there is no SQL interface for this.<br />
Cluster could definitely benefit from this.<br />
* http://archives.postgresql.org/pgsql-performance/2011-04/msg00093.php<br />
* http://www.postgresql.org/message-id/CADVWZZJ5AS%3DXVrDwfTQqQP_V1+_fTYcZhq%3Dd5CbCXoALCjObbg@mail.gmail.com<br />
}}<br />
<br />
{{TodoItem<br />
|Consider sorting entries before inserting into btree index<br />
* [http://archives.postgresql.org/pgsql-general/2008-01/msg01010.php <nowiki>Re: ATTN: Clodaldo was Performance problem. Could it be related to 8.3-beta4?</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow creation of an index that can do comparisons to test if a value is between two column values<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-05/msg00757.php <nowiki>Proposal: temporal extension &quot;period&quot; data type</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider using "effective_io_concurrency" for index scans<br />
|Currently only bitmap scans use this, which might be fine because most multi-row index scans use bitmap scans.<br />
* [http://www.postgresql.org/message-id/CAGTBQpZzf70n0PYJ%3DVQLd+jb3wJGo%3D2TXmY+SkJD6G_vjC5QNg@mail.gmail.com Prefetch index pages for B-Tree index scans]<br />
}}<br />
<br />
{{TodoItem<br />
|Fix problem with btree page splits during checkpoints<br />
* http://archives.postgresql.org/pgsql-hackers/2010-11/msg00052.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-09/msg00184.php<br />
}}<br />
<br />
{{TodoItem<br />
|[http://archives.postgresql.org/pgsql-hackers/2012-05/msg00669.php Support amgettuple() in GIN (useful for exclusion constraints)]<br />
}}<br />
<br />
{{TodoItem<br />
| Allow "loose" or "skip" scans on btree indexes in which the first column has low cardinality<br />
* http://archives.postgresql.org/pgsql-performance/2012-08/msg00159.php<br />
}}<br />
<br />
{{TodoItem<br />
| Make the planner's "special index operator" mechanism extensible<br />
* http://www.postgresql.org/message-id/27270.1364700924@sss.pgh.pa.us<br />
}}<br />
<br />
{{TodoItem<br />
| Allow index only count for indexes which doesn't support index only scan<br />
}}<br />
<br />
{{TodoItem<br />
|Improve GIN performance<br />
* [http://www.postgresql.org/message-id/52F373CC.4050800@vmware.com Small GIN optimizations (after 9.4)]<br />
}}<br />
<br />
=== GIST ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Add more GIST index support for geometric data types}}<br />
<br />
{{TodoItem<br />
|Allow GIST indexes to create certain complex index types, like digital trees (see Aoki)}}<br />
<br />
{{TodoItem<br />
|Fix performance issues in contrib/seg and contrib/cube GiST support<br />
* [http://archives.postgresql.org/message-id/alpine.DEB.2.00.0904161633160.4053@aragorn.flymine.org GiST index performance]<br />
* [http://archives.postgresql.org/message-id/alpine.DEB.2.00.0904221704470.22330@aragorn.flymine.org draft patch]<br />
* [http://archives.postgresql.org/pgsql-performance/2009-05/msg00069.php <nowiki>Re: GiST index performance</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-performance/2009-06/msg00068.php <nowiki>GiST index performance</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|[http://archives.postgresql.org/message-id/4DC8D284-05CF-4E3D-9670-AC9A32C37A36@justatheory.com GiST index support for arrays]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow index only scan for GIST indexes (when possible)}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Hash ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Add UNIQUE capability to hash indexes}}<br />
<br />
{{TodoItem<br />
|Add hash WAL logging for crash recovery<br />
* http://archives.postgresql.org/pgsql-performance/2011-09/msg00196.php<br />
* [http://www.postgresql.org/message-id/CA+TgmoZyMoJSrFxHXQ06G8jhjXQcsKvDiHB_8z_7nc7hj7iHYQ@mail.gmail.com Save Hash Indexes]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow multi-column hash indexes}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
== Sorting ==<br />
<br />
{{TodoItem<br />
|Consider whether duplicate keys should be sorted by block/offset<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-03/msg00558.php <nowiki>Remove hacks for old bad qsort() implementations?</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider being smarter about memory and external files used during sorts<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-11/msg01101.php <nowiki>Sorting Improvements for 8.4</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-12/msg00045.php <nowiki>Re: Sorting Improvements for 8.4</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider detoasting keys before sorting}}<br />
<br />
{{TodoItemDone<br />
|Allow sorts to use more available memory<br />
* http://archives.postgresql.org/pgsql-hackers/2007-11/msg01026.php<br />
* http://archives.postgresql.org/pgsql-hackers/2010-09/msg01123.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-02/msg01957.php<br />
}}<br />
<br />
{{TodoItem<br />
|Allow sorts of skinny tuples to use even more available memory.<br />
* Now that it is not limited by MaxAllocSize, don't limit by INT_MAX either.<br />
* http://www.postgresql.org/message-id/CA+U5nMKkRMin1pV8VMpS6_n7hcOWSG0kZS3oFL9JOa8DV6vJyQ@mail.gmail.com<br />
}}<br />
<br />
== Fsync ==<br />
<br />
{{TodoItem<br />
|Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options and whether fsync does anything<br />
|Ideally this requires a separate test program like /contrib/pg_test_fsync that can be run at initdb time or optionally later.<br />
}}<br />
<br />
{{TodoItem<br />
|Consider sorting writes during checkpoint<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-06/msg00541.php <nowiki>Sorted writes in checkpoint</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-patches/2008-07/msg00050.php <nowiki>Re: Sorting writes during checkpoint</nowiki>]<br />
* http://archives.postgresql.org/pgsql-hackers/2010-10/msg02012.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-02/msg00278.php<br />
* http://archives.postgresql.org/pgsql-hackers/2012-01/msg00493.php<br />
}}<br />
<br />
== Cache Usage ==<br />
<br />
{{TodoItem<br />
|Provide a way to calculate an &quot;estimated COUNT(*)&quot;<br />
|Perhaps by using the optimizer's cardinality estimates or random sampling.<br />
* [http://archives.postgresql.org/pgsql-hackers/2005-11/msg00943.php <nowiki>Re: Improving count(*)</nowiki>]<br />
* http://wiki.postgresql.org/wiki/Slow_Counting<br />
}}<br />
<br />
{{TodoItem<br />
|Consider automatic caching of statements at various levels:<br />
* Parsed query tree<br />
* Query execute plan<br />
* Query results <br />
<br />
:<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00823.php <nowiki>Cached Query Plans (was: global prepared statements)</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider increasing internal areas (NUM_CLOG_BUFFERS) when shared buffers is increased<br />
* [http://archives.postgresql.org/pgsql-hackers/2005-10/msg01419.php <nowiki>Re: slru.c race condition (was Re: TRAP: FailedAssertion(&quot;!((itemid)-&gt;lp_flags &amp; 0x01)&quot;,)</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-08/msg00030.php <nowiki>clog_buffers to 64 in 8.3?</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-performance/2007-08/msg00024.php <nowiki>CLOG Patch</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider decreasing the amount of memory used by PrivateRefCount<br />
|<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-11/msg00797.php <nowiki>PrivateRefCount (for 8.3)</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-01/msg00752.php <nowiki>Re: PrivateRefCount (for 8.3)</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider allowing higher priority queries to have referenced buffer cache pages stay in memory longer<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-11/msg00562.php <nowiki>Re: How to keep a table in memory?</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve cache lookup speed for sessions accessing many relations<br />
* http://archives.postgresql.org/pgsql-hackers/2012-11/msg00356.php<br />
}}<br />
<br />
{{TodoItem<br />
|Fix memory leak caused by negative catcache entries<br />
* [http://www.postgresql.org/message-id/51C0A1FF.2050404@vmware.com <nowiki>Re: Memory leak in PL/pgSQL function which CREATE/SELECT/DROP a temporary table</nowiki>]<br />
}}<br />
<br />
== Vacuum ==<br />
<br />
{{TodoItem<br />
|Auto-fill the free space map by scanning the buffer cache or by checking pages written by the background writer<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-02/msg01125.php <nowiki>Dead Space Map</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-03/msg00011.php <nowiki>Re: Automatic free space map filling</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow concurrent inserts to use recently created pages rather than creating new ones<br />
* http://archives.postgresql.org/pgsql-hackers/2010-05/msg00853.php<br />
}}<br />
<br />
{{TodoItem<br />
|Consider having single-page pruning update the visibility map<br />
* <nowiki>https://commitfest.postgresql.org/action/patch_view?id=75</nowiki><br />
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg02344.php <nowiki>Re: visibility maps and heap_prune</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow VACUUM FULL and CLUSTER to update the visibility map<br />
* [http://www.postgresql.org/message-id/20130112191404.255800@gmx.com index-only scans : abnormal heap fetches after VACUUM FULL]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve tracking of total relation tuple counts now that vacuum doesn't always scan the whole heap<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-06/msg00531.php Partial vacuum versus pg_class.reltuples]<br />
}}<br />
<br />
{{TodoItem<br />
|Bias FSM towards returning free space near the beginning of the heap file, in hopes that empty pages at the end can be truncated by VACUUM<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-09/msg01124.php <nowiki>FSM search modes</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider a more compact data representation for dead tuple locations within VACUUM<br />
* [http://archives.postgresql.org/pgsql-patches/2007-05/msg00143.php <nowiki>Re: Have vacuum emit a warning when it runs out of maintenance_work_mem</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Provide more information in order to improve user-side estimates of dead space bloat in relations<br />
* [http://archives.postgresql.org/pgsql-general/2009-05/msg01039.php <nowiki>Re: Bloated Table</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve locking behaviour of vacuum during trailing page truncation<br />
* http://archives.postgresql.org/pgsql-bugs/2011-03/msg00319.php<br />
* http://archives.postgresql.org/message-id/4D8DF88E.7080205@Yahoo.com<br />
}}<br />
<br />
{{TodoItem<br />
|Reduce the number of table scans performed by vacuum<br />
* http://archives.postgresql.org/pgsql-hackers/2011-05/msg01119.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-06/msg00605.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-07/msg00624.php<br />
}}<br />
<br />
{{TodoItem<br />
|Vacuum Gin indexes in physically order rather than logical order<br />
* http://archives.postgresql.org/pgsql-hackers/2012-04/msg00443.php<br />
}}<br />
<br />
{{TodoItem<br />
|Avoid creation of the free space map for small tables<br />
* http://archives.postgresql.org/pgsql-hackers/2011-11/msg01751.php<br />
* http://archives.postgresql.org/pgsql-hackers/2012-08/msg00552.php<br />
* http://archives.postgresql.org/pgsql-hackers/2012-08/msg00615.php<br />
}}<br />
<br />
=== Auto-vacuum ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItemEasy<br />
|Issue log message to suggest VACUUM FULL if a table is nearly empty?}}<br />
<br />
{{TodoItem<br />
|Prevent long-lived temporary tables from causing frozen-xid advancement starvation<br />
|The problem is that autovacuum cannot vacuum them to set frozen xids; only the session that created them can do that. <br />
* [http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php <nowiki>Re: AutoVacuum Behaviour Question</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Prevent autovacuum from running if an old transaction is still running from the last vacuum<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-11/msg00899.php <nowiki>Re: Autovacuum and OldestXmin</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Have autoanalyze of parent tables occur when child tables are modified<br />
* http://archives.postgresql.org/pgsql-performance/2010-06/msg00137.php<br />
* http://archives.postgresql.org/pgsql-performance/2010-10/msg00271.php<br />
}}<br />
<br />
{{TodoItem<br />
|Allow visibility map all-visible bits to be set even when an auto-ANALYZE is running<br />
* http://archives.postgresql.org/pgsql-hackers/2012-01/msg00356.php<br />
}}<br />
<br />
{{TodoItem<br />
|Allow parallel cores to be used by vacuumdb<br />
* [http://archives.postgresql.org/message-id/4F10A728.7090403@agliodbs.com vacuumdb -j]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve autovacuum tuning<br />
* http://www.postgresql.org/message-id/5078AD6B.8060802@agliodbs.com<br />
* http://www.postgresql.org/message-id/20130124215715.GE4528@alvh.no-ip.org<br />
}}<br />
<br />
{{TodoItem<br />
|Improve setting of visibility map bits for read-only and insert-only workloads<br />
* http://www.postgresql.org/message-id/20130906001437.GA29264@momjian.us<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
== Locking ==<br />
<br />
{{TodoItem<br />
|Fix priority ordering of read and write light-weight locks<br />
* [http://archives.postgresql.org/pgsql-hackers/2004-11/msg00893.php <nowiki>lwlocks and starvation</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2004-11/msg00905.php <nowiki>Re: lwlocks and starvation</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Fix problem when multiple subtransactions of the same outer transaction hold different types of locks, and one subtransaction aborts<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-11/msg01011.php <nowiki>FOR SHARE vs FOR UPDATE locks</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-12/msg00001.php <nowiki>Re: FOR SHARE vs FOR UPDATE locks</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-02/msg00435.php <nowiki>Re: [PATCHES] [pgsql-patches] Phantom Command IDs, updated patch</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-05/msg00773.php <nowiki>Re: savepoints and upgrading locks</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow UPDATEs on only non-referential integrity columns not to conflict with referential integrity locks<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-02/msg00073.php <nowiki>Referential Integrity and SHARE locks</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add idle_in_transaction_timeout GUC so locks are not held for long periods of time}}<br />
<br />
{{TodoItem<br />
|Improve deadlock detection when a page cleaning lock conflicts with a shared buffer that is pinned<br />
* [http://archives.postgresql.org/pgsql-bugs/2008-01/msg00138.php <nowiki>BUG #3883: Autovacuum deadlock with truncate?</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-01/msg00873.php <nowiki>Thoughts about bug #3883</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-committers/2008-01/msg00365.php <nowiki>Re: pgsql: Add checks to TRUNCATE, CLUSTER, and REINDEX to prevent</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Detect deadlocks involving LockBufferForCleanup()<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-01/msg00873.php <nowiki>Thoughts about bug #3883</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow finer control over who is cancelled in a deadlock<br />
* http://archives.postgresql.org/pgsql-hackers/2011-03/msg01727.php<br />
}}<br />
<br />
== Startup Time Improvements ==<br />
<br />
{{TodoItem<br />
|Experiment with multi-threaded backend for backend creation<br />
|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.}}<br />
<br />
{{TodoItem<br />
|Allow backends to change their database without restart<br />
|This allows for faster server startup.<br />
* http://archives.postgresql.org/pgsql-hackers/2010-11/msg00843.php<br />
* http://archives.postgresql.org/pgsql-hackers/2010-12/msg00336.php<br />
}}<br />
<br />
== Write-Ahead Log ==<br />
<br />
{{TodoItem<br />
|Eliminate need to write full pages to WAL before page modification<br />
|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. <br />
* [http://archives.postgresql.org/pgsql-hackers/2002-06/msg00655.php <nowiki>Re: Index Scans become Seq Scans after VACUUM ANALYSE</nowiki>]<br />
* http://archives.postgresql.org/pgsql-hackers/2011-05/msg01191.php<br />
* [http://archives.postgresql.org/message-id/20120105061916.GB21048@fetter.org WIP double writes]<br />
* [http://archives.postgresql.org/message-id/4EFC449F02000025000441CD@gw.wicourts.gov double writes]<br />
* [http://archives.postgresql.org/message-id/20120110214344.GB21106@fetter.org Double-write with Fast Checksums]<br />
* [http://archives.postgresql.org/message-id/1962493974.656458.1327703514780.JavaMail.root@zimbra-prod-mbox-4.vmware.com double writes using "double-write buffer" approach]<br />
* http://archives.postgresql.org/pgsql-hackers/2012-10/msg01463.php<br />
}}<br />
<br />
{{TodoItem<br />
|When full page writes are off, write CRC to WAL and check file system blocks on recovery<br />
|If CRC check fails during recovery, remember the page in case a later CRC for that page properly matches. The difficulty is that hint bits are not WAL logged, meaning a valid page might not match the earlier CRC.}}<br />
<br />
{{TodoItem<br />
|Write full pages during file system write and not when the page is modified in the buffer cache<br />
|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.<br />
* [http://archives.postgresql.org/message-id/CAGvK12UST-tPhyLrSLuSpwFxZbAO79yYrhV2xaLmS2MkUxNUVQ@mail.gmail.com Page Checksums + Double Writes]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider compression of full page writes<br />
* [http://www.postgresql.org/message-id/CAHGQGwGqG8e9YN0fNCUZqTTT%3DhNr7Ly516kfT5ffqf4pp1qnHg@mail.gmail.com Compression of full-page-writes]<br />
}}<br />
<br />
{{TodoItem<br />
|Reduce WAL traffic so only modified values are written rather than entire rows<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-03/msg01589.php <nowiki>Reduction in WAL for UPDATEs</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow WAL information to recover corrupted pg_controldata<br />
* [http://archives.postgresql.org/pgsql-patches/2006-06/msg00025.php <nowiki>Re: [HACKERS] pg_resetxlog -r flag</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Find a way to reduce rotational delay when repeatedly writing last WAL page<br />
|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. <br />
* [http://archives.postgresql.org/pgsql-hackers/2002-11/msg00483.php <nowiki>500 tpsQL + WAL log implementation</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Speed WAL recovery by allowing more than one page to be prefetched<br />
|This should be done utilizing the same infrastructure used for prefetching in general to avoid introducing complex error-prone code in WAL replay. <br />
* [http://archives.postgresql.org/pgsql-general/2007-12/msg00683.php <nowiki>Slow PITR restore</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-12/msg00497.php <nowiki>Re: [GENERAL] Slow PITR restore</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-02/msg01279.php <nowiki>Read-ahead and parallelism in redo recovery</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve WAL concurrency by increasing lock granularity<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-02/msg00556.php <nowiki>Reworking WAL locking</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Be more aggressive about creating WAL files<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01325.php <nowiki>Re: PANIC caused by open_sync on Linux</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2004-07/msg01075.php <nowiki>PreallocXlogFiles</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2005-04/msg00556.php <nowiki>WAL/PITR additional items</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Have resource managers report the duration of their status changes<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01468.php <nowiki>Recovery of Multi-stage WAL actions</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Close deleted WAL files held open in *nix by long-lived read-only backends<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-11/msg01754.php <nowiki>Deleted WAL files held open by backends in Linux</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-12/msg00060.php <nowiki>Re: Deleted WAL files held open by backends in Linux</nowiki>]<br />
}}<br />
<br />
== Optimizer / Executor ==<br />
<br />
{{TodoItem<br />
|Improve selectivity functions for geometric operators}}<br />
<br />
{{TodoItem<br />
|Consider increasing the default values of from_collapse_limit, join_collapse_limit, and/or geqo_threshold<br />
* [http://archives.postgresql.org/message-id/4136ffa0905210551u22eeb31bn5655dbe7c9a3aed5@mail.gmail.com from_collapse_limit vs. geqo_threshold]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve ability to display optimizer analysis using OPTIMIZER_DEBUG<br />
* http://archives.postgresql.org/pgsql-hackers/2012-08/msg00597.php<br />
}}<br />
<br />
{{TodoItem<br />
|Log statements where the optimizer row estimates were dramatically different from the number of rows actually found?}}<br />
<br />
{{TodoItem<br />
|Consider compressed annealing to search for query plans<br />
|This might replace GEQO.<br />
* http://archives.postgresql.org/message-id/15658.1241278636%40sss.pgh.pa.us<br />
}}<br />
<br />
{{TodoItem<br />
|Improve use of expression indexes for ORDER BY <br />
* [http://archives.postgresql.org/pgsql-hackers/2009-08/msg01553.php <nowiki>Resjunk sort columns, Heikki's index-only quals patch, and bug #5000</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Modify the planner to better estimate caching effects<br />
* http://archives.postgresql.org/pgsql-performance/2010-11/msg00117.php<br />
}}<br />
<br />
{{TodoItem<br />
|Allow shared buffer cache contents to affect index cost computations<br />
* http://archives.postgresql.org/pgsql-hackers/2011-06/msg01140.php<br />
}}<br />
<br />
{{TodoItem<br />
|Allow the CTE (Common Table Expression) optimization fence to be optionally disabled<br />
* http://archives.postgresql.org/pgsql-hackers/2012-09/msg00700.php<br />
* http://archives.postgresql.org/pgsql-performance/2012-11/msg00161.php<br />
}}<br />
<br />
{{TodoItem<br />
|Teach the planner how to better use partial indexes for index-only scans<br />
* http://www.postgresql.org/message-id/25141.1345072858@sss.pgh.pa.us<br />
* http://www.postgresql.org/message-id/79C7D74D-59B0-4D97-A5E5-55553EF299AA@justatheory.com<br />
}}<br />
<br />
=== Hashing ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Consider using a hash for joining to a large IN (VALUES ...) list<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-05/msg00450.php <nowiki>Planning large IN lists</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow single batch hash joins to preserve outer pathkeys<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-09/msg00806.php Re: Potential Join Performance Issue]<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-04/msg00153.php a few crazy ideas about hash joins]<br />
}}<br />
<br />
{{TodoItem<br />
|"lazy" hash tables - look up only the tuples that are actually requested<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-04/msg00153.php a few crazy ideas about hash joins]<br />
}}<br />
<br />
{{TodoItem<br />
|Avoid building the same hash table more than once during the same query<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-04/msg00153.php a few crazy ideas about hash joins]<br />
}}<br />
<br />
{{TodoItem<br />
|Avoid hashing for distinct and then re-hashing for hash join<br />
* [http://archives.postgresql.org/message-id/4136ffa0902191346g62081081v8607f0b92c206f0a@mail.gmail.com Re: Fixing Grittner's planner issues]<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-04/msg00153.php a few crazy ideas about hash joins]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
== Background Writer ==<br />
<br />
{{TodoItem<br />
|Consider having the background writer update the transaction status hint bits before writing out the page<br />
|Implementing this requires the background writer to have access to system catalogs and the transaction status log.}}<br />
<br />
{{TodoItem<br />
|Consider adding buffers the background writer finds reusable to the free list <br />
* [http://archives.postgresql.org/pgsql-hackers/2007-04/msg00781.php <nowiki>Background LRU Writer/free list</nowiki>]<br />
* [http://archives.postgresql.org/message-id/CA+U5nMKtvyDcV4zTr7bq7t6cA2nBfLxCJ8tQgVBnc5ddRPO+Bg@mail.gmail.com our buffer replacement strategy is kind of lame]<br />
* [http://www.postgresql.org/message-id/CAOeZVic4HikhmzVD%3DZP4JY9g8PgpyiQQOXOELWP%3DkR+%3DH1Frgg@mail.gmail.com Page replacement algorithm in buffer cache]<br />
* [http://www.postgresql.org/message-id/002f01ce50a8$e057c7a0$a10756e0$@kapila@huawei.com Move unused buffers to freelist]<br />
}}<br />
<br />
{{TodoItem<br />
|Automatically tune bgwriter_delay based on activity rather then using a fixed interval<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-04/msg00781.php <nowiki>Background LRU Writer/free list</nowiki>]<br />
* [http://archives.postgresql.org/message-id/CA+U5nMKtvyDcV4zTr7bq7t6cA2nBfLxCJ8tQgVBnc5ddRPO+Bg@mail.gmail.com our buffer replacement strategy is kind of lame]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider whether increasing BM_MAX_USAGE_COUNT improves performance<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-06/msg01007.php <nowiki>Bgwriter LRU cleaning: we've been going at this all wrong</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Test to see if calling PreallocXlogFiles() from the background writer will help with WAL segment creation latency<br />
* [http://archives.postgresql.org/pgsql-patches/2007-06/msg00340.php <nowiki>Re: Load Distributed Checkpoints, final patch</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add auto-tuning of work_mem<br />
* [http://www.postgresql.org/message-id/20131009143046.GT22450@momjian.us Auto-tuning work_mem and maintenance_work_mem]<br />
}}<br />
<br />
== Concurrent Use of Resources ==<br />
<br />
{{TodoItem<br />
|Do async I/O for faster random read-ahead of data<br />
|Async I/O allows multiple I/O requests to be sent to the disk with results coming back asynchronously.<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-10/msg00820.php <nowiki>Asynchronous I/O Support</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-performance/2007-09/msg00255.php <nowiki>Re: random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-12/msg00027.php <nowiki>There's random access and then there's random access</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-patches/2008-01/msg00170.php <nowiki>Bitmap index scan preread using posix_fadvise (Was: There's random access and then there's random access)</nowiki>]<br />
The above patch is already applied as of 8.4, but it still remains to figure out how to handle plain indexscans effectively.<br />
* [http://archives.postgresql.org//pgsql-hackers/2009-01/msg00806.php Problems with the patch submitted for posix_fadvise in index scans]<br />
}}<br />
<br />
{{TodoItem<br />
|Experiment with multi-threaded backend for better I/O utilization<br />
|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.<br />
* http://archives.postgresql.org/pgsql-performance/2011-02/msg00123.php<br />
* http://archives.postgresql.org/pgsql-hackers/2012-10/msg01139.php<br />
}}<br />
<br />
{{TodoItem<br />
|Experiment with multi-threaded backend for better CPU utilization<br />
|This would allow several CPUs to be used for a single query, such as for sorting or query execution.<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-10/msg00945.php <nowiki>Multi CPU Queries - Feedback and/or suggestions wanted!</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|SMP scalability improvements<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-07/msg00439.php <nowiki>Straightforward changes for increased SMP scalability</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-09/msg00206.php <nowiki>Re: Reducing Transaction Start/End Contention</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-03/msg00361.php <nowiki>Re: Reducing Transaction Start/End Contention</nowiki>]<br />
}}<br />
<br />
== TOAST ==<br />
<br />
{{TodoItem<br />
|Allow user configuration of TOAST thresholds<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-02/msg00213.php <nowiki>Re: Proposed adjustments in MaxTupleSize and toastthresholds</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-08/msg00082.php <nowiki>pg_lzcompress strategy parameters</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Reduce unnecessary cases of deTOASTing<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-09/msg00895.php <nowiki>Re: [PATCHES] Eliminate more detoast copies for packed varlenas</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Reduce costs of repeat de-TOASTing of values<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-06/msg01096.php <nowiki>WIP patch: reducing overhead for repeat de-TOASTing</nowiki>]<br />
}}<br />
<br />
== Monitoring ==<br />
{{TodoItem<br />
|Expand pg_stat_activity for easier integration with monitoring tools<br />
|* http://archives.postgresql.org/message-id/4DFA13A5.2060200@2ndQuadrant.com<br />
}}<br />
<br />
{{TodoItem<br />
|Add column to pg_stat_activity that shows the progress of long-running commands like CREATE INDEX and VACUUM<br />
* [http://archives.postgresql.org/pgsql-patches/2008-04/msg00203.php <nowiki>EXPLAIN progress info</nowiki>]<br />
* The CLUSTER/VACUUM FULL implementation would also be useful to track this way<br />
}}<br />
<br />
{{TodoItem<br />
|Have pg_stat_activity display query strings in the correct client encoding<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-01/msg00131.php <nowiki>pg_stats queries versus per-database encodings</nowiki>]<br />
}}<br />
<br />
{{TodoItemEasy<br />
|Expose pg_controldata via an SQL interface<br />
|Helpful for monitoring replicated databases<br />
* http://archives.postgresql.org/message-id/4B901D73.8030003@agliodbs.com<br />
* [http://archives.postgresql.org/message-id/4B959D7A.6010907@joeconway.com initial patch]<br />
}}<br />
<br />
{{TodoItem<br />
| Add entry creation timestamp column to pg_stat_replication<br />
* http://archives.postgresql.org/pgsql-hackers/2011-08/msg00694.php<br />
}}<br />
<br />
{{TodoItem<br />
| Allow reporting of stalls due to wal_buffer wrap-around<br />
* http://archives.postgresql.org/pgsql-hackers/2012-02/msg00826.php<br />
}}<br />
<br />
{{TodoItem<br />
| Restructure pg_stat_database columns tup_returned and tup_fetched to return meaningful values<br />
* http://www.postgresql.org/message-id/20121012060345.GA29214@toroid.org<br />
}}<br />
<br />
== Miscellaneous Performance ==<br />
<br />
{{TodoItem<br />
|Use mmap() rather than shared memory for shared buffers?<br />
|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. We could also consider mmap() for writing WAL.<br />
* http://archives.postgresql.org/pgsql-hackers/2010-11/msg00750.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-04/msg00756.php<br />
* http://www.postgresql.org/message-id/20140115114909.GI4963@suse.de<br />
}}<br />
<br />
{{TodoItem<br />
|Rather than consider mmap()-ing in 8k pages, consider mmap()'ing entire files into a backend?<br />
|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.<br />
* http://archives.postgresql.org/pgsql-hackers/2011-03/msg01239.php<br />
}}<br />
<br />
{{TodoItem<br />
|Consider ways of storing rows more compactly on disk:<br />
* Reduce the row header size?<br />
* Consider reducing on-disk varlena length from four bytes to two because a heap row cannot be more than 64k in length}}<br />
<br />
{{TodoItem<br />
|Consider transaction start/end performance improvements<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-07/msg00948.php <nowiki>Reducing Transaction Start/End Contention</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-03/msg00361.php <nowiki>Re: Reducing Transaction Start/End Contention</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow configuration of backend priorities via the operating system<br />
|Though backend priorities make priority inversion during lock waits possible, research shows that this is not a huge problem.<br />
* [http://archives.postgresql.org/pgsql-general/2007-02/msg00493.php <nowiki>Priorities for users or queries?</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider increasing the minimum allowed number of shared buffers<br />
* [http://archives.postgresql.org/pgsql-bugs/2008-02/msg00157.php <nowiki>Re: [PATCH] Don't bail with legitimate -N/-B options</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider if CommandCounterIncrement() can avoid its AcceptInvalidationMessages() call<br />
* [http://archives.postgresql.org/pgsql-committers/2007-11/msg00585.php <nowiki>pgsql: Avoid incrementing the CommandCounter when</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider Cartesian joins when both relations are needed to form an indexscan qualification for a third relation<br />
* [http://archives.postgresql.org/pgsql-performance/2007-12/msg00090.php <nowiki>Re: TB-sized databases</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider not storing a NULL bitmap on disk if all the NULLs are trailing<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-12/msg00624.php <nowiki>Proposal for Null Bitmap Optimization(for Trailing NULLs)</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-patches/2007-12/msg00109.php <nowiki>Re: [HACKERS] Proposal for Null Bitmap Optimization(for TrailingNULLs)</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Sort large UPDATE/DELETEs so it is done in heap order<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-01/msg01119.php <nowiki>Possible future performance improvement: sort updates/deletes by ctid</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider decreasing the I/O caused by updating tuple hint bits<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-05/msg00847.php <nowiki>Hint Bits and Write I/O</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-patches/2008-07/msg00199.php <nowiki>Re: [HACKERS] Hint Bits and Write I/O</nowiki>]<br />
* http://archives.postgresql.org/pgsql-hackers/2010-10/msg00695.php<br />
* http://archives.postgresql.org/pgsql-hackers/2010-11/msg00792.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-01/msg01063.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-03/msg01408.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-03/msg01453.php<br />
}}<br />
<br />
{{TodoItem<br />
|Avoid the requirement of freezing pages that are infrequently modified <br />
|If all rows on a page are visible, it is possible to set a bit in the visibility map (once the visibility map is 100% reliable) and not need to freeze the page, avoiding a page rewrite<br />
* http://archives.postgresql.org/message-id/4BF701CF.2090205@agliodbs.com<br />
* http://archives.postgresql.org/pgsql-hackers/2010-06/msg00082.php<br />
* http://www.postgresql.org/message-id/20130523175148.GA29374@alap2.anarazel.de<br />
* http://www.postgresql.org/message-id/CA+TgmoaEmnoLZmVbb8gvY69NA8zw9BWpiZ9+TLz-LnaBOZi7JA@mail.gmail.com<br />
* http://www.postgresql.org/message-id/51A7553E.5070601@vmware.com<br />
}}<br />
<br />
{{TodoItem<br />
|Avoid reading in b-tree pages when replaying vacuum records in hot standby mode<br />
* [http://archives.postgresql.org/message-id/1272571938.4161.14739.camel@ebony <nowiki>Hot Standby tuning for btree_xlog_vacuum()</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Restructure truncation logic to be more resistant to failure<br />
|This also involves not writing dirty buffers for a truncated or dropped relation<br />
* http://archives.postgresql.org/pgsql-hackers/2010-08/msg01032.php<br />
}}<br />
<br />
{{TodoItem<br />
|Consider adding logic to increase large tables by more than 8k<br />
|This would reduce file system fragmentation<br />
* http://archives.postgresql.org/pgsql-bugs/2011-03/msg00337.php<br />
}}<br />
<br />
== Miscellaneous Other ==<br />
<br />
{{TodoItem<br />
|Deal with encoding issues for filenames in the server filesystem<br />
* {{MessageLink|20090413184335.39BE.52131E4D@oss.ntt.co.jp|a proposed patch here}}<br />
* {{MessageLink|8484.1244655656@sss.pgh.pa.us|some issues about it here}}<br />
* {{MessageLink|20100107103740.97A5.52131E4D@oss.ntt.co.jp|Windows-specific patch here}}<br />
}}<br />
<br />
{{TodoItem<br />
|Deal with encoding issues in the output of localeconv()<br />
* [http://archives.postgresql.org/message-id/40c6d9160904210658y590377cfw6dbbecb53d2b8be0@mail.gmail.com bug report]<br />
* [http://archives.postgresql.org/message-id/49EF8DA0.90008@tpf.co.jp draft patch]<br />
* [http://archives.postgresql.org/message-id/21710.1243620986@sss.pgh.pa.us review of patch]<br />
}}<br />
<br />
{{TodoItem<br />
|Provide schema name and other fields available from SQL GET DIAGNOSTICS in error reports<br />
* [http://archives.postgresql.org/message-id/dcc563d10810211907n3c59a920ia9eb7cd2a6d5ea58@mail.gmail.com <nowiki>How to get schema name which violates fk constraint</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-11/msg00846.php <nowiki>patch - Report the schema along table name in a referential failure error message</nowiki>]<br />
* {{MessageLink|3191.1263306359@sss.pgh.pa.us|Re: NOT NULL violation and error-message}}<br />
* [http://archives.postgresql.org/pgsql-hackers/2009-08/msg00213.php <nowiki>the case for machine-readable error fields</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add 64-bit support to /contrib/pgbench<br />
* http://archives.postgresql.org/pgsql-hackers/2010-07/msg00153.php<br />
* http://archives.postgresql.org/pgsql-hackers/2011-02/msg00705.php<br />
}}<br />
<br />
{{TodoItem<br />
|Use sa_mask to close race conditions between signal handlers<br />
* http://www.postgresql.org/message-id/20130911013107.GA225735@tornado.leadboat.com<br />
}}<br />
<br />
== Source Code ==<br />
<br />
{{TodoItemEasy<br />
|Remove warnings created by -Wcast-align}}<br />
<br />
{{TodoItem<br />
|Move platform-specific ps status display info from ps_status.c to ports}}<br />
<br />
{{TodoItem<br />
|Consider a faster CRC32 algorithm<br />
* http://archives.postgresql.org/pgsql-hackers/2010-05/msg01112.php<br />
}}<br />
<br />
{{TodoItem<br />
|Allow cross-compiling by generating the zic database on the target system}}<br />
<br />
{{TodoItem<br />
|Improve NLS maintenance of libpgport messages linked onto applications}}<br />
<br />
{{TodoItem<br />
|Use UTF8 encoding for NLS messages so all server encodings can read them properly}}<br />
<br />
{{TodoItem<br />
|Allow creation of universal binaries for Darwin<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg00884.php <nowiki>Getting to universal binaries for Darwin</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider GnuTLS if OpenSSL license becomes a problem<br />
* http://archives.postgresql.org/pgsql-hackers/2011-02/msg00892.php<br />
* [http://archives.postgresql.org/pgsql-patches/2006-05/msg00040.php <nowiki>[PATCH] Add support for GnuTLS</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-12/msg01213.php <nowiki>TODO: GNU TLS</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider making NAMEDATALEN more configurable in future releases}}<br />
<br />
{{TodoItem<br />
|Research use of signals and sleep wake ups<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-07/msg00003.php <nowiki>Restartable signals 'n all that</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow C++ code to more easily access backend code<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-12/msg00302.php <nowiki>Mostly Harmless: Welcoming our C++ friends</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider simplifying how memory context resets handle child contexts<br />
* [http://archives.postgresql.org/pgsql-patches/2007-08/msg00067.php <nowiki>Re: Memory leak in nodeAgg</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Create three versions of libpgport to simplify client code<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg00154.php <nowiki>8.4 TODO item: make src/port support libpq and ecpg directly</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve detection of shared memory segments being used by others by checking the SysV shared memory field 'nattch'<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-01/msg00656.php <nowiki>postgresql in FreeBSD jails: proposal</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-01/msg00673.php <nowiki>Re: postgresql in FreeBSD jails: proposal</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Implement the non-threaded Avahi service discovery protocol<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-02/msg00939.php <nowiki>Re: [PATCHES] Avahi support for Postgresql</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-patches/2008-02/msg00097.php <nowiki>Re: Avahi support for Postgresql</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-03/msg01211.php <nowiki>Re: [PATCHES] Avahi support for Postgresql</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-patches/2008-04/msg00001.php <nowiki>Re: [HACKERS] Avahi support for Postgresql</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Reduce data row alignment requirements on some 64-bit systems<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-10/msg00369.php <nowiki>[WIP] Reduce alignment requirements on 64-bit systems.</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Restructure TOAST internal storage format for greater flexibility<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-11/msg00049.php <nowiki>Re: PG_PAGE_LAYOUT_VERSION 5 - time for change</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
| Add regression tests for pg_dump/restore<br />
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg01967.php <nowiki>"make install-check-pg_dump" target in src/regress]</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
| Research different memory allocation methods for lists<br />
* http://archives.postgresql.org/pgsql-hackers/2011-04/msg01467.php <br />
}}<br />
<br />
{{TodoItem<br />
| Consider removing the attribute options cache<br />
* http://archives.postgresql.org/pgsql-hackers/2011-03/msg00039.php<br />
}}<br />
<br />
{{TodoItem<br />
| Restructure /contrib section<br />
* http://archives.postgresql.org/pgsql-hackers/2011-06/msg00705.php<br />
}}<br />
<br />
{{TodoItem<br />
| Consider adding explicit huge page support<br />
* http://archives.postgresql.org/pgsql-hackers/2012-07/msg00123.php<br />
}}<br />
<br />
=== /contrib/pg_upgrade ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Handle large object comments<br />
|This is difficult to do because the large object doesn't exist when --schema-only is loaded.<br />
}}<br />
<br />
{{TodoItem<br />
|Consider using pg_depend for checking object usage in version.c<br />
}}<br />
<br />
{{TodoItem<br />
|If reindex is necessary, allow it to be done in parallel with pg_dump custom format<br />
}}<br />
<br />
{{TodoItem<br />
|Migrate pg_statistic by dumping it out as a flat file, so analyze is not necessary<br />
|pg_class.oid is not preserved so schema.tablename must be used.<br />
* [http://archives.postgresql.org/message-id/CAAZKuFaWdLkK8eozSAooZBets9y_mfo2HS6urPAKXEPbd-JLCA@mail.gmail.com pg_upgrade and statistics]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve testing, perhaps using the buildfarm<br />
|The buildfarm has access to multiple versions of PostgreSQL.<br />
}}<br />
<br />
{{TodoItem<br />
|Create machine-readable output of pg_controldata<br />
|This would avoid parsing its output. The problem is we need pg_controldata output from both the old and new clusters so we would need to support both formats.<br />
}}<br />
<br />
{{TodoItem<br />
|Find cleaner way to start/stop dedicated servers for upgrades<br />
* http://archives.postgresql.org/pgsql-hackers/2012-08/msg00275.php<br />
}}<br />
<br />
{{TodoItem<br />
|Consider a way to run pg_upgrade on standby servers<br />
* http://archives.postgresql.org/pgsql-hackers/2012-07/msg00453.php<br />
* http://archives.postgresql.org/pgsql-hackers/2012-09/msg00056.php<br />
}}<br />
<br />
{{TodoItem<br />
|Desired changes that would prevent upgrades with pg_upgrade<br />
* 32-bit page checksums<br />
* Add metapage to GiST indexes<br />
* Clean up hstore's internal representation<br />
* Remove tuple infomask bit HEAP_MOVED_OFF and HEAP_MOVED_IN<br />
* [http://www.postgresql.org/message-id/CAK+WP1xdmyswEehMuetNztM4H199Z1w9KWRHVMKzyyFM+hV%3DzA@mail.gmail.com fix char() index trailing space handling]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Windows ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Remove configure.in check for link failure when cause is found}}<br />
<br />
{{TodoItem<br />
|Remove readdir() errno patch when runtime/mingwex/dirent.c rev 1.4 is released}}<br />
<br />
{{TodoItem<br />
|Allow psql to use readline once non-US code pages work with backslashes}}<br />
<br />
{{TodoItem<br />
|Fix problem with shared memory on the Win32 Terminal Server}}<br />
<br />
{{TodoItem<br />
|Improve signal handling<br />
* [http://archives.postgresql.org/pgsql-patches/2005-06/msg00027.php <nowiki>Simplify Win32 Signaling code</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Convert MSVC build system to remove most batch files<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-08/msg00961.php <nowiki>MSVC build system</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Support pgxs when using MSVC}}<br />
<br />
{{TodoItem<br />
|Fix MSVC NLS support, like for to_char()<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-02/msg00485.php <nowiki>NLS on MSVC strikes back!</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-patches/2008-02/msg00038.php <nowiki>Fix for 8.3 MSVC locale (Was [HACKERS] NLS on MSVC strikes back!)</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Find a correct rint() substitute on Windows<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-01/msg00808.php <nowiki>Minor bug in src/port/rint.c</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Fix global namespace issues when using multiple terminal server sessions<br />
* [http://archives.postgresql.org/message-id/48F3BFCC.8030107@dunslane.net problems with Windows global namespace]}}<br />
<br />
{{TodoItem<br />
|Change from the current autoconf/gmake build system to cmake<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-12/msg01869.php <nowiki>About CMake (was Re: [COMMITTERS] pgsql: Append major version number and for libraries soname major)</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve consistency of path separator usage<br />
* http://archives.postgresql.org/message-id/49C0BDC5.4010002@hagander.net<br />
}}<br />
<br />
{{TodoItem<br />
|Fix cross-compiling on Windows<br />
* http://archives.postgresql.org/pgsql-bugs/2010-10/msg00110.php<br />
}}<br />
<br />
{{TodoItem<br />
|Reduce file statistics overhead on directory reads<br />
* http://www.postgresql.org/message-id/1338325561.82125.YahooMailNeo@web39304.mail.mud.yahoo.com<br />
}}<br />
<br />
<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Wire Protocol Changes ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Allow dynamic character set handling}}<br />
<br />
{{TodoItem<br />
|Let the client indicate character encoding of database names, user names, and passwords<br />
* http://www.postgresql.org/message-id/16160.1360540050@sss.pgh.pa.us}}<br />
* http://www.postgresql.org/message-id/20131220030725.GA1411150@tornado.leadboat.com<br />
<br />
{{TodoItem<br />
|Add decoded type, length, precision}}<br />
<br />
{{TodoItem<br />
|Mark result columns as known-not-null when possible<br />
* [http://archives.postgresql.org/pgsql-hackers/2010-11/msg01029.php <nowiki>Adding nullable indicator to Describe</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Provide more control over planner treatment of statements being prepared}}<br />
<br />
{{TodoItem<br />
|Use compression<br />
|If SSL is used, hopefully avoid the overhead of key negotiation and encryption<br />
* http://archives.postgresql.org/pgsql-hackers/2012-06/msg00793.php<br />
}}<br />
<br />
{{TodoItem<br />
|Update clients to use data types, typmod, schema.table.column names of result sets using new statement protocol}}<br />
<br />
{{TodoItem<br />
|Set protocol for wire format negotiation<br />
* [http://archives.postgresql.org/message-id/CACMqXCKkGrGXxQhjHCKCe0B8hn6sTt-1sdgHZOSGQMxrusOsQA@mail.gmail.com GUC_REPORT for protocol tunables]<br />
}}<br />
<br />
{{TodoItem<br />
|Make sure upgrading to a 4.1 protocol version will actually work smoothly<br />
* [http://archives.postgresql.org/message-id/28307.1318255008@sss.pgh.pa.us Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow multi-state authentication<br />
* http://www.postgresql.org/message-id/51A44185.5060306@2ndquadrant.com<br />
}}<br />
<br />
{{TodoItem<br />
|Identify the affected object in CommandComplete message?<br />
* http://www.postgresql.org/message-id/CAAfz9KNGVoyM+z_2tnPKTDXG_RdR9a33Y5s+zQ9LdwTTsqqZng@mail.gmail.com<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
== Documentation ==<br />
<br />
{{TodoItemEasy <br />
| Add contrib functions to the index<br />
* Add the functions and GUCs in the contrib modules to [http://www.postgresql.org/docs/current/static/sql-createindex.html the documentation index]: [http://archives.postgresql.org/message-id/50A2E173.6030404@2ndQuadrant.com per list discussion]<br />
}}<br />
<br />
{{TodoItem<br />
|Convert single quotes to apostrophes in the PDF documentation<br />
* [http://archives.postgresql.org/pgsql-docs/2007-12/msg00059.php <nowiki>SGML docs and pdf single-quotes</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Provide a manpage for postgresql.conf<br />
* {{messageLink|20080819194311.GH4428@alvh.no-ip.org|A smaller default postgresql.conf}}<br />
* {{messageLink|200808211910.37524.peter_e@gmx.net|A smaller default postgresql.conf}}<br />
}}<br />
<br />
{{TodoItem<br />
|Change the manpage-generating toolchain to use the new XML-based docbook2x tools<br />
* {{messageLink|200808211910.37524.peter_e@gmx.net|A smaller default postgresql.conf}}<br />
}}<br />
<br />
{{TodoItem<br />
|Consider changing documentation format from SGML to XML<br />
* [http://archives.postgresql.org/pgsql-docs/2006-12/msg00152.php <nowiki>Re: Authoring Tools WAS: Switching to XML</nowiki>]<br />
* http://archives.postgresql.org/pgsql-docs/2011-04/msg00020.php<br />
* http://wiki.postgresql.org/wiki/Switching_PostgreSQL_documentation_from_SGML_to_XML<br />
}}<br />
<br />
{{TodoItem<br />
|Document support for N<nowiki>' '</nowiki> national character string literals, if it matches the SQL standard<br />
* http://archives.postgresql.org/message-id/1275895438.1849.1.camel@fsopti579.F-Secure.com<br />
}}<br />
<br />
{{TodoItem<br />
|Add diagrams to the documentation<br />
* http://archives.postgresql.org/pgsql-docs/2010-07/msg00001.php<br />
}}<br />
<br />
== Exotic Features ==<br />
<br />
{{TodoItem<br />
|Add pre-parsing phase that converts non-ISO syntax to supported syntax<br />
|This could allow SQL written for other databases to run without modification.}}<br />
<br />
{{TodoItem<br />
|Allow plug-in modules to emulate features from other databases}}<br />
<br />
{{TodoItem<br />
|Add features of Oracle-style packages<br />
|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 &quot;packages&quot; syntax at all.<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-08/msg00384.php <nowiki>proposal for PL packages for 8.3.</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider allowing control of upper/lower case folding of unquoted identifiers<br />
* [http://archives.postgresql.org/pgsql-hackers/2004-04/msg00818.php <nowiki>Bringing PostgreSQL torwards the standard regarding case folding</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2006-10/msg01527.php <nowiki>Re: [SQL] Case Preservation disregarding case sensitivity?</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-03/msg00849.php <nowiki>TODO Item: Consider allowing control of upper/lower case folding of unquoted, identifiers</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg00415.php <nowiki>Identifier case folding notes</nowiki>]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg00415.php <nowiki>Identifier case folding notes</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add autonomous transactions<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php <nowiki>autonomous transactions</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Give query progress indication<br />
* [[Query progress indication]]<br />
}}<br />
<br />
{{TodoItem<br />
|Rethink our type system<br />
* [[Rethinking datatypes]]<br />
}}<br />
<br />
{{TodoItem<br />
|Improve push-down of joins, aggregates, and sorts to foreign data wrappers<br />
* http://www.postgresql.org/message-id/20131121150515.GC23976@momjian.us<br />
}}<br />
<br />
== Features We Do ''Not'' Want ==<br />
<br />
The following features have been discussed ad nauseum on the PostgreSQL mailing lists and the consensus has been that the project is not interested in them. As such, if you are going to bring them up as potential features, you will want to be familiar with all of the arguments against these features which have been previously made over the years. If you decide to work on such features anyway, you should be aware that you face a higher-than-normal barrier to get the Project to accept them.<br />
<br />
{{TodoItem<br />
|All backends running as threads in a single process (not wanted)<br />
|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, and MySQL and DB2 have demonstrated that threads introduce as many issues as they solve. Threading specific operations such as I/O, seq scans, and connection management has been discussed and will probably be implemented to enable specific performance features. Moving to a threaded engine would also require halting all other work on PostgreSQL for one to two years.}}<br />
<br />
{{TodoItem<br />
|"Oracle-style" optimizer hints (not wanted)<br />
|Optimizer hints, as implemented in Oracle and other RDBMSes, are used to work around problems in the optimizer and introduce upgrade and maintenance issues. We would rather have such problems reported and fixed. We have discussed a more sophisticated system of per-class cost adjustment instead, but a specification remains to be developed. See [[OptimizerHintsDiscussion|Optimizer Hints Discussion]] for further information.}}<br />
<br />
{{TodoItem<br />
|Embedded server (not wanted)<br />
|While PostgreSQL clients runs fine in limited-resource environments, the server requires multiple processes and a stable pool of resources to run reliably 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. Besides, there are several very mature embedded SQL databases already available.}}<br />
<br />
{{TodoItem<br />
|Obfuscated function source code (not wanted)<br />
|Obfuscating function source code has minimal protective benefits because anyone with super-user access can find a way to view the code. At the same time, it would greatly complicate backups and other administrative tasks. To prevent non-super-users from viewing function source code, remove SELECT permission on pg_proc.<br />
* [http://archives.postgresql.org/pgsql-general/2008-09/msg00668.php <nowiki>Obfuscated stored procedures (was Re: Oracle and Postgresql)</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Indeterminate behavior for the GROUP BY clause (not wanted)<br />
|At least one other database product allows specification of a subset of the result columns which GROUP BY would need to be able to provide predictable results; the server is free to return any value from the group. This is not viewed as a desirable feature. PostgreSQL 9.1 allows result columns that are not referenced by GROUP BY if a primary key for the same table is referenced in GROUP BY.<br />
* [http://archives.postgresql.org/pgsql-hackers/2010-03/msg00297.php <nowiki>Re: SQL compatibility reminder: MySQL vs PostgreSQL</nowiki>]<br />
}}<br />
<br />
</div><br />
<br />
[[Category:Todo]]</div>Robinshttps://wiki.postgresql.org/index.php?title=PgCon_2013_Developer_Meeting&diff=19825PgCon 2013 Developer Meeting2013-05-23T01:42:02Z<p>Robins: Correct spelling error SEPostres -> SEPostgres</p>
<hr />
<div>A meeting of the most active PostgreSQL developers is being planned for Wednesday 22nd May, 2013 near the University of Ottawa, prior to pgCon 2013. In order to keep the numbers manageable, this meeting is '''by invitation only'''. Unfortunately it is quite possible that we've overlooked important code developers during the planning of the event - if you feel you fall into this category and would like to attend, please contact Dave Page (dpage@pgadmin.org). <br />
<br />
Please note that this year the attendee numbers have been kept low in order to keep the meeting more productive. Invitations have been sent only to developers that have been highly active on the database server over the 9.3 release cycle. We have not invited any contributors based on their contributions to related projects, or seniority in regional user groups or sponsoring companies, unlike in previous years.<br />
<br />
This is a PostgreSQL Community event. Room and refreshments/food sponsored by EnterpriseDB. Other companies sponsored attendance for their developers.<br />
<br />
== Time & Location ==<br />
<br />
The meeting will be from 8:30AM to 5PM, and will be in the "Red Experience" room at:<br />
<br />
Novotel Ottawa<br />
33 Nicholas Street<br />
Ottawa<br />
Ontario<br />
K1N 9M7<br />
<br />
Food and drink will be provided throughout the day, including breakfast from 8AM.<br />
<br />
[http://maps.google.ca/maps?f=q&source=s_q&hl=en&geocode=&q=novotel+ottawa&aq=&sll=49.891235,-97.15369&sspn=36.237851,79.013672&ie=UTF8&hq=novotel+ottawa&hnear=&ll=45.421528,-75.683699&spn=0.036869,0.077162&z=14&iwloc=A&layer=c&cbll=45.425741,-75.689638&panoid=Z4FUGnkZkdHAOkIxyjjS9Q&cbp=12,25.83,,0,-0.6 View on Google Maps]<br />
<br />
== Attendees ==<br />
<br />
The following people have RSVPed to the meeting (in alphabetical order, by surname):<br />
<br />
* Josh Berkus (secretary)<br />
* Jeff Davis<br />
* Andrew Dunstan<br />
* Peter Eisentraut<br />
* Dimitri Fontaine<br />
* Andres Freund<br />
* Stephen Frost<br />
* Peter Geoghegan<br />
* Kevin Grittner<br />
* Robert Haas<br />
* Magnus Hagander<br />
* KaiGai Kohei<br />
* Alexander Korotkov<br />
* Tom Lane<br />
* Fujii Masao<br />
* Noah Misch<br />
* Bruce Momjian<br />
* Dave Page (chair)<br />
* Simon Riggs<br />
<br />
== Proposed Agenda Items ==<br />
<br />
Please list proposed agenda items here:<br />
<br />
* 9.4 Commitfest schedule and Commitfest tools.<br />
* [http://wiki.postgresql.org/wiki/Parallel_Query_Execution Parallel Query Execution] (Bruce, Noah)<br />
* logical changeset generation review & integration (Andres)<br />
* utilization of upcoming non-volatile RAM device (Kaigai)<br />
* pluggable plan/exec nodes (Kaigai)<br />
** to offload targetlist calculation, sorting, aggregates, ...<br />
* [[GIN generalization]] (Alexander)<br />
* An Extensibility Roadmap (dim) (http://pgsql.tapoueh.org/temp/extensibility.pdf) (15 min)<br />
* Representing severity - derive severity from SQLSTATE (Peter Geoghegan - see http://www.postgresql.org/message-id/CA+TgmoZEjq7va+SfDZQwk6E4emEWThENNyxfqEGhB3iuoT1OJw@mail.gmail.com) (10 min)<br />
* Error logging infrastructure - store normalized statistics about errors in a circular buffer (Peter Geoghegan). Arguably this could be discussed alongside SQLSTATE item. (10 min)<br />
* Failback with backup (Fujii Masao - related discussion is: http://www.postgresql.org/message-id/CAF8Q-Gxg3PQTf71NVECe-6OzRaew5pWhk7yQtbJgWrFu513s+Q@mail.gmail.com)<br />
* Volume Management (Stephen Frost - wiki page will be forthcoming before the meeting)<br />
* AXLE Project - Big data analytics for Postgres (Simon Riggs) - an overview of the feature plan, how project works and what community can expect (15 min)<br />
* Incremental maintenance of materialized views (Kevin) - differential REFRESH and infrastructure for ''counting'' algorithm (30 min)<br />
<br />
== Agenda ==<br />
<br />
{| border="1" cellpadding="4" cellspacing="0"<br />
!Time<br />
!Item<br />
!Presenter<br />
|- style="font-style:italic;background-color:lightgray;"<br />
|08:00<br />
|Breakfast<br />
|<br />
<br />
|- style="font-style:italic;background-color:lightgray;"<br />
|08:30 - 08:45<br />
|Welcome and introductions<br />
|Dave Page<br />
<br />
|-<br />
|08:45 - 09:45<br />
|Parallel Query Execution<br />
|Bruce/Noah<br />
<br />
|-<br />
|09:45 - 10:15<br />
|Pluggable plan/exec nodes<br />
|KaiGai<br />
<br />
|-<br />
|10:15 - 10:30<br />
|Volume Management<br />
|Stephen Frost<br />
<br />
|- style="font-style:italic;background-color:lightgray;"<br />
|10:30 - 10:45<br />
|Coffee break<br />
|<br />
<br />
|-<br />
|10:45 - 11:00<br />
|Utilization of upcoming non-volatile RAM devices<br />
|KaiGai<br />
<br />
|-<br />
|11:00 - 11:30<br />
|Logical changeset generation review & integration<br />
|Andres<br />
<br />
|-<br />
|11:30 - 11:40<br />
|Representing severity<br />
|Peter G.<br />
<br />
|-<br />
|11:40 - 11:50<br />
|Error logging infrastructure<br />
|Peter G.<br />
<br />
|-<br />
|11:50 - 12:30<br />
|Incremental maintenance of materialized views<br />
|Kevin<br />
<br />
|- style="font-style:italic;background-color:lightgray;"<br />
|12:30 - 13:30<br />
|Lunch <br />
|<br />
<br />
|-<br />
|13:30 - 14:15<br />
|GIN generalization<br />
|Alexander<br />
<br />
|-<br />
|14:15 - 14:30<br />
|An Extensibility Roadmap<br />
|Dimitri<br />
<br />
|-<br />
|14:30 - 15:00<br />
|Failback with backup<br />
|Fujii<br />
<br />
|- style="font-style:italic;background-color:lightgray;"<br />
|15:00 - 15:15<br />
|Tea break<br />
|<br />
<br />
|-<br />
|15:15 - 15:45<br />
|9.4 Commitfest schedule and tools<br />
|Josh<br />
<br />
|-<br />
|15:45 - 16:45<br />
|Goals, priorities, and resources for 9.4<br />
|All<br />
<br />
|- style="font-style:italic;background-color:lightgray;"<br />
|16:45 - 17:00<br />
|Any other business/group photo<br />
|Dave Page<br />
<br />
|- style="font-style:italic;background-color:lightgray;"<br />
|17:00<br />
|Finish<br />
| <br />
|}<br />
<br />
== Notes ==<br />
<br />
Attending:<br />
<br />
* Dave Page, EnterpriseDB<br />
* Andres Freund, 2ndQuadrant<br />
* Kevin Grittner, EnterpriseDB<br />
* Dimitri Fontaine, 2ndQuadrant<br />
* Andrew Dunstan, PostgreSQL Experts<br />
* Noah Misch, EnterpriseDB<br />
* Bruce Momjian, EnterpriseDB<br />
* Fujii Masao, NTT Data<br />
* Tom Lane, Salesforce<br />
* Magnus Hagander, Redpill Linpro<br />
* Robert Haas, EnterpriseDB<br />
* Josh Berkus, PostgreSQL Experts<br />
* Kaigai Kohei, NEC<br />
* Jeff Davis, Teradata<br />
* Alexander Korotkov<br />
* Peter Geoghegan, Heroku<br />
* Peter Eisentraut, Meetme<br />
* Stephen Frost<br />
<br />
=== Parallelism ===<br />
<br />
Bruce Momjian is looking at where Postgres is and hardware changes, and it's time to look at parallelism. Unlike the Windows port and pgUpgrade, there's no clear "done" with Parallelism. We're going to have to do a lot of small things, but not one big feature. Concern anout code cleanliness and stability. What is going to have to happen is that we'll attack one small thing, and build the infrastructure for parallelism.<br />
<br />
Robert Haas is talking about EnterpriseDB's commitment to parallelism. The two things EDB wants is materialized views and parallel query. The way we're approaching this is the same way as 2Q approached logical replication for the last release cycle. We're doing this as a company, and we have buy-in from our management. So far there's a wiki page on parallel sort and Noah's posted some stuff to pgsql-hackers. The first part is to get a credible worker system in place, and then we can tackle parallelising particular things.<br />
<br />
Stephen Frost pointed out that users are currently ad-hoc implementing parallelism in their middleware code. Bruce said that there was a basic set of steps for all parallel tasks. There's a false sense that threads automatically give you infrastructure for parallelism. Bruce doesn't think that's true. Having the worker/marshalling stuff sprinkles all over the code would be really bad, so we want central infrastructure.<br />
<br />
Jeff Davis pointed out that there were different approaches to parallelism. One is "cluster parallelism". Do we know what approaches were taking? Cluster parallelism involves making the parallel tasks according to data partitions. It's popular in data warehousing. Robert Haas doesn't expect to get that far in one release cycle.<br />
<br />
Haas: People come up with great ideas for PostgreSQL, and they do two things: either they figure out how to do it without modifing the query planner, or they fail. So we looked at index building, which wouldn't require dealing with the query planner. But the general problem of parallel query planning, we have to solve harder problems. I don't want to get bogged down in those sorts of questions at the outset, because there's a bunch of stuff to get done to execute parallel jobs in general.<br />
<br />
Josh Berkus suggested implementing a framework for parallel function execution because then users could implement parallel code for themselves. It would help the Geo folks. Noah thinks this is possible today, but isn't specific how. Tom argues against exposing it to users in early iterations because the API will change.<br />
<br />
There's a few things you need:<br />
* and efficient way for passing data to the parallel backends, probably using a shared memory facility, because sockets are too slow.<br />
* some logic for starting and stopping worker processes. Custom background workers aren't quite what we need for this. Also different from Autovacuum, which is a bit kludgy.<br />
* you need to be able to do stuff in the worker processes as if they were the parent process. They need to share the parent worker's state, and there are a lot of state things which are not shared. If the master takes new snapshots or acquires extra XIDs, not sure how to share that. Some things will need to be prohibited in parallel mode. Threads don't solve this. Syscache lookups are also a problem, but we need them.<br />
<br />
Noah wants to target parallel sort, specifically parallel memory sort. This hits a lot of the areas we need to tackle to make parallelism work in general. We need a cost model as well. How are we going to mark the functions which are safe to run in a parallel worker. We don't want to just call functions *_parallel because that will change. Maybe there will be an internal column in pgproc, as a short-term solution.<br />
<br />
Peter E. asked about timeline. For 9.4, we want to at least have an index build which runs a user-specified amount of parallelism. It needs to be reasonably fast.<br />
<br />
Peter G. asked about having a cost model for parallelism. Right now we don't have costing for how long it takes to sort things based on the number of rows. Sorting a text column in bad collation can be 1000X as expensive as sorting integers, for example. We might pick a single operator and make that the cost reference operator. Perfect costing isn't possible, but we can do some approximates. The initial operations we choose for parallelism will be very long operations. Startup costs are too high otherwise. We're not going to parallelize something that's 200ms. Something that takes 10s or a minute or a couple minutes.<br />
<br />
Haas thinks that a lot of people will be appalled for starting up a parallel worker. That can be optimized later. It's OK for the initial version to be unoptimized. Even if it takes a full second to start up a new backend, there are sorting tasks which take large numbers of seconds. Those are existing issues which we'll hammer on as we get into this space; we may fix starting up a new connection speed in the process.<br />
<br />
Josh pointed out that taking a hour to build an index, it's probably an external sort. Noah posted a patch to allow larger internal sorts, over 1GB. Andrew pointed out that a process model would tie us to certain large operations. Threads would add a lot of overhead to everything, though. We'd have to rewrite palloc. Haas things we can get the minimum unit down to something fairly small. Andrew pointed out that on windows process creation is very expensive. Haas doesn't want rewrite the entire internal infrastructure.<br />
<br />
With Threads, everything is shared by default, with processes, everything is unshared by default. The process model and explicit sharing is a shorter path from where we are currently. Parallelism helps with CPU-bound processes, but not IO. Josh argued with Kevin that there are some types of storage where this isn't true. Kevin just pointed out that if the resource you're using the most of isn't bottlenecked, then it's not helpful to parallelize. Haas pointed out that parallelizing seq scan on a single rotating disk won't help, as opposed to parallelizing scan from memory, which would be much faster. Our cost model isn't up to this; we might even have to have a recheck model where the executor notices things are slow and switches approaches.<br />
<br />
Bruce pointed out how Informix switched to threads between 5 and 7 and it killed the database. Parallelism will take Postgres into new markets.<br />
<br />
Andrew pointed out that prefork backends will help us form new connections if we can get it to work. Haas pointed out that we're going to have to cut nonessential issues to avoid taking forever.<br />
<br />
=== Pluggable plan/exec nodes ===<br />
<br />
Kaigai is working on GPU execution. When he worked on writable FDW, pseudo-column approach for foreign scan node returning an already computed value, but that was rejected, because the scan plan needs to return the data structure as its definition. So Kaigai wants to add an API to add a plan node to the exeuction node, allowing executor to run extension code during query execution. When plan tree tries to scan large table with sequential scan, and the target list has a complex calculation, we can have a pseudo-column which does this calculation on a GPU.<br />
<br />
Kaigai is talking about planner and executor. Haas doesn't understand how we would have pluggable planner nodes, as opposed to executor nodes. How would you allow it generate completely new types of plan nodes? We can replace existing plan nodes, but new types of nodes would require a new extensibility infrastructure. To do this, we need two new infrastructures to inject plan nodes and executor nodes. But Kaigai is mainly focused on is replacing existing scans and sort nodes. He didn't investigate the difficulty on planner extension yet.<br />
<br />
Peter E. pointed out that 65% of the work will be the ability to add new nodes at all. Replacement will be MUCH easier. However, the ability to add new nodes would be very useful to PostgreSQL in general. Tom thinks that it could be done. Haas pointed out that we have a lot of wierd optimizations about what plan node connects to which other plan node. Tom doesn't think that we have that many. Noah says we'll probably use a hook.<br />
<br />
For a new executor node we have a dispatch table, it's easy. Plan nodes could use a jump table too. Right now we have function volatility markers; for nodes we'll need the same thing. But that's a problem only for expression nodes.<br />
<br />
This was discussed in the cluster meeting. PostgresXC wanted pluggable nodes for cluster scan, as do some other people. So a general pluggability infrastructure would be good. If we have pluggable scan nodes, we can plug in cluster scan as well as GPU scan.<br />
<br />
Jeff Davis pointed out that range join could be our first pluggable node. Haas pointed out that opclass support requirements might make it difficult; there are easier cases. Range join might need to be hardcoded. Pluggable planner stuff is hard.<br />
<br />
This would also maybe get people who fork Postgres to stay closer to the core project and implement extensions instead of having an incompatible fork which then doesn't work with others.<br />
<br />
=== Volume Management ===<br />
<br />
Right now we have tablespaces. Having some more automation around using them would help. Like we want the indexes on a separate tablespace from the heap; there ought to be automation for this. Somebody hacked up something like this ... maybe Depesz, in 2007.<br />
<br />
Haas asked if having indexes on a separate volume was actually faster. Frost asserted that it was. Josh brought up that with new small fast storage there's reasons to want stuff to move around again. Also, index-only scans. If I only have one column, then I can do index-only scans, so I want to put the index on faster storage. Josh pointed out that indexes-separate worked back when at OSDL.<br />
<br />
Stephen Frost pointed out that they have pairs of drives, with a whole lot of pairs. Stephen asked about whether or not we'll ever have things like Oracle Volumes. Kevin said that that configuration works on raw devices, but not so much on complex filesystems. FRost says that for specific workloads, it really works to parallelize everything for massive joins.<br />
<br />
Several people asserted that modern RAID is fairly efficient. Josh asked if any default automated rules would work for a general class.<br />
<br />
Frost explained Oracle Volumes. They can be used to go to raw drives. Volumes are disks or drives or files. You can have multiple volumes under a single tablespace, and Oracle will manage them together. Do we want to do that? Maybe we should just use LVM.<br />
<br />
There's also some other things we could do with volumes, like compressed volumes. Noah has seen tablespaces abused 5 times as much as used properly. We should be careful that what we're adding is really useful. People want things like encrypted & compressed tablespaces. Every time something like this comes up, Tom or someone says "use a filesystem which provides that." There are some advantages to having the database do that, but there's a lot of development effort.<br />
<br />
Noah suggested that event triggers would do this. Frost says that they already have code, they want to simplify it. Josh points out that there aren't simple rules for this; most DWs don't have rules which are as simple as "indexes go here, tables go there". A lot of this is misplaced Oracle knowledge. Josh brought up the backup issue again.<br />
<br />
=== Utilization of upcoming non-volatile RAM devices ===<br />
<br />
Kagai is just presenting an idea and asking for opinions. Wants to consider the impact of these new devices. Wants to boost the performance to write the transaction logs. Once we have mmaped to the nvram device, then we can consider that completion of log writing. Non-volatile ram will be exposed as main memory. There are Linux patches which allows mmaping to these devices. Once we have these devices, it dramatically reduces the overhead to write the transaction log.<br />
<br />
Kaigai said that this topic comes from the forks on ram storage devices. We wanted improvement of row-writing performance. Also appliable for general fast SSD storage. Wants to add fork around row-writing and create extensions where we maybe have nvram and mmapped region for the xlog.<br />
<br />
Josh points out that this dovetails with transactional memory, which allows lock exchange for multiple K of data and multiple instruction. Like we might not need as much from the WAL. Others disputed that.<br />
<br />
Kaigai wants to implement this for the write-ahead log. Then later we can look at the heap. NVRam has limited write cycles, so we don't want to use it for main storage. <br />
<br />
=== Logical changeset generation review & integration ===<br />
<br />
Andres gave a progress report on current status of the changeset generation patch. It's mostly done, but we need to integrate it and it needs substantial review. He needs someone to help, not from 2Q -- someone independant. How can he proceed to get the pieces into core?<br />
<br />
Haas suggested that they could swap for review on parallel query. The patch is currently about 12,000 lines. Peter G says he can take a shot at it, but he wants someone else.<br />
<br />
Frost asked how much was additional stuff there is vs. changes to existing behavior. There's new kinds of TOAST tuples. There's mapping relfilenode back to the OID, which is controversial. Andres says that it's safe the way they do it, and if it's broken in logical replication then it's already broken in Hot Standby, so we should fix it. There's some changes to heapam.c, like preventing full page writes from removing the tuple contents.<br />
<br />
Haas points out that Heikki objects to most of the functionality in core. Andres says that he can reduce complexity in some areas. Noah says that having zero implications for nonusers it can't possibly work. If it's a good thing, we're going to have to accept some code complexity cost to do it.<br />
<br />
The write-ahead log currently is designed to do crash recovery. We've pushed that for hot standby and PITR, but this is pushing it even further, we're adding another level of pain. TOAST tuples are a good example of this; how we crash recover them doesn't work for logical replication. To what extent do we use the WAL format we have now, and how much do we modify it? What's the performance cost?<br />
<br />
Some things don't need much intervention. But we have to look up stuff in the catalogs, and do catalog time travel to figure out what the object references mean. Peter E asked about simply creating a completely separate log for logical replication. Andres says that the tried this, but it had a huge extra cost, because you have to add a whole bunch of extra data to it. That would double synchronous writes, at least. So are we not going to investigate this? The logical replication stream would need to include catalog updates.<br />
<br />
Haas points out that the kludges for this aren't as bad as hot standby. Magnus points out that MySQL has to do 2PC between the binlog and the innodb log. But a lot of people are going to want this feature.<br />
<br />
Steve Singer has written 80% of the code for basing Slony on LCR. 2Q is also working on their own replicated solution for this.<br />
<br />
So the other modification we need to deal with xmin for pg_catalog and user tables differently. But this had some benefits for general Postgres efficiency. Can we split this up at all? The earlier patches can be reviewed separately.<br />
<br />
One other thing we devised for this is logical replication slots. You need to know where readers last left off. We could maybe use this for binary replication as well. You can decode on a hot standby, not just on a master. Primary key updates are handled. We only have the TOAST tuples if they change, though. We have before images, but they have some limitations. Kevin asked about rolling backwards to an earlier state.<br />
<br />
Haas will review some of the patch, but not the whole thing. We had the same issue with streaming replication, not enough reviewers. Haas would like to get it done early in the first commitfest. Andres will submit but is not sure we can commit it in the first round.<br />
<br />
=== Representing Severity ===<br />
<br />
Peter G. agitated about that there's no principled way to discover whether a "severe" error occurs, such as corruption from errors in xlog.c. There's no way that DBAs can confirm that an error of particular severity has occured, except grepping for particular strings. There's multiple distinctions here. Such as there are various "can't happen" errors which our developers want to see as soon as possible. Heroku also wants to bring errors to the attention of the lists automatically. The tail-n-mail approach is not scalable.<br />
<br />
Haas's object is whether it's possible to agree on categorization about whether or not something is severe. We have hundreds of ereports, so we need a very simple categorization scheme which everyone can agree on. Tom points out that "can't happen" and "severe" aren't necessarily the same thing. Maybe we could use SQLSTATE and tweak some things. Everyone thinks this is the way to go.<br />
<br />
It would be very useful to be able to filter the volume of logging too. We need to log sqlstate though. We might in addition put the severity concept into the log. We'd have to clean up how we use SQLSTATE too. We could supply some default filters, but users would need to tweak them. Filtering the log is a separate feature from fixing the SQLSTATE. That can be done later.<br />
<br />
=== Error Logging Infrastructure ===<br />
<br />
Peter G: As a complimentary feature, it would be great to be able to store errors in a circular buffer available, like pg_stat_statements. Peter E is doing a presentation related to this tommorrow using logging hooks. You can already do this.<br />
<br />
Peter E argues against doing more aggregated views like pg_stat_statements, because it doesn't provide historical or granular data. With logging hooks, you can build this more granularly. It already exists in 9.2. The average DBA can't write code in C, logging hooks allow you to work around this, you can get log data in JSON. Peter E argued against having a hardcoded system for this.<br />
<br />
Josh pointed out the efficiency argument. Robert pointed out the usefulness of "last message repeated 351 times". Peter G envisaged doing pg_stat_errors as a contrib module. This could be build on logging hooks. Dimitri pointed out that you can also do this with FDWs and CSV logs. Some discussion about how does Oracle Enterprise Manager do this? PEM uses CSV logs.<br />
<br />
Discussion about alternative approaches ensued.<br />
<br />
Heroku can feed back some information about errors using log aggregation. Frost would love to have performance information from Heroku.<br />
<br />
=== Incremental Maintenance of Matviews ===<br />
<br />
Kevin: Matching other DBs who have matviews will require a 5 year plan. We have a foundation in 9.3, you can declare a view, and it's materialized. It's just a foundation at this point. For 9.4, we want the first level of incremental maintenance. We can't get all the optimizations in, we just want to make the infractructure for the simplest cases in there.<br />
<br />
For the first commitfest, I want a transactional REFRESH using the approach on the lists. There will always be cases where incremental maintenance won't work. So we want transactional refres first, so that we can analyze whether it pays to update using the delta. If every row is touched, that's a loser. So we want to fall back to the full refresh if the delta is too large.<br />
<br />
He won't speculate on what we can get into each subsequent release. Planning to use the counting algorithm, it's very well established. Will only handle the simple cases at first, and will work through complex cases and see how far they get.<br />
<br />
Need a new system column, count_t, which counts the number of ways a give tuple can be derived from source data. It's a kind of reference count. There's a completely different algo for recursive views. Handling NOT EXISTS is a separate implementation too.<br />
<br />
What he wants to get in these first releases is how to implement the count_t column, how to implement other stuff in the first commitfest. Then the other CFs can be refinements. Peter E points out that count could overflow. The new column will be added only for new matviews which are specified as "incremental". We'll probably have other stratgies for incremental update, and it'll only be used by the counting strategy. You'd have to do an ALTER table to change that.<br />
<br />
Thinking of doing the incremental change as synchronous in the transaction. Initially will not work for async maintenance. You need a delta with a count which can be plus or minus, you need a snapshot for before and after the change. You need to generate the deltas with each change to the base table, but you don't need to update the matview immediately. <br />
<br />
There was discussion about how this applies to expensive, infrequently updated materialized views.<br />
<br />
The extra column would only be present in incremental materialized views and the deltas. There would also need conditional code in execution nodes which know how to handle the column. Haas questioned if there was some way we could add a non-system column. How do the executor nodes know not to display the system column. It's not like SELinux because it wouldn't be present in every table.<br />
<br />
Maybe we need a general notion of invisible columns. That would be generally useful. Then we could have extra state information. Bucardo could use it. A lot of things want to hide columns from "select *". We should add this concept. <br />
<br />
There was discussion about having stable system views. This change will break things like the ODBC driver, because it gets the list of columns from pg_attribute. We can start with just matviews. But right now drivers assume that anything with a positive attnum is visible. But there's no patch for logical column numbers now. Alvaro has a patch for it, it's complete but it's bitrotted. This is just like attisdropped; it broke a lot of things. Alvaro might update it, he's not sure he'll have time. But we have to get this done in CF1.<br />
<br />
If the next version is 10.0, though, we could break things. So maybe it's a good time.<br />
<br />
=== Lunch Notes ===<br />
<br />
Robert suggested getting rid of the v2 protocol in the next version. He thinks there's hacks in COPY which slow it down and are required for support of v2. But people want to see numbers on that. v2 is also used for JDBC, but that's to fix the plan cache issue which is fixed in 9.2. We need to see concrete improvements, though, and then there'd be an easy switch. We also should know what drivers are using v2, like pygresql.<br />
<br />
=== GIN generalization ===<br />
<br />
Alexander talked about general advances in GIN. Many people use external full text search because it's faster. One of the reasons GIN is slower is that it's used only for filtering, forcing the executor to page all of the results, and ranking. And it doesn't do LIMIT.<br />
<br />
So how the extended indexing solution works: there's positional information, so that we can calculate relevance. So there's now a structure to add extra infromation to the vector for any additional information. We can extract positions of words. To keep the index small we're using run-length compression. The index is then about the same size as the uncompressed index, with a lot of extra information.<br />
<br />
The problem is that we use the infrastructure from KNN. GIN declares that it can do ORDER BY with an operator, so you can get ORDER BY and filtering from the same index. Noah asked what ordering operator he's talking about. GIN calculates distance on item vectors, and then sorts by an array, and then executes gin_get_tuple(), which is new. It can then return them one-by-one for sorting. It's kind of like KNN, but it works differently, but it's much cheaper than getting the tuple from the heap. We need to tell the planner that we can return the original tuple from the index.<br />
<br />
This will be a new operator class. And we modify the operator classes for tsvector.<br />
<br />
Peter E asked for applications outside tsearch. Alexander said that it would also work for similarity searches. Can also be used for regex index. You can build a regex, index it, and then search for text strings which match that regex.<br />
<br />
Andres pointed out an alternate method for doing this, but it requires improvements to the planner. Haas encouraged maybe taking this approach. If we assume that the planner can get an expression from the index, then we need sorting before we go into the heap. That's going to be hard. That's a real issue for any steps which happen between checking the index and the heap. You could see tuples which aren't visible. This might not be a problem for the GIN case, but it's liable to be a problem for the general case.<br />
<br />
Maybe this is related to bitmap indexes. Btree operators need to be able to deal with invisible tuples, so this isn't new.<br />
<br />
Discussion of implementation methods ensued. There was question about what doesn't work in KNN-Gist infrastructure. Like it doesn't work without a WHERE clause. But this could be generalized. This will break amproc, but it's not like anyone makes access methods outside of core. Maybe we should get rid of pg_am and just have a jump table, pg_am just isn't really useful. We can't drop it because the oids there are used as keys elsewhere, and there's really not much cost.<br />
<br />
Another infrastructure question is that currently GIN only has to know one data type. Now when we introduce additional information, we need to know datatypes. SP-GIst needs 3 datatypes. That's why we have spgist_config. For GIN we'll need a new configuration method to return this datatype. So there's a question of breaking older stuff. Can you support both opclasses? Storage parameter would define it, so it would appear in conflict.<br />
<br />
This should probably be GIN2 with new access methods, etc. We'll be changing the storage format of GIN indexes. But that would require supporting the original GIN indefinitely. We could bump the version number on the GIN node page. Two choices: write the code so that it supports the old format, or add a whole new access method. Are we changing the operators or the access methods? Changes are mostly in GIN, a lot less in the operator class. Peter E pointed out that the compression could be a separate feature. Alexander says that the the change is not easily separable because of some of the performance optimizations.<br />
<br />
There was more implementation discussion about the methods for doing online upgrade if we change GIN. Rewriting on selects is out. In general we want to deprecate the old GIN, but we'll need a couple versions to do it.<br />
<br />
Frost raised the idea of general infrastructure to support upgrade-in-place. Like we could have a flag in pg_class. But several people dismissed this idea as not really helpful.<br />
<br />
=== An Extensibility Roadmap ===<br />
<br />
See slides linked from this page.<br />
<br />
We've already did some extensibility with create extension and create event trigger. Now I need the extension templates. I want reviewers and committers to understand where I want to go.<br />
<br />
DDL Execution Plans is after that ... like EXPLAIN ALTER TABLE. Dimitri wants CREATE EXTENSION to fetch code from the internet. But core doesn't want that, so Dimitri wants to do that using extension templates and event triggers. Then he wants a PL/C language which compiles on-the-fly. This would allow creating extensions from source dynamically. But the source is in a place only root has access to.<br />
<br />
Dimitri wants extensions to follow replication. There's some security issues with that. There's issue with having such things like downloading in core and having it not suck. There's a lot of reliability issues. Haas isn't keen on the whole idea. But Dimitri doesn't want to code all of the extension features in C. But this is already a problem with external PLs.<br />
<br />
Dimitri wants to be able to build full set of extensions using only a PostgreSQL connection and superuser access. Josh talked about the developer-push issue. But there's security issues with arbitrary C code, so a lot of people have issues with it. Peter G suggested we could somehow sandbox the C code. Haas says that this is possible, but you need kernel-level help.<br />
<br />
Josh says 3 problems: developer push, relication, and backup. But what if we solved it for non-C cases, but not C cases? Some people thing it would be useful. Extension Templates for packaged user code would be very useful for in-house code, so it can be packaged as an extension.<br />
<br />
=== Failback with Backup ===<br />
<br />
Fujii hears complaints from users: why do we need to take a full snapshot for failback? If the database is very large, this can take a long time. Three problems: timeline ID mismatch, WAL record mismatch, database inconsistency.<br />
<br />
Timeline ID: we can't start the old master as a replica because of the timeline ID. This is resolved now in 9.3.<br />
<br />
WAL Record Inconsistency: if the master crashes after writing a WAL record but not sending it to the standby, then it has WAL records the standby doesn't have. We can resolve this by removing all WAL records in the old master before starting it up in standby mode. Old master will then try to start recovery, and them start replication to retrieve the WAL records. One problem: last checkpoint record may not be replicated to the standby. Could we make the checkpointer wait for replication? If the Standby goes offline checkpoint would hang.<br />
<br />
Frost asked how large of a user problem this is? People cited examples.<br />
<br />
Haas suggested a holdback for datafile sync. Magnus pointed out that if we distinguish between failover and switchover. So we could have failback only for switchover circumstances. Jeff pointed out that you have to break replication and resync standbys after upgrade. He also thinks that we can use the WAL to help a diff between datafiles. Controlled switchover would fix the upgrade case too maybe.<br />
<br />
We could create a list of dirty blocks from the WAL. But there are a lot of special cases which would prevent rollback. <br />
<br />
Database inconsistency can happen if the master crashes. There can be some missing database changes. One solution is implementing undo logic, but that's very very difficult. Fujii's approach would be to add some write points to the master. Before the master writes the database to the disk, it writes the WAL to disk. It could wait for replication of WAL before writing to the LSN. Except for hint bits. And what if the replica goes away?<br />
<br />
Fujii wants to solve both controlled switchover case as well as the crash & failover case. The controlled swithover case is a lot easier to solve. You could freeze out existing connections, finish writes, and then switch over. There's a lot of similar ideas in Oracle QS.<br />
<br />
Josh suggested that the crash case isn't any different if we're already in synch rep. Others disagreed. Haas suggested that we could use Andres' infrastructure of slots for replication. Noah suggested that we could delay hint bit application until we're after walflushlocation. Maybe we should only solve this for controlled switchover.<br />
<br />
Jeff: for checksums, we already store the first modified record after the checkpoint. We could store very abbreviated information the WAL for checkpoints. There would be some significant cost for hint bits on a seq scan. If you want to be able to turn the WAL log into an undo log. You only have to copy certain pages. You could log only the first change since the last checkpoint. <br />
<br />
=== 9.4 Commitfest schedule and tools ===<br />
<br />
The CF schedule will be the same as last year. This time do what we said we'd do, not what we actually did. So include the triage periods etc. Josh will run first CF.<br />
<br />
Discussion about patch development during commitfest. Ending a commitfest takes a lot of work. There's a lot of work for moving stuff along. We could make a commitment that -hackers will not argue with the CF manager. Josh also wants to have a commitfest assistant. He called for volunteers. The author ought to be the one to take the patch out, it shouldn't wait on the CFM. Each patch deserves one solid review per CF, but not more than one.<br />
<br />
Kevin points out that the biggest issue is finding reviewers and getting the reviewers to do the review. Josh asked if having reviewers put their name down is actually helpful. Maybe we need to be really aggressive about taking names off. There's a big difference between Noah doing a review and some new reviewer doing a review. Josh suggested clearing reviewer after 5 days. Frost suggested removing them earlier. Maybe more tightly tying them to the archives would be good.<br />
<br />
Lots of discussion ensued.<br />
<br />
We should tell people to not put their names down until they are ready to start reviewing. The 10K line patches are not the problem, the 10 line patches are.<br />
<br />
Josh discussed replacing the CF tool, either with Gerrit or Reviewboard or something new. Frost suggested Github. Josh discussed some issues for reviewers. Discussion ensued, but not recorded because the secretary was speaking. Josh also wants automated patch build. Other people suggested automated apply.<br />
<br />
Josh will develop a spec for a new tool. He will run it past the CF managers for comment. Then post it to -hackers for comment. Haas is skeptical about 3rd-party tools. He hates git stuff. Frost discussed using forks in github. We should be able to track a git branch in the tool.<br />
<br />
=== Goals, priorities, and resources for 9.4 ===<br />
<br />
* Dave hopes to start writing pgAdmin4 this year.<br />
* Kevin will work on materialized views.<br />
* Dimitri: Extension templates and event triggers.<br />
* Andrew: most JSON work is complete, the rest can be extensions. Wants to work on grouping sets.<br />
* Noah working on EDB features (see above), and bug fixes.<br />
* Bruce is working on pgPool to make it better and more usable, maintainable and debugged.<br />
* Fujii is working on PostgreSQL replication. Would also like to work on pg_trigram for Japanese.<br />
* Simon (via Skype) Tablesample patch, COPY tuning, and security reviews (SEPostgres).<br />
* Tom will take an interest in pluggable storage. Maybe also Autonomous transactions.<br />
* Magnus will work on a new CF tool. Also more improvements to pgbasebackup.<br />
* Haas will be working on side issues on matviews and parallel query as they come up.<br />
* Josh will be working on new CF tool. With time/funding will work on automated testing.<br />
* Kaigai will work on row-level security. Also GPU stuff.<br />
* Jeff will be working on convincing Robert to remove pdallvisible. Also corruption detection. And Range JOIN, or inclusion constriants.<br />
* Alexander will work on new GIN.<br />
* Peter G will be working on error stuff (per above). Also wants to work on fixing archiving falling behind and panic shutdown. Also doing a little bit of work on upsert.<br />
* Andres will be doing logical replication.<br />
* Peter E has the transforms feature outstanding. And wants to move the documentation to XML. Wants to improve test coverage.<br />
* Frost will be doing pg infrastructure. Wants to help with CFs. Also fix some stuff with optimizer.<br />
* Greg Smith is working on autovacuum style I/O limits for other statements.<br />
<br />
[[Category:PostgreSQL Events]]</div>Robinshttps://wiki.postgresql.org/index.php?title=CodeCoverage&diff=19673CodeCoverage2013-05-11T00:17:32Z<p>Robins: Removed an earlier set of steps, and instead pointing to the correct page in PG documentation</p>
<hr />
<div>I have a patch that I will be submitting to add to the build system the capability of reporting on test code coverage metrics for the test suite. Actually it can show coverage for any application run against PostgreSQL. Download [[Image:Coverage.tar.gz]] to see an example report. Gunzip and un-tar the file and click on coverage/index.html. I had to delete most of the files to decrease the file size for upload, so only the links for access work.<br />
<br />
gcov reports line, branch, and function coverage, but lcov only reports on line coverage. I've added a link to the html to the gcov output that shows summary statistics for each file for line, branch, and function calls.<br />
<br />
The report gives a very clear and browseable view of what parts of the system might benefit from more extensive testing. It's obviously useful for planning future testing, but also can be used in conjunction with debugging to see what lines and functions are being exercised or missed by existing tests of the functionality under investigation. It could even be helpful to give a static view of lines hit by a bug test case in lieue of using a debugger. Also, when you're writing a unit test for new functionality, it would be good to check what you're actually hitting with the test.<br />
<br />
It uses gcov together with gcc to generate the statistics, and the lcov suite to create the html report. Both of these would obviously have to be installed to get a coverage report, but this would be an optional feature of the build. It only works with gcc.<br />
<br />
To generate coverage statistics, you run configure with --enable-coverage and after building and running tests, you do make coverage. The process generates data files in the same directories as source & object files and produces a coverage directory at the top level with the html files. I've also set it up so a tar file with the html is generated. That is what I've attached.<br />
<br />
More information on PostgreSQL test coverage at http://www.postgresql.org/docs/9.2/static/regress-coverage.html.<br />
<br />
More information on gcov at http://gcc.gnu.org/onlinedocs/gcc/Gcov.html, lcov at http://ltp.sourceforge.net/documentation/how-to/ltp.php (coverage/lcov tabs).<br />
<br />
[[Category:Development]]</div>Robinshttps://wiki.postgresql.org/index.php?title=CodeCoverage&diff=19672CodeCoverage2013-05-11T00:02:17Z<p>Robins: Enlist steps that'd be required to generate a coverage report</p>
<hr />
<div>I have a patch that I will be submitting to add to the build system the capability of reporting on test code coverage metrics for the test suite. Actually it can show coverage for any application run against PostgreSQL. Download [[Image:Coverage.tar.gz]] to see an example report. Gunzip and un-tar the file and click on coverage/index.html. I had to delete most of the files to decrease the file size for upload, so only the links for access work.<br />
<br />
gcov reports line, branch, and function coverage, but lcov only reports on line coverage. I've added a link to the html to the gcov output that shows summary statistics for each file for line, branch, and function calls.<br />
<br />
The report gives a very clear and browseable view of what parts of the system might benefit from more extensive testing. It's obviously useful for planning future testing, but also can be used in conjunction with debugging to see what lines and functions are being exercised or missed by existing tests of the functionality under investigation. It could even be helpful to give a static view of lines hit by a bug test case in lieue of using a debugger. Also, when you're writing a unit test for new functionality, it would be good to check what you're actually hitting with the test.<br />
<br />
It uses gcov together with gcc to generate the statistics, and the lcov suite to create the html report. Both of these would obviously have to be installed to get a coverage report, but this would be an optional feature of the build. It only works with gcc.<br />
<br />
To generate coverage statistics, you run configure with --enable-coverage and after building and running tests, you do make coverage. The process generates data files in the same directories as source & object files and produces a coverage directory at the top level with the html files. I've also set it up so a tar file with the html is generated. That is what I've attached.<br />
<br />
<br />
So the sequence of steps would be:<br />
<br />
make clean<br />
<br />
./configure --enable-coverage<br />
<br />
make check<br />
<br />
make coverage<br />
<br />
firefox coverage/index.html<br />
<br />
<br />
More information on gcov at http://gcc.gnu.org/onlinedocs/gcc/Gcov.html, lcov at http://ltp.sourceforge.net/documentation/how-to/ltp.php (coverage/lcov tabs).<br />
<br />
[[Category:Development]]</div>Robinshttps://wiki.postgresql.org/index.php?title=PgAdmin_Internals&diff=18945PgAdmin Internals2013-02-01T01:13:33Z<p>Robins: 'Bash bootstrap' is required via SVN [and Git] checkout</p>
<hr />
<div>= pgAdmin Development =<br />
<br />
== Introduction ==<br />
<br />
The information about pgAdmin development can be located at:<br />
<br />
* http://www.pgadmin.org/development/<br />
<br />
* The pgAdmin developers mailing list is pgadmin-hackers@postgresql.org<br />
<br />
== Physical Source Lines of Code [SLOC]==<br />
<br />
The source code of pgAdmin have the following main attributes, found using sloccount program [2]:<br />
<br />
{| style="color:#009ace;background-color:#eeeeee;" border="1" cellspacing="0"<br />
|+align="bottom" style="color:#e76700;" | '''Table 1-1. pgAdmin development effort. Retrieved at 03-16-2012 04:30'''<br />
|-<br />
!align="left" | Used Version<br />
|align="right"| 1.14.2<br />
|-<br />
!align="left" | Total Physical Source Lines of Code (SLOC)<br />
|align="right"| 172,137<br />
|-<br />
|align="left" | '''Development Effort Estimate, Person-Years (Person-Months) <BR>''' ''(Basic COCOMO model, Person-Months = 2.4 * (KSLOC**1.05))''<br />
|align="right"| 44.53 (534.42)<br />
|-<br />
|align="left" | '''Schedule Estimate, Years (Months)''' <BR> ''(Basic COCOMO model, Months = 2.5 * (person-months**0.38))''<br />
|align="right"| 2.27 (27.20)<br />
|-<br />
!align="left" | Estimated Average Number of Developers (Effort/Schedule) <br />
|align="right"| 19.65<br />
|-<br />
|align="left" | '''Total Estimated Cost to Develop''' <BR> ''(average salary = $56,286/year, overhead = 2.40).''<br />
|align="right"| $ 6,016,024<br />
|}<br />
<br />
Another interesting information it's the distribution of the physical source lines of code between different programming languages in the project:<br />
<br />
{| style="color:#009ace;background-color:#eeeeee;" border="1" cellspacing="0"<br />
|+align="bottom" style="color:#e76700;" | '''Table 1-2. Programming Languages'''<br />
|-<br />
!align="left" | C++<br />
|align="right"| 163662 (95.08%)<br />
|-<br />
!align="left" | sh<br />
|align="right"| 4331 (2.52%)<br />
|-<br />
!align="left" | Ansi C<br />
|align="right"| 1636 (0.95%)<br />
|-<br />
!align="left" | Pascal<br />
|align="right"| 1120 (0.65%)<br />
|-<br />
!align="left" | yacc<br />
|align="right"| 927 (0.54%)<br />
|-<br />
!align="left" | lex<br />
|align="right"| 421 (0.24%)<br />
|-<br />
!align="left" | perl<br />
|align="right"| 40 (0.02%)<br />
|}<br />
<br />
<br />
And finally the physical source lines of code distribution between directories:<br />
<br />
{| style="color:#009ace;background-color:#eeeeee;" border="1" cellspacing="0"<br />
|+align="bottom" style="color:#e76700;" | '''Table 1-3. Programming Languages divide by directory at pgAdmin folder'''<br />
|-<br />
! Directory Name<br />
! Sloc<br />
! Sloc by Language<br />
|-<br />
!align="left" | ui<br />
|align="right"| 34183<br />
|align="right"| cpp=34182,sh=1<br />
|-<br />
!align="left" | include<br />
|align="right"| 23547<br />
|align="right"| cpp=22651,ansic=896<br />
|-<br />
!align="left" | schema<br />
|align="right"| 19165<br />
|align="right"| cpp=19165<br />
|-<br />
!align="left" | dlg<br />
|align="right"| 17987<br />
|align="right"| cpp=17987<br />
|-<br />
!align="left" | frm<br />
|align="right"| 15931<br />
|align="right"| cpp=15931<br />
|-<br />
!align="left" | ogl<br />
|align="right"| 12693<br />
|align="right"| cpp=12693<br />
|-<br />
!align="left" | pgscript<br />
|align="right"| 11561<br />
|align="right"| cpp=10172,yacc=927,lex=421,sh=41<br />
|-<br />
!align="left" | utils<br />
|align="right"| 5320<br />
|align="right"| cpp=3883,pascal=1120,ansic=295,perl=22<br />
|-<br />
!align="left" | gqb<br />
|align="right"| 4724<br />
|align="right"| cpp=4724<br />
|-<br />
!align="left" | slony<br />
|align="right"| 4480<br />
|align="right"| cpp=4480<br />
|-<br />
!align="left" | ctl<br />
|align="right"| 4335<br />
|align="right"| cpp=4335<br />
|-<br />
!align="left" | debugger<br />
|align="right"| 2932<br />
|align="right"| cpp=2932<br />
|-<br />
!align="left" | agent<br />
|align="right"| 2535<br />
|align="right"| cpp=2535<br />
|-<br />
!align="left" | top_dir<br />
|align="right"| 1455<br />
|align="right"| cpp=1054<br />
|-<br />
!align="left" | db<br />
|align="right"| 1364<br />
|align="right"| cpp=1281,ansic=83<br />
|}<br />
<br />
== Class naming conventions ==<br />
<br />
Naming conventions make programs more understandable by making them easier to read. They can also give information about the function of the identifier-for example, whether it's a constant, package, or class-which can be helpful in understanding the code. For this reason the following rules were followed:<br />
<br />
* '''foo''' ''represents an object of'' '''type Foo'''<br />
* '''fooFactory''' ''creates'' '''foos'''<br />
* '''fooCollection''' ''is a'' '''collection of foos'''<br />
* '''fooCollectionFactory''' ''creates a'' '''fooCollection'''<br />
* '''fooObject''' ''is an object that'' '''resides under/in foo'''<br />
* '''fooObjCollection''' ''is a collection of'' '''fooObjects'''<br />
* '''fooObjFactory''' ''creates'' '''fooObjects'''<br />
<br />
== Coding Style ==<br />
<br />
The following rules gives coding conventions for pgAdmin:<br />
<br />
# Braces are always on lines of their own.<br />
# Only preprocessor directives, globals and function headers/closing braces should normally be left-justified<br />
# Indent == Tab (most developers set their tab width to 4).<br />
# Leave a blank line between logical blocks of code for clarity.<br />
# Add a space after all but the last semi-colon in for loops for clarity.<br />
# Introduce each function in a comment<br />
<br />
By Example:<br />
<br />
'''Source code before applying coding conventions:'''<br />
<pre> <br />
bool gqbArrayCollection::existsObject(gqbObject *item){<br />
gqbObject *found=NULL;<br />
int size=gqbArray.GetCount();<br />
for(int i=0;i<size;i++){<br />
if (gqbArray.Item(i)==item){<br />
found=gqbArray.Item(i);<br />
break;<br />
}<br />
}<br />
if(found)<br />
return true;<br />
else<br />
return false;<br />
}<br />
</pre> <br />
<br />
'''Source code after applying coding conventions:'''<br />
<pre> <br />
// Check if an item exists in the array.<br />
bool gqbArrayCollection::existsObject(gqbObject *item)<br />
{<br />
gqbObject *found=NULL;<br />
int size=gqbArray.GetCount();<br />
<br />
for (int i=0; i<size; i++)<br />
{<br />
if (gqbArray.Item(i)==item)<br />
{<br />
found=gqbArray.Item(i);<br />
break;<br />
}<br />
}<br />
<br />
if (found)<br />
return true;<br />
else<br />
return false;<br />
}<br />
</pre><br />
<br />
== wxWidgets ==<br />
<br />
''Because pgAdmin is written in C++ using the wxWidgets we should know some relevant information about it [1]:''<br />
<br />
* '''wxWidgets''' (formerly '''wxWindows''') is a widget toolkit for creating graphical user interfaces (GUIs) for cross-platform applications. wxWidgets enables a program's GUI code to compile and run on several computer platforms with minimal or no code changes. It covers systems such as Microsoft Windows, Mac OS, Linux/Unix (X11, Motif, and GTK+), OpenVMS, OS/2 and AmigaOS. A version for embedded systems is under development.<br />
<br />
* The wxWidgets library is implemented in C++, with bindings available for many commonly used programming languages, among them, Python (wxPython), Erlang (wxErlang), Haskell (wxHaskell), Lua (wxLua), Perl (wxPerl), Ruby (wxRuby), Smalltalk (wxSqueak), Java (wx4j) and even JavaScript (wxJavaScript).<br />
<br />
* wxWidgets is best described as a native mode toolkit as it provides a thin abstraction to a platform's native widgets, as opposed to emulating the display of widgets using graphic primitives. Calling a native widget on the target platform results in a more native looking interface than toolkits such as [[Swing (Java)|Swing]] (for Java), as well as offering performance and other benefits. <br />
<br />
* The toolkit is also not restricted to GUI development, having a built-in [[Open Database Connectivity|ODBC]]-based database library, an [[inter-process communication]] layer, [[internet socket|socket]] networking functionality, and more.<br />
<br />
* A good free pdf book to learn wxWidgets can be found at: http://www.informit.com/store/product.aspx?isbn=0131473816 in the download section.<br />
<br />
== Development Environment ==<br />
<br />
The Development environment for pgAdmin can be in theory any accepted by the widget toolkit for creating graphical user interfaces (GUIs) for cross-platform applications known as wxWidgets, It covers systems such as Microsoft Windows, Mac OS, Linux/Unix (X11, Motif, and GTK+), OpenVMS, OS/2 and AmigaOS. Here describes how to build pgAdmin from source on *nix, Mac and Windows:<br />
<br />
=== Linux, FreeBSD, Solaris and other unix variants ===<br />
You will need:<br />
* A modern development environment: <br />
** GTK 2.2 or above.<br />
** GNU automake 1.9 or above.<br />
** GNU autoconf 2.59 or above.<br />
** GNU gcc 3.4 or above.<br />
* Following libraries:<br />
** wxGTK 2.8.x ''from http://www.wxwidgets.org/''<br />
** libxml2 2.6.18 or above ''from http://www.xmlsoft.org/''<br />
** libxslt 1.1.x or above ''from http://www.xmlsoft.org/''<br />
** PostgreSQL 8.1 or above ''from http://www.postgresql.org/''<br />
<br />
==== Building ====<br />
<br />
# Install libxml2 and libxslt if they are not already present on your system, per the instructions included with them.<br />
# Install PostgreSQL per the instructions included. It is recommended that you build with OpenSSL support, using the ''--with-openssl'' configure option.<br />
# Unpack the wxGTK tarball to a convenient location, and build and install it as follows:<br />
#* '''Note:''' A script is included in the pgAdmin source tarball(xtra/wx-build/build-wxgtk) which will build and install wxWidgets in each combination of shared/static/debug/release builds for you. (You can follow below steps for wxGTK manual compilation too).<br />
#* cd /path/to/wxGTK/source/<br />
#* ./configure --with-gtk --enable-gtk2 --enable-unicode<br />
#* make<br />
#* sudo make install<br />
# Install wxWidgets contrib modules.<br />
#* cd contrib/<br />
#* make<br />
#* sudo make install<br />
#Unpack the pgAdmin tarball to a convenient location, and build and install it as follows:<br />
#* bash bootstrap '''#Required only if building from an SVN / Git checkout'''<br />
#* ./configure<br />
#* make all<br />
#* sudo make install<br />
If any of the prerequisite components are installed in locations that the <br />
configure script cannot find, you may specify their locations on the command<br />
line. See the configure help (./configure --help) for details.<br />
<br />
=== Mac OS X ===<br />
You will need:<br />
<br />
* A modern development environment: <br />
** XCode 2.0 or above<br />
** GNU automake 1.9 or above.<br />
** GNU autoconf 2.59 or above.<br />
* wxMac 2.8.x ''from http://www.wxwidgets.org/''<br />
* libxml2 2.6.18 or above ''from http://www.xmlsoft.org/''<br />
* libxslt 1.1.x or above ''from http://www.xmlsoft.org/''<br />
* PostgreSQL 8.1 or above ''from http://www.postgresql.org/''<br />
<br />
==== Building ==== <br />
<br />
# Install libxml2 and libxslt per the instructions included with them into a non-system location. The default versions included with OS X Panther and Tiger are too old and will not work correctly with pgAdmin.<br />
# Install PostgreSQL per the instructions included. It is recommended that you build with OpenSSL support, using the --with-openssl configure option.<br />
# Unpack the wxMac tarball to a convenient location, and build and install it as follows:<br />
#* cd /path/to/wxMac/source/<br />
#* ./configure --with-mac --enable-gtk2 --enable-unicode<br />
#* make<br />
#* sudo make install<br />
# Install wxWidgets contrib modules:<br />
#* cd contrib/<br />
#* make<br />
#* sudo make install<br />
#* ''Note 1:'' You may also pass the --enable-universal_binary option to configure to build a Universal binary. This will also require a Universal build of PostgreSQL's libpq library.<br />
#* ''Note 2:'' A script is included in the pgAdmin source tarball (''xtra/wx-build/build-wxmac'') which will install a Universal build ofwxWidgets in each combination of shared/static/debug/release builds for you.<br />
# Unpack the pgAdmin tarball to a convenient location, and build and install it as follows:<br />
#* bash bootstrap # Required only if building from an SVN / Git checkout<br />
#* ./configure --enable-appbundle<br />
#* make all<br />
#* make install<br />
#* This final step will build an OSX appbundle called pgAdmin3.app in the root of your source tree. As this can take a minute or two, the ''pkg/mac/debug-bundle.sh'' script may be used to build a non-relocatable 'fake' appbundle using symbolic links directly to the executable files. This appbundle is called pgAdmin3-debug.app, and doesn't need to be rebuilt when pgAdmin is recompiled.<br />
If any of the prerequisite components are installed in locations that the <br />
configure script cannot find, you may specify their locations on the command<br />
line. See the configure help (./configure --help) for details.<br />
<br />
=== Windows ===<br />
You will need:<br />
<br />
* Windows 2000 or above.<br />
* Microsoft Visual C++ 2005.<br />
* The Windows 2003 R2 Platform SDK [When you install it, select custom install and then only add to selection Microsoft Windows Core SDK and Microsoft Data Access Service (MDAC) SDK]. (Not included with VC++ Express Edition, should be download & installed)<br />
* Windows Installer XML v3.<br />
* wxMSW 2.8.x from http://www.wxwidgets.org/<br />
* libxml2 2.6.18 or above from http://www.xmlsoft.org/<br />
* libxslt 1.1.x or above from http://www.xmlsoft.org/<br />
* iconv 1.9.x or above from http://gnuwin32.sourceforge.net/<br />
* PostgreSQL 8.1 or above from http://www.postgresql.org/<br />
<br />
Precompiled XML/XSLT and dependency packages for Windows can be found at <br />
''http://zlatkovic.com''. You probably need zlib 1.2.x as well as libxml2 and <br />
libxslt.<br />
<br />
==== Building ====<br />
# Unpack the wxMSW source code to a convenient location pointed to by the '''%WXWIN%''' environment variable.<br />
# Install PostgreSQL to a convenient location pointed to by the '''%PGDIR%''' environment variable.<br />
# Unpack the pgAdmin source code to a convenient location.<br />
# From a Visual Studio command prompt, run the wxWidgets build script included in the pgAdmin source tree at ''xtra/wx-build/build-wxmsw.bat''<br />
# Unpack the libxml2, libxslt and dependency binaries in a directory pointed to by the '''%PGBUILD%''' environment variable.<br />
# Start Visual C++ and load the pgAdmin solution file. Build the required targets.<br />
<br />
==== Note from microsoft site for Visual C++ Express Edition 2005 Users: ==== <br />
<br />
'''[[Update:]]''' First read this http://www.microsoft.com/express/2005/platformsdk/default.aspx<br />
<br />
You can use Visual C++ Express to build .NET Framework applications immediately after<br />
installation. In order to use Visual C++ Express to build Win32 applications, you'll need to take<br />
just a few more steps. I'll list the steps necessary for building Win32 applications using Visual C+<br />
+ Express.<br />
<br />
'''1. Install the Microsoft Platform SDK:''' Follow the instructions and install the SDK for the x86 platform.<br />
<br />
'''2. Update the Visual C++ directories in the Projects and Solutions section in the Options dialog box. Add the paths to the appropriate subsection:'''<BR><br />
● Executable files: C:\Program Files\Microsoft Platform SDK for Windows Server 2003 R2\Bin<BR><br />
● Include files: C:\Program Files\Microsoft Platform SDK for Windows Server 2003 R2\Include<BR><br />
● Library files: C:\Program Files\Microsoft Platform SDK for Windows Server 2003 R2\Lib<BR><br />
<br />
Note: Alternatively, you can update the Visual C++ Directories by modifying the VCProjectEngine.<br />
dll.express.config file located in the \vc\vcpackages subdirectory of the Visual C++ Express install<br />
location. Please make sure that you also delete the file "vccomponents.dat" located in the "%<br />
USERPROFILE%\Local Settings\Application Data\Microsoft\VCExpress\8.0" if it exists before<br />
restarting Visual C++ Express Edition.<br />
<br />
<br />
And / Or simply run this<br />
Star Menu-><br />
Microsoft Platform SDK for Windows Server 2003 R2-><br />
Visual Studio Registration-><br />
Register PSDK Directories with Visual Studio.<br />
<br />
<br />
'''[[Common Errors]]'''<br />
<br />
Only use this recommendations if you reach any of this errors:<br />
<br />
'''Update the corewin_express.vsprops file:'''<br />
One more step is needed to make the Win32 template work in Visual C++ Express. This can be done in two ways.<br />
<br />
1. Click on Project (on each one pgAdmin3, pgaevent, pgAgent), Properties, Linker, Input, Additional Dependencies and made sure the folllowing were all in the list (or else you will get unresovled externals to Windows API).<br />
<br />
user32.lib<br />
gdi32.lib<br />
oleaut32.lib<br />
ole32.lib<br />
comdlg32.lib<br />
shell32.lib<br />
winmm.lib<br />
comctl32.lib<br />
rpcrt4.lib<br />
wsock32.lib<br />
odbc32.lib<br />
advapi32.lib<br />
<br />
This avoid errors from type '''LNK2019'''<br />
<br />
<br />
2.You need to edit the corewin_express.vsprops file (found in C:\Program Files\Microsoft Visual Studio 8\VC<br />
\VCProjectDefaults) and Change the string that reads: AdditionalDependencies="kernel32.lib"<br />
to AdditionalDependencies="kernel32.lib user32.lib gdi32.lib winspool.lib comdlg32.lib advapi32.lib<br />
shell32.lib ole32.lib oleaut32.lib uuid.lib"<br />
<br />
<br />
'''To avoid error VCBLD0001 "VCProjectEngine.dll" Could not be loaded''' on non-eglish version of visual c++ express edition (spanish, french and others). This Problem seems to be that for non english versions, vcbuild cannot load the specific languages dll. SOLUTION:<br />
copy the content of the localized folder (for french it is ...vcpackages\1036, spanish it is ...vcpackages\3082) in vcpackages. It worked on french and spanish version.<br />
<br />
<br />
3. VC++ 2005 express bug: Some errors relate to imposibility of upgrade of vcbuild /nologo /upgrade wx_%%f.dsp, can be solve in this way: open with VC C++ express each .DSP file (adv aui base core html net wxexpat wxjpeg wxpng wxregex wxtiff wxzlib xml xrc ogl stc) and allow conversion of format, save and after that step just run script build-wxmsw.bat again.<br />
<br />
<br />
4. Sometimes in certain machines, the script of pgadmin build-wxmsw.bat cannot compile wxwidgets (some kind of bug in cmd vc++ prompot) just open each of the .dsp files visually with the IDE (adv aui base core html net wxexpat wxjpeg wxpng wxregex wxtiff wxzlib xml xrc ogl stc) and compile manually in Unicode Debug and Release mode (both).<BR><br />
'''Check this url:''' http://wiki.wxwidgets.org/Troubleshooting_building_wxWidgets_using_Microsoft_VC<br />
<br />
<br />
5. Sometimes you can avoid the error '''fatal error C1083: Cannot Open file: 'windows.h': No such file or directory''' if using build-wxmsw.bat script and only follow the above instructions and try to compile wxwidgets library. This solution works for me:<br />
<br />
a.) Open the build-wxmsw.bat script and this lines:<br />
<br />
''REM Location of wxWidgets source<br />
set WX=%WXWIN%<br />
set HERE=%CD%'' '''after this add the lines'''<br />
<br />
REM Fix Error error C1083 'windows.h' (use /useenv option when compiling)<br />
set PDSKWIN=C:\Archivos de programa\Microsoft Platform SDK for Windows Server 2003 R2<br />
set INCLUDE=%PDSKWIN%\Include;%INCLUDE%<br />
set LIB=%PDSKWIN%\Lib;%LIB%<br />
<br />
<br />
b.) Look for this string "vcbuild /nohtmllog /nologo" and change for this one " vcbuild /useenv /nohtmllog /nologo twice.<br />
''inside cd %WX%\build\msw loop<br />
...''<br />
vcbuild '''/useenv''' /nohtmllog /nologo wx_%%f.vcproj "Unicode %%b"<br />
''and inside cd ..\..\contrib\build'' loop<br />
vcbuild '''/useenv''' /nohtmllog /nologo %%f.vcproj "Unicode %%b"<br />
<br />
<br />
<br />
6. Error: ...\include\wx/platform.h(196) : fatal error C1083: Cannot open include file: 'wx/setup.h': No such file or directory <br />
This error occurs when you don't add the following directory...<br />
%WXWIN%\include\msvc (replace %WXWIN% for right value)<br />
<br />
..in the options :<br />
VC> Tools> Options> Projects and Solutions> Directories> Include files (top right combo box)<br />
<br />
add above directory: %WXWIN%\include\msvc<br />
<br />
== Class Hierarchy ==<br />
<br />
[Add info here!]<br />
<br />
== SVN Directory Structure == <br />
===pgadmin===<br />
===== agent ===== <br />
pgAdmin Agent related classes:<br />
<br />
* '''dlgJob.cpp''' - PostgreSQL Job Property<br />
* '''dlgSchedule.cpp''' - PostgreSQL Schedule Property<br />
* '''dlgStep.cpp''' - PostgreSQL Step Property<br />
* '''pgaStep.cpp''' - PostgreSQL Agent Step<br />
* '''pgaJob.cpp''' - PostgreSQL Agent Job<br />
* '''pgaSchedule.cpp''' - PostgreSQL Agent Schedule<br />
* '''pgaStep.cpp''' - PostgreSQL Agent Step<br />
<br />
==== db ====<br />
Contains postgresql database related files:<br />
* '''keywords.c''' - lexical token lookup for reserved words in PostgreSQL<br />
* '''pgConn.cpp''' - PostgreSQL Connection class<br />
* '''pgQueryThread.cpp''' - PostgreSQL threaded query class<br />
* '''pgSet.cpp''' - PostgreSQL ResultSet class<br />
<br />
==== dlg ==== <br />
Contains the source code for all dialogs used by pgAdmin like by example:<br />
* '''dlgOperator.cpp''' - PostgreSQL Operator Property<br />
* '''dlgHbaConfig.cpp''' - Configure setting<br />
* '''dlgFindReplace.cpp''' - Search and replace<br />
'''And more...'''<br />
<br />
==== include ==== <br />
Contains all headers files (.h) used by pgAdmin, is divided in several folders for organization purpose.<br />
<br />
==== schema ==== <br />
Contains all classes related to postgresql schemas, like by example:<br />
<br />
* '''pgSchema.cpp''' - schema class<br />
* '''pgCollection.cpp''' - Simple object for use with 'collection' nodes<br />
* '''pgForeignKey.cpp''' - ForeignKey class<br />
<br />
'''and others.'''<br />
<br />
==== ui ==== <br />
Contains xrc dialogue resource files.<br />
<br />
==== ctl ==== <br />
Contains the source code for controls used by pgAdmin:<br />
<br />
* '''calbox.cpp''' - Date-picker control box<br />
* '''ctlComboBox.cpp''' - enhanced combobox control<br />
* '''ctlListView.cpp''' - enhanced listview control<br />
* '''ctlMenuToolbar.cpp''' - Menu tool bar<br />
** ''Note:'' This code is used with the authors permission from Paul Nelson (http://www.pnelsoncomposer.com/)<br />
* '''ctlSecurityPanel.cpp''' - Panel with security information<br />
* '''ctlSQLBox.cpp''' - SQL syntax highlighting textbox<br />
* '''ctlSQLGrid.cpp''' - SQL Query result window<br />
* '''ctlSQLResult.cpp''' - SQL Query result window<br />
* '''ctlTree.cpp''' - wxTreeCtrl containing pgObjects<br />
* '''explainCanvas.cpp''' - Explain Canvas<br />
* '''explainShape.cpp''' - Explain Shapes<br />
* '''timespin.cpp''' - timeSpin SpinCtrl<br />
* '''xh_calb.cpp''' - wxCalendarBox handler<br />
* '''xh_ctlcombo.cpp''' - ctlComboBox handler<br />
* '''xh_ctltree.cpp''' - ctlTree handler<br />
* '''xh_sqlbox.cpp''' - ctlSQLBox handler<br />
* '''xh_timespin.cpp''' - wxTimeSpinCtrl handler<br />
<br />
==== debugger ==== <br />
Contains debugger related classes like:<br />
<br />
* '''debugger.cpp''' - Debugger factories<br />
* '''dlgDirectDbg.cpp''' - debugger <br />
* '''frmDebugger.cpp''' - debugger<br />
<br />
'''And more'''<br />
<br />
==== frm ==== <br />
Contains forms related classes like:<br />
<br />
* '''frmAbout.cpp''' - About Box<br />
* '''frmMain.cpp''' - The main form<br />
** '''events.cpp''' - Event handlers, browser and statistics functions for frmMain.<br />
* '''frmPassword.cpp''' - Change password<br />
* '''frmQuery.cpp''' - SQL Query Box<br />
* '''frmReport.cpp''' - The report file dialogue<br />
<br />
==== slony ==== <br />
Constains classes related to administration of Slony-I Clusters.<br />
<br />
==== utils ==== <br />
Utility classes used by pgAdmin like:<br />
<br />
* '''factory.cpp''' - Object classes factory<br />
* '''favourites.cpp''' - Query favourites<br />
* '''md5.cpp''' - MD5 password encryption functions<br />
* '''misc.cpp''' - Miscellaneous Utilities<br />
* '''pgconfig.cpp''' - backend configuration classes<br />
* '''sysLogger.cpp''' - Log handling class<br />
* '''sysProcess.cpp''' - External process<br />
* '''sysSettings.cpp''' - Settings handling class<br />
* '''utffile.cpp''' - file io with BOM interpretation<br />
<br />
'''And more.'''<br />
<br />
== Developer Notes ==<br />
<br />
=== To add actions to the main window (frmMain) ===<br />
<br />
* Create the frmXXX class that will render the new function.<br />
* Create an actionFactory (or contextActionFactory, if the action will display in context menus too) derived factory for each action to be performed, and register it in frmMain::CreateMenus(). Never touch menu.h, or anything for that. Easy examples are frmGrantWizard or frmEditGrid.<br />
** Implement CheckEnable() for each factory, checking the current object if the action applies.<br />
** Implement StartDialog(), which brings up the dialog.<br />
<br />
Following this, you should be able to add new actions on objects by just adding<br />
the source and registering the new menu in frmMain.cpp. If you think you need <br />
to modify more files, you're probably wrong.<br />
<br />
=== To add objects to the object tree ===<br />
<br />
* Implement the class with proper hierarchy. pgCast and dlgCast are simple examples. <br />
* Instantiate a collection in the pgXXX object where it should be located under.<br />
<br />
=== Property page dialogue layout ===<br />
<br />
Design of dialogs should follow these rules as much as possible<br />
<br />
* Standard dialog heights are 150, 250, and 280<br />
* Standard dialog widths are 220, and 300<br />
* Notebook is at 2,2d, size (216,216d)<br />
* Standard button size is (50,15d)<br />
* First control at (5,5d)<br />
* Standard controls are at (70,[y] d), size (135,12d)<br />
* Vertical constrol spacing is 15d, for radio buttons and checkboxes 12d<br />
* Static text has a vertical offset of 2 relative to other controls<br />
* Static text should be designed with 50 % space in reserve, to leave enough space for translated strings.<br />
<br />
== References ==<br />
<br />
[1] http://en.wikipedia.org/wiki/WxWidgets<br />
<BR><br />
[2] http://www.dwheeler.com/sloccount/<br />
<br />
[[Category:PGAdmin]]</div>Robinshttps://wiki.postgresql.org/index.php?title=PgAdmin_Internals&diff=16400PgAdmin Internals2012-03-15T23:16:37Z<p>Robins: Update SLOC values to version 1.14.2</p>
<hr />
<div>= pgAdmin Development =<br />
<br />
== Introduction ==<br />
<br />
The information about pgAdmin development can be located at:<br />
<br />
* http://www.pgadmin.org/development/<br />
<br />
* The pgAdmin developers mailing list is pgadmin-hackers@postgresql.org<br />
<br />
== Physical Source Lines of Code [SLOC]==<br />
<br />
The source code of pgAdmin have the following main attributes, found using sloccount program [2]:<br />
<br />
{| style="color:#009ace;background-color:#eeeeee;" border="1" cellspacing="0"<br />
|+align="bottom" style="color:#e76700;" | '''Table 1-1. pgAdmin development effort. Retrieved at 03-16-2012 04:30'''<br />
|-<br />
!align="left" | Used Version<br />
|align="right"| 1.14.2<br />
|-<br />
!align="left" | Total Physical Source Lines of Code (SLOC)<br />
|align="right"| 172,137<br />
|-<br />
|align="left" | '''Development Effort Estimate, Person-Years (Person-Months) <BR>''' ''(Basic COCOMO model, Person-Months = 2.4 * (KSLOC**1.05))''<br />
|align="right"| 44.53 (534.42)<br />
|-<br />
|align="left" | '''Schedule Estimate, Years (Months)''' <BR> ''(Basic COCOMO model, Months = 2.5 * (person-months**0.38))''<br />
|align="right"| 2.27 (27.20)<br />
|-<br />
!align="left" | Estimated Average Number of Developers (Effort/Schedule) <br />
|align="right"| 19.65<br />
|-<br />
|align="left" | '''Total Estimated Cost to Develop''' <BR> ''(average salary = $56,286/year, overhead = 2.40).''<br />
|align="right"| $ 6,016,024<br />
|}<br />
<br />
Another interesting information it's the distribution of the physical source lines of code between different programming languages in the project:<br />
<br />
{| style="color:#009ace;background-color:#eeeeee;" border="1" cellspacing="0"<br />
|+align="bottom" style="color:#e76700;" | '''Table 1-2. Programming Languages'''<br />
|-<br />
!align="left" | C++<br />
|align="right"| 163662 (95.08%)<br />
|-<br />
!align="left" | sh<br />
|align="right"| 4331 (2.52%)<br />
|-<br />
!align="left" | Ansi C<br />
|align="right"| 1636 (0.95%)<br />
|-<br />
!align="left" | Pascal<br />
|align="right"| 1120 (0.65%)<br />
|-<br />
!align="left" | yacc<br />
|align="right"| 927 (0.54%)<br />
|-<br />
!align="left" | lex<br />
|align="right"| 421 (0.24%)<br />
|-<br />
!align="left" | perl<br />
|align="right"| 40 (0.02%)<br />
|}<br />
<br />
<br />
And finally the physical source lines of code distribution between directories:<br />
<br />
{| style="color:#009ace;background-color:#eeeeee;" border="1" cellspacing="0"<br />
|+align="bottom" style="color:#e76700;" | '''Table 1-3. Programming Languages divide by directory at pgAdmin folder'''<br />
|-<br />
! Directory Name<br />
! Sloc<br />
! Sloc by Language<br />
|-<br />
!align="left" | ui<br />
|align="right"| 34183<br />
|align="right"| cpp=34182,sh=1<br />
|-<br />
!align="left" | include<br />
|align="right"| 23547<br />
|align="right"| cpp=22651,ansic=896<br />
|-<br />
!align="left" | schema<br />
|align="right"| 19165<br />
|align="right"| cpp=19165<br />
|-<br />
!align="left" | dlg<br />
|align="right"| 17987<br />
|align="right"| cpp=17987<br />
|-<br />
!align="left" | frm<br />
|align="right"| 15931<br />
|align="right"| cpp=15931<br />
|-<br />
!align="left" | ogl<br />
|align="right"| 12693<br />
|align="right"| cpp=12693<br />
|-<br />
!align="left" | pgscript<br />
|align="right"| 11561<br />
|align="right"| cpp=10172,yacc=927,lex=421,sh=41<br />
|-<br />
!align="left" | utils<br />
|align="right"| 5320<br />
|align="right"| cpp=3883,pascal=1120,ansic=295,perl=22<br />
|-<br />
!align="left" | gqb<br />
|align="right"| 4724<br />
|align="right"| cpp=4724<br />
|-<br />
!align="left" | slony<br />
|align="right"| 4480<br />
|align="right"| cpp=4480<br />
|-<br />
!align="left" | ctl<br />
|align="right"| 4335<br />
|align="right"| cpp=4335<br />
|-<br />
!align="left" | debugger<br />
|align="right"| 2932<br />
|align="right"| cpp=2932<br />
|-<br />
!align="left" | agent<br />
|align="right"| 2535<br />
|align="right"| cpp=2535<br />
|-<br />
!align="left" | top_dir<br />
|align="right"| 1455<br />
|align="right"| cpp=1054<br />
|-<br />
!align="left" | db<br />
|align="right"| 1364<br />
|align="right"| cpp=1281,ansic=83<br />
|}<br />
<br />
== Class naming conventions ==<br />
<br />
Naming conventions make programs more understandable by making them easier to read. They can also give information about the function of the identifier-for example, whether it's a constant, package, or class-which can be helpful in understanding the code. For this reason the following rules were followed:<br />
<br />
* '''foo''' ''represents an object of'' '''type Foo'''<br />
* '''fooFactory''' ''creates'' '''foos'''<br />
* '''fooCollection''' ''is a'' '''collection of foos'''<br />
* '''fooCollectionFactory''' ''creates a'' '''fooCollection'''<br />
* '''fooObject''' ''is an object that'' '''resides under/in foo'''<br />
* '''fooObjCollection''' ''is a collection of'' '''fooObjects'''<br />
* '''fooObjFactory''' ''creates'' '''fooObjects'''<br />
<br />
== Coding Style ==<br />
<br />
The following rules gives coding conventions for pgAdmin:<br />
<br />
# Braces are always on lines of their own.<br />
# Only preprocessor directives, globals and function headers/closing braces should normally be left-justified<br />
# Indent == Tab (most developers set their tab width to 4).<br />
# Leave a blank line between logical blocks of code for clarity.<br />
# Add a space after all but the last semi-colon in for loops for clarity.<br />
# Introduce each function in a comment<br />
<br />
By Example:<br />
<br />
'''Source code before applying coding conventions:'''<br />
<pre> <br />
bool gqbArrayCollection::existsObject(gqbObject *item){<br />
gqbObject *found=NULL;<br />
int size=gqbArray.GetCount();<br />
for(int i=0;i<size;i++){<br />
if (gqbArray.Item(i)==item){<br />
found=gqbArray.Item(i);<br />
break;<br />
}<br />
}<br />
if(found)<br />
return true;<br />
else<br />
return false;<br />
}<br />
</pre> <br />
<br />
'''Source code after applying coding conventions:'''<br />
<pre> <br />
// Check if an item exists in the array.<br />
bool gqbArrayCollection::existsObject(gqbObject *item)<br />
{<br />
gqbObject *found=NULL;<br />
int size=gqbArray.GetCount();<br />
<br />
for (int i=0; i<size; i++)<br />
{<br />
if (gqbArray.Item(i)==item)<br />
{<br />
found=gqbArray.Item(i);<br />
break;<br />
}<br />
}<br />
<br />
if (found)<br />
return true;<br />
else<br />
return false;<br />
}<br />
</pre><br />
<br />
== wxWidgets ==<br />
<br />
''Because pgAdmin is written in C++ using the wxWidgets we should know some relevant information about it [1]:''<br />
<br />
* '''wxWidgets''' (formerly '''wxWindows''') is a widget toolkit for creating graphical user interfaces (GUIs) for cross-platform applications. wxWidgets enables a program's GUI code to compile and run on several computer platforms with minimal or no code changes. It covers systems such as Microsoft Windows, Mac OS, Linux/Unix (X11, Motif, and GTK+), OpenVMS, OS/2 and AmigaOS. A version for embedded systems is under development.<br />
<br />
* The wxWidgets library is implemented in C++, with bindings available for many commonly used programming languages, among them, Python (wxPython), Erlang (wxErlang), Haskell (wxHaskell), Lua (wxLua), Perl (wxPerl), Ruby (wxRuby), Smalltalk (wxSqueak), Java (wx4j) and even JavaScript (wxJavaScript).<br />
<br />
* wxWidgets is best described as a native mode toolkit as it provides a thin abstraction to a platform's native widgets, as opposed to emulating the display of widgets using graphic primitives. Calling a native widget on the target platform results in a more native looking interface than toolkits such as [[Swing (Java)|Swing]] (for Java), as well as offering performance and other benefits. <br />
<br />
* The toolkit is also not restricted to GUI development, having a built-in [[Open Database Connectivity|ODBC]]-based database library, an [[inter-process communication]] layer, [[internet socket|socket]] networking functionality, and more.<br />
<br />
* A good free pdf book to learn wxWidgets can be found at: http://www.informit.com/store/product.aspx?isbn=0131473816 in the download section.<br />
<br />
== Development Environment ==<br />
<br />
The Development environment for pgAdmin can be in theory any accepted by the widget toolkit for creating graphical user interfaces (GUIs) for cross-platform applications known as wxWidgets, It covers systems such as Microsoft Windows, Mac OS, Linux/Unix (X11, Motif, and GTK+), OpenVMS, OS/2 and AmigaOS. Here describes how to build pgAdmin from source on *nix, Mac and Windows:<br />
<br />
=== Linux, FreeBSD, Solaris and other unix variants ===<br />
You will need:<br />
* A modern development environment: <br />
** GTK 2.2 or above.<br />
** GNU automake 1.9 or above.<br />
** GNU autoconf 2.59 or above.<br />
** GNU gcc 3.4 or above.<br />
* Following libraries:<br />
** wxGTK 2.8.x ''from http://www.wxwidgets.org/''<br />
** libxml2 2.6.18 or above ''from http://www.xmlsoft.org/''<br />
** libxslt 1.1.x or above ''from http://www.xmlsoft.org/''<br />
** PostgreSQL 8.1 or above ''from http://www.postgresql.org/''<br />
<br />
==== Building ====<br />
<br />
# Install libxml2 and libxslt if they are not already present on your system, per the instructions included with them.<br />
# Install PostgreSQL per the instructions included. It is recommended that you build with OpenSSL support, using the ''--with-openssl'' configure option.<br />
# Unpack the wxGTK tarball to a convenient location, and build and install it as follows:<br />
#* '''Note:''' A script is included in the pgAdmin source tarball(xtra/wx-build/build-wxgtk) which will build and install wxWidgets in each combination of shared/static/debug/release builds for you. (You can follow below steps for wxGTK manual compilation too).<br />
#* cd /path/to/wxGTK/source/<br />
#* ./configure --with-gtk --enable-gtk2 --enable-unicode<br />
#* make<br />
#* sudo make install<br />
# Install wxWidgets contrib modules.<br />
#* cd contrib/<br />
#* make<br />
#* sudo make install<br />
#Unpack the pgAdmin tarball to a convenient location, and build and install it as follows:<br />
#* bash bootstrap '''#Required only if building from an SVN checkout'''<br />
#* ./configure<br />
#* make all<br />
#* sudo make install<br />
If any of the prerequisite components are installed in locations that the <br />
configure script cannot find, you may specify their locations on the command<br />
line. See the configure help (./configure --help) for details.<br />
<br />
=== Mac OS X ===<br />
You will need:<br />
<br />
* A modern development environment: <br />
** XCode 2.0 or above<br />
** GNU automake 1.9 or above.<br />
** GNU autoconf 2.59 or above.<br />
* wxMac 2.8.x ''from http://www.wxwidgets.org/''<br />
* libxml2 2.6.18 or above ''from http://www.xmlsoft.org/''<br />
* libxslt 1.1.x or above ''from http://www.xmlsoft.org/''<br />
* PostgreSQL 8.1 or above ''from http://www.postgresql.org/''<br />
<br />
==== Building ==== <br />
<br />
# Install libxml2 and libxslt per the instructions included with them into a non-system location. The default versions included with OS X Panther and Tiger are too old and will not work correctly with pgAdmin.<br />
# Install PostgreSQL per the instructions included. It is recommended that you build with OpenSSL support, using the --with-openssl configure option.<br />
# Unpack the wxMac tarball to a convenient location, and build and install it as follows:<br />
#* cd /path/to/wxMac/source/<br />
#* ./configure --with-mac --enable-gtk2 --enable-unicode<br />
#* make<br />
#* sudo make install<br />
# Install wxWidgets contrib modules:<br />
#* cd contrib/<br />
#* make<br />
#* sudo make install<br />
#* ''Note 1:'' You may also pass the --enable-universal_binary option to configure to build a Universal binary. This will also require a Universal build of PostgreSQL's libpq library.<br />
#* ''Note 2:'' A script is included in the pgAdmin source tarball (''xtra/wx-build/build-wxmac'') which will install a Universal build ofwxWidgets in each combination of shared/static/debug/release builds for you.<br />
# Unpack the pgAdmin tarball to a convenient location, and build and install it as follows:<br />
#* bash bootstrap # Required only if building from an SVN checkout<br />
#* ./configure --enable-appbundle<br />
#* make all<br />
#* make install<br />
#* This final step will build an OSX appbundle called pgAdmin3.app in the root of your source tree. As this can take a minute or two, the ''pkg/mac/debug-bundle.sh'' script may be used to build a non-relocatable 'fake' appbundle using symbolic links directly to the executable files. This appbundle is called pgAdmin3-debug.app, and doesn't need to be rebuilt when pgAdmin is recompiled.<br />
If any of the prerequisite components are installed in locations that the <br />
configure script cannot find, you may specify their locations on the command<br />
line. See the configure help (./configure --help) for details.<br />
<br />
=== Windows ===<br />
You will need:<br />
<br />
* Windows 2000 or above.<br />
* Microsoft Visual C++ 2005.<br />
* The Windows 2003 R2 Platform SDK [When you install it, select custom install and then only add to selection Microsoft Windows Core SDK and Microsoft Data Access Service (MDAC) SDK]. (Not included with VC++ Express Edition, should be download & installed)<br />
* Windows Installer XML v3.<br />
* wxMSW 2.8.x from http://www.wxwidgets.org/<br />
* libxml2 2.6.18 or above from http://www.xmlsoft.org/<br />
* libxslt 1.1.x or above from http://www.xmlsoft.org/<br />
* iconv 1.9.x or above from http://gnuwin32.sourceforge.net/<br />
* PostgreSQL 8.1 or above from http://www.postgresql.org/<br />
<br />
Precompiled XML/XSLT and dependency packages for Windows can be found at <br />
''http://zlatkovic.com''. You probably need zlib 1.2.x as well as libxml2 and <br />
libxslt.<br />
<br />
==== Building ====<br />
# Unpack the wxMSW source code to a convenient location pointed to by the '''%WXWIN%''' environment variable.<br />
# Install PostgreSQL to a convenient location pointed to by the '''%PGDIR%''' environment variable.<br />
# Unpack the pgAdmin source code to a convenient location.<br />
# From a Visual Studio command prompt, run the wxWidgets build script included in the pgAdmin source tree at ''xtra/wx-build/build-wxmsw.bat''<br />
# Unpack the libxml2, libxslt and dependency binaries in a directory pointed to by the '''%PGBUILD%''' environment variable.<br />
# Start Visual C++ and load the pgAdmin solution file. Build the required targets.<br />
<br />
==== Note from microsoft site for Visual C++ Express Edition 2005 Users: ==== <br />
<br />
'''[[Update:]]''' First read this http://www.microsoft.com/express/2005/platformsdk/default.aspx<br />
<br />
You can use Visual C++ Express to build .NET Framework applications immediately after<br />
installation. In order to use Visual C++ Express to build Win32 applications, you'll need to take<br />
just a few more steps. I'll list the steps necessary for building Win32 applications using Visual C+<br />
+ Express.<br />
<br />
'''1. Install the Microsoft Platform SDK:''' Follow the instructions and install the SDK for the x86 platform.<br />
<br />
'''2. Update the Visual C++ directories in the Projects and Solutions section in the Options dialog box. Add the paths to the appropriate subsection:'''<BR><br />
● Executable files: C:\Program Files\Microsoft Platform SDK for Windows Server 2003 R2\Bin<BR><br />
● Include files: C:\Program Files\Microsoft Platform SDK for Windows Server 2003 R2\Include<BR><br />
● Library files: C:\Program Files\Microsoft Platform SDK for Windows Server 2003 R2\Lib<BR><br />
<br />
Note: Alternatively, you can update the Visual C++ Directories by modifying the VCProjectEngine.<br />
dll.express.config file located in the \vc\vcpackages subdirectory of the Visual C++ Express install<br />
location. Please make sure that you also delete the file "vccomponents.dat" located in the "%<br />
USERPROFILE%\Local Settings\Application Data\Microsoft\VCExpress\8.0" if it exists before<br />
restarting Visual C++ Express Edition.<br />
<br />
<br />
And / Or simply run this<br />
Star Menu-><br />
Microsoft Platform SDK for Windows Server 2003 R2-><br />
Visual Studio Registration-><br />
Register PSDK Directories with Visual Studio.<br />
<br />
<br />
'''[[Common Errors]]'''<br />
<br />
Only use this recommendations if you reach any of this errors:<br />
<br />
'''Update the corewin_express.vsprops file:'''<br />
One more step is needed to make the Win32 template work in Visual C++ Express. This can be done in two ways.<br />
<br />
1. Click on Project (on each one pgAdmin3, pgaevent, pgAgent), Properties, Linker, Input, Additional Dependencies and made sure the folllowing were all in the list (or else you will get unresovled externals to Windows API).<br />
<br />
user32.lib<br />
gdi32.lib<br />
oleaut32.lib<br />
ole32.lib<br />
comdlg32.lib<br />
shell32.lib<br />
winmm.lib<br />
comctl32.lib<br />
rpcrt4.lib<br />
wsock32.lib<br />
odbc32.lib<br />
advapi32.lib<br />
<br />
This avoid errors from type '''LNK2019'''<br />
<br />
<br />
2.You need to edit the corewin_express.vsprops file (found in C:\Program Files\Microsoft Visual Studio 8\VC<br />
\VCProjectDefaults) and Change the string that reads: AdditionalDependencies="kernel32.lib"<br />
to AdditionalDependencies="kernel32.lib user32.lib gdi32.lib winspool.lib comdlg32.lib advapi32.lib<br />
shell32.lib ole32.lib oleaut32.lib uuid.lib"<br />
<br />
<br />
'''To avoid error VCBLD0001 "VCProjectEngine.dll" Could not be loaded''' on non-eglish version of visual c++ express edition (spanish, french and others). This Problem seems to be that for non english versions, vcbuild cannot load the specific languages dll. SOLUTION:<br />
copy the content of the localized folder (for french it is ...vcpackages\1036, spanish it is ...vcpackages\3082) in vcpackages. It worked on french and spanish version.<br />
<br />
<br />
3. VC++ 2005 express bug: Some errors relate to imposibility of upgrade of vcbuild /nologo /upgrade wx_%%f.dsp, can be solve in this way: open with VC C++ express each .DSP file (adv aui base core html net wxexpat wxjpeg wxpng wxregex wxtiff wxzlib xml xrc ogl stc) and allow conversion of format, save and after that step just run script build-wxmsw.bat again.<br />
<br />
<br />
4. Sometimes in certain machines, the script of pgadmin build-wxmsw.bat cannot compile wxwidgets (some kind of bug in cmd vc++ prompot) just open each of the .dsp files visually with the IDE (adv aui base core html net wxexpat wxjpeg wxpng wxregex wxtiff wxzlib xml xrc ogl stc) and compile manually in Unicode Debug and Release mode (both).<BR><br />
'''Check this url:''' http://wiki.wxwidgets.org/Troubleshooting_building_wxWidgets_using_Microsoft_VC<br />
<br />
<br />
5. Sometimes you can avoid the error '''fatal error C1083: Cannot Open file: 'windows.h': No such file or directory''' if using build-wxmsw.bat script and only follow the above instructions and try to compile wxwidgets library. This solution works for me:<br />
<br />
a.) Open the build-wxmsw.bat script and this lines:<br />
<br />
''REM Location of wxWidgets source<br />
set WX=%WXWIN%<br />
set HERE=%CD%'' '''after this add the lines'''<br />
<br />
REM Fix Error error C1083 'windows.h' (use /useenv option when compiling)<br />
set PDSKWIN=C:\Archivos de programa\Microsoft Platform SDK for Windows Server 2003 R2<br />
set INCLUDE=%PDSKWIN%\Include;%INCLUDE%<br />
set LIB=%PDSKWIN%\Lib;%LIB%<br />
<br />
<br />
b.) Look for this string "vcbuild /nohtmllog /nologo" and change for this one " vcbuild /useenv /nohtmllog /nologo twice.<br />
''inside cd %WX%\build\msw loop<br />
...''<br />
vcbuild '''/useenv''' /nohtmllog /nologo wx_%%f.vcproj "Unicode %%b"<br />
''and inside cd ..\..\contrib\build'' loop<br />
vcbuild '''/useenv''' /nohtmllog /nologo %%f.vcproj "Unicode %%b"<br />
<br />
<br />
<br />
6. Error: ...\include\wx/platform.h(196) : fatal error C1083: Cannot open include file: 'wx/setup.h': No such file or directory <br />
This error occurs when you don't add the following directory...<br />
%WXWIN%\include\msvc (replace %WXWIN% for right value)<br />
<br />
..in the options :<br />
VC> Tools> Options> Projects and Solutions> Directories> Include files (top right combo box)<br />
<br />
add above directory: %WXWIN%\include\msvc<br />
<br />
== Class Hierarchy ==<br />
<br />
[Add info here!]<br />
<br />
== SVN Directory Structure == <br />
===pgadmin===<br />
===== agent ===== <br />
pgAdmin Agent related classes:<br />
<br />
* '''dlgJob.cpp''' - PostgreSQL Job Property<br />
* '''dlgSchedule.cpp''' - PostgreSQL Schedule Property<br />
* '''dlgStep.cpp''' - PostgreSQL Step Property<br />
* '''pgaStep.cpp''' - PostgreSQL Agent Step<br />
* '''pgaJob.cpp''' - PostgreSQL Agent Job<br />
* '''pgaSchedule.cpp''' - PostgreSQL Agent Schedule<br />
* '''pgaStep.cpp''' - PostgreSQL Agent Step<br />
<br />
==== db ====<br />
Contains postgresql database related files:<br />
* '''keywords.c''' - lexical token lookup for reserved words in PostgreSQL<br />
* '''pgConn.cpp''' - PostgreSQL Connection class<br />
* '''pgQueryThread.cpp''' - PostgreSQL threaded query class<br />
* '''pgSet.cpp''' - PostgreSQL ResultSet class<br />
<br />
==== dlg ==== <br />
Contains the source code for all dialogs used by pgAdmin like by example:<br />
* '''dlgOperator.cpp''' - PostgreSQL Operator Property<br />
* '''dlgHbaConfig.cpp''' - Configure setting<br />
* '''dlgFindReplace.cpp''' - Search and replace<br />
'''And more...'''<br />
<br />
==== include ==== <br />
Contains all headers files (.h) used by pgAdmin, is divided in several folders for organization purpose.<br />
<br />
==== schema ==== <br />
Contains all classes related to postgresql schemas, like by example:<br />
<br />
* '''pgSchema.cpp''' - schema class<br />
* '''pgCollection.cpp''' - Simple object for use with 'collection' nodes<br />
* '''pgForeignKey.cpp''' - ForeignKey class<br />
<br />
'''and others.'''<br />
<br />
==== ui ==== <br />
Contains xrc dialogue resource files.<br />
<br />
==== ctl ==== <br />
Contains the source code for controls used by pgAdmin:<br />
<br />
* '''calbox.cpp''' - Date-picker control box<br />
* '''ctlComboBox.cpp''' - enhanced combobox control<br />
* '''ctlListView.cpp''' - enhanced listview control<br />
* '''ctlMenuToolbar.cpp''' - Menu tool bar<br />
** ''Note:'' This code is used with the authors permission from Paul Nelson (http://www.pnelsoncomposer.com/)<br />
* '''ctlSecurityPanel.cpp''' - Panel with security information<br />
* '''ctlSQLBox.cpp''' - SQL syntax highlighting textbox<br />
* '''ctlSQLGrid.cpp''' - SQL Query result window<br />
* '''ctlSQLResult.cpp''' - SQL Query result window<br />
* '''ctlTree.cpp''' - wxTreeCtrl containing pgObjects<br />
* '''explainCanvas.cpp''' - Explain Canvas<br />
* '''explainShape.cpp''' - Explain Shapes<br />
* '''timespin.cpp''' - timeSpin SpinCtrl<br />
* '''xh_calb.cpp''' - wxCalendarBox handler<br />
* '''xh_ctlcombo.cpp''' - ctlComboBox handler<br />
* '''xh_ctltree.cpp''' - ctlTree handler<br />
* '''xh_sqlbox.cpp''' - ctlSQLBox handler<br />
* '''xh_timespin.cpp''' - wxTimeSpinCtrl handler<br />
<br />
==== debugger ==== <br />
Contains debugger related classes like:<br />
<br />
* '''debugger.cpp''' - Debugger factories<br />
* '''dlgDirectDbg.cpp''' - debugger <br />
* '''frmDebugger.cpp''' - debugger<br />
<br />
'''And more'''<br />
<br />
==== frm ==== <br />
Contains forms related classes like:<br />
<br />
* '''frmAbout.cpp''' - About Box<br />
* '''frmMain.cpp''' - The main form<br />
** '''events.cpp''' - Event handlers, browser and statistics functions for frmMain.<br />
* '''frmPassword.cpp''' - Change password<br />
* '''frmQuery.cpp''' - SQL Query Box<br />
* '''frmReport.cpp''' - The report file dialogue<br />
<br />
==== slony ==== <br />
Constains classes related to administration of Slony-I Clusters.<br />
<br />
==== utils ==== <br />
Utility classes used by pgAdmin like:<br />
<br />
* '''factory.cpp''' - Object classes factory<br />
* '''favourites.cpp''' - Query favourites<br />
* '''md5.cpp''' - MD5 password encryption functions<br />
* '''misc.cpp''' - Miscellaneous Utilities<br />
* '''pgconfig.cpp''' - backend configuration classes<br />
* '''sysLogger.cpp''' - Log handling class<br />
* '''sysProcess.cpp''' - External process<br />
* '''sysSettings.cpp''' - Settings handling class<br />
* '''utffile.cpp''' - file io with BOM interpretation<br />
<br />
'''And more.'''<br />
<br />
== Developer Notes ==<br />
<br />
=== To add actions to the main window (frmMain) ===<br />
<br />
* Create the frmXXX class that will render the new function.<br />
* Create an actionFactory (or contextActionFactory, if the action will display in context menus too) derived factory for each action to be performed, and register it in frmMain::CreateMenus(). Never touch menu.h, or anything for that. Easy examples are frmGrantWizard or frmEditGrid.<br />
** Implement CheckEnable() for each factory, checking the current object if the action applies.<br />
** Implement StartDialog(), which brings up the dialog.<br />
<br />
Following this, you should be able to add new actions on objects by just adding<br />
the source and registering the new menu in frmMain.cpp. If you think you need <br />
to modify more files, you're probably wrong.<br />
<br />
=== To add objects to the object tree ===<br />
<br />
* Implement the class with proper hierarchy. pgCast and dlgCast are simple examples. <br />
* Instantiate a collection in the pgXXX object where it should be located under.<br />
<br />
=== Property page dialogue layout ===<br />
<br />
Design of dialogs should follow these rules as much as possible<br />
<br />
* Standard dialog heights are 150, 250, and 280<br />
* Standard dialog widths are 220, and 300<br />
* Notebook is at 2,2d, size (216,216d)<br />
* Standard button size is (50,15d)<br />
* First control at (5,5d)<br />
* Standard controls are at (70,[y] d), size (135,12d)<br />
* Vertical constrol spacing is 15d, for radio buttons and checkboxes 12d<br />
* Static text has a vertical offset of 2 relative to other controls<br />
* Static text should be designed with 50 % space in reserve, to leave enough space for translated strings.<br />
<br />
== References ==<br />
<br />
[1] http://en.wikipedia.org/wiki/WxWidgets<br />
<BR><br />
[2] http://www.dwheeler.com/sloccount/</div>Robins