https://wiki.postgresql.org/api.php?action=feedcontributions&user=Saper&feedformat=atomPostgreSQL wiki - User contributions [en]2024-03-28T23:05:30ZUser contributionsMediaWiki 1.35.13https://wiki.postgresql.org/index.php?title=So,_you_want_to_be_a_developer%3F&diff=38544So, you want to be a developer?2023-12-21T22:49:10Z<p>Saper: /* Resources on contributing to PostgreSQL */ pgconf.eu 2023: +Melanie Plageman, +MIchael Paquier</p>
<hr />
<div>'''by Selena Deckelmann'''<br />
<br />
This document is meant as a guide for brand new developers, seeking to contribute to PostgreSQL, but unsure about how to get started, or "the right way" to get involved. Feedback is welcome, as are additional links to important documents, examples, tutorials and personal stories about contributing to the project.<br />
<br />
We also have a [[Developer_FAQ]]<br />
<br />
== How to get started ==<br />
<br />
=== Overview ===<br />
<br />
Contributing to core PostgreSQL requires a few basic development tools - git, a C development environment and perl. Most modern Linux and BSD operating systems come with "-devel" packages usable for your development needs. At a very high level, you will: <br />
<br />
* Get the basic tools installed and working (git, a C development environment, and perl)<br />
* Clone our git source code repository<br />
* Compile PostgreSQL and successfully run the regression test suite<br />
<br />
Now, you should be ready to start hacking on code!<br />
<br />
=== Source code ===<br />
<br />
Source code can be found at http://git.postgresql.org/gitweb?p=postgresql.git;a=summary<br />
<br />
Once you have git installed, you can check this code out locally with the command: <br />
<br />
git clone https://git.postgresql.org/git/postgresql.git<br />
<br />
While there are release tarballs available, you should use a git clone to work on code with the community.<br />
<br />
=== Hacking PostgreSQL Resources ===<br />
<br />
There are a couple of different resources out on the net about how to go about actually hacking on PostgreSQL; these are just a few:<br />
<br />
Neil Conway and Gavin Sherry's original "Introduction to Hacking PostgreSQL": http://www.neilconway.org/talks/hacking/ ; presented at PgCon 2007 (http://www.pgcon.org/2007/schedule/events/8.en.html) and the PostgreSQL 10-year Anniversary Summit<br />
<br />
Stephen Frost's 2013 PgCon Talk "Hacking PostgreSQL" (http://www.pgcon.org/2013/schedule/events/545.en.html) slides are here: http://snowman.net/~sfrost/hackingpg-pgcon13_20130506.pdf and his 2011 PgCon Talk "Review of Patch Reviewing" (http://www.pgcon.org/2011/schedule/events/368.en.html) slides are here: http://www.pgcon.org/2011/schedule/attachments/189_pg_patch_review_20110516.odp<br />
<br />
Andrew Dunstan's "How to be a Happy Hacker", video here: http://www.youtube.com/watch?v=yFDyM29tB6k<br />
<br />
Fabrízio Mello and Dickson Guedes's "Hacking PostgreSQL" youtube channel (PT-BR): https://www.youtube.com/channel/UCjq4gJg4tYy0NqEEo3t60IA<br />
<br />
=== Style Guide ===<br />
<br />
Working with our source code involves some general rules. These are documented in our core documentation: http://www.postgresql.org/docs/current/static/source.html<br />
<br />
At a high level, we use 4-space tabbed indenting, strict ANSI C comment formatting, and our variable and function naming convention is to match the surrounding code. For example, if you see that variables use a CamelCase style, match that. If they use underscores, or are lowercase, match that. Readability and consistency within a section of code is of greater importance than universal consistency. If a section of code is being substantially reworked, developers sometimes will rework private function names and variable names to match current convention. However, projects to simply rename variables for the sake of renaming them to match current notions of coding style will be rejected.<br />
<br />
=== Bug fixing ===<br />
<br />
Bugs are posted to the mailing list: pgsql-bugs@postgresql.org<br />
<br />
You can see an archive of reported bugs at: https://www.postgresql.org/list/pgsql-bugs/<br />
<br />
Typically, a bug is posted via our [http://www.postgresql.org/support/submitbug/ bug reporting form], and then members of this list respond. Of course, not every issue posted to this list is actually a bug. A good way to learn more about how this process works is to subscribe to the list and observe for a while, before jumping in. Our software is quite complex, and development work spans a couple decades. With that history, many changes and ideas have been suggested, attempted, failed and succeeded. Please don't be discouraged if your initial ideas are rejected, significantly refactored or long-time contributors provide critical feedback to ideas or code. If contributors are responding, it is likely that they are attempting to provide direction to your work and suggesting that you try a different approach, rather than give up.<br />
<br />
Another source of bug reports is the pgsql-general@postgresql.org mailing list. Subscribing and responding to issues posted to this list is a great way to become familiar with the common problems everyday users of PostgreSQL face. Many members of our development community are on this list and respond regularly to user issues. Reading archives, and attempting to respond to issues as they come up is a significant and useful contribution to our community. <br />
<br />
Generally speaking, bug fixes are back-ported to affected branches whenever possible.<br />
<br />
=== TODOs ===<br />
<br />
It's worth checking if the feature of interest is found in the TODO list on our wiki: http://wiki.postgresql.org/wiki/TODO.<br />
<br />
The entries there often have additional information about the feature and may point to reasons why it hasn't been implemented yet. We have attempted to organize issues and link in relevant discussions from our mailing list archives. Please read background information if it is available before attempting to resolve a TODO. If no background information is available, it is appropriate to post a question to pgsql-hackers@postgresql.org and request additional information and inquire about the status of any ongoing work on the problem.<br />
<br />
=== Searching the PostgreSQL archives ===<br />
<br />
Starting a project should always begin with a search of our PostgreSQL mailing list archives. You can start at: https://www.postgresql.org/list/pgsql-hackers/.<br />
<br />
Our project's policy is to discuss as much of ongoing code work in public, including any in-progress patches, whenever possible. You may find significant and useful, but uncommitted, code in our archives that can either inform you about current or past work, or reduce the amount of work needed to accomplish a task. There are also some changes to our core project that were rejected, but are perfectly reasonable solutions to problems. Bottom line; searching our archives is a critical skill any member of our community must learn to be effective.<br />
<br />
=== Brand new features ===<br />
<br />
If you have a brand new idea for PostgreSQL, and you've already looked through our archives, scanned the TODO list and reviewed the code relevant to the change you'd like to make, it's time to dive into the pgsql-hackers@postgresql.org mailing list. <br />
<br />
This is a very active list - posting 20-100 or more messages a day. If you are working on a project, it is prudent that you subscribe to the mailing list for at least the duration of the project. The list is quite large, and is made up of contributors and observers from the last 15 years of development effort.<br />
<br />
Bruce Momjian created a presentation on how to get your patch accepted by the PostgreSQL community: http://momjian.us/main/writings/pgsql/patch.pdf<br />
<br />
Your initial post for a new project to our mailing list should include: <br />
<br />
* A description of the problem to be solved, or feature to be implemented.<br />
* Links to relevant standards documentation.<br />
* A short description of the areas of source code to be modified.<br />
* Intended timeline for implementation.<br />
* Links to relevant previous discussions on PostgreSQL mailing lists about the problem or feature.<br />
* CC any members of the development community you'll be directly working with on the project.<br />
* Link to a wiki page on wiki.postgresql.org for ongoing status updates.<br />
<br />
Your best chance of success in implementing a new feature is getting early involvement from members of the development community. It is entirely appropriate and necessary to initiate conversations about features on the pgsql-hackers mailing list, and request feedback in public from those developers who have worked on relevant or similar features in the past. We encourage this communication, and most active developers are willing and interested in providing mentorship in public for work that you undertake.<br />
<br />
New features are always committed to 'master' (the development branch in the git repository). It is the project's policy not to add features to released major versions.<br />
<br />
=== Commitfest and timing ===<br />
<br />
The Commitfest process was designed to keep track of incoming patches, help synchronize development and commit effort, make the review process more obvious and transparent, and to encourage new people to participate in the development of PostgreSQL. <br />
<br />
Developers are required to submit patches to the pgsql-hackers@postgresql.org mailing list before they will be reviewed. Once the email with the patch has been archived on the postgresql.org site, the patch can be linked into the Commitfest application (https://commitfest.postgresql.org). Commitfests are scheduled to start on the 15th of the month, and occur about every two months. We have had about five commitfests per year since the process was created.<br />
<br />
Not all patches are required to go through the commitfest process, although most of any substantial size or requiring detailed code review will.<br />
<br />
For the last couple of years, getting a major feature into a major dot release generally requires getting the patch into the review queue sometime between July-December. Feature freeze may happen in February, and new features will not be accepted until the new major release is complete. (A description and commentary on this is available at: http://rhaas.blogspot.com/2010/07/concurrent-development.html)<br />
<br />
More information about Commitfests is at: http://wiki.postgresql.org/wiki/CommitFest<br />
<br />
== Participating in the development community ==<br />
<br />
Information about the mailing lists is available on the [[Mailing Lists]] page, also reproduced here<br />
for your convenience.<br />
<br />
=== Mailing List Culture === <br />
<br />
The PostgreSQL community exists world-wide on our mailing lists. As you dive into our community, you will encounter people with wildly varying levels of expertise for databases, software development and system administration. Excellent technical and professional advice is given freely on the mailing lists, but there is no guarantee or expectation that anyone can solve any particular problem. Flaming or personal attacks are not tolerated on our mailing lists, IRC or related forums connected to the postgresql.org site. <br />
<br />
Above all, the PostgreSQL community's expectation is that each person treats the other with respect, and grants each other the benefit-of-the-doubt when it comes to terse or critical language. The Robustness Principle applies to participation in our community: Be conservative in what you send; be liberal in what you accept.<br />
<br />
That said, our community is known for its aggressive and technical discussion style. For those unfamiliar with our community, our discussions can come across as insulting or overly critical. Please keep in mind that as a new contributor, you are encountering a new culture. Every culture has different rules about appropriate behavior, social norms, and expectations. Much like when learning a new language or visiting a new, unfamiliar country, your experiences while joining the PostgreSQL community will undoubtably include an "adjustment cycle". That can and likely will include high and low moments, friendly or otherwise.<br />
<br />
As with any encounter with unfamiliar culture, you must take some time to get acquainted. Take extra time to communicate clearly. Ask for clarification if you're confused or a response doesn't make sense to you. Be careful to avoid personal attacks if someone makes a mistake. If there's one universal constant, it is that everyone makes mistakes.<br />
<br />
Remember that we are a learning community, and with few exceptions, people are communicating with the intention of learning, sharing and refining ideas.<br />
<br />
=== Email etiquette mechanics ===<br />
<br />
Signatures that include "confidentiality notices" are useless in the context of PostgreSQL mailing lists. All messages to our lists are archived publicly, are immediately available worldwide and will not be removed from our archives. Please remove the notices from your email to our lists, particularly when posting code that you wish to be contributed or shared with our community.<br />
<br />
When replying, please be respectful and use appropriate quoting. See the [https://web.archive.org/web/20170426175120/http://www.gweep.ca/~edmonds/usenet/ml-etiquette.html Mailing List Etiquette FAQ] for details about what constitutes appropriate quoting when replying to mailing lists. <br />
<br />
Our mailing lists are generally set to "reply to sender", but the preferred way to participate in threads is to "reply all". That means that you'll include both the email address of the sender and the mailing list in your response. Also, please do not send HTML-enriched email to the mailing lists.<br />
<br />
Finally, our community generally does not "top post" in response to mailing list threads (See [https://en.wikipedia.org/wiki/Posting_style#Top-posting Wikipedia: Top Posting]for a definition of top posting).<br />
<br />
=== Using the discussion lists ===<br />
<br />
You can send an email directly to any of the mailing lists, without subscribing first. <br />
Any responses you receive or send should be sent to the list ''and'' CC correspondents.<br />
<br />
If you wish to receive the mail traffic sent to a list, you can join using the [https://lists.postgresql.org/ subscribe] form. You should receive an email in response from the mailing list manager software that handles the lists. If you wish change the various settings associated with your subscription or unsubscribe, you can do so using the [https://lists.postgresql.org/ web] interface.<br />
<br />
If you follow discussion through the web interface instead of subscribing,<br />
you will at some point wish to reply to a message sent to the list. '''Do not''' simply copy<br />
the message body and paste it into a message with a similar subject as a way to join the conversation.<br />
The mailing list relies on the "In-Reply-To" mail header in order to associate individual messages<br />
to their thread. If you don't know how to add this header manually, you should instead make use<br />
of the "raw" link [https://www.postgresql.org/message-id/CA+OCxoxAm_iEh21sxHiYzZxK9_3JjdzHLX4ib--ZbH73yfb_zA@mail.gmail.com provided] on every message view to download the message as a file<br />
(in mbox format), then import it into your favorite email client and use the usual "Reply All"<br />
way of responding to mailing list messages.<br />
<br />
=== Overview of discussion lists ===<br />
<br />
We have two primary lists related to usage and development of postgresql: [https://www.postgresql.org/list/pgsql-general/ pgsql-general@postgresql.org] and [https://www.postgresql.org/list/pgsql-hackers/ pgsql-hackers@postgresql.org]. pgsql-general is the correct place to start if you are having a problem with your PostgreSQL installation, need help with installation, are a software developer using PostgreSQL or have a general question about the project. pgsql-hackers is the correct place to go if you have a patch to submit, would like to learn more about how to develop PostgreSQL itself, or are interested in database internals. We also have the [https://www.postgresql.org/list/pgsql-novice/ pgsql-novice@postgresql.org] list if you would like to try posting a question a smaller list, with a group of people who are there specifically to answer very basic questions.<br />
<br />
If you are primarily interested in performance tuning, benchmarking or case studies from existing users regarding performance, [https://www.postgresql.org/list/pgsql-performance/ pgsql-performance@postgresql.org] is a great list to join.<br />
<br />
If you're interested in contributing to website maintenance or editing, or system administration of PostgreSQL infrastructure, join the [https://www.postgresql.org/list/pgsql-www/ pgsql-www@postgresql.org] mailing list.<br />
<br />
If you have something to contribute to the PostgreSQL documentation, join the [https://www.postgresql.org/list/pgsql-docs/ pgsql-docs@postgresql.org] mailing list. The documentation is always in need of copy editors, testers and example generation.<br />
<br />
If you're interested in staffing booths at conferences, giving talks at conferences, starting a user group or participating in a user group, join the [https://www.postgresql.org/list/pgsql-advocacy/ pgsql-advocacy@postgresql.org] mailing list. We are always in need of booth volunteers, speakers, case study writers and bloggers.<br />
<br />
If you think you've found a bug in PostgreSQL and are new to our project, we suggest you ask about it on the [https://www.postgresql.org/list/pgsql-general/ pgsql-general] list first, and then read our [http://www.postgresql.org/docs/current/static/bug-reporting.html Bug Submission Guidelines] and then go to our [http://www.postgresql.org/support/submitbug Bug Reporting form].<br />
<br />
We also have User Group mailing lists, language-specific lists and some other specific projects with their own communities. You can find a comprehensive list of these at: [http://www.postgresql.org/community/lists/ http://www.postgresql.org/community/lists/]<br />
<br />
=== Wiki ===<br />
<br />
Our wiki is active and frequently updated at: http://wiki.postgresql.org. We encourage contributors to add to the material there, and to make corrections to any errors found.<br />
<br />
=== Projects related to PostgreSQL ===<br />
<br />
There are hundreds of projects that are dependent upon, related to or extend PostgreSQL. You can find partial list of those projects at [https://www.postgresql.org/docs/current/external-projects.html External Projects] doc page, [https://pgxn.org/ PGXN] or the [https://www.postgresql.org/download/product-categories/ the software catalogue]. Projects are written in a variety of languages, supported by international teams, and are generally fun to hack on. Spend some time exploring the ecosystem of projects around PostgreSQL to get a better feel for the variety and scope of ways that our database is used worldwide.<br />
<br />
=== Our philosophy about conversations/code in public ===<br />
<br />
The PostgreSQL project believes that public code review is the way to achieve our excellent quality of code. Therefore, patches for PostgreSQL must be discussed and submitted in public, and all patches are reviewed publicly. One exception to this policy is that security vulnerabilities may be disclosed to a private mailing list before fixes are published to help prevent exploitation of vulnerable users. <br />
<br />
Related to that, conversations about code, design decisions and user experience occur on the mailing lists. We try to steer all project conversations to the mailing lists so that there is a record of the thought process behind decisions, and so that all the participants and observers of our lists can learn from them.<br />
<br />
=== Resources on contributing to PostgreSQL ===<br />
<br />
* [[Submitting a Patch|Submitting A Patch]]: http://wiki.postgresql.org/wiki/Submitting_a_Patch<br />
* Greg Smith, Exposing PostgreSQL Internals with User-Defined Functions http://www.pgcon.org/2010/schedule/attachments/142_HackingWithUDFs.pdf<br />
* Josh Berkus, 50 ways to contribute to PostgreSQL http://www.slideshare.net/PGExperts/50-ways-to-love-your-project<br />
* Laetitia Avrot, De-mystifying contributing to PostgreSQL https://www.slideshare.net/LtitiaAvrot/demystifying-contributing-to-postgresql<br />
* Melanie Plageman, [https://www.postgresql.eu/events/pgconfeu2023/schedule/session/4904-making-your-patch-more-committable/ Making your patch more committable] - https://speakerdeck.com/melanieplageman/making-your-patch-more-committable<br />
* Michael Paquier, [https://www.postgresql.eu/events/pgconfeu2023/schedule/session/4859-postgresql-hacker-tips/ PostgreSQL hacker tips] - https://www.postgresql.eu/events/pgconfeu2023/sessions/session/4859/slides/399/PGConfEU2023_PostgreSQL_Hacker_Tips.pdf<br />
<br />
== Acknowledgments ==<br />
<br />
Thanks to Dave Page for feedback, editing and lots of questions.<br />
<br />
[[Category:Community]]<br />
[[Category:Getting a patch accepted]]</div>Saperhttps://wiki.postgresql.org/index.php?title=So,_you_want_to_be_a_developer%3F&diff=38543So, you want to be a developer?2023-12-21T22:41:26Z<p>Saper: cat typo</p>
<hr />
<div>'''by Selena Deckelmann'''<br />
<br />
This document is meant as a guide for brand new developers, seeking to contribute to PostgreSQL, but unsure about how to get started, or "the right way" to get involved. Feedback is welcome, as are additional links to important documents, examples, tutorials and personal stories about contributing to the project.<br />
<br />
We also have a [[Developer_FAQ]]<br />
<br />
== How to get started ==<br />
<br />
=== Overview ===<br />
<br />
Contributing to core PostgreSQL requires a few basic development tools - git, a C development environment and perl. Most modern Linux and BSD operating systems come with "-devel" packages usable for your development needs. At a very high level, you will: <br />
<br />
* Get the basic tools installed and working (git, a C development environment, and perl)<br />
* Clone our git source code repository<br />
* Compile PostgreSQL and successfully run the regression test suite<br />
<br />
Now, you should be ready to start hacking on code!<br />
<br />
=== Source code ===<br />
<br />
Source code can be found at http://git.postgresql.org/gitweb?p=postgresql.git;a=summary<br />
<br />
Once you have git installed, you can check this code out locally with the command: <br />
<br />
git clone https://git.postgresql.org/git/postgresql.git<br />
<br />
While there are release tarballs available, you should use a git clone to work on code with the community.<br />
<br />
=== Hacking PostgreSQL Resources ===<br />
<br />
There are a couple of different resources out on the net about how to go about actually hacking on PostgreSQL; these are just a few:<br />
<br />
Neil Conway and Gavin Sherry's original "Introduction to Hacking PostgreSQL": http://www.neilconway.org/talks/hacking/ ; presented at PgCon 2007 (http://www.pgcon.org/2007/schedule/events/8.en.html) and the PostgreSQL 10-year Anniversary Summit<br />
<br />
Stephen Frost's 2013 PgCon Talk "Hacking PostgreSQL" (http://www.pgcon.org/2013/schedule/events/545.en.html) slides are here: http://snowman.net/~sfrost/hackingpg-pgcon13_20130506.pdf and his 2011 PgCon Talk "Review of Patch Reviewing" (http://www.pgcon.org/2011/schedule/events/368.en.html) slides are here: http://www.pgcon.org/2011/schedule/attachments/189_pg_patch_review_20110516.odp<br />
<br />
Andrew Dunstan's "How to be a Happy Hacker", video here: http://www.youtube.com/watch?v=yFDyM29tB6k<br />
<br />
Fabrízio Mello and Dickson Guedes's "Hacking PostgreSQL" youtube channel (PT-BR): https://www.youtube.com/channel/UCjq4gJg4tYy0NqEEo3t60IA<br />
<br />
=== Style Guide ===<br />
<br />
Working with our source code involves some general rules. These are documented in our core documentation: http://www.postgresql.org/docs/current/static/source.html<br />
<br />
At a high level, we use 4-space tabbed indenting, strict ANSI C comment formatting, and our variable and function naming convention is to match the surrounding code. For example, if you see that variables use a CamelCase style, match that. If they use underscores, or are lowercase, match that. Readability and consistency within a section of code is of greater importance than universal consistency. If a section of code is being substantially reworked, developers sometimes will rework private function names and variable names to match current convention. However, projects to simply rename variables for the sake of renaming them to match current notions of coding style will be rejected.<br />
<br />
=== Bug fixing ===<br />
<br />
Bugs are posted to the mailing list: pgsql-bugs@postgresql.org<br />
<br />
You can see an archive of reported bugs at: https://www.postgresql.org/list/pgsql-bugs/<br />
<br />
Typically, a bug is posted via our [http://www.postgresql.org/support/submitbug/ bug reporting form], and then members of this list respond. Of course, not every issue posted to this list is actually a bug. A good way to learn more about how this process works is to subscribe to the list and observe for a while, before jumping in. Our software is quite complex, and development work spans a couple decades. With that history, many changes and ideas have been suggested, attempted, failed and succeeded. Please don't be discouraged if your initial ideas are rejected, significantly refactored or long-time contributors provide critical feedback to ideas or code. If contributors are responding, it is likely that they are attempting to provide direction to your work and suggesting that you try a different approach, rather than give up.<br />
<br />
Another source of bug reports is the pgsql-general@postgresql.org mailing list. Subscribing and responding to issues posted to this list is a great way to become familiar with the common problems everyday users of PostgreSQL face. Many members of our development community are on this list and respond regularly to user issues. Reading archives, and attempting to respond to issues as they come up is a significant and useful contribution to our community. <br />
<br />
Generally speaking, bug fixes are back-ported to affected branches whenever possible.<br />
<br />
=== TODOs ===<br />
<br />
It's worth checking if the feature of interest is found in the TODO list on our wiki: http://wiki.postgresql.org/wiki/TODO.<br />
<br />
The entries there often have additional information about the feature and may point to reasons why it hasn't been implemented yet. We have attempted to organize issues and link in relevant discussions from our mailing list archives. Please read background information if it is available before attempting to resolve a TODO. If no background information is available, it is appropriate to post a question to pgsql-hackers@postgresql.org and request additional information and inquire about the status of any ongoing work on the problem.<br />
<br />
=== Searching the PostgreSQL archives ===<br />
<br />
Starting a project should always begin with a search of our PostgreSQL mailing list archives. You can start at: https://www.postgresql.org/list/pgsql-hackers/.<br />
<br />
Our project's policy is to discuss as much of ongoing code work in public, including any in-progress patches, whenever possible. You may find significant and useful, but uncommitted, code in our archives that can either inform you about current or past work, or reduce the amount of work needed to accomplish a task. There are also some changes to our core project that were rejected, but are perfectly reasonable solutions to problems. Bottom line; searching our archives is a critical skill any member of our community must learn to be effective.<br />
<br />
=== Brand new features ===<br />
<br />
If you have a brand new idea for PostgreSQL, and you've already looked through our archives, scanned the TODO list and reviewed the code relevant to the change you'd like to make, it's time to dive into the pgsql-hackers@postgresql.org mailing list. <br />
<br />
This is a very active list - posting 20-100 or more messages a day. If you are working on a project, it is prudent that you subscribe to the mailing list for at least the duration of the project. The list is quite large, and is made up of contributors and observers from the last 15 years of development effort.<br />
<br />
Bruce Momjian created a presentation on how to get your patch accepted by the PostgreSQL community: http://momjian.us/main/writings/pgsql/patch.pdf<br />
<br />
Your initial post for a new project to our mailing list should include: <br />
<br />
* A description of the problem to be solved, or feature to be implemented.<br />
* Links to relevant standards documentation.<br />
* A short description of the areas of source code to be modified.<br />
* Intended timeline for implementation.<br />
* Links to relevant previous discussions on PostgreSQL mailing lists about the problem or feature.<br />
* CC any members of the development community you'll be directly working with on the project.<br />
* Link to a wiki page on wiki.postgresql.org for ongoing status updates.<br />
<br />
Your best chance of success in implementing a new feature is getting early involvement from members of the development community. It is entirely appropriate and necessary to initiate conversations about features on the pgsql-hackers mailing list, and request feedback in public from those developers who have worked on relevant or similar features in the past. We encourage this communication, and most active developers are willing and interested in providing mentorship in public for work that you undertake.<br />
<br />
New features are always committed to 'master' (the development branch in the git repository). It is the project's policy not to add features to released major versions.<br />
<br />
=== Commitfest and timing ===<br />
<br />
The Commitfest process was designed to keep track of incoming patches, help synchronize development and commit effort, make the review process more obvious and transparent, and to encourage new people to participate in the development of PostgreSQL. <br />
<br />
Developers are required to submit patches to the pgsql-hackers@postgresql.org mailing list before they will be reviewed. Once the email with the patch has been archived on the postgresql.org site, the patch can be linked into the Commitfest application (https://commitfest.postgresql.org). Commitfests are scheduled to start on the 15th of the month, and occur about every two months. We have had about five commitfests per year since the process was created.<br />
<br />
Not all patches are required to go through the commitfest process, although most of any substantial size or requiring detailed code review will.<br />
<br />
For the last couple of years, getting a major feature into a major dot release generally requires getting the patch into the review queue sometime between July-December. Feature freeze may happen in February, and new features will not be accepted until the new major release is complete. (A description and commentary on this is available at: http://rhaas.blogspot.com/2010/07/concurrent-development.html)<br />
<br />
More information about Commitfests is at: http://wiki.postgresql.org/wiki/CommitFest<br />
<br />
== Participating in the development community ==<br />
<br />
Information about the mailing lists is available on the [[Mailing Lists]] page, also reproduced here<br />
for your convenience.<br />
<br />
=== Mailing List Culture === <br />
<br />
The PostgreSQL community exists world-wide on our mailing lists. As you dive into our community, you will encounter people with wildly varying levels of expertise for databases, software development and system administration. Excellent technical and professional advice is given freely on the mailing lists, but there is no guarantee or expectation that anyone can solve any particular problem. Flaming or personal attacks are not tolerated on our mailing lists, IRC or related forums connected to the postgresql.org site. <br />
<br />
Above all, the PostgreSQL community's expectation is that each person treats the other with respect, and grants each other the benefit-of-the-doubt when it comes to terse or critical language. The Robustness Principle applies to participation in our community: Be conservative in what you send; be liberal in what you accept.<br />
<br />
That said, our community is known for its aggressive and technical discussion style. For those unfamiliar with our community, our discussions can come across as insulting or overly critical. Please keep in mind that as a new contributor, you are encountering a new culture. Every culture has different rules about appropriate behavior, social norms, and expectations. Much like when learning a new language or visiting a new, unfamiliar country, your experiences while joining the PostgreSQL community will undoubtably include an "adjustment cycle". That can and likely will include high and low moments, friendly or otherwise.<br />
<br />
As with any encounter with unfamiliar culture, you must take some time to get acquainted. Take extra time to communicate clearly. Ask for clarification if you're confused or a response doesn't make sense to you. Be careful to avoid personal attacks if someone makes a mistake. If there's one universal constant, it is that everyone makes mistakes.<br />
<br />
Remember that we are a learning community, and with few exceptions, people are communicating with the intention of learning, sharing and refining ideas.<br />
<br />
=== Email etiquette mechanics ===<br />
<br />
Signatures that include "confidentiality notices" are useless in the context of PostgreSQL mailing lists. All messages to our lists are archived publicly, are immediately available worldwide and will not be removed from our archives. Please remove the notices from your email to our lists, particularly when posting code that you wish to be contributed or shared with our community.<br />
<br />
When replying, please be respectful and use appropriate quoting. See the [https://web.archive.org/web/20170426175120/http://www.gweep.ca/~edmonds/usenet/ml-etiquette.html Mailing List Etiquette FAQ] for details about what constitutes appropriate quoting when replying to mailing lists. <br />
<br />
Our mailing lists are generally set to "reply to sender", but the preferred way to participate in threads is to "reply all". That means that you'll include both the email address of the sender and the mailing list in your response. Also, please do not send HTML-enriched email to the mailing lists.<br />
<br />
Finally, our community generally does not "top post" in response to mailing list threads (See [https://en.wikipedia.org/wiki/Posting_style#Top-posting Wikipedia: Top Posting]for a definition of top posting).<br />
<br />
=== Using the discussion lists ===<br />
<br />
You can send an email directly to any of the mailing lists, without subscribing first. <br />
Any responses you receive or send should be sent to the list ''and'' CC correspondents.<br />
<br />
If you wish to receive the mail traffic sent to a list, you can join using the [https://lists.postgresql.org/ subscribe] form. You should receive an email in response from the mailing list manager software that handles the lists. If you wish change the various settings associated with your subscription or unsubscribe, you can do so using the [https://lists.postgresql.org/ web] interface.<br />
<br />
If you follow discussion through the web interface instead of subscribing,<br />
you will at some point wish to reply to a message sent to the list. '''Do not''' simply copy<br />
the message body and paste it into a message with a similar subject as a way to join the conversation.<br />
The mailing list relies on the "In-Reply-To" mail header in order to associate individual messages<br />
to their thread. If you don't know how to add this header manually, you should instead make use<br />
of the "raw" link [https://www.postgresql.org/message-id/CA+OCxoxAm_iEh21sxHiYzZxK9_3JjdzHLX4ib--ZbH73yfb_zA@mail.gmail.com provided] on every message view to download the message as a file<br />
(in mbox format), then import it into your favorite email client and use the usual "Reply All"<br />
way of responding to mailing list messages.<br />
<br />
=== Overview of discussion lists ===<br />
<br />
We have two primary lists related to usage and development of postgresql: [https://www.postgresql.org/list/pgsql-general/ pgsql-general@postgresql.org] and [https://www.postgresql.org/list/pgsql-hackers/ pgsql-hackers@postgresql.org]. pgsql-general is the correct place to start if you are having a problem with your PostgreSQL installation, need help with installation, are a software developer using PostgreSQL or have a general question about the project. pgsql-hackers is the correct place to go if you have a patch to submit, would like to learn more about how to develop PostgreSQL itself, or are interested in database internals. We also have the [https://www.postgresql.org/list/pgsql-novice/ pgsql-novice@postgresql.org] list if you would like to try posting a question a smaller list, with a group of people who are there specifically to answer very basic questions.<br />
<br />
If you are primarily interested in performance tuning, benchmarking or case studies from existing users regarding performance, [https://www.postgresql.org/list/pgsql-performance/ pgsql-performance@postgresql.org] is a great list to join.<br />
<br />
If you're interested in contributing to website maintenance or editing, or system administration of PostgreSQL infrastructure, join the [https://www.postgresql.org/list/pgsql-www/ pgsql-www@postgresql.org] mailing list.<br />
<br />
If you have something to contribute to the PostgreSQL documentation, join the [https://www.postgresql.org/list/pgsql-docs/ pgsql-docs@postgresql.org] mailing list. The documentation is always in need of copy editors, testers and example generation.<br />
<br />
If you're interested in staffing booths at conferences, giving talks at conferences, starting a user group or participating in a user group, join the [https://www.postgresql.org/list/pgsql-advocacy/ pgsql-advocacy@postgresql.org] mailing list. We are always in need of booth volunteers, speakers, case study writers and bloggers.<br />
<br />
If you think you've found a bug in PostgreSQL and are new to our project, we suggest you ask about it on the [https://www.postgresql.org/list/pgsql-general/ pgsql-general] list first, and then read our [http://www.postgresql.org/docs/current/static/bug-reporting.html Bug Submission Guidelines] and then go to our [http://www.postgresql.org/support/submitbug Bug Reporting form].<br />
<br />
We also have User Group mailing lists, language-specific lists and some other specific projects with their own communities. You can find a comprehensive list of these at: [http://www.postgresql.org/community/lists/ http://www.postgresql.org/community/lists/]<br />
<br />
=== Wiki ===<br />
<br />
Our wiki is active and frequently updated at: http://wiki.postgresql.org. We encourage contributors to add to the material there, and to make corrections to any errors found.<br />
<br />
=== Projects related to PostgreSQL ===<br />
<br />
There are hundreds of projects that are dependent upon, related to or extend PostgreSQL. You can find partial list of those projects at [https://www.postgresql.org/docs/current/external-projects.html External Projects] doc page, [https://pgxn.org/ PGXN] or the [https://www.postgresql.org/download/product-categories/ the software catalogue]. Projects are written in a variety of languages, supported by international teams, and are generally fun to hack on. Spend some time exploring the ecosystem of projects around PostgreSQL to get a better feel for the variety and scope of ways that our database is used worldwide.<br />
<br />
=== Our philosophy about conversations/code in public ===<br />
<br />
The PostgreSQL project believes that public code review is the way to achieve our excellent quality of code. Therefore, patches for PostgreSQL must be discussed and submitted in public, and all patches are reviewed publicly. One exception to this policy is that security vulnerabilities may be disclosed to a private mailing list before fixes are published to help prevent exploitation of vulnerable users. <br />
<br />
Related to that, conversations about code, design decisions and user experience occur on the mailing lists. We try to steer all project conversations to the mailing lists so that there is a record of the thought process behind decisions, and so that all the participants and observers of our lists can learn from them.<br />
<br />
=== Resources on contributing to PostgreSQL ===<br />
<br />
* Submitting A Patch: http://wiki.postgresql.org/wiki/Submitting_a_Patch<br />
* Greg Smith, Exposing PostgreSQL Internals with User-Defined Functions http://www.pgcon.org/2010/schedule/attachments/142_HackingWithUDFs.pdf<br />
* Josh Berkus, 50 ways to contribute to PostgreSQL http://www.slideshare.net/PGExperts/50-ways-to-love-your-project<br />
* Laetitia Avrot, De-mystifying contributing to PostgreSQL https://www.slideshare.net/LtitiaAvrot/demystifying-contributing-to-postgresql<br />
<br />
== Acknowledgments ==<br />
<br />
Thanks to Dave Page for feedback, editing and lots of questions.<br />
<br />
[[Category:Community]]<br />
[[Category:Getting a patch accepted]]</div>Saperhttps://wiki.postgresql.org/index.php?title=So,_you_want_to_be_a_developer%3F&diff=38542So, you want to be a developer?2023-12-21T22:40:54Z<p>Saper: </p>
<hr />
<div>'''by Selena Deckelmann'''<br />
<br />
This document is meant as a guide for brand new developers, seeking to contribute to PostgreSQL, but unsure about how to get started, or "the right way" to get involved. Feedback is welcome, as are additional links to important documents, examples, tutorials and personal stories about contributing to the project.<br />
<br />
We also have a [[Developer_FAQ]]<br />
<br />
== How to get started ==<br />
<br />
=== Overview ===<br />
<br />
Contributing to core PostgreSQL requires a few basic development tools - git, a C development environment and perl. Most modern Linux and BSD operating systems come with "-devel" packages usable for your development needs. At a very high level, you will: <br />
<br />
* Get the basic tools installed and working (git, a C development environment, and perl)<br />
* Clone our git source code repository<br />
* Compile PostgreSQL and successfully run the regression test suite<br />
<br />
Now, you should be ready to start hacking on code!<br />
<br />
=== Source code ===<br />
<br />
Source code can be found at http://git.postgresql.org/gitweb?p=postgresql.git;a=summary<br />
<br />
Once you have git installed, you can check this code out locally with the command: <br />
<br />
git clone https://git.postgresql.org/git/postgresql.git<br />
<br />
While there are release tarballs available, you should use a git clone to work on code with the community.<br />
<br />
=== Hacking PostgreSQL Resources ===<br />
<br />
There are a couple of different resources out on the net about how to go about actually hacking on PostgreSQL; these are just a few:<br />
<br />
Neil Conway and Gavin Sherry's original "Introduction to Hacking PostgreSQL": http://www.neilconway.org/talks/hacking/ ; presented at PgCon 2007 (http://www.pgcon.org/2007/schedule/events/8.en.html) and the PostgreSQL 10-year Anniversary Summit<br />
<br />
Stephen Frost's 2013 PgCon Talk "Hacking PostgreSQL" (http://www.pgcon.org/2013/schedule/events/545.en.html) slides are here: http://snowman.net/~sfrost/hackingpg-pgcon13_20130506.pdf and his 2011 PgCon Talk "Review of Patch Reviewing" (http://www.pgcon.org/2011/schedule/events/368.en.html) slides are here: http://www.pgcon.org/2011/schedule/attachments/189_pg_patch_review_20110516.odp<br />
<br />
Andrew Dunstan's "How to be a Happy Hacker", video here: http://www.youtube.com/watch?v=yFDyM29tB6k<br />
<br />
Fabrízio Mello and Dickson Guedes's "Hacking PostgreSQL" youtube channel (PT-BR): https://www.youtube.com/channel/UCjq4gJg4tYy0NqEEo3t60IA<br />
<br />
=== Style Guide ===<br />
<br />
Working with our source code involves some general rules. These are documented in our core documentation: http://www.postgresql.org/docs/current/static/source.html<br />
<br />
At a high level, we use 4-space tabbed indenting, strict ANSI C comment formatting, and our variable and function naming convention is to match the surrounding code. For example, if you see that variables use a CamelCase style, match that. If they use underscores, or are lowercase, match that. Readability and consistency within a section of code is of greater importance than universal consistency. If a section of code is being substantially reworked, developers sometimes will rework private function names and variable names to match current convention. However, projects to simply rename variables for the sake of renaming them to match current notions of coding style will be rejected.<br />
<br />
=== Bug fixing ===<br />
<br />
Bugs are posted to the mailing list: pgsql-bugs@postgresql.org<br />
<br />
You can see an archive of reported bugs at: https://www.postgresql.org/list/pgsql-bugs/<br />
<br />
Typically, a bug is posted via our [http://www.postgresql.org/support/submitbug/ bug reporting form], and then members of this list respond. Of course, not every issue posted to this list is actually a bug. A good way to learn more about how this process works is to subscribe to the list and observe for a while, before jumping in. Our software is quite complex, and development work spans a couple decades. With that history, many changes and ideas have been suggested, attempted, failed and succeeded. Please don't be discouraged if your initial ideas are rejected, significantly refactored or long-time contributors provide critical feedback to ideas or code. If contributors are responding, it is likely that they are attempting to provide direction to your work and suggesting that you try a different approach, rather than give up.<br />
<br />
Another source of bug reports is the pgsql-general@postgresql.org mailing list. Subscribing and responding to issues posted to this list is a great way to become familiar with the common problems everyday users of PostgreSQL face. Many members of our development community are on this list and respond regularly to user issues. Reading archives, and attempting to respond to issues as they come up is a significant and useful contribution to our community. <br />
<br />
Generally speaking, bug fixes are back-ported to affected branches whenever possible.<br />
<br />
=== TODOs ===<br />
<br />
It's worth checking if the feature of interest is found in the TODO list on our wiki: http://wiki.postgresql.org/wiki/TODO.<br />
<br />
The entries there often have additional information about the feature and may point to reasons why it hasn't been implemented yet. We have attempted to organize issues and link in relevant discussions from our mailing list archives. Please read background information if it is available before attempting to resolve a TODO. If no background information is available, it is appropriate to post a question to pgsql-hackers@postgresql.org and request additional information and inquire about the status of any ongoing work on the problem.<br />
<br />
=== Searching the PostgreSQL archives ===<br />
<br />
Starting a project should always begin with a search of our PostgreSQL mailing list archives. You can start at: https://www.postgresql.org/list/pgsql-hackers/.<br />
<br />
Our project's policy is to discuss as much of ongoing code work in public, including any in-progress patches, whenever possible. You may find significant and useful, but uncommitted, code in our archives that can either inform you about current or past work, or reduce the amount of work needed to accomplish a task. There are also some changes to our core project that were rejected, but are perfectly reasonable solutions to problems. Bottom line; searching our archives is a critical skill any member of our community must learn to be effective.<br />
<br />
=== Brand new features ===<br />
<br />
If you have a brand new idea for PostgreSQL, and you've already looked through our archives, scanned the TODO list and reviewed the code relevant to the change you'd like to make, it's time to dive into the pgsql-hackers@postgresql.org mailing list. <br />
<br />
This is a very active list - posting 20-100 or more messages a day. If you are working on a project, it is prudent that you subscribe to the mailing list for at least the duration of the project. The list is quite large, and is made up of contributors and observers from the last 15 years of development effort.<br />
<br />
Bruce Momjian created a presentation on how to get your patch accepted by the PostgreSQL community: http://momjian.us/main/writings/pgsql/patch.pdf<br />
<br />
Your initial post for a new project to our mailing list should include: <br />
<br />
* A description of the problem to be solved, or feature to be implemented.<br />
* Links to relevant standards documentation.<br />
* A short description of the areas of source code to be modified.<br />
* Intended timeline for implementation.<br />
* Links to relevant previous discussions on PostgreSQL mailing lists about the problem or feature.<br />
* CC any members of the development community you'll be directly working with on the project.<br />
* Link to a wiki page on wiki.postgresql.org for ongoing status updates.<br />
<br />
Your best chance of success in implementing a new feature is getting early involvement from members of the development community. It is entirely appropriate and necessary to initiate conversations about features on the pgsql-hackers mailing list, and request feedback in public from those developers who have worked on relevant or similar features in the past. We encourage this communication, and most active developers are willing and interested in providing mentorship in public for work that you undertake.<br />
<br />
New features are always committed to 'master' (the development branch in the git repository). It is the project's policy not to add features to released major versions.<br />
<br />
=== Commitfest and timing ===<br />
<br />
The Commitfest process was designed to keep track of incoming patches, help synchronize development and commit effort, make the review process more obvious and transparent, and to encourage new people to participate in the development of PostgreSQL. <br />
<br />
Developers are required to submit patches to the pgsql-hackers@postgresql.org mailing list before they will be reviewed. Once the email with the patch has been archived on the postgresql.org site, the patch can be linked into the Commitfest application (https://commitfest.postgresql.org). Commitfests are scheduled to start on the 15th of the month, and occur about every two months. We have had about five commitfests per year since the process was created.<br />
<br />
Not all patches are required to go through the commitfest process, although most of any substantial size or requiring detailed code review will.<br />
<br />
For the last couple of years, getting a major feature into a major dot release generally requires getting the patch into the review queue sometime between July-December. Feature freeze may happen in February, and new features will not be accepted until the new major release is complete. (A description and commentary on this is available at: http://rhaas.blogspot.com/2010/07/concurrent-development.html)<br />
<br />
More information about Commitfests is at: http://wiki.postgresql.org/wiki/CommitFest<br />
<br />
== Participating in the development community ==<br />
<br />
Information about the mailing lists is available on the [[Mailing Lists]] page, also reproduced here<br />
for your convenience.<br />
<br />
=== Mailing List Culture === <br />
<br />
The PostgreSQL community exists world-wide on our mailing lists. As you dive into our community, you will encounter people with wildly varying levels of expertise for databases, software development and system administration. Excellent technical and professional advice is given freely on the mailing lists, but there is no guarantee or expectation that anyone can solve any particular problem. Flaming or personal attacks are not tolerated on our mailing lists, IRC or related forums connected to the postgresql.org site. <br />
<br />
Above all, the PostgreSQL community's expectation is that each person treats the other with respect, and grants each other the benefit-of-the-doubt when it comes to terse or critical language. The Robustness Principle applies to participation in our community: Be conservative in what you send; be liberal in what you accept.<br />
<br />
That said, our community is known for its aggressive and technical discussion style. For those unfamiliar with our community, our discussions can come across as insulting or overly critical. Please keep in mind that as a new contributor, you are encountering a new culture. Every culture has different rules about appropriate behavior, social norms, and expectations. Much like when learning a new language or visiting a new, unfamiliar country, your experiences while joining the PostgreSQL community will undoubtably include an "adjustment cycle". That can and likely will include high and low moments, friendly or otherwise.<br />
<br />
As with any encounter with unfamiliar culture, you must take some time to get acquainted. Take extra time to communicate clearly. Ask for clarification if you're confused or a response doesn't make sense to you. Be careful to avoid personal attacks if someone makes a mistake. If there's one universal constant, it is that everyone makes mistakes.<br />
<br />
Remember that we are a learning community, and with few exceptions, people are communicating with the intention of learning, sharing and refining ideas.<br />
<br />
=== Email etiquette mechanics ===<br />
<br />
Signatures that include "confidentiality notices" are useless in the context of PostgreSQL mailing lists. All messages to our lists are archived publicly, are immediately available worldwide and will not be removed from our archives. Please remove the notices from your email to our lists, particularly when posting code that you wish to be contributed or shared with our community.<br />
<br />
When replying, please be respectful and use appropriate quoting. See the [https://web.archive.org/web/20170426175120/http://www.gweep.ca/~edmonds/usenet/ml-etiquette.html Mailing List Etiquette FAQ] for details about what constitutes appropriate quoting when replying to mailing lists. <br />
<br />
Our mailing lists are generally set to "reply to sender", but the preferred way to participate in threads is to "reply all". That means that you'll include both the email address of the sender and the mailing list in your response. Also, please do not send HTML-enriched email to the mailing lists.<br />
<br />
Finally, our community generally does not "top post" in response to mailing list threads (See [https://en.wikipedia.org/wiki/Posting_style#Top-posting Wikipedia: Top Posting]for a definition of top posting).<br />
<br />
=== Using the discussion lists ===<br />
<br />
You can send an email directly to any of the mailing lists, without subscribing first. <br />
Any responses you receive or send should be sent to the list ''and'' CC correspondents.<br />
<br />
If you wish to receive the mail traffic sent to a list, you can join using the [https://lists.postgresql.org/ subscribe] form. You should receive an email in response from the mailing list manager software that handles the lists. If you wish change the various settings associated with your subscription or unsubscribe, you can do so using the [https://lists.postgresql.org/ web] interface.<br />
<br />
If you follow discussion through the web interface instead of subscribing,<br />
you will at some point wish to reply to a message sent to the list. '''Do not''' simply copy<br />
the message body and paste it into a message with a similar subject as a way to join the conversation.<br />
The mailing list relies on the "In-Reply-To" mail header in order to associate individual messages<br />
to their thread. If you don't know how to add this header manually, you should instead make use<br />
of the "raw" link [https://www.postgresql.org/message-id/CA+OCxoxAm_iEh21sxHiYzZxK9_3JjdzHLX4ib--ZbH73yfb_zA@mail.gmail.com provided] on every message view to download the message as a file<br />
(in mbox format), then import it into your favorite email client and use the usual "Reply All"<br />
way of responding to mailing list messages.<br />
<br />
=== Overview of discussion lists ===<br />
<br />
We have two primary lists related to usage and development of postgresql: [https://www.postgresql.org/list/pgsql-general/ pgsql-general@postgresql.org] and [https://www.postgresql.org/list/pgsql-hackers/ pgsql-hackers@postgresql.org]. pgsql-general is the correct place to start if you are having a problem with your PostgreSQL installation, need help with installation, are a software developer using PostgreSQL or have a general question about the project. pgsql-hackers is the correct place to go if you have a patch to submit, would like to learn more about how to develop PostgreSQL itself, or are interested in database internals. We also have the [https://www.postgresql.org/list/pgsql-novice/ pgsql-novice@postgresql.org] list if you would like to try posting a question a smaller list, with a group of people who are there specifically to answer very basic questions.<br />
<br />
If you are primarily interested in performance tuning, benchmarking or case studies from existing users regarding performance, [https://www.postgresql.org/list/pgsql-performance/ pgsql-performance@postgresql.org] is a great list to join.<br />
<br />
If you're interested in contributing to website maintenance or editing, or system administration of PostgreSQL infrastructure, join the [https://www.postgresql.org/list/pgsql-www/ pgsql-www@postgresql.org] mailing list.<br />
<br />
If you have something to contribute to the PostgreSQL documentation, join the [https://www.postgresql.org/list/pgsql-docs/ pgsql-docs@postgresql.org] mailing list. The documentation is always in need of copy editors, testers and example generation.<br />
<br />
If you're interested in staffing booths at conferences, giving talks at conferences, starting a user group or participating in a user group, join the [https://www.postgresql.org/list/pgsql-advocacy/ pgsql-advocacy@postgresql.org] mailing list. We are always in need of booth volunteers, speakers, case study writers and bloggers.<br />
<br />
If you think you've found a bug in PostgreSQL and are new to our project, we suggest you ask about it on the [https://www.postgresql.org/list/pgsql-general/ pgsql-general] list first, and then read our [http://www.postgresql.org/docs/current/static/bug-reporting.html Bug Submission Guidelines] and then go to our [http://www.postgresql.org/support/submitbug Bug Reporting form].<br />
<br />
We also have User Group mailing lists, language-specific lists and some other specific projects with their own communities. You can find a comprehensive list of these at: [http://www.postgresql.org/community/lists/ http://www.postgresql.org/community/lists/]<br />
<br />
=== Wiki ===<br />
<br />
Our wiki is active and frequently updated at: http://wiki.postgresql.org. We encourage contributors to add to the material there, and to make corrections to any errors found.<br />
<br />
=== Projects related to PostgreSQL ===<br />
<br />
There are hundreds of projects that are dependent upon, related to or extend PostgreSQL. You can find partial list of those projects at [https://www.postgresql.org/docs/current/external-projects.html External Projects] doc page, [https://pgxn.org/ PGXN] or the [https://www.postgresql.org/download/product-categories/ the software catalogue]. Projects are written in a variety of languages, supported by international teams, and are generally fun to hack on. Spend some time exploring the ecosystem of projects around PostgreSQL to get a better feel for the variety and scope of ways that our database is used worldwide.<br />
<br />
=== Our philosophy about conversations/code in public ===<br />
<br />
The PostgreSQL project believes that public code review is the way to achieve our excellent quality of code. Therefore, patches for PostgreSQL must be discussed and submitted in public, and all patches are reviewed publicly. One exception to this policy is that security vulnerabilities may be disclosed to a private mailing list before fixes are published to help prevent exploitation of vulnerable users. <br />
<br />
Related to that, conversations about code, design decisions and user experience occur on the mailing lists. We try to steer all project conversations to the mailing lists so that there is a record of the thought process behind decisions, and so that all the participants and observers of our lists can learn from them.<br />
<br />
=== Resources on contributing to PostgreSQL ===<br />
<br />
* Submitting A Patch: http://wiki.postgresql.org/wiki/Submitting_a_Patch<br />
* Greg Smith, Exposing PostgreSQL Internals with User-Defined Functions http://www.pgcon.org/2010/schedule/attachments/142_HackingWithUDFs.pdf<br />
* Josh Berkus, 50 ways to contribute to PostgreSQL http://www.slideshare.net/PGExperts/50-ways-to-love-your-project<br />
* Laetitia Avrot, De-mystifying contributing to PostgreSQL https://www.slideshare.net/LtitiaAvrot/demystifying-contributing-to-postgresql<br />
<br />
== Acknowledgments ==<br />
<br />
Thanks to Dave Page for feedback, editing and lots of questions.<br />
<br />
[[Category:Community]]<br />
[[Category:Getting your patch accepted]]</div>Saperhttps://wiki.postgresql.org/index.php?title=ProposalTemplate&diff=38474ProposalTemplate2023-12-14T03:09:08Z<p>Saper: +category</p>
<hr />
<div>== Proposal Template For a New Feature ==<br />
<br />
<br />
'''One-line Summary:''' Explain in one line regarding the feature so that most hackers can go through it and see if is interesting enough to read the rest of the proposal.<br />
<br />
<br />
'''Business Use-case:''' Explain the problem that you are trying to solve with the proposal.<br />
<br />
<br />
'''User impact with the change:''' What will change from the perspective of existing PostgreSQL user? (Maybe if it is a new feature they never had that syntax support or currently it gives some error, etc.)<br />
<br />
<br />
'''Implementation details:''' If you already have an idea on how it can be implemented.<br />
<br />
'''Estimated Development Time:''' Man-hours estimated for the proposal to be implemented. (If not sure, mention unknown)<br />
<br />
<br />
'''Opportunity Window Period:''' An end-date if by which if it is not completed, it will be useless to work on the implementation<br />
<br />
<br />
'''Budget Money:''' A range that the sponsor has in mind for the price of development of the proposal<br />
<br />
<br />
'''Contact Information:''' Contact information of the sponsor<br />
<br />
<br />
Category: Include the text: &#91;&#91;Category:Proposals]] so that MediaWiki categorizes your proposal appropriately.<br />
<br />
[[Category:Proposals|!]]<br />
[[Category:Getting a patch accepted]]</div>Saperhttps://wiki.postgresql.org/index.php?title=Creating_Clean_Patches&diff=38473Creating Clean Patches2023-12-14T03:08:52Z<p>Saper: +category</p>
<hr />
<div>__NOTOC__<br />
====Examples of creating better formatted patches. Originally written by [[User:Gsmith|Greg Smith]], PostgreSQL Major Contributor and proud formatting curmudgeon====<br />
<br />
If you surveyed people about how important they felt "hygiene" was to being a successful programmer, you may not get very flattering responses. But good coders are notorious for being picky about code hygiene, particularly formatting. There are some bad ways to learn this. Submitting something that's formatted badly to an open-source project with strong standards is one. Until you've collaborated with someone by exchanging patches, it's really not obvious what you should do though. <br />
<br />
Watching new patch submitters struggle to learn good practice inspired this article. It provides a tour of the sort of boring trivia that you need to go through in order to make a good patch. In this case the target is meeting the coding standards for PostgreSQL (see "What's the formatting style used in PostgreSQL source code?" in the [[Developer FAQ]]) using git as your version control tool. This sort of work fits between [[Working with Git]] and [[Submitting a Patch]] in the normal development order of this project. But the basic concepts for what makes a good patch should apply to any project that exchanges code in the form of source code patches.<br />
<br />
== Patch coding ==<br />
We need a patch to tweak here first, so here's a trivial but useful example. There can be some bottlenecks in PostgreSQL when adding new data to a table, what the database calls "relation extension". The idea is that if you allocate new space in a table or index (two common types of relations), the database sometimes makes the relation a database page (8K) bigger. A first step toward experimenting with this code would be to add some logging that confirms it's happening where I expect in the source code; that's the patch I'll explore here. I wrote a quick patch, borrowing pieces from nearby code, and the result looks like this when I populate a database using the pgbench command:<br />
<br />
<pre><br />
INFO: extending relation base/16494/16507 to add block 16373<br />
CONTEXT: COPY pgbench_accounts, line 998754: "998754 10 0 "<br />
INFO: extending relation base/16494/16507 to add block 16374<br />
CONTEXT: COPY pgbench_accounts, line 998815: "998815 10 0 "<br />
</pre><br />
<br />
That's a start. I can see exactly how fast the file is growing, decode the relation filename if I want to, and I can even estimate how many rows of input data are fitting into a database page with this logging. After making the code changes, I run "git diff" to get a patch out.<br />
<br />
== Respect your reader ==<br />
<br />
This is where some people make their first mistake: since the code worked, they "ship" the patch as a submission with no further work. The new code you wrote is not the final product here; the patch diff is. You should review that patch as carefully as you did the new code, look at everything it touches, before submitting it to a project. This can catch formatting errors, and fixing some of those things will make your patch smaller. A smaller patch means less work for a reviewer who is trying to decide if it's worthwhile, and less work for a committer to apply. (In smaller projects that may be the same person). Minimizing how long it takes someone to review your patch highly increases the odds it will be accepted into an open-source project.<br />
<br />
== Reducing patch diff size ==<br />
Let's see what comes out of my trivial logging patch:<br />
<br />
<pre><br />
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c<br />
index f96685d..a257b9d 100644<br />
--- a/src/backend/storage/buffer/bufmgr.c<br />
+++ b/src/backend/storage/buffer/bufmgr.c<br />
@@ -420,11 +420,14 @@ ReadBuffer_common(SMgrRelation smgr, char relpersistence, ForkNumber forkNum,<br />
bufBlock = isLocalBuf ? LocalBufHdrGetBlock(bufHdr) : BufHdrGetBlock(bufHdr);<br />
<br />
if (isExtend)<br />
- {<br />
+ { <br />
/* new buffers are zero-filled */<br />
MemSet((char *) bufBlock, 0, BLCKSZ);<br />
smgrextend(smgr, forkNum, blockNum, (char *) bufBlock, false);<br />
- }<br />
+ elog(INFO, "extending relation %s to add block %u",<br />
+ relpath(smgr->smgr_rnode, forkNum),<br />
+ blockNum);<br />
+ }<br />
else<br />
{<br />
/*<br />
</pre><br />
<br />
What's all that garbage doing there? What I was hoping for is the three new "elog" lines I added to be the only thing here. But there's all this extra junk. How come? Well, I intentionally made a mistake many people make accidentally. The lines containing braces: { } I touched while developing the patch, and my editor mangled them even though the final content looks the same in the file. PostgreSQL convention says that indentation should be done with 4 character tabs. My editor changed them to spaces. This is why you see a "-" sign removing a line with the opening brace, then a changed line after it adding it right back in. To fix this, I need to go back and reformat all the lines I touched, changing the spaces before the braces to the right size tabs. I do a round of that, then get another diff:<br />
<br />
<pre><br />
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c<br />
index f96685d..a240c79 100644<br />
--- a/src/backend/storage/buffer/bufmgr.c<br />
+++ b/src/backend/storage/buffer/bufmgr.c<br />
@@ -420,10 +420,13 @@ ReadBuffer_common(SMgrRelation smgr, char relpersistence, ForkNumber forkNum,<br />
bufBlock = isLocalBuf ? LocalBufHdrGetBlock(bufHdr) : BufHdrGetBlock(bufHdr);<br />
<br />
if (isExtend)<br />
- {<br />
+ { <br />
/* new buffers are zero-filled */<br />
MemSet((char *) bufBlock, 0, BLCKSZ);<br />
smgrextend(smgr, forkNum, blockNum, (char *) bufBlock, false);<br />
+ elog(INFO, "extending relation %s to add block %u",<br />
+ relpath(smgr->smgr_rnode, forkNum),<br />
+ blockNum);<br />
}<br />
else<br />
{<br />
</pre><br />
<br />
That's better...but the remaining problem is even more subtle. Why in the world is it deleting the opening brace here and then putting it right back again? Well, in this case the error I introduced was adding some trailing whitespace. You can't see it here, but the line I touched is actually "{ ", where the original was just "{". Try highlighting it in your web browser, you may be able to see it there. That's making this diff bigger than it needs to be, as well as violating code conventions.<br />
<br />
== Invisible whitespace ==<br />
<br />
At this point you might be thinking "how am I supposed to find invisible whitespace to remove it?", which is an excellent question. One option is to use a color terminal and execute the diff like this:<br />
<br />
<pre><br />
git diff --color<br />
</pre><br />
<br />
In that mode, the unwanted space I added jumps right out. Edit to fix, new diff:<br />
<br />
<pre><br />
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c<br />
index f96685d..eec261b 100644<br />
--- a/src/backend/storage/buffer/bufmgr.c<br />
+++ b/src/backend/storage/buffer/bufmgr.c<br />
@@ -424,6 +424,9 @@ ReadBuffer_common(SMgrRelation smgr, char relpersistence, ForkNumber forkNum,<br />
/* new buffers are zero-filled */<br />
MemSet((char *) bufBlock, 0, BLCKSZ);<br />
smgrextend(smgr, forkNum, blockNum, (char *) bufBlock, false);<br />
+ elog(INFO, "extending relation %s to add block %u",<br />
+ relpath(smgr->smgr_rnode, forkNum),<br />
+ blockNum);<br />
}<br />
else<br />
{<br />
</pre><br />
<br />
Now this is what a patch diff you can be proud to submit should look like. It's really obvious exactly what was changed. There are no unrelated changes confusing things. Tabs and whitespace are used correctly so that all of the new code lines up with the existing lines. <br />
<br />
A clean patch submission is more likely to be considered for its code merits, where a messy one wastes time for the reviewer and automatically biases them against you from the start. Go back and look at what a mess the original patch was to read compared to this one. If you'd never seen this code change before, exactly what is modified is clear here. In the earlier versions, all the whitespace changes made it much harder to extract that meaning. This is despite the fact that there is no difference in the actual code here! The compiler will do exactly the same thing in both cases. This is about making the patch readable to a person though, and following the coding standards for a project improves your odds. You might consider having your text editor automatically remove trailing white space, although this is something that you'll only want to do selectively as trailing white space can be appropriate and necessary when editing other types of files.<br />
<br />
== Squashing fixes with rebasing ==<br />
<br />
Getting even more specific to git (most of the ideas here apply to any version control system), regularly rebasing is useful for several aspects to cleaner patch creation. Let's say you didn't notice the whitespace error until after you'd committed the change in your local branch. You can fix that in an extra commit, but you're going to have a messier code history. If you didn't send your local tree anywhere else yet, there's one way you can deal with this easily though. Commit your whitespace fix as a new commit, then run:<br />
<br />
<pre><br />
git rebase -i origin/master<br />
</pre><br />
<br />
Where you substitute the name of your origin branch, the one you want to create a diff against, for the last part there. You'll see a history like this:<br />
<br />
<pre><br />
pick 57c7bfe Awesome new feature<br />
pick 08dcf68 Whitespace cleanup<br />
</pre><br />
<br />
What you can do now is edit this so that the second patch is "squashed" into the first, by changing this to look like:<br />
<br />
<pre><br />
pick 57c7bfe Awesome new feature<br />
s 08dcf68 Whitespace cleanup<br />
</pre><br />
<br />
Save that file, and you'll get a second editor screen to adjust the combined commit message. Erase the one related to the whitespace cleanup, save that, and now this little whitespace mistake is history. I'll sometimes do that shuffle several times, each time compacting a bit more of the whitespace or code down:<br />
<br />
<pre><br />
git diff --color HEAD^ HEAD<br />
vi <file><br />
git commit -a<br />
git rebase -i origin/master<br />
</pre><br />
<br />
This cycle is worth practicing on test code: commit, diff, fix whitespace, rebase squash, new diff. It's unfortunately easy to corrupt your work using rebase, so be careful with it. I will normally save a copy of the initial patch, just out of paranoia, each time through this loop. It's useful as a backup if you make a mistake, and you can use tools like "wc" to track improvements in reducing its size during each loop. (There's certainly a faster way to accomplish this series of steps using git, but this approach is simple to understand and remember)<br />
<br />
Learning how to use rebase well is also important if you developed your patch over a long enough period of time that the master tree you're working against has changed significantly. You can move earlier commits so they are the most recent ones in the commit history with it, which is also critical to get a clean diff that applies to a project you're submitting to.<br />
<br />
== Patient diffs ==<br />
<br />
<br />
Sometimes diff produces output that, while clean, isn't the best way to represent a new addition. The most common case is where a code block using braces ends up out of sync by one line, where your new code is shown as re-using an opening or closing brace from existing code in an odd way. There is an alternate method diff can used, called "patience", that often does better in this situation:<br />
<br />
<pre><br />
git diff --color HEAD^ HEAD --patience<br />
</pre><br />
<br />
The description and example at [http://bramcohen.livejournal.com/73318.html Patience Diff Advantages] show how this might work. If your code is offset strangely because it re-uses existing lines that happen to be in your addition, you should try patience mode and see if it produces a cleaner patch.<br />
<br />
== Code reading and patch diff creation ==<br />
<br />
Reading your code changes as a patch is a great exercise for lots of reasons. I've found plenty of errors by reviewing my own code this way. Sometimes seeing the diff makes me think of ways I can reduce the code footprint of what I've changed, which can lead to a smaller and cleaner patch. For a substantial patch, successful patch contributors can easily spend an hour or more doing this sort of work before submitting to a project, tweaking the formatting to shrink the patch to the bare minimum. It makes your code better, and it makes it more likely to be read, accepted, and committed. When I am merging submissions from contributors to one of the projects I lead, or reviewing submissions to PostgreSQL, seeing basic formatting errors or a sloppy diff means I start out assuming the rest of the code isn't likely to be clean, either. That's not the first impression you want to leave.<br />
<br />
You might think this is all boring grunt work, but getting the details of your code perfect is one of the habits that great programmers cultivate. Minimizing the size of your diff, and making sure it's as clean as possible, is one of the most important skills you can achieve if you want to contribute code to any software project. The patch is not just the output from a coding process: it's a product in its own right, one that you should carefully and directly monitor for good quality. The patch is a proposal add to the permanent history of the project, and errors, omissions, and unnecessary parts of the patch complicate the project history and affect processes like cherry-picking, reverting, and bisecting.<br />
<br />
[[Category:Development]]<br />
[[Category:Getting a patch accepted]]</div>Saperhttps://wiki.postgresql.org/index.php?title=Reviewing_a_Patch&diff=38472Reviewing a Patch2023-12-14T02:46:17Z<p>Saper: +category</p>
<hr />
<div>{{Languages}}<br />
<br />
PostgreSQL development is organized into [[CommitFest]] periods that need plenty of reviewers to help with all the submissions. Many people feel that they're not qualified to do a full review of patches submitted to PostgreSQL. But review includes many different tasks, and even if you can't do all of them you can help the project by taking on the earlier phases. If you can apply a patch and you can use the new feature, you're already qualified to start reviewing it. <br />
<br />
The eventual committer will do their own review before the patch goes into the codebase. The task of a reviewer is to take off load from committers by catching simple problems. The reviewer's job is not necessarily to guarantee some level of quality, but just to report any problems they are able to find. That task is done if you think the patch is ready for in-depth review from a committer. See [http://archives.postgresql.org/pgsql-hackers/2009-07/msg01103.php this patch review] as one example of the output a thorough review might produce. Reviews for other patches might, of course, contain different sections or for that matter, look completely different.<br />
<br />
See also [[Media:11_eggyknap-patch-review.pdf|Josh Tolley's presentation]] (2009) for a tongue-in-cheek but not inaccurate description of who is welcome, and [http://www.pgcon.org/2011/schedule/events/368.en.html Review of Patch Reviewing] (2011) or [https://github.com/dwsteele/conference/blob/release/PostgresPatchReview-PGConfEU-2016/slides/slides.pdf Reviewing PostgreSQL Patches for Fun and Profit] (2016) for more serious looks at the material that's covered here.<br />
<br />
Tactical points:<br />
<br />
* If a commitfest is currently in progress, it is available [https://commitfest.postgresql.org/inprogress here].<br />
* Every commitfest ought to have a commitfest manager that you can ask for help picking a patch or on procedural matters.<br />
** The current commitfest manager is: [mailto:daniel@yesql.se Daniel Gustafsson]<br />
* You don't have to ask for permission to sign yourself up.<br />
* Please sign up as soon as you know that you plan to do the review. (Some people hold back on signing up until they have actually done the review. Please don't do that; it doesn't help us plan our reviewing resources.)<br />
* On the other hand, we ask that initial reviews are sent in within about five days, to avoid "blocking" reviewing spots. But it's OK to send in a partial review or ask for more time. Just keep communicating.<br />
* Reviews and other development communications should generally be done via the [http://www.postgresql.org/list/pgsql-hackers/ pgsql-hackers] mailing list.<br />
* Send reviews as replies to the email containing the patch. Try to keep the email threading intact. Don't start a new thread for the review, or chances are the original author won't see it.<br />
<br />
Phases a patch review generally goes through include:<br />
<br />
== Submission review (skills needed: patch, English comprehension) ==<br />
<br />
* Is the patch in a patch format which has context? (eg: [http://en.wikipedia.org/wiki/Diff#Context_format context diff format])<br />
** Patches in 'normal' or 'plain' diff formats, which only show the lines changed and no context, are not acceptable.<br />
** Ideally, submitters should choose either context (diff -c) format or unified (diff -u) format based on which makes the submitter's patch easier to read.<br />
* Does it apply cleanly to the current git master?<br />
* Does it include reasonable tests, necessary doc patches, etc?<br />
<br />
== Usability review (skills needed: test-fu, ability to find and read spec) == <br />
<br />
Read what the patch is supposed to do, and consider:<br />
<br />
* Does the patch actually implement that? <br />
* Do we want that? <br />
* Do we already have it? <br />
* Does it follow SQL spec, or the community-agreed behavior? <br />
* Does it include pg_dump support (if applicable)?<br />
* Are there dangers? <br />
* Have all the bases been covered?<br />
<br />
== Feature test (skills needed: patch, configure, make, pipe errors to log) ==<br />
<br />
Apply the patch, compile it and test:<br />
<br />
* Does the feature work as advertised?<br />
* Are there corner cases the author has failed to consider?<br />
* Are there any assertion failures or crashes?<br />
**Reviews should be done with the ''configure'' options<br />
''--enable-cassert'' and ''--enable-debug'' turned on; see [[Working with git]] for a full example. These will help find issues with the code that might otherwise be missed. But note a copy of PostgreSQL built using these parameters will be substantially slower than one built without them. If you're working on something performance-related, such as testing whether a patch slows anything down, be sure to build without these flags before testing execution speed. Some, but not all, of the penalty of ''--enable-cassert'' can be turned off at server start time by putting ''debug_assertions = false'' in your postgresql.conf. See [http://www.postgresql.org/docs/current/static/runtime-config-developer.html Developer Options] for more details about that setting; it defaults to true in builds done with ''--enable-cassert''. Also note that while ''--enable-debug'' shouldn't have any performance penalty when building with ''gcc'', it definitely does with most other compilers.<br />
<br />
== Performance review (skills needed: ability to time performance) ==<br />
<br />
* Does the patch slow down simple tests? <br />
* If it claims to improve performance, does it?<br />
* Does it slow down other things?<br />
<br />
== Coding review (skills needed: guideline comparison, experience with portability issues, minor C-reading skills) ==<br />
<br />
Read the changes to the code in detail and consider:<br />
<br />
* Does it follow the project [https://www.postgresql.org/docs/current/source.html coding guidelines]? <br />
* Are there portability issues? <br />
* Will it work on Windows/BSD etc? <br />
* Are the comments sufficient and accurate?<br />
* Does it do what it says, correctly?<br />
* Does it produce compiler warnings?<br />
* Can you make it crash?<br />
<br />
== Architecture review (skills needed: experience with whole-PostgreSQL-project architecture) ==<br />
<br />
Consider the changes to the code in the context of the project as a whole:<br />
<br />
* Is everything done in a way that fits together coherently with other features/modules? <br />
* Are there interdependencies that can cause problems?<br />
<br />
== Review review ==<br />
<br />
* Did the reviewer cover all the things that kind of reviewer is supposed to do?<br />
<br />
[[Category:Development]]<br />
[[Category:Getting a patch accepted]]</div>Saperhttps://wiki.postgresql.org/index.php?title=Patch_Reviews&diff=38471Patch Reviews2023-12-14T02:45:28Z<p>Saper: +category</p>
<hr />
<div><pre><br />
Add Optional TZ Parameter to TSTZ Functions<br />
===========================================<br />
<br />
Idea<br />
----<br />
<br />
Currently, timestamp-with-timezone (tstz) functions use the 'timezone' GUC<br />
variable to guide their decision about which timezone to use for their<br />
calculations.<br />
<br />
It would be nice if these functions took a TZ name as an optional parameter, and<br />
used that for the calculations, instead of the value of 'timezone' GUC<br />
parameter.<br />
<br />
One use-case for this additional functionality would be multi-timezone<br />
applications. Currently, such applications cannot easily perform timestamp<br />
calculations for different timezones. With this change, it would make it easy<br />
for such applications to perform such calculations.<br />
<br />
Discussion(s)<br />
-------------<br />
<br />
[Proposal][] seems to have gone well. The responses include a committer's<br />
buy-in.<br />
<br />
[Proposal]:<br />
https://www.postgresql.org/message-id/flat/01a84551-48dd-1359-bf7e-f6b0203a6bd0%40sztoch.pl<br />
<br />
Committer Buy-in<br />
----------------<br />
<br />
This [initial response by Tom Lane][], in the [proposal][] thread sounds like he<br />
has bought into the idea.<br />
<br />
[initial response by Tom Lane]:<br />
https://www.postgresql.org/message-id/38924.1655214183%40sss.pgh.pa.us<br />
<br />
Design<br />
------<br />
<br />
Implementation Details<br />
----------------------<br />
<br />
Tests<br />
-----<br />
<br />
<br />
</pre><br />
<br />
<!--<br />
This comment serves the purpose of the in-progress standardization of<br />
[my][Gurjeet] patch-submission process to the Postgres community.<br />
<br />
Patch Submission Guidelines<br />
===========================<br />
<br />
A patch must meet all the criteria of the Postgres project, _and_ at least one<br />
committer must agree to commit the patch.<br />
<br />
These guidelines are supposed to be a condensed version of the more detailed<br />
Wiki page [Submitting a Patch][]. Additionally, anything that can be automated,<br />
should be automated by these guidelines.<br />
<br />
[Submitting a Patch]: https://wiki.postgresql.org/wiki/Submitting_a_Patch<br />
<br />
1. Use CommonMark-compliant Markdown syntax to write the Submission<br />
<br />
Markdown is chosen becuase it prioritizes readability over its other goals.<br />
It's easy to read, easy to write, and easy to supplant with HTML, when<br />
needed.<br />
<br />
CommonMark is used because that initiative is doing the herculean job of<br />
unifying the implementations of Markdown renderers, and the initiative is<br />
doing that job well.<br />
<br />
1. Use `<pre> ... </pre>` tags on Postgres Wiki to preserve the Markdown<br />
<br />
Since Postgres uses MediaWiki, we need to use this wrapping technique to<br />
prevent MediaWiki from misinterpreting our formatting keep the Markdown<br />
formatting.<br />
<br />
Do *not* disable text-wrapping of the Markdown. The Mediawiki formatting<br />
docs show that the default CSS on contents in `<pre>` allows browsers to<br />
wrap text, and that the wrapping can be disabled by using<br />
`style=white-space:pre;`. We don't want to disable text-wrapping, so that<br />
the readers using narrow screens can read the Markdown comfortably,<br />
without having to scroll the screen sideways.<br />
<br />
2. Wrap text lines at 80-column width<br />
<br />
Keeping with the practice of making things easy to read, the Submission must<br />
wrap free-form text after at most 80 columns. This requirement does not<br />
apply to text that must not be broken to retain its semantic meaning, for<br />
example, URLs, certain code segments, etc.<br />
<br />
3. Use sections to answer important questions<br />
<br />
Since at least one committer must agree to commit the patch, a Patch<br />
Submission must answer any questions that the committer may ask. Hence, it<br />
is helpful to have sections like 'Goal', 'Design', 'User Interface',<br />
'Implementation Details', 'Unknowns/Risks', etc.<br />
<br />
[Gurjeet]: http://Gurje.et<br />
<br />
<br />
--><br />
<br />
[[Category:Getting a patch accepted]]</div>Saperhttps://wiki.postgresql.org/index.php?title=Category:Getting_a_patch_accepted&diff=38470Category:Getting a patch accepted2023-12-14T02:44:17Z<p>Saper: rationale for this</p>
<hr />
<div>This is a category to collect pages that might help new contributors to get their patches accepted by the committers.<br />
<br />
Inspired by: https://www.postgresql.eu/events/pgconfeu2023/schedule/session/4904-making-your-patch-more-committable/</div>Saperhttps://wiki.postgresql.org/index.php?title=How_to_sponsor_a_feature&diff=38469How to sponsor a feature2023-12-14T02:42:53Z<p>Saper: +category</p>
<hr />
<div>== How to sponsor a feature ==<br />
<br />
=== Propose the Feature ===<br />
<br />
* Write a detailed proposal. Here is a [[ProposalTemplate|proposal template]]<br />
* Send it to the pgsql-hackers list.<br />
* Ask for feedback, and pay attention both to what comes back and to silence.<br />
* Lather, rinse, repeat.<br />
* Avoid sending such proposals during a [[CommitFest Help|CommitFest]].<br />
<br />
=== Create A Specification ===<br />
<br />
You can:<br />
<br />
* Create this on your own.<br />
* Solicit community help.<br />
* Hire people to create the specification.<br />
<br />
=== Gather Consensus on the Specification ===<br />
<br />
* Send the specification back to pgsql-hackers and make sure you get affirmative agreement before proceeding further.<br />
<br />
=== Submitting Patches ===<br />
<br />
* Review [[Submitting a Patch]] prior to submitting your patch.<br />
* During development, make sure you send patches early and often. Giant patch dumps are a great way to get your patch rejected and/or ignored.<br />
* Send patches to the pgsql-hackers list along with a reminder of what has gone before. Pointers into the archives are good if some time has elapsed.<br />
<br />
[[Category:Community]]<br />
[[Category:Getting a patch accepted]]</div>Saperhttps://wiki.postgresql.org/index.php?title=Submitting_a_Patch&diff=38468Submitting a Patch2023-12-14T02:42:19Z<p>Saper: +category</p>
<hr />
<div>== Initial patch design ==<br />
<br />
If you have a trivial patch that serves an obvious need, you may be able to write the patch and submit it directly to the [http://archives.postgresql.org/pgsql-hackers/ pgsql-hackers] mailing list without having its design reviewed first. But in general, a non-trivial change should be discussed (potentially before the code is even written) on the [http://archives.postgresql.org/pgsql-hackers/ pgsql-hackers] list before being submitted as a patch.<br />
<br />
For general coding style guidelines, see the [[Developer FAQ]] and the [https://www.postgresql.org/docs/current/source.html PostgreSQL Coding Conventions].<br />
<br />
=== Design your interface first ===<br />
<br />
Ask yourself these questions: <br />
<br />
* Will the user interact with this new feature? if so, how? <br />
* What is the syntax? Have ideas, and the ability to defend technical decisions you believe strongly in.<br />
* What are the exact semantics/behaviors?<br />
* Are there any backward compatibility issues? <br />
* Get community buy-in at this level of detail before you start coding. But not necessarily consensus.<br />
* Write an opening paragraph to your email to the -hackers list that answers these questions:<br />
**This is the kind of problem I'm trying to solve<br />
**This is what it is doing right now<br />
**This is what it will do.<br />
<br />
Mostly, get someone from the community involved in your ideas as early as possible so that you can even get half-baked ideas vetted early, rather than creating something in a vacuum. Similarly, it's easier to make progress and keep patches focused if you concentrate on the smallest portion of the idea you can execute perfectly. Resist the temptation to build a giant patch all at once, as those are much less likely to be reviewed usefully and therefore committed. You should take a look at how your patch will eventually be [[Reviewing a Patch|reviewed]], so you can make sure that review is likely to succeed.<br />
<br />
=== Save us the trouble of reformatting your code ===<br />
<br />
Please read [https://www.postgresql.org/docs/devel/source-format.html PostgreSQL Coding Conventions]. Also, follow the style of the adjacent code! Ultimately, we rely on ''pgindent'' to maintain a consistent style across our source code. [https://www.postgresql.org/message-id/4251.1250051236@sss.pgh.pa.us Suggestions from Tom] clarify some of the trickier situations you might run into. [[Creating Clean Patches]] provides a walkthrough of how to do self-review and improvement of your patches to fix code formatting issues.<br />
<br />
Naming things (code variables, functions, etc) is important, and when in doubt, ask someone else to help you with names. We tend to use [http://en.wikipedia.org/wiki/CamelCase CamelCase] or underscores: <code>thisStyleIsOkay</code> or <code>this_is_okay_too</code>.<br />
<br />
Generally, try to blend in with the surrounding code. Do not use #ifdef's to enable your changes. Comments are for clarification and to explain the ''why'' question, not for delineating your code from the surroundings nor to re-state what the code is doing. After your patch, the code should look as if it has always been written in that way, instead of the new feature having been bolted on after the fact.<br />
<br />
Please remove any spurious whitespace. Using <code>git diff --check</code> (or <code>git show --check</code> for the latest commit) makes bogus whitespace stand out.<br />
<br />
== Patch submission==<br />
<br />
Once you believe your patch is complete, please read through the complete patch that you will be sending to the mailing list. Ensure that the patch didn't mistakenly include unintended changes (such as whitespace changes, test code, #ifdef 0'd out blocks, etc). The patch should then be submitted via e-mail to the [http://www.postgresql.org/list/pgsql-hackers/ pgsql-hackers] mailing list. At that time, or after you wait for initial feedback, you should also add it to the page for the next [https://commitfest.postgresql.org/ CommitFest].<br />
<br />
Normally changes should be submitted as a single patch that includes every file touched. If the patch is large and can be logically separated into distinct and separately commit-able sections for easier review, with a clear order they get applied in described when applicable, that can be more straightforward for reviewers to work with for more complicated patches. Patches should come out of ``git diff``, ``git show`` or ``git format-patch``, normally in [http://en.wikipedia.org/wiki/Diff#Unified_format Unified Diff format], though context diff is also acceptable; 'normal' or 'plain' diff formats (which do not include context lines) are not acceptable. See [[Working_with_Git#Context_diffs_with_Git|Working with git]] and [[Creating Clean Patches]] for ways to do this well. [http://petereisentraut.blogspot.com/2009/09/how-to-submit-patch-by-email.html How to submit a patch by email] for more details about mailing in patches. If you're a new submitter, the suggestion there about using your judgment on patch formatting is not a recommended practice however--you should be using the standard unified diff format.<br />
<br />
Please note that PostgreSQL uses a BSD/MIT-style license for its code. By posting a patch to the public PostgreSQL mailing lists, you are giving the PostgreSQL Global Development Group the non-revocable right to distribute your patch under the [http://www.postgresql.org/about/licence/ PostgreSQL license].<br />
<br />
Please include all of the following information with each patch submitted; kindly do not format your email like you're filling out a form.<br />
<br />
* Project name.<br />
* Uniquely identifiable file name, so we can tell difference between your v1 and v24.<br />
* What the patch does in a short paragraph.<br />
* Whether the patch is for discussion or for application (see WIP notes below)<br />
* Which branch the patch is against (ordinarily this will be ''master''). For more on branches in PostgreSQL, see [[Working_with_Git#Using_Back_Branches|Using Back Branches]].<br />
* Whether it compiles and tests successfully, so we know nothing obvious is broken.<br />
* Whether it contains any platform-specific items and if so, has it been tested on other platforms.<br />
* Confirm that the patch includes [[Regression test authoring|regression tests]] to check the new feature actually works as described.<br />
* Include documentation on how to use the new feature, including examples. See the [http://www.postgresql.org/docs/current/static/docguide.html documentation documentation] for more information.<br />
* Describe the effect your patch has on performance, if any. <br />
* Try to include a few lines about why you chose to do things particular ways, rather than let your reviewer guess what was happening. This can be done as code comments, but it might also be an additional reviewers' guide, or additions to a README file in one of the code directories.<br />
* If your patch addresses a [[Todo]] item, please give the name of the Todo item in your email. This is so that the reviewers will know that the item needs to be marked as done if your patch is applied.<br />
<br />
It is helpful for early patches, ones not intended to be of commit quality, to be labeled clearly as such so that the appropriate style of review is done. The abbreviation WIP ("Work in Progress") is the standard shorthand to attach to patches intended for review not as a commit candidate, but for design feedback. Labeling your patch as a WIP on your e-mail subject line and on the matching description in the CF application will advise reviewers to focus more on the general approach, rather than on things like coding style that can normally be ignored in the early portion of a patch's lifecycle.<br />
<br />
=== Reasons your patch might be returned ===<br />
<br />
Submitting the patch is just the first step towards getting it committed. Very few patches are committed exactly as originally submitted, even those submitted by experienced professional developers. For any non-trivial patch you should plan for at least 3 versions before final acceptance.<br />
<br />
There are a few common reasons patches are returned without getting the review consideration the submitter was hoping for:<br />
<br />
* '''The fastest way to get your patch rejected is to make unrelated changes'''. Reformatting lines that haven't changed, changing unrelated comments you felt were poorly worded, touching code not necessary to your change, etc. Each patch should have the minimum set of changes required to work robustly. If you do not follow the code formatting suggestions above, expect your patch to be returned to you with the feedback of "follow the code conventions", quite likely without any other review.<br />
<br />
* '''Consider how the patch would be reviewed'''. The things a reviewer will evaluate are listed in [[Reviewing a Patch]]. You should look at this list and consider if you will pass it before submission. We recommend that new code contributors first spend time doing review, so that you will already be familiar with this list and process. The review guidelines are used because they work; doing your own self-review before submitting your patch is recommended.<br />
<br />
* '''Performance gain is claimed without test case'''. Performance patches are fun to write but hard to validate. If the patch is intended to improve performance, it's a good idea to include some reproducible tests to demonstrate the improvement. If a reviewer cannot duplicate your claimed performance improvement in a short period of time, it's very likely your patch will be returned. Do not expect that a reviewer is going to find your performance feature so interesting that they will build an entire test suite to prove it works. You should have done that as part of patch validation, and included the necessary framework for testing with the submission.<br />
<br />
* '''Did not include documentation or regression tests'''. Any patch without these two items is automatically considered a WIP one--you might get review of your patch, but do not expect it to be committed. See [[Regression test authoring]] for more information about how to write these tests. Documenting the design at a high level in your submission e-mail is also recommended.<br />
<br />
* '''Failed to address earlier criticisms of this design'''. Many patches try to do things that have been tried or considered before, where the earlier discussion found a problem with the obvious approach. Not saying how your new submission addresses those issues suggests you have the same problems. The [[Todo|todo list]] is a good source for finding past discussion of ideas for patches.<br />
<br />
The objective of all of these suggested items is to ensure that the reviewer's time is not wasted. You spent time writing the code, but that does '''not''' mean you can demand time, energy and interest from a reviewer. Follow these guidelines and become familiar with the review process. Make it easy on yourself and others so that your patch is accepted quickly, easily and with good humor on all sides.<br />
<br />
The presentation [http://www.pgcon.org/2011/schedule/events/303.en.html How To Get Your PostgreSQL Patch Accepted] provides some additional suggestions for how to submit a patch that will be considered more seriously.<br />
<br />
=== Submission timing ===<br />
<br />
To improve the odds of the right discussion of your patch or idea happening, pay attention to what the community work cycle is. If for example you send in a brand new idea in the beta phase, don't be surprised if no one is paying attention because they are focused on release work. Come back when the beta is done, please!<br />
<br />
PostgreSQL development is organized with periodic [[CommitFest|CommitFests]], which are periods where new development halts in order to focus on patch review and committing. It's best if you can avoid sending in a new patch during the occasional weeks when there is an active CommitFest; you can check the schedule via the [https://commitfest.postgresql.org/ CommitFest application]. If your schedule doesn't allow waiting until an active CommitFest is over, you should explicitly label your submission as intended for the next CommitFest, not the current one, so that it's clear it's not intended to be part of the active review process. Additionally, keep in mind that reviews of your patch may not occur until the next CommitFest begins, since everyone else is likely busy working on their own patches for submission.<br />
<br />
== Patch review and commit ==<br />
<br />
There's a few different workflows a patch can follow after it's been submitted that lead to it being committed:<br />
<br />
Workflow A:<br />
# You post patch to pgsql-hackers<br />
# A committer picks it up immediately and commits it.<br />
<br />
Workflow B:<br />
# You post a patch to pgsql-hackers<br />
# You add the patch to the [http://commitfest.postgresql.org/action/commitfest_view/open open commitfest] queue<br />
# A committer picks up the patch from the queue, and commits it<br />
<br />
At any of these stages, your patch might instead be rejected for technical, style, or other reasons. These rejections will normally come with feedback on whether an improved version of that patch would be more acceptable. In those cases, you should consider updating your patch based on that feedback and re-submit.<br />
<br />
== Mutual Review Offset Obligations ==<br />
<br />
PostgreSQL is a community project which relies on peer review. Each patch submitter to a CommitFest is expected to review at least one other patch. Ideally the patch selected for mutual review will be of similar size and/or scope to the one submitted. Someone who submits a feature that's thousands of lines long can't just review a tiny submission by someone else and expect the community to be satisfied. The idea is similar to the "offset" concept used for balancing gas emissions.<br />
<br />
This policy permits the project to review, accept, reject, or return feedback on each patch in a timely fashion. Few other open source projects provide such a guarantee for processing submissions. Failure to participate in the review process means the PostgreSQL community can't promise you review and feedback of your own patches. Actively reviewing another patch during the CommitFest also helps keeps contributors engaged, so that they can process feedback on their submission. Very few submissions are committed without first going through feedback and revisions during a CommitFest.<br />
<br />
For paid contributors to PostgreSQL, this policy means that you should make it clear to your sponsor that doing patch review is an obligation. When time is budgeted and the schedule laid out for developing a new feature for submission to the PostgreSQL project, that should include review resources for another feature as a necessary part of its overhead.<br />
<br />
== Followup on submissions ==<br />
<br />
=== How do you get someone to respond to you? ===<br />
<br />
You've sent an email to -hackers and no one has responded. What do you do?<br />
<br />
* Make sure you've added your patch to the [http://commitfest.postgresql.org/action/commitfest_view/open open commitfest] queue.<br />
* Start out by reviewing a patch or responding to email on the lists. Even if it is unrelated to what you're doing.<br />
* Start with submitting a patch that is small and uncontroversial to help them understand you, and to get you familiar with the overall process.<br />
* People are more willing to listen and work with someone who is already contributing.<br />
* Also, in our community -- if no one objects, then there is implicit approval. Within reason!<br />
<br />
Participating in community is a process, not a single event.<br />
<br />
=== Submitting patch updates ===<br />
<br />
When submitting a new version of a previously submitted patch, you should do a few additional things:<br />
<br />
* Uniquely identify the new version. You can use <code>git format-patch -vN</code>, incrementing N each time; or you can add an incrementing numerical suffix manually. Using the ".patch" extension allows some reviewers to more easily read it in their email client/code editor.<br />
<br />
* Make sure it's easy to find any earlier discussion of the patch, by providing <code>Message-Id</code>-based links to the [https://www.postgresql.org/list/pgsql-hackers/ mailing list posts]. Don't expect that everyone will still be able to find previous submissions on their own. You can usually get the message ID of your email by looking for the header '''Message-Id''' in your email client. Try ''View message source'' or ''View original'' if it's not obviously visible.<br />
<br />
[[Category:Community]]<br />
[[Category:Getting a patch accepted]]</div>Saperhttps://wiki.postgresql.org/index.php?title=CommitFest_Checklist&diff=38467CommitFest Checklist2023-12-14T02:41:40Z<p>Saper: +category</p>
<hr />
<div>= CommitFest Checklist and Timeline =<br />
<br />
This page contains a list of things to do and when to do them for the CommitFest Manager (CFM). (It's currently in flux and is being rewritten to reflect modern practice.)<br />
<br />
== Volunteer ==<br />
<br />
Several weeks prior to the beginning of the commitfest (and after a quick check to see whether the job is already filled), send an email to pgsql-hackers to volunteer for the CFM position. It's essentially first-come, first-served.<br />
<br />
== Three to Four Weeks before CF ==<br />
<br />
With luck, you will know at least this far out that you are going to be CommitFest Manager. If so, do the following things:<br />
<br />
* attempt to recruit an Assistant CFM from -hackers and other places. This is optional, but recommended if you're new to the position, or if the CF is large.<br />
* begin saving all pgsql-hackers email in your local mail store for searching, if you don't already do this regularly.<br />
* check that you have administrative permissions on the CF. (You should see an <code>administration</code> link in the top bar.) If not, request permissions fix from pgsql-www.<br />
<br />
== 5 to 7 days before CF ==<br />
<br />
* Post a reminder to pgsql-hackers (and your blog?) that the CF is beginning on the date, and that they should get their patches registered/submitted.<br />
* Do a patch sweep (see below).<br />
* Go over the pending patches and assign them categories or change their categories if they're wrong.<br />
* Check each and every patch in the CF, and find/update:<br />
** patches which are marked Waiting on Author, and change their status to Needs Review if the author has since responded to the feedback<br />
** patches which have been already committed/rejected/returned before the CF, and update their status accordingly<br />
** patches which no longer apply according to the [http://cfbot.cputube.org/ cfbot CI], and remind the author to send in a rebased version before the CF starts<br />
<br />
Patch Sweep: search the pgsql-hackers archives for messages with patch attachments that were sent after the start of the prior commitfest. (This is usually a two-month stretch of time, but sometimes it's as long as four.) Make sure that all patches submitted to -hackers are in the CommitFest (or in the prior CommitFest). If they are not, email the patch author asking if they want to submit their patch to the CF (see template below). New contributors should probably be added to the CF even if they are slow to respond, but veteran contributors should not be.<br />
<br />
== 2 days before CF ==<br />
<br />
* Send reminder to -hackers that the CF is beginning in 2 days.<br />
<br />
== First day of CF ==<br />
<br />
* Change CommitFest status from "Open" to "In Progress"<br />
* Change status of next CommitFest if needed.<br />
* Announce that the CF has begun on -hackers and that any new patches will need to be put on the next commitfest.<br />
* Send a reminder to all patch submitters that they have a patch in the commitfest, and that they will be expected to review patches of equivalent complexity by other people.<br />
<br />
== Each 5 to 7 days of the CF ==<br />
<br />
* Check patches marked "waiting on author".<br />
** If the author has responded to feedback and is once again waiting for review, switch the status to "needs review".<br />
* Check patches that are marked "waiting for review" and have a review assigned.<br />
** If a review has been made and found stuff to fix, and the author has not yet responded, mark the patch as "waiting on author". For really huge patches, you may wish to wait a little longer than 5-7 days.<br />
** If the review says it's "ready for committer", change it to that status.<br />
** If a prior conversation appears to have died out, consider asking for a status update on the list.<br />
* Check patches marked "ready for committer".<br />
** Check cfbot for these patches. Consider nudging the authors of patchsets that need to be rebased, if there's a danger that a committer won't be able to apply the set by the time they get to it. (If the author is also a committer, this reminder is probably not necessary.)<br />
<br />
== 5 to 7 days before end of CF ==<br />
<br />
* For any open patches:<br />
** If there hasn't been recent discussion, send an email to the reviewer reminding them CF is almost over and asking if they're doing ok or could use some help.<br />
** If there is recent discussion, post to the discussion thread asking if it'll likely be ready by the end of CF.<br />
<br />
== 3 days before end of CF ==<br />
<br />
* Post to -hackers with a reminder of what will happen to open patches on the last day of CF.<br />
<br />
== Last day of CF ==<br />
<br />
* For patches marked "Waiting for Author" and having at least one review, set to "Returned with Feedback" and send the appropriate email.<br />
* For patches marked "Needs review"<br />
** If it received at least one good review, move it to the next CF (removing the current reviewer reservation)<br />
** Otherwise, leave them pending<br />
<br />
== Sudden Death Overtime ==<br />
<br />
Once the closure date for the CF has passed, how patches get dealt with changes:<br />
<br />
* As soon as a patch gets a solid review, if it needs any changes at all it's marked "returned with feedback".<br />
* Patches which were marked "ready for committer" and turn out to need further work also get "returned" instead of going to "waiting on author"<br />
* Patches which require further spec discussion, performance testing, etc., get "returned" immediately.<br />
* Patches from submitters who have not, themselves, done any review, and are still "needs review" get pushed to the next Commitfest.<br />
<br />
The goal is to close out the CF as soon as possible, without skipping review for any patch which still deserves a review.<br />
<br />
== After CF ==<br />
<br />
* Close CF in app (but make sure all patches are moved first; otherwise build machinery like cfbot will drop them)<br />
* Make the next CF current<br />
* Send reminder email to authors with patches that were Returned with Feedback reminding them to add their patch to the next CF if they plan to continue working on it.<br />
* Send "thank you" email to reviewers, reminding them they are more than welcome review in the next CF even if they haven't submitted a patch.<br />
* Assemble two lists of reviewers for the eventual release notes:<br />
** Reviewers who did substantial work on the patch (usually code work) to make it go in and the patches they worked on, and<br />
** Reviewers who did a regular review, as one big group<br />
* Have a beer or three - you've earned it. :-)<br />
<br />
= Email Templates =<br />
<br />
=== Patch Sweep ===<br />
<br />
For new contributors:<br />
<br />
[contributor name]:<br />
<br />
You sent in your patch, [name of patch file] to pgsql-hackers on [date posted]. <br />
You may not be aware, but in order for your patch to be included in PostgreSQL, <br />
it needs to be part of the next CommitFest. Accordingly, I have added your <br />
patch to the list here [link to CF entry]. If you want to withdraw your patch, <br />
or if you simply won't be around to answer questions during the 30 days of <br />
this CommitFest, please let me know and I'll remove it. Thanks!<br />
<br />
<br />
For experienced contributors:<br />
<br />
[contributor name]:<br />
<br />
You sent in your patch, [name of patch file] to pgsql-hackers on [date posted], <br />
but you did not post it to the next CommitFest [link to CF page]. If this was <br />
intentional, then you need take no action. However, if you want your patch to <br />
be reviewed as part of the upcoming CommitFest, then you need to add it yourself<br />
before [start date]. Thanks for your contributions.<br />
<br />
=== Patch Reminder ===<br />
<br />
<br />
[contributor name]:<br />
<br />
As a reminder, you have the following patches in the current CommitFest:<br />
<br />
[list of links to their patches]<br />
<br />
If you are not prepared to follow up on review to these patches during this <br />
CommitFest, please let me know and I will remove them now. I also remind<br />
you that each patch submitter is expected to review at least one patch from<br />
another submitter during the CommitFest, so please pick someone else's<br />
patch to review as soon as you can. <br />
<br />
Thanks for your contributions to PostgreSQL!<br />
<br />
=== Reviewer Clear ===<br />
<br />
<br />
[reviewer name]:<br />
<br />
Since you haven't had time to write a review of [patch] in the last 5 days, <br />
we've taken your name off the reviewer list for this patch. Of course, you <br />
are still very welcome to review it if you can find time. We're just removing <br />
your name so that other reviewers know the patch still needs attention. <br />
We understand that day jobs and other things get in the way of doing patch review <br />
when you want to, so please come back and review a patch or two later <br />
when you have more time. <br />
<br />
Thanks!<br />
<br />
=== Returned with Feedback ===<br />
<br />
[author name]:<br />
<br />
[patch], which you submitted to this CommitFest, has been awaiting your attention <br />
for more than five days. As such, we have moved it to "Returned with Feedback" <br />
and removed it from the reviewing queue. Depending on timing, this may be reversable, <br />
so let us know if there are extenuating circumstances. In any case, you are welcome <br />
to address the feedback you have received, and resubmit the patch to the next CommitFest. <br />
<br />
Thank you for contributing to PostgreSQL.<br />
<br />
=== List of Non-Reviewing Submitters ===<br />
<br />
Subject: Submitters who have not reviewed<br />
<br />
Per the Developer Meeting of 2012, each patch submitter is required <br />
to do at least one patch review for each submitted patch. The following<br />
people have submitted one or more patches to this CommitFest, and have <br />
neither claimed any patch for review, nor responded to a private reminder<br />
to do so. This list is being posted in hopes that these submitters will <br />
see it and do a review soon. Without each submitter doing a review, <br />
the PostgreSQL project cannot promise to review all patches.<br />
<br />
[list of names]<br />
<br />
<br />
[[Category:CommitFest]]<br />
[[Category:Getting a patch accepted]]</div>Saperhttps://wiki.postgresql.org/index.php?title=Category:Mobile_appliction&diff=34698Category:Mobile appliction2020-03-17T15:32:01Z<p>Saper: category name typo</p>
<hr />
<div>''This was a typo, please see [[:Category:Mobile application]] instead.''</div>Saperhttps://wiki.postgresql.org/index.php?title=Category:Mobile_application&diff=34697Category:Mobile application2020-03-17T15:26:17Z<p>Saper: category name typo</p>
<hr />
<div>Applications for Android, iPhone, Windows Phone<br />
<br />
[[Category:Tool]]</div>Saperhttps://wiki.postgresql.org/index.php?title=AndroidAppContest&diff=34696AndroidAppContest2020-03-17T15:25:31Z<p>Saper: </p>
<hr />
<div>= PostgreSQL Android Application Contest =<br />
<br />
We are having a contest to create an open source PostgreSQL application for the Android OS. The prize is a brand-new Nexus One phone generously donated by Google.<br />
<br />
== Timeline ==<br />
<br />
* April 25, 2010: contest announced<br />
* July 6, 2010: entries due<br />
* <strike>PDXPUG Day, OSCON, July 19</strike> OSCON 2010: winners announced<br />
<br />
== Contestants ==<br />
<br />
* Stephen Denne - [http://www.datacute.co.nz/pgquilt/pgquilt.html pgQuilt] ([http://www.datacute.co.nz/pgquilt/pgQuilt.apk .apk])<br />
* Magnus Hagander - [http://github.com/mhagander/mailinglistmoderator Mailing List Moderator] ([http://github.com/downloads/mhagander/mailinglistmoderator/mailinglistmoderator.apk .apk])<br />
* Roland Tritsch - [http://code.google.com/p/chargefinder/ ChargeFinder] ([http://chargefinder.googlecode.com/files/CFMain-V1.1.0.apk .apk])<br />
* Mark Wong - [http://github.com/markwkm/PGTop/ PGTop] ([http://github.com/downloads/markwkm/PGTop/PGTop-1.1.apk .apk])<br />
* Marek Wróbel & Albert Łącki - [http://users.v-lo.krakow.pl/~mwrobel/bcinventory/ Barcode Inventory] ([http://users.v-lo.krakow.pl/~mwrobel/bcinventory-media/download/BarcodeInventory.apk .apk])<br />
<br />
== Prize ==<br />
<br />
Winner: Brand-new developer version (unlocked) Nexus One phone from Google.<br />
<br />
All participants who ''complete'' an app will get a Postgres 9 shirt.<br />
<br />
Also, all participants will be blogged and announced.<br />
<br />
== Rules ==<br />
<br />
# The contest participant must have done the majority of the coding work on the application.<br />
# The application can be anything which is functionally dependent on or complementary to PostgreSQL. This would include things like a simple PostgreSQL GUI or query creator, a PostGIS geo application, or similar. <br />
# Applications which do not actually use the database, but are complementary to PostgreSQL use, such as an EXPLAIN ANALYZE viewer or PostgreSQL documentation searcher, are also permitted.<br />
# PostgreSQL need not (and really should not) be run on the phone. For this reason, the application can depend on having a data connection.<br />
# The source code must be distributed under an OSI-approved license, and delivered with the completed application to the contest judges. Source code will be distributed by the judges via a Git repo at git.postgresql.org. BSD or PostgreSQL license is preferred but not required.<br />
# The application must be available in an Android-standard package, but submitting it for the Marketplace is not a requirement.<br />
# You must live somewhere we can affordably ship the Nexus One from the USA and reasonably expect it to arrive intact. If in doubt, ask us.<br />
# The application cannot already be available before April 15, 2010.<br />
# All three judges must be able to install and try the application on their phones. This means making the application compatible with Android version 1 (likely 1.6 is good enough).<br />
<br />
== Judges ==<br />
<br />
*Josh Berkus ''(testing on both G1 and Nexus One)''<br />
*Selena Deckelmann ''(testing on G1)''<br />
*Matthew Momjian ''(testing on G1)''<br />
<br />
== E-mail for Participation ==<br />
<br />
Please join the [http://groups.google.com/group/pgandroid pgAndroid Google Group]<br />
<br />
== Questions asked by participants ==<br />
<br />
Q: Where can I find the android SDK?<br />
A: http://developer.android.com/sdk/index.html<br />
<br />
Q: What is the minimum SDK API level required? (i.e. is it sufficient to support android 1.6 to run on the G1 phones?)<br />
A: 1.6 should be sufficient.<br />
<br />
[[Category:Mobile application]]</div>Saperhttps://wiki.postgresql.org/index.php?title=Design_Tools&diff=34695Design Tools2020-03-17T15:24:02Z<p>Saper: /* Commandline Tools */ +schemalint</p>
<hr />
<div>Tools to help with designing a schema, via creating [https://en.wikipedia.org/wiki/Entity–relationship_model Entity-Relationship diagrams] and similar. Most are GUI. <br />
<br />
List also includes tools to help with "visualization" or "documentation" of already existing databases. <br />
<br />
For tools to "run SQL and see its output" see [[PostgreSQL Clients]].<br />
<br />
<br />
== Windows ==<br />
<br />
=== Proprietary ===<br />
<br />
==== pgModeler ====<br />
<br />
https://github.com/pgmodeler<br />
<br />
https://pgmodeler.io/<br />
<br />
Windows / Linux / macOS<br />
<br />
This is open source (GPLv3) and can be freely built yourself, if you have Qt available, but downloadable binaries appear to be time-limited demos that can be unlocked with paypal.<br />
<br />
==== Aqua Data Studio ====<br />
<br />
http://www.aquafold.com/aquadatastudio/er_modeler.html<br />
<br />
The Aqua Data Studio Entity Relationship Modeler (ER Modeler) helps you design complex database models for all major RDBMS vendors and versions. Use the Forward Engineer feature to model entities and convert them into SQL Scripts, or Reverse Engineer existing databases to visualize a database model.<br />
<br />
==== DeZign ====<br />
<br />
http://www.datanamic.com/dezign/index.html<br />
<br />
Aan intuitive database design and modeling tool for developers and DBA's that can help you model, create and maintain databases. The software uses entity relationship diagrams (ERDs, data models) to graphically design databases and automatically generates the most popular SQL and desktop databases.<br />
<br />
==== PostgreSQL Maestro ====<br />
<br />
https://www.sqlmaestro.com/products/postgresql/maestro/<br />
<br />
==== Toad Data Modeller ====<br />
<br />
https://www.quest.com/products/toad-data-modeler/<br />
<br />
Toad Data Modeler enables you to rapidly deploy accurate changes to data structures across more than 20 different platforms. It allows you to construct logical and physical data models, compare and synchronize models, quickly generate complex SQL/DDL, create and modify scripts, as well as reverse and forward engineer both databases and data warehouse systems.<br />
<br />
May have free versions? Website is a bit of a wreck.<br />
<br />
==== EMS SQL Manager ====<br />
<br />
https://www.sqlmanager.net/en/products/postgresql/manager<br />
<br />
== Cross-Platform ==<br />
<br />
=== Open Source (Free) ===<br />
<br />
==== Kexi ====<br />
<br />
http://www.kexi-project.org/<br />
<br />
A visual database application creator, c.f. Access or FileMaker. Windows/linux.<br />
<br />
==== Open System Architect ====<br />
<br />
http://www.codebydesign.com<br />
<br />
Windows / macOS / Linux / Solaris<br />
<br />
OSA currently supports data modelling (physical and logical) with UML in the works.<br />
<br />
==== SQL Power*Architect ====<br />
<br />
http://software.sqlpower.ca/page/architect<br />
<br />
Java<br />
<br />
The SQL Power Architect data modeling tool was created by data warehouse designers and has many unique features geared specifically for the data warehouse architect. It allows users to reverse-engineer existing databases, perform data profiling on source databases, and auto-generate ETL metadata.<br />
<br />
==== Valentina Studio ====<br />
<br />
https://www.valentina-db.com/en/valentina-studio-overview<br />
<br />
Windows / macOS / Linux<br />
<br />
Free version supports reverse-engineering an existing schema, but only the proprietary version supports forward-engineering.<br />
<br />
==== Open ModelSphere ====<br />
<br />
http://www.modelsphere.com/org/<br />
<br />
Java<br />
<br />
Open ModelSphere is a powerful data, process and UML modeling tool - supporting user interfaces in English and French.<br />
<br />
==== Umbrello ====<br />
<br />
https://umbrello.kde.org<br />
<br />
Windows / macOS / Linux<br />
<br />
Umbrello UML Modeller is a Unified Modelling Language (UML) diagram program based on KDE Technology.<br />
<br />
UML allows you to create diagrams of software and other systems in a standard format to document or design the structure of your programs.<br />
<br />
==== ERDesignerNG ====<br />
<br />
http://mogwai.sourceforge.net/erdesignerng.html<br />
<br />
Java, GPL<br />
<br />
=== Proprietary ===<br />
<br />
==== DbSchema ====<br />
<br />
http://www.dbschema.com<br />
<br />
Windows / macOS / Linux / Java<br />
<br />
Features interactive diagrams, relational data browse, schema compare and synchronization, query builder, query editor, HTML5 documentation, random data generator, forms and reports.<br />
<br />
==== DbVisualizer ====<br />
<br />
http://www.dbvis.com<br />
<br />
Windows / macOS / Linux / Java<br />
<br />
A client that does a lot of things other than schema design. It has a free version that provides many of the features, but not apparently design and DDL export. DbVisualizer is a feature rich, intuitive multi-database tool for developers, database administrators, and increasingly for advanced analysts providing a single powerful interface across a wide variety of operating systems. With its easy-to-use and clean interface, DbVisualizer has proven to be one of the most cost effective database tools available, yet to mention that it runs on all major operating systems and supports all major RDBMS that are available. Users only need to learn and master one application. DbVisualizer integrates transparently with the operating system being used.<br />
<br />
==== DbWrench ====<br />
<br />
http://www.dbwrench.com<br />
<br />
Windows / macOS / Linux / Java<br />
<br />
Diagramming / Forward & Reverse Engineering<br />
<br />
==== StarUML ====<br />
<br />
http://staruml.io<br />
<br />
Windows / macOS / Ubuntu<br />
<br />
https://github.com/adrianandrei-ca/staruml-postgresql - extension to support PostgreSQL<br />
<br />
(This is version 2. The much older, open source version 1 is available at http://staruml.sourceforge.net/v1/download.php)<br />
<br />
==== Vertabelo ====<br />
<br />
http://www.vertabelo.com<br />
<br />
Vertabelo is an online database designer working under Chrome. It free to use for smaller projects and have a proprietary versions for larger database projects.<br />
<br />
Features:<br />
* Intuitive HTML5 web interface (Chrome)<br />
* OS independent<br />
* Sharing DB model with team members<br />
* Support for PostgreSQL, MySQL, Oracle, MS SQL Server, DB2, SQLite, HSQLDB,<br />
* Model versioning<br />
* Dynamic/Visual search<br />
* Live model validation<br />
* Reverse engineering<br />
<br />
==== Navicat ====<br />
<br />
https://www.navicat.com/en/products/navicat-for-postgresql<br />
<br />
Windows, macOS, iOS<br />
<br />
A general purpose client with good modeling features.<br />
<br />
== Commandline Tools ==<br />
<br />
Tools that take a description of a database schema in one format and convert it to SQL, and sometimes vice-versa.<br />
<br />
=== SQLFairy ===<br />
<br />
http://sqlfairy.sourceforge.net<br />
<br />
Perl, manipulate structured data definitions (mostly database schemas) in interesting ways, such as converting among different dialects of CREATE syntax (e.g., MySQL-to-Oracle), visualizations of schemas (pseudo-ER diagrams: GraphViz or GD), automatic code generation (using Class::DBI), converting non-RDBMS files to SQL schemas (xSV text files, Excel spreadsheets), serializing parsed schemas (via Storable, YAML and XML), creating documentation (HTML and POD), and more.<br />
<br />
=== DbVisualizer ===<br />
<br />
http://www.dbvis.com/<br />
<br />
Windows/OS X/Linux/UNIX<br />
<br />
DbVisualizer is a feature rich, intuitive multi-database tool for developers, database administrators, and increasingly for advanced analysts providing a single powerful interface across a wide variety of operating systems. With its easy-to-use and clean interface, DbVisualizer has proven to be one of the most cost effective database tools available, yet to mention that it runs on all major operating systems and supports all major RDBMS that are available. Users only need to learn and master one application. DbVisualizer integrates transparently with the operating system being used.<br />
<br />
=== schemalint ===<br />
<br />
https://github.com/kristiandupont/schemalint<br />
<br />
A tool to verify the database schema against [[Don't Do This]] recommendations.<br />
<br />
[[Category:Tool]]<br />
<br />
== Unknown ==<br />
<br />
==== Autodoc ====<br />
<br />
https://github.com/cbbrowne/autodoc<br />
<br />
perl, open source<br />
<br />
This is a utility which will run through PostgreSQL system tables and returns HTML, DOT, and several styles of XML which describe the database.<br />
<br />
As a result, documentation about a project can be generated quickly and be automatically updatable, yet have a quite professional look if you do some DSSSL/CSS work.<br />
<br />
==== SQLFairy ====<br />
<br />
http://sqlfairy.sourceforge.net<br />
<br />
Perl, manipulate structured data definitions (mostly database schemas) in interesting ways, such as converting among different dialects of CREATE syntax (e.g., MySQL-to-Oracle), visualizations of schemas (pseudo-ER diagrams: GraphViz or GD), automatic code generation (using Class::DBI), converting non-RDBMS files to SQL schemas (xSV text files, Excel spreadsheets), serializing parsed schemas (via Storable, YAML and XML), creating documentation (HTML and POD), and more.<br />
<br />
<br />
==== DB Doc ====<br />
<br />
http://www.yohz.com/dbdoc_details.htm<br />
<br />
Windows/Linux(Wine)<br />
<br />
DB Doc helps you document your database structure and objects. Documents can be generated as PDF reports, HTML pages, Microsoft Word (docx) file, or a single compiled HTML file. The layout is fully customizable, and you can quickly view inter-object dependencies using hyperlinks.<br />
<br />
==== MicroOLAP Database Designer ====<br />
<br />
http://www.microolap.com/products/database/postgresql-designer/<br />
<br />
Windows ODBC<br />
<br />
Database Designer for PostgreSQL is an easy CASE tool with an intuitive graphical interface allowing you to build a clear and effective database structure visually, see the complete picture (diagram) representing all the tables, references between them, views, stored procedures and other objects. Then you can easily generate a physical database on a server, modify it according to any changes you made to the diagram using fast ALTER statements.<br />
<br />
<br />
==== GenMyModel ====<br />
<br />
https://www.genmymodel.com<br />
<br />
GenMyModel is an online modeling tool supporting [http://www.genmymodel.com/database-diagram-online database modeling]. It is free to use for smaller projects and have a proprietary version for larger database projects.<br />
<br />
Features:<br />
* Intuitive HTML5 web interface (Chrome, Firefox, Safari, Internet Explorer)<br />
* OS independent<br />
* Instant sharing and collaboration<br />
* Customizable SQL generators<br />
* Model versioning<br />
* Live model validation<br />
<br />
==== dbForge Studio for PostgreSQL ====<br />
<br />
* [https://www.devart.com/dbforge/postgresql/studio/ dbForge Studio for PostgreSQL]<br />
<br />
==== ModelRight ====<br />
<br />
* [http://www.modelright.com/ ModelRight]<br />
<br />
==== WaveMaker ====<br />
<br />
??? Doesn't seem to mention Postgres.<br />
<br />
* [http://www.wavemaker.com/ WaveMaker Visual Ajax Studio]<br />
<br />
== Other Resources ==<br />
<br />
* [[Community Guide to PostgreSQL GUI Tools]] miscellaneous utilities<br />
* [[PostgreSQL Clients]] GUI SQL clients<br />
* Old possibly abandoned projects, see [[Community_Guide_to_PostgreSQL_Tools_Abandoned]]</div>Saperhttps://wiki.postgresql.org/index.php?title=Don%27t_Do_This&diff=34694Don't Do This2020-03-17T15:19:50Z<p>Saper: +schemalint</p>
<hr />
<div>__TOC__<br />
<br />
A short list of common mistakes.<br />
<br />
* Kristian Dupont provides [https://github.com/kristiandupont/schemalint schemalint] a tool to verify the database schema against those recommendations.<br />
<br />
= Database Encoding =<br />
<br />
== Don't use SQL_ASCII ==<br />
<br />
=== Why not? ===<br />
<code>SQL_ASCII</code> means "no conversions" for the purpose of all encoding conversion functions. That is to say, the original bytes are simply treated as being in the new encoding, subject to validity checks, without any regard for what they mean. Unless extreme care is taken, an <code>SQL_ASCII</code> database will usually end up storing a mixture of many different encodings with no way to recover the original characters reliably.<br />
<br />
=== When should you? ===<br />
<br />
If your input data is already in a hopeless mixture of unlabelled encodings, such as IRC channel logs or non-MIME-compliant emails, then SQL_ASCII might be useful as a last resort—but consider using <code>bytea</code> first instead, or whether you could autodetect UTF8 and assume non-UTF8 data is in some specific encoding such as WIN1252.<br />
<br />
= Tool usage =<br />
<br />
== Don't use psql -W or --password ==<br />
<br />
Don't use <tt>psql -W</tt> or <tt>psql --password</tt>.<br />
<br />
=== Why not? ===<br />
<br />
Using the --password or -W flags will tell [https://www.postgresql.org/docs/current/static/reference-client.html psql] to prompt you for a password, before trying to connect to the server - so you'll be prompted for a password even if the server doesn't require one.<br />
<br />
It's never required, as if the server does require a password psql will prompt you for one, and it can be very confusing when setting up permissions. If you're connecting with -W to a server configured to allow you access via <tt>peer</tt> authentication you may think that it's requiring a password when it really isn't. And if the user you're logging in as doesn't have a password set or you enter the wrong password at the prompt you'll still be logged in and think you have the right password - but you won't be able to log in from other clients (that connect via localhost) or when logged in as other users.<br />
<br />
=== When should you? ===<br />
<br />
Never, pretty much. It will save a round trip to the server but that's about it.<br />
<br />
<br /><!----------------------------------------------------------------><br />
== Don't use rules ==<br />
<br />
Don't use [https://www.postgresql.org/docs/current/static/sql-createrule.html rules]. If you think you want to, use a [https://www.postgresql.org/docs/current/static/plpgsql-trigger.html trigger] instead.<br />
<br />
=== Why not? ===<br />
<br />
Rules are incredibly powerful, but they don't do what they look like they do. They look like they're some conditional logic, but they actually rewrite a query to modify it or add additional queries to it.<br />
<br />
That means that [http://blog.rhodiumtoad.org.uk/2010/06/21/the-rule-challenge/ all non-trivial rules are incorrect].<br />
<br />
Depesz has [https://www.depesz.com/2010/06/15/to-rule-or-not-to-rule-that-is-the-question/ more to say] about them.<br />
<br />
=== When should you? ===<br />
<br />
Never. While the rewriter is an implementation detail of VIEWs, there is no reason to pry up this cover plate directly.<br />
<br />
<br /><!----------------------------------------------------------------><br />
== Don't use table inheritance ==<br />
<br />
Don't use [https://www.postgresql.org/docs/current/tutorial-inheritance.html table inheritance]. If you think you want to, use foreign keys instead.<br />
<br />
=== Why not? ===<br />
<br />
Table inheritance was a part of a fad wherein the database was closely coupled to object-oriented code. It turned out that coupling things that closely didn't actually produce the desired results.<br />
<br />
=== When should you? ===<br />
<br />
Never &hellip;almost. Now that table partitioning is done natively, that common use case for table inheritance has been replaced by a native feature that handles tuple routing, etc., without bespoke code.<br />
<br />
One of the very few exceptions would be [https://github.com/arkhipov/temporal_tables temporal_tables] extension if you are in a pinch and want to use that for row versioning in place of a lacking SQL 2011 support. Table inheritance will provide a small shortcut instead of using <code>UNION ALL</code> to get both historical as well as current rows.<br />
Even then you ought to be wary of [http://clarkdave.net/2015/02/historical-records-with-postgresql-and-temporal-tables-and-sql-2011/#using-inheritance caveats] while working with parent table.<br />
<!--====================================================================================================--><br />
<br />
= SQL constructs =<br />
<br />
== Don't use NOT IN ==<br />
<br />
Don't use <code>NOT IN</code>, or any combination of <code>NOT</code> and <code>IN</code> such as <code>NOT (x IN (select…))</code>.<br />
<br />
(If you think you wanted <code>NOT IN (select …)</code> then you should rewrite to use <code>NOT EXISTS</code> instead.)<br />
<br />
=== Why not? ===<br />
<br />
Two reasons:<br />
<br />
1. <code>NOT IN</code> behaves in unexpected ways if there is a null present:<br />
<br />
<code>select * from foo where col not in (1,null); -- always returns 0 rows</code><br />
<br />
<code>select * from foo where col not in (select x from bar);<br />
-- returns 0 rows if any value of bar.x is null</code><br />
<br />
This happens because <code>col IN (1,null)</code> returns <code>TRUE</code> if col=1, and <code>NULL</code> otherwise (i.e. it can never return <code>FALSE</code>). Since <code>NOT (TRUE)</code> is <code>FALSE</code>, but <code>NOT (NULL)</code> is still <code>NULL</code>, there is no way that <code>NOT (col IN (1,null))</code> (which is the same thing as <code>col NOT IN (1,null)</code>) can return <code>TRUE</code> under any circumstances.<br />
<br />
2. Because of point 1 above, <code>NOT IN (SELECT ...)</code> does not optimize very well. In particular, the planner can't transform it into an anti-join, and so it becomes either a hashed Subplan or a plain Subplan. The hashed subplan is fast, but the planner only allows that plan for small result sets; the plain subplan is '''horrifically''' slow (in fact O(N²)). This means that the performance can look good in small-scale tests but then slow down by 5 or more orders of magnitude once a size threshold is crossed; you '''do not''' want this to happen.<br />
<br />
=== When should you? ===<br />
<br />
<code>NOT IN (<i>list,of,values,...</i>)</code> is mostly safe ''unless'' you might have a null in the list (via a parameter or otherwise). So it's sometimes natural and even advisable to use it when excluding specific constant values from a query result.<br />
<br />
<br /><!----------------------------------------------------------------><br />
== Don't use upper case table or column names ==<br />
<br />
Don't use NamesLikeThis, use names_like_this.<br />
<br />
=== Why not? ===<br />
<br />
PostgreSQL folds all names - of tables, columns, functions and everything else - to lower case unless they're "double quoted".<br />
<br />
So <tt>create table Foo()</tt> will create a table called <tt>foo</tt>, while <tt>create table "Bar"()</tt> will create a table called <tt>Bar</tt>.<br />
<br />
These select commands will work: <tt>select * from Foo</tt>, <tt>select * from foo</tt>, <tt>select * from "Bar"</tt>.<br />
<br />
These will fail with "no such table": <tt>select * from "Foo"</tt>, <tt>select * from Bar</tt>, <tt>select * from bar</tt>.<br />
<br />
This means that if you use uppercase characters in your table or column names you have to either ''always'' double quote them or ''never'' double quote them. That's annoying enough by hand, but when you start using other tools to access the database, some of which always quote all names and some don't, it gets very confusing.<br />
<br />
Stick to using a-z, 0-9 and underscore for names and you never have to worry about quoting them.<br />
<br />
=== When should you? ===<br />
<br />
If it's important that "pretty" names are displaying in report output then you might want to use them. But you can also use column aliases to use lower case names in a table and still get pretty names in the output of a query: <tt>select character_name as "Character Name" from foo</tt>.<br />
<br />
<br /><!----------------------------------------------------------------><br />
== Don't use BETWEEN (especially with timestamps) ==<br />
<br />
=== Why not? ===<br />
<br />
<code>BETWEEN</code> uses a closed-interval comparison: the values of both ends of the specified range are included in the result.<br />
<br />
This is a particular problem with queries of the form<br />
<br />
<code>SELECT * FROM blah WHERE timestampcol BETWEEN '2018-06-01' AND '2018-06-08'</code><br />
<br />
This will include results where the timestamp is ''exactly'' 2018-06-08 00:00:00.000000, but not timestamps later in that same day. So the query might seem to work, but as soon as you get an entry exactly on midnight, you'll end up double-counting it.<br />
<br />
Instead, do:<br />
<br />
<code>SELECT * FROM blah WHERE timestampcol >= '2018-06-01' AND timestampcol < '2018-06-08'</code><br />
<br />
=== When should you? ===<br />
<br />
<code>BETWEEN</code> is safe for discrete quantities like integers or dates, as long as you remember that both ends of the range are included in the result. But it's a bad habit to get into.<br />
<br />
<!--====================================================================================================--><br />
<br />
= Date/Time storage =<br />
<br />
== Don't use timestamp (without time zone) ==<br />
<br />
Don't use the <tt>timestamp</tt> type to store timestamps, use <tt>timestamptz</tt> (also known as <tt>timestamp with time zone</tt>) instead.<br />
<br />
=== Why not? ===<br />
<br />
<tt>timestamptz</tt> records a single moment in time. Despite what the name says it doesn't store a timestamp, just a point in time described as the number of microseconds since January 1st, 2000 in UTC. You can insert values in any timezone and it'll store the point in time that value describes. By default it will display times in your current timezone, but you can use <tt>at time zone</tt> to display it in other time zones.<br />
<br />
Because it stores a point in time it will do the right thing with arithmetic involving timestamps entered in different timezones - including between timestamps from the same location on different sides of a daylight savings time change.<br />
<br />
<tt>timestamp</tt> (also known as <tt>timestamp without time zone</tt>) doesn't do any of that, it just stores a date and time you give it. You can think of it being a picture of a calendar and a clock rather than a point in time. Without additional information - the timezone - you don't know what time it records. Because of that, arithmetic between timestamps from different locations or between timestamps from summer and winter may give the wrong answer.<br />
<br />
So if what you want to store is a point in time, rather than a picture of a clock, use timestamptz.<br />
<br />
[https://it.toolbox.com/blogs/josh-berkus/zone-of-misunderstanding-092811 More about timestamptz].<br />
<br />
=== When should you? ===<br />
<br />
If you're dealing with timestamps in an abstract way, or just saving and retrieving them from an app, where you aren't going to be doing arithmetic with them then timestamp might be suitable.<br />
<br />
<br /><!----------------------------------------------------------------><br />
== Don't use timestamp (without time zone) to store UTC times ==<br />
<br />
Storing UTC values in a <code>timestamp without time zone</code> column is, unfortunately, a practice commonly inherited from other databases that lack usable timezone support.<br />
<br />
Use <code>timestamp with time zone</code> instead.<br />
<br />
=== Why not? ===<br />
<br />
Because there is no way for the database to know that UTC is the intended timezone for the column values.<br />
<br />
This complicates many otherwise useful time calculations. For example, "last midnight in the timezone given by u.timezone" becomes this:<br />
<br />
<code>date_trunc('day', now() AT TIME ZONE u.timezone) AT TIME ZONE u.timezone AT TIME ZONE 'UTC'</code><br />
<br />
And "the midnight prior to <code>x.datecol</code> in u.timezone" becomes this:<br />
<br />
<code>date_trunc('day', x.datecol AT TIME ZONE 'UTC' AT TIME ZONE u.timezone)<br />
AT TIME ZONE u.timezone AT TIME ZONE 'UTC'</code><br />
<br />
=== When should you? ===<br />
<br />
If compatibility with non-timezone-supporting databases trumps all other considerations.<br />
<br />
<br /><!----------------------------------------------------------------><br />
== Don't use timetz ==<br />
<br />
Don't use the <tt>timetz</tt> type. You probably want <tt>timestamptz</tt> instead.<br />
<br />
=== Why not? ===<br />
<br />
Even the manual tells you it's only implemented for SQL compliance.<br />
<br />
<blockquote>The type time with time zone is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness. In most cases, a combination of date, time, timestamp without time zone, and timestamp with time zone should provide a complete range of date/time functionality required by any application.</blockquote><br />
<br />
=== When should you? ===<br />
<br />
Never.<br />
<br />
<br /><!----------------------------------------------------------------><br />
== Don't use CURRENT_TIME ==<br />
<br />
Don't use the <code>CURRENT_TIME</code> function. Use whichever of these is appropriate:<br />
<br />
* <code>CURRENT_TIMESTAMP</code> or <code>now()</code> if you want a <code>timestamp with time zone</code>,<br />
* <code>LOCALTIMESTAMP</code> if you want a <code>timestamp without time zone</code>,<br />
* <code>CURRENT_DATE</code> if you want a <code>date</code>,<br />
* <code>LOCALTIME</code> if you want a <code>time</code><br />
<br />
=== Why not? ===<br />
<br />
It returns a value of type <code>timetz</code>, for which see the previous entry.<br />
<br />
=== When should you? ===<br />
<br />
Never.<br />
<br />
<br /><!----------------------------------------------------------------><br />
== Don't use timestamp(0) or timestamptz(0) ==<br />
<br />
Don't use a precision specification, especially not 0, for timestamp columns or casts to timestamp.<br />
<br />
Use <code>date_trunc('second', blah)</code> instead.<br />
<br />
=== Why not? ===<br />
<br />
Because it rounds off the fractional part rather than truncating it as everyone would expect. This can cause unexpected issues; consider that when you store <code>now()</code> into such a column, you might be storing a value half a second in the future.<br />
<br />
=== When should you? ===<br />
<br />
Never.<br />
<br />
<!--====================================================================================================--><br />
<br />
= Text storage =<br />
<br />
== Don't use char(n) ==<br />
<br />
Don't use the type <tt>char(n)</tt>. You probably want <tt>text</tt>.<br />
<br />
=== Why not? ===<br />
<br />
Any string you insert into a <code>char(n)</code> field will be padded with spaces to the declared width. That's probably not what you actually want.<br />
<br />
The manual says:<br />
<br />
<blockquote>Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way. However, trailing spaces are treated as semantically insignificant and disregarded when comparing two values of type character. In collations where whitespace is significant, this behavior can produce unexpected results; for example <code>SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2)</code> returns true, even though C locale would consider a space to be greater than a newline. Trailing spaces are removed when converting a character value to one of the other string types. Note that trailing spaces are semantically significant in character varying and text values, and when using pattern matching, that is LIKE and regular expressions.</blockquote><br />
<br />
That should scare you off it.<br />
<br />
The space-padding does waste space, but doesn't make operations on it any faster; in fact the reverse, thanks to the need to strip spaces in many contexts.<br />
<br />
It's important to note that from a storage point of view <code>char(n)</code> '''is not a fixed-width type'''. The actual number of bytes varies since characters may take more than one byte, and the stored values are therefore treated as variable-length anyway (even though the space padding is included in the storage).<br />
<br />
=== When should you? ===<br />
<br />
When you're porting very, very old software that uses fixed width fields. Or when you read the snippet from the manual above and think "yes, that makes perfect sense and is a good match for my requirements" rather than gibbering and running away.<br />
<br />
<br /><!----------------------------------------------------------------><br />
== Don't use char(n) even for fixed-length identifiers ==<br />
<br />
Sometimes people respond to "don't use <code>char(n)</code>" with "but my values must always be exactly N characters long" (e.g. country codes, hashes, or identifiers from some other system). '''It is still a bad idea to use <code>char(n)</code> even in these cases.'''<br />
<br />
Use <code>text</code>, or a domain over text, with <code>CHECK(length(VALUE)=3)</code> or <code><nowiki>CHECK(VALUE ~ '^[[:alpha:]]{3}$')</nowiki></code> or similar.<br />
<br />
=== Why not? ===<br />
<br />
Because <code>char(n)</code> doesn't reject values that are too short, it just silently pads them with spaces. So there's no actual benefit over using <code>text</code> with a constraint that checks for the exact length. As a bonus, such a check can also verify that the value is in the correct format.<br />
<br />
Remember, '''there is no performance benefit whatsoever to using <code>char(n)</code> over <code>varchar(n)</code>.''' In fact the reverse is true. One particular problem that comes up is that if you try and compare a <code>char(n)</code> field against a parameter where the driver has explicitly specified a type of <code>text</code> or <code>varchar</code>, you may be unexpectedly unable to use an index for the comparison. This can be hard to debug since it doesn't show up on manual queries.<br />
<br />
=== When should you? ===<br />
<br />
Never.<br />
<br />
<br /><!----------------------------------------------------------------><br />
<br />
== Don't use varchar(n) by default ==<br />
<br />
Don't use the type <tt>varchar(n)</tt> by default. Consider <tt>varchar</tt> (without the length limit) or <tt>text</tt> instead.<br />
<br />
=== Why not? ===<br />
<br />
<tt>varchar(n)</tt> is a variable width text field that will throw an error if you try and insert a string longer than n characters (not bytes) into it.<br />
<br />
<tt>varchar</tt> (without the <tt>(n)</tt>) or <tt>text</tt> are similar, but without the length limit. If you insert the same string into the three field types they will take up exactly the same amount of space, and you won't be able to measure any difference in performance.<br />
<br />
If what you really need is a text field with an length limit then varchar(n) is great, but if you pick an arbitrary length and choose varchar(20) for a surname field you're risking production errors in the future when Hubert Blaine Wolfeschlegelsteinhausenbergerdorff signs up for your service.<br />
<br />
Some databases don't have a type that can hold arbitrary long text, or if they do it's not as convenient or efficient or well-supported as varchar(n). Users from those databases will often use something like <tt>varchar(255)</tt> when what they really want is <tt>text</tt>.<br />
<br />
If you need to constrain the value in a field you probably need something more specific than a maximum length - maybe a minimum length too, or a limited set of characters - and a [https://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS check constraint] can do all of those things as well as a maximum string length.<br />
<br />
=== When should you? ===<br />
<br />
When you want to, really. If what you want is a text field that will throw an error if you insert too long a string into it, and you don't want to use an explicit check constraint then varchar(n) is a perfectly good type. Just don't use it automatically without thinking about it.<br />
<br />
Also, the varchar type is in the SQL standard, unlike the text type, so it might be the best choice for writing super-portable applications.<br />
<br />
<!--====================================================================================================--><br />
<br />
= Other data types =<br />
<br />
== Don't use money ==<br />
<br />
The <tt>money</tt> data type isn't actually very good for storing monetary values. Numeric, or (rarely) integer may be better.<br />
<br />
=== Why not? ===<br />
<br />
[https://www.postgresql.org/message-id/flat/20130328092819.237c0106@imp#20130328092819.237c0106@imp lots of reasons.]<br />
<br />
It's a fixed-point type, implemented as a machine int, so arithmetic with it is fast. But it doesn't handle fractions of a cent (or equivalents in other currencies), it's rounding behaviour is probably not what you want.<br />
<br />
It doesn't store a currency with the value, rather assuming that all money columns contain the currency specified by the database's [https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-LC-MONETARY lc_monetary] locale setting. If you change the lc_monetary setting for any reason, all money columns will contain the wrong value. That means that if you insert '$10.00' while lc_monetary is set to 'en_US.UTF-8' the value you retrieve may be '10,00 Lei' or '¥1,000' if lc_monetary is changed.<br />
<br />
Storing a value as a numeric, possibly with the currency being used in an adjacent column, might be better.<br />
<br />
=== When should you? ===<br />
<br />
If you're only working in a single currency, aren't dealing with fractional cents and are only doing addition and subtraction then money might be the right thing.<br />
<br />
<br /><!----------------------------------------------------------------><br />
<br />
== Don't use serial ==<br />
<br />
For new applications, identity columns should be used instead.<br />
<br />
=== Why not? ===<br />
<br />
The serial types have some [https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/ weird behaviors] that make schema, dependency, and permission management unnecessarily cumbersome.<br />
<br />
=== When should you? ===<br />
<br />
* If you need support to PostgreSQL older than version 10.<br />
* In certain combinations with table inheritance (but see there)<br />
* More generally, if you somehow use the same sequence from multiple tables, although in those cases an explicit declaration might be preferable over the serial types.<br />
<br />
<!--====================================================================================================--><br />
<br />
= Authentication =<br />
<br />
== Don't use <tt>trust</tt> authentication over TCP/IP (<tt>host</tt>, <tt>hostssl</tt>) ==<br />
<br />
Don't use <tt>trust</tt> authentication over any TCP/IP method (e.g. host, hostssl) in any production environment.<br />
<br />
Especially '''do not''' set a line like this in your <tt>pg_hba.conf</tt> file:<br />
<br />
<code>host all all 0.0.0.0/0 trust</code><br />
<br />
which allows anyone on the Internet to authenticate as any PostgreSQL user in your cluster, including the PostgreSQL superuser.<br />
<br />
There is a [https://www.postgresql.org/docs/current/client-authentication.html list of authentication methods] you can choose that are better for establishing a remote connection to PostgreSQL. It is fairly easy to set up a [https://www.postgresql.org/docs/current/auth-password.html password] based authentication method, the recommendation being <tt>scram-sha-256</tt> that is available in PostgreSQL 10 and above.<br />
<br />
=== Why not? ===<br />
<br />
The [https://www.postgresql.org/docs/current/auth-trust.html manual] says:<br />
<br />
<blockquote><tt>trust</tt> authentication is only suitable for TCP/IP connections if you trust every user on every machine that is allowed to connect to the server by the <tt>pg_hba.conf</tt> lines that specify <tt>trust</tt>. It is seldom reasonable to use trust for any TCP/IP connections other than those from localhost (127.0.0.1).</blockquote><br />
<br />
With <tt>trust</tt> authentication, any user can claim to be any other user and PostgreSQL will trust that assertion. This means that someone can claim to be the <tt>postgres</tt> superuser account and PostgreSQL will accept that claim and allow them to log in.<br />
<br />
To take this a step further, it is also not a good idea to allow <tt>trust</tt> authentication to be used on <tt>local</tt> UNIX socket connections in a production environment, as anyone with access to the instance running PostgreSQL could log in as any user.<br />
<br />
=== When should you? ===<br />
<br />
The short answer is '''never'''.<br />
<br />
The longer answer is there are a few scenarios where <tt>trust</tt> authentication may be appropriate:<br />
<br />
* Running tests against a PostgreSQL server as part of a CI/CD job that is on a trusted network<br />
* Working on your local development machine, but only allowing TCP/IP connections over localhost<br />
<br />
but you should see if any of the alternative methods work better for you. For example, on UNIX-based systems, you can connect to your local development environment using <tt>peer</tt> authentication.</div>Saperhttps://wiki.postgresql.org/index.php?title=Development_Articles&diff=34693Development Articles2020-03-17T15:19:47Z<p>Saper: +schemalint</p>
<hr />
<div>These are articles about developing with PostgreSQL (not on [[Development information|developing PostgreSQL]] itself).<br />
<br />
== General Development ==<br />
<br />
* [http://www.webdotdev.com/nvd/articles-reviews/postgresql/programming-otl-using-postgresql-and-unixodbc-in-the-solaris-environment-348.html Programming OTL using PostgreSQL and unixODBC in the Solaris environment: a step-by-step guide] by Carlos Crosetti (2006-02-13)<br />
* [http://www.postgresql.org/files/documentation/books/aw_pgsql/writing_apps/ Writing Applications for PostgreSQL] by Bruce Momjian<br />
* [http://www.codeproject.com/KB/database/PostgreSQLTasks.aspx PostgreSQL & PostGIS operations] by Mohammed Thabet<br />
* [https://github.com/kristiandupont/schemalint schemalint] by Kristian Dupont - a tool to verify the database schema against [[Don't Do This]] recommendations.<br />
<br />
== Client Libraries ==<br />
<br />
[[Client Libraries]] access PostgreSQL from your favorite language<br />
<br />
== JDBC/J2EE ==<br />
<br />
* [http://www.linuxfocus.org/English/March2003/article285.shtml Accessing PostgreSQL through JDBC via a Java SSL tunnel] by Chianglin Ng<br />
<br />
== Lua ==<br />
<br />
* [[Lua|Accessing PostgreSQL Databases Using the Lua Scripting Language]]<br />
<br />
== Materialized Views ==<br />
See [[Materialized Views]] for more information<br />
<br />
* [http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Implementing Materialized Views in PostgreSQL] by Jonathan Gardner<br />
* [http://www.pgcon.org/2008/schedule/events/69.en.html Materialized Views that Really Work] by Dan Chak<br />
<br />
== Microsoft Access and .NET ==<br />
<br />
* [http://www.developer.com/open/article.php/631251 Connecting to PostgreSQL from Windows Platforms] by Damond Walker<br />
* [http://pgfoundry.org/projects/npgsql/ The npgsql Project] - .Net Data Provider for PostgreSQL<br />
* [[Using Microsoft .NET with the PostgreSQL Database Server via ODBC]] by Matthew Stanfield (2002-04-15)<br />
* [http://www.postgresonline.com/journal/index.php?/archives/37-REST-in-PostgreSQL-Part-2-A-The-REST-Server-service-with-ASP.NET.html REST in PostgreSQL Part 2 A - The REST Server service with ASP.NET] - Postgres OnLine Journal (March 2008)<br />
*[http://www.postgresonline.com/journal/index.php?/archives/24-Using-MS-Access-with-PostgreSQL.html Using MS Access with PostgreSQL] Postgres OnLine Journal (January/February 2008)<br />
*[http://www.c-sharpcorner.com/UploadFile/thabet084/CrystalReport_PostgreSQL08252009111048AM/CrystalReport_PostgreSQL.aspx?ArticleID=02d7c2d1-2fb6-4b1f-bc0c-79dfda6cc54c Crystal Report with PostgreSQL using dataset]By Mohammed Thabet Zaky (August 2009)<br />
<br />
== PL/pgSQL ==<br />
<br />
* HOWTO: [[Return more than one row of data from PL/pgSQL functions]] by Stephan Szabo<br />
*[http://www.postgresonline.com/journal/index.php?/archives/16-Trojan-SQL-Function-Hack---A-PL-Lemma-in-Disguise.html Trojan SQL Function Hack - A PL Lemma in Disguise] Postgres OnLine Journal (January/February 2008) - Trick to using set returning functions in non-constant join (e.g. simulate SQL Server 2005 CROSS APPLY)<br />
* [http://www.postgres.cz/index.php/PL/pgSQL_(en) Introduction to PL/pgSQL] by Pavel Stehule<br />
<br />
== Python ==<br />
*[[Using psycopg2 with PostgreSQL]]<br />
<br />
== PL/Python ==<br />
*[http://www.postgresonline.com/journal/index.php?/archives/99-Quick-Intro-to-PLPython.html Quick Intro to PLPython] Postgres OnLine Journal (January 2009)<br />
*[http://www.postgresonline.com/journal/index.php?/archives/100-PLPython-Part-2-Control-Flow-and-Returning-Sets.html PLPython Part 2: Control Flow and Returning Sets] Postgres OnLine Journal (February 2009)<br />
*[http://www.postgresonline.com/journal/index.php?/archives/101-PLPython-Part-3-Using-custom-classes,-pulling-data-from-PostgreSQL.html PLPython Part 3: Using custom classes, pulling data from PostgreSQL] Postgres OnLine Journal (February 2009)<br />
*[http://www.postgresonline.com/journal/index.php?/archives/102-PLPython-Part-4-PLPython-meets-aggregates.html PLPython Part 4: PLPython meets aggregates] Postgres OnLine Journal (February/March 2009)<br />
*[http://www.postgresonline.com/journal/index.php?/archives/107-PLPython-Part-5-PLPython-meets-PostgreSQL-Multi-column-aggregates-and-SVG-plots.html PLPython Part 5: PLPython meets PostgreSQL Multi-column aggregates and SVG plots] Postgres OnLine Journal (February/March 2009)<br />
<br />
*[http://www.postgresonline.com/journal/index.php?/archives/106-PL-Python-Cheatsheet-Overview.html PL Python Cheatsheet] Postgres OnLine Journal (February/March 2009)<br />
<br />
== PL/R ==<br />
*[http://www.joeconway.com/web/guest/pl/r PL/R Wiki] <br />
*[http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut01 Up and Running with PL/R An Almost Idiot's Guide] <br />
*[http://www.postgresonline.com/journal/archives/188-plr_part1.html Quick Intro to PL/R and R] - 2010-11-28 Postgres OnLine Journal<br />
*[http://www.postgresonline.com/journal/archives/189-plr_part2.html PL/R Part 2: Functions that take arguments and the power of aggregation] - 2010-12-10 Postgres OnLine Journal<br />
*[http://www.postgresonline.com/journal/archives/190-plr_part3.html PL/R Part 3: Sharing Functions across PL/R functions with plr_module] - 2010-12-20 Postgres OnLine Journal<br />
<br />
== Smalltalk ==<br />
<br />
* [http://www.io.com/~jimm/projects/squeak_postgresql/ Squeak Smalltalk client by Jim Menard (2005-2006)]<br />
<br />
* [http://www.cincomsmalltalk.com/userblogs/jsavidge/blogView?showComments=true&title=Installing+PostgreSQL+to+use+with+VisualWorks+StORE&entry=3319025226 Installing the PostgreSQL driver and client library from the Store in VisualWorks Smalltalk by Cincom (2006)]<br />
<br />
== Referential Integrity ==<br />
<br />
* [[Compensating for Unimplemented Features in PostgreSQL 7.1]] by John Pagakis and Todd Gauthier (2001-08-21)<br />
* [[Referential Integrity Tutorial & Hacking the Referential Integrity tables]] by Joel Burton (2001-09-03)<br />
* [http://www.freebsddiary.org/postgresql-dropping-constraints.php Removing Foreign Keys] by Dan Langille<br />
<br />
== Web development ==<br />
* [http://whatcodecraves.com/articles/2008/02/05/setup_rails_with_postgresql/ Setup Rails with PostgreSQL]<br />
* [http://www.tgds.net/ultra.html Using Macromedia UltraDev with PostgreSQL, Tomcat and Apache] by Tony Grant<br />
* [http://www.holindis.co.uk/PostgreSQLApacheTomcatWin32_v1.1.pdf Apache 2.0, Tomcat 5.5, WARs & PostgreSQL 8.1 JDBC DataSources on Windows] by Chris Drawater (Jan 2006)<br />
* Italian: [http://cuneo.linux.it/materiale/index.php?txt=pgsql/index Appunti di PostgreSQL] by Giorgio Ponza - GlugCN (2006-03-14)<br />
<br />
[[Category:General articles and guides]]</div>Saperhttps://wiki.postgresql.org/index.php?title=Todo&diff=34414Todo2019-11-29T21:14:55Z<p>Saper: /* SQL Commands */ post is gone, replace with the archived version</p>
<hr />
<div><div style="margin: 1ex 1em; float: right;"><br />
__TOC__<br />
</div><br />
<br />
This list contains some known PostgreSQL bugs, some feature requests, and some things we are not even sure we want. Many of these items are hard, and some are perhaps impossible. 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 12 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_Lists|mailing list]].</b><br />
<br />
== Development Process ==<br />
<br />
<b>WARNING 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 />
== Administration ==<br />
<br />
{{TodoItem<br />
|Allow administrators to cancel long-lived prepared transactions<br />
* [http://www.postgresql.org/message-id/20961.1403630269@sss.pgh.pa.us Re: idle_in_transaction_timeout]<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 />
|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 />
|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 />
* {{MessageLink|alpine.DEB.2.20.1701081007440.10378@lancre|proposal: session server side variables}}<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 />
|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 />
{{TodoItem<br />
|Consider supporting incremental base backups<br />
* http://www.postgresql.org/message-id/543D5AA7.9@2ndquadrant.it<br />
}}<br />
<br />
=== Configuration files ===<br />
{{TodoSubsection}}<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 />
|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 />
|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 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 />
* http://archives.postgresql.org/pgsql-hackers/2011-12/msg01224.php<br />
}}<br />
<br />
{{TodoItem<br />
|Fix log_line_prefix to display the transaction id (%x) for statements not in a transaction block<br />
* Currently it displays zero.<br />
}}<br />
<br />
{{TodoItem<br />
|Adjust rounding behavior for numeric GUC values<br />
* http://www.postgresql.org/message-id/53BE3815.4010203@po.ntts.co.jp<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 />
* [https://www.postgresql.org/message-id/20180214042443.GB1993%40paquier.xyz Remarks about the difficulty of the item]<br />
}}<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 tablespaces on RAM-based partitions for temporary objects<br />
* [https://www.postgresql.org/message-id/20170529185308.GB28209@momjian.us Use of non-restart-safe storage by temp_tablespaces]<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 moving toast table to its own tablespace]<br />
* {{messageLink|CAFEQCbH756DyyAPQ1ykh3+b+kE1-EhWRww1WO_x5v38C-uLnUg@mail.gmail.com|patch : Allow toast tables to be moved to a different tablespace}}<br />
}}<br />
<br />
{{TodoItem<br />
|Close race in DROP TABLESPACE on Windows<br />
* http://www.postgresql.org/message-id/20141108050423.GA642055@tornado.leadboat.com<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Statistics Collector ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Testing pgstat via pg_regress is tricky and inefficient. Consider making a dedicated pgstat test-suite.<br />
* [http://www.postgresql.org/message-id/20141216142937.GX1768@alvh.no-ip.org <nowiki>Re: REVIEW: Track TRUNCATE via pgstat</nowiki>]<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 />
* [http://www.postgresql.org/message-id/53F5AB0A.5060502@dalibo.com Track number of files ready to be archived in pg_stat_archiver]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== SSL ===<br />
{{TodoSubsection}}<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 />
{{TodoItemDone<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 />
{{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 being 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 />
{{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 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 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 deleting enumerated values from an existing enumerated data type<br />
* [http://www.postgresql.org/message-id/CAO%3D2mx6uvgPaPDf-rHqG8%3D1MZnGyVDMQeh8zS4euRyyg4D35OQ@mail.gmail.com Alter or rename enum value]<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 />
| 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 />
* https://www.postgresql.org/message-id/4EB095C8.1050703@agliodbs.com<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 />
* [https://www.postgresql.org/message-id/Pine.LNX.4.44.0410211300430.2015-100000@zigo.dhs.org <nowiki>timestamp with time zone a la sql99</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Have timestamp subtraction not call justify_hours()?<br />
* [https://www.postgresql.org/message-id/21619.1160067054@sss.pgh.pa.us <nowiki>timestamp subtraction (was Re: formatting intervals with to_char)</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow a comma to denote fractional seconds in ISO-8601-compliant times (and timestamps)<br />
* https://www.postgresql.org/message-id/7D5AC9AB-238D-4FE7-8857-18D98190A4D9@justatheory.com<br />
* https://www.postgresql.org/message-id/19944.1529810240%40sss.pgh.pa.us<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Arrays ===<br />
{{TodoSubsection}}<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 />
=== 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 />
|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 />
}}<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 />
}}<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 />
|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 />
}}<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 />
|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 />
}}<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 />
|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 />
* [https://www.postgresql.org/message-id/CALkFZpcbipVJO%3DxVvNQMZ7uLUgHzBn65GdjtBHdeb47QV4XzLw@mail.gmail.com Implement Boyer-Moore searching in LIKE queries]<br />
}}<br />
<br />
{{TodoItem<br />
|Prevent malicious functions from being executed with the permissions of unsuspecting users<br />
|Indexed 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 />
=== 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 />
|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 />
|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://www.postgresql.org/message-id/20131220030725.GA1411150@tornado.leadboat.com multibyte messages are displayed incorrectly on the client]<br />
}}<br />
<br />
{{TodoItem<br />
|Windows: Cache MessageEncoding conversion for use outside transactions<br />
* http://www.postgresql.org/message-id/20150812055719.GA1945333@tornado.leadboat.com<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 />
{{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 />
* [http://www.postgresql.org/message-id/87mwe4k46y.fsf@commandprompt.com Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?]<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 />
{{TodoItem<br />
|Allow ALTER INDEX ... RENAME concurrently<br />
* [https://www.postgresql.org/message-id/1531767486.432607658@f357.i.mail.ru Alter index rename concurrently to]<br />
}}<br />
<br />
== SQL Commands ==<br />
<br />
{{TodoItem<br />
|Add CORRESPONDING BY to UNION/INTERSECT/EXCEPT<br />
* [http://web.archive.org/web/20161019182747/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 />
|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 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 />
|Add SQL-standard MERGE command<br />
|MERGE is typically used to merge two tables, for data warehousing type use cases.<br />
* PostgreSQL has an "UPSERT" command as of version 9.5, with the addition of "ON CONFLICT DO UPDATE". SQL MERGE is independently useful, though.<br />
* A patch for this that made it into Postgres 11 was reverted.<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 />
|Simplify dropping roles that have objects in several databases}}<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 />
}}<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 />
=== 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 />
* [https://www.postgresql.org/message-id/CAB7nPqTPXgX9HiyhhtAgpW7jbA1iskMCSoqXPEEB_KYXYy1E1Q@mail.gmail.com Prevent ALTER TABLE DROP NOT NULL on child tables if parent column has it]<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 />
* [http://www.postgresql.org/message-id/CAFj8pRC2h6qhHsFbcE7b_7SagiS6o%3D5J2UvCwCb05Ka1XFv_Ng@mail.gmail.com Implementation of global temporary tables?]<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 />
{{TodoEndSubsection}}<br />
<br />
=== UPDATE ===<br />
{{TodoSubsection}}<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 />
}}<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 />
|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 />
* [http://www.postgresql.org/message-id/20141209174146.GP1768@alvh.no-ip.org logical column ordering]<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 partial indexes<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 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 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 />
|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 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 Why doesn't COPY support the HEADER options for tab-separated output?]<br />
}}<br />
<br />
{{TodoItem<br />
|Have COPY FREEZE set PD_ALL_VISIBLE<br />
* [http://www.postgresql.org/message-id/CAMkU%3D1w3osJJ2FneELhhNRLxfZitDgp9FPHee08NT2FQFmz_pQ@mail.gmail.com items]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== GRANT/REVOKE ===<br />
{{TodoSubsection}}<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 />
=== 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 />
|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 />
{{TodoItem<br />
|Remove quadratic time in statistics sender when analyzing many tables<br />
* [http://www.postgresql.org/message-id/flat/CAMkU%3D1wLjAsmJNuB6ZObZmGHqi9jLbK6n1eSgnOc5J1-AUsvUA@mail.gmail.com Thousands of schemas and ANALYZE goes out of memory]<br />
}}<br />
<br />
{{TodoItem<br />
|Reduce memory use when analyzing many tables in a single command by making catcache and syscache flushable or bounded.<br />
* [http://www.postgresql.org/message-id/flat/CAMkU%3D1yZnAYvMHENt8%3D9pgwE8q5zmX+mG%3DSXbFHiLkq_qn0B7Q@mail.gmail.com Thousands of schemas and ANALYZE goes out of memory]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== EXPLAIN ===<br />
{{TodoSubsection}}<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 />
|Have EXPLAIN ANALYZE report buckets and memory usage for HashAggregate<br />
* [https://www.postgresql.org/message-id/flat/2527f5cb-5992-ae66-f3ec-4aa2396065ec%402ndquadrant.com <nowiki>to-do item for explain analyze of hash aggregates? </nowiki>]<br />
}}<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 />
{{TodoItem<br />
|Do not scan the table when a check constraint is added in the same command that adds the column<br />
* [http://www.postgresql.org/message-id/CAMkU%3D1z5vXZ8Txd9_8hvNFovtbGuP4VTitFRN59XDncEHVGtJA@mail.gmail.com skip table scan for adding column with provable check constraints]<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 />
{{TodoItemDone<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 />
{{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 />
{{TodoItem<br />
|Allow regex operations in PL/Perl using UTF8 characters in non-UTF8 encoded databases}}<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://pgxn.org/dist/colnames/ <nowiki>colnames: Extension to retrieve column names from a record</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/Python ===<br />
{{TodoSubsection}}<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 />
{{TodoEndSubsection}}<br />
<br />
== Clients ==<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 />
{{TodoItem<br />
|Improve pg_rewind<br />
* [https://www.postgresql.org/message-id/CAN-RpxDhc_8JOq%3DcT9vd6MqWQaS0ZtvSf2LFV1V+bjOoEz02ow@mail.gmail.com Proposal: pg_rewind to skip config files]<br />
}}<br />
<br />
=== psql ===<br />
{{TodoSubsection}}<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 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 />
{{TodoItemDone<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 />
|This items has been fixed in commit 750c5e.<br />
<br/><br />
<code><br />
Fix psql \s to work with recent libedit, and add pager support.<br />
<br />
psql's \s (print command history) doesn't work at all with recent libedit<br />
versions when printing to the terminal, because libedit tries to do an<br />
fchmod() on the target file which will fail if the target is /dev/tty.<br />
(We'd already noted this in the context of the target being /dev/null.)<br />
Even before that, it didn't work pleasantly, because libedit likes to<br />
encode the command history file (to ensure successful reloading), which<br />
renders it nigh unreadable, not to mention significantly different-looking<br />
depending on exactly which libedit version you have. So let's forget using<br />
write_history() for this purpose, and instead print the data ourselves,<br />
using logic similar to that used to iterate over the history for newline<br />
encoding/decoding purposes.<br />
<br />
While we're at it, insert the ability to use the pager when \s is printing<br />
to the terminal. This has been an acknowledged shortcoming of \s for many<br />
years, so while you could argue it's not exactly a back-patchable bug fix<br />
it still seems like a good improvement. Anyone who's seriously annoyed<br />
at this can use "\s /dev/tty" or local equivalent to get the old behavior.<br />
<br />
Experimentation with this showed that the history iteration logic was<br />
actually rather broken when used with libedit. It turns out that with<br />
libedit you have to use previous_history() not next_history() to advance<br />
to more recent history entries. The easiest and most robust fix for this<br />
seems to be to make a run-time test to verify which function to call.<br />
We had not noticed this because libedit doesn't really need the newline<br />
encoding logic: its own encoding ensures that command entries containing<br />
newlines are reloaded correctly (unlike libreadline). So the effective<br />
behavior with recent libedits was that only the oldest history entry got<br />
newline-encoded or newline-decoded. However, because of yet other bugs in<br />
history_set_pos(), some old versions of libedit allowed the existing loop<br />
logic to reach entries besides the oldest, which means there may be libedit<br />
~/.psql_history files out there containing encoded newlines in more than<br />
just the oldest entry. To ensure we can reload such files, it seems<br />
appropriate to back-patch this fix, even though that will result in some<br />
incompatibility with older psql versions (ie, multiline history entries<br />
written by a psql with this fix will look corrupted to a psql without it,<br />
if its libedit is reasonably up to date).<br />
<br />
Stepan Rutz and Tom Lane<br />
</code><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 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 />
|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 />
|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 />
|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 />
|Dump security labels and comments on databases in a way that allows to load a dump into a differently named database<br />
* [http://www.postgresql.org/message-id/20150710115735.GH26521@alap3.anarazel.de security labels on databases are bad for dump & restore]<br />
|}}<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 />
{{TodoItem<br />
|Avoid using platform-dependent names for locales 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 />
|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 />
{{TodoItemDone<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 />
* [http://www.postgresql.org/message-id/flat/20150730064941.GA1582735@tornado.leadboat.com rehash five years later]<br />
* {{messageLink|21573.1475162949@sss.pgh.pa.us|sketch for division of labor between pg_dump and pg_dumpall}}<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 />
{{TodoItem<br />
|Prevent PL/pgSQL comment from throwing an error in a non-superuser restore<br />
* [http://www.postgresql.org/message-id/E1VuYH7-0008Rz-SV@wrigleys.postgresql.org Reloading dump fails at COMMENT ON EXTENSION plpgsql]<br />
}}<br />
<br />
{{TodoItem<br />
|Delay REFRESH MATERIALIZED VIEW until dependent indexes are created<br />
* [http://www.postgresql.org/message-id/20140820021530.2534.43156@wrigleys.postgresql.org pg_restore unusable for expensive matviews]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== 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 />
|Migrate pg_statistic by dumping it out as a flat file, so analyze is not necessary<br />
* [http://archives.postgresql.org/message-id/CAAZKuFaWdLkK8eozSAooZBets9y_mfo2HS6urPAKXEPbd-JLCA@mail.gmail.com pg_upgrade and statistics]<br />
* [https://www.postgresql.org/message-id/20171205140135.GA25023%40momjian.us Speeding up pg_upgrade]<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 />
|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 />
* [http://www.postgresql.org/message-id/CAPpHfdtxXMjyZxwND09ZLBBACVbWb5J9bLUf67CndR4VKFDgwg@mail.gmail.com Use non-collation-aware comparisons for GIN opclasses]<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 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 />
|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 />
{{TodoItem<br />
|When receiving a FATAL error remember it, so that it doesn't profess ingnorance about why the session was closed<br />
* [http://www.postgresql.org/message-id/CA+TgmoZ4P1cQetjOxQoHiG072UcE7dpE7dTBV8hMOidhwhof+g@mail.gmail.com<nowiki>Idle In Transaction Session Timeout, revived</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Pipelining support for libpq async API and an array-valued PQexecPrepared that uses it<br />
* [http://www.postgresql.org/message-id/CAMsr+YHE8Rt800yWcHEL8SrgruK0ng_nBmtKV6YMZ2BAzRBZzw@mail.gmail.com Foreign table batched inserts]<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 />
|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 />
|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 />
|Allow unique indexes across inherited tables (requires multi-table indexes)<br />
* Postgres 11 allows unique indexes across partitions if the partition key is part of the index.<br />
}}<br />
<br />
{{TodoItem<br />
|Research whether ALTER TABLE / SET SCHEMA should work on inheritance hierarchies (and thus support ONLY)}}<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 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 />
|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 />
* [https://wiki.postgresql.org/wiki/Reindex_concurrently Wiki page listing current situation on the matter]<br />
* [http://www.postgresql.org/message-id/CAB7nPqSTFkuc7dZxCDX4HOTU63YXHRroNv2aoUzyD-Zz_8Z_Zg@mail.gmail.com REINDEX CONCURRENTLY 2.0]<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 />
|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 />
|Allow GIN indexes to be used for exclusion constraints<br />
* http://archives.postgresql.org/pgsql-hackers/2012-05/msg00669.php<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 don't support index-only scans<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 />
{{TodoItem<br />
| Teach GIN cost estimation about "fast scans"<br />
* http://www.postgresql.org/message-id/53208B4D.5000806@vmware.com<br />
}}<br />
<br />
{{TodoItem<br />
| Allow unlogged indexes<br />
* http://www.postgresql.org/message-id/11561.1414793261@sss.pgh.pa.us<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 more 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 />
{{TodoEndSubsection}}<br />
<br />
=== Hash ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Add UNIQUE capability to hash indexes<br />
* [https://www.postgresql.org/message-id/592254A5.9000809@anastigmatix.net Re: PG10 Crash-safe and replicable Hash Indexes and UNIQUE]<br />
}}<br />
<br />
{{TodoItem<br />
|Allow multi-column hash indexes<br />
* This requires all columns to be specified for a query to use the index.<br />
* [https://www.postgresql.org/message-id/CA+Tgmoax6DhnKsuE_gzY5qkvmPEok77JAP1h8wOTbf+dg2Ycrw@mail.gmail.com Write Ahead Logging for Hash Indexes]<br />
}}<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 />
}}<br />
<br />
{{TodoItem<br />
|Consider detoasting keys before sorting}}<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 Re: MemoryContextAllocHuge(): selectively bypassing MaxAllocSize]<br />
}}<br />
<br />
== Cache Usage ==<br />
<br />
{{TodoItem<br />
|Consider automatic caching of statements at various levels:<br />
* Parsed query tree<br />
* Query execute plan<br />
* Query results <br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00823.php <nowiki>Cached Query Plans (was: global prepared statements)</nowiki>]<br />
* [https://www.postgresql.org/message-id/CAFj8pRAGLaiEm8ur5DWEBo7qHRWTk9HxkuUAz00CZZtJj-LkCA%40mail.gmail.com PoC plpgsql - possibility to force custom or generic plan]<br />
* [https://www.postgresql.org/message-id/CADT4RqAd_74m6MUbXAPsYzqXG3F6wWVhS_dFJijrfXs7N+QGHQ@mail.gmail.com Cached/global query plans, autopreparation]<br />
}}<br />
<br />
{{TodoItem<br />
|Consider allowing higher priority queries to have referenced shared buffer 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 />
|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 />
* [http://www.postgresql.org/message-id/20150424190403.GP4369@alvh.no-ip.org Re: Feedback on getting rid of VACUUM FULL]<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 />
|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-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 />
}}<br />
<br />
=== Auto-vacuum ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Issue log message to suggest VACUUM FULL if a table is nearly empty?<br />
*[http://www.postgresql.org/message-id/F40B0968DB0A904DA78A924E633BE78645FAAF@SYDEXCHTMP2.au.fjanz.com discussion]<br />
}}<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. <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 />
}}<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 />
|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 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 />
|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 />
|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 />
|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 />
|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 />
|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 />
* https://www.postgresql.org/message-id/5351711493487900@web53g.yandex.ru<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 />
|Use "lazy" hash tables to 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 />
* [http://www.postgresql.org/message-id/CAMkU%3D1zf1Yo0dYJzJ-pk9o4mwLuMD4Uzw6Jck7u1nC_Xb2gYWA@mail.gmail.com Re: Autovacuum fails to keep visibility map up-to-date in mostly-insert-only-tables]<br />
}}<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 />
== 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 />
|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 />
}}<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 />
<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 />
{{TodoItemDone<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 />
{{TodoItem<br />
| Improve handling of pg_stat_statements handling of bind "IN" variables<br />
* [http://www.postgresql.org/message-id/CAM3SWZSpdPB3uErnXWMt3q74y0r+84ZsOt2U3HKKes_V7O+0Qg@mail.gmail.com Revisiting pg_stat_statements and IN()]<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 />
|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 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 />
|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 />
{{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 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 />
|Enhance foreign data wrappers, parallelism, partitioning, and perhaps add a global snapshot/transaction manager to allow creation of a proof-of-concept built-in sharding solution<br />
|Ideally these enhancements and new facilities will be available to external sharding solutions as well.<br />
* [https://wiki.postgresql.org/wiki/Built-in_Sharding Built-in sharding wiki]<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 />
|Have GB18030 handle more than 2-byte Unicode code points<br />
* [http://www.postgresql.org/message-id/20150309205145.4031.32069@wrigleys.postgresql.org GB18030 encoding doesn't support Unicode characters over 0xFFFF]<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 />
|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 />
{{TodoItem<br />
|Allow pg_export_snapshot() to run on hot standby servers<br />
|This will allow parallel pg_dump on such servers.<br />
* [http://www.postgresql.org/message-id/CA+U5nML2VMJ3R2YBAZ+CsAsTWF3LuYSo16Wu9+yXFrfy4%3Df2fA@mail.gmail.com pg_export_snapshot on standby side]<br />
}}<br />
<br />
{{TodoItem<br />
|Provide a way to enumerate and unregister background workers<br />
|Right now the only way to unregister bgworkers is from within the worker with <tt>proc_exit(0)</tt> or registering with <tt>BGW_NEVER_RESTART</tt><br />
* https://www.postgresql.org/message-id/CAMsr%2BYG-fD%2BmP-BNZDheVYucC7%3DoUn8ByTQSFz7RKkVX2MRS2Q%40mail.gmail.com<br />
}}<br />
<br />
== Source Code ==<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}}<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 />
* [https://www.postgresql.org/message-id/flat/CABgyVxDBd3EvRdo-Rd6eo8QPEqV8%3DShaU2SJfo16wfE0R-hXTA%40mail.gmail.com C++ port of Postgres]<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 />
|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 />
| 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 />
=== Windows ===<br />
{{TodoSubsection}}<br />
<br />
{{TodoItem<br />
|Remove configure.in check for link failure when the cause is found}}<br />
<br />
{{TodoItem<br />
|Allow psql to use readline once non-US code pages work with backslashes}}<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 />
|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 />
|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 />
{{TodoItem<br />
|Fix hang with long file paths<br />
* [http://www.postgresql.org/message-id/CAA4eK1JxaBofxpcgLqCx9EB%3Dm3PaXr9iFU9%3DV3ddDswsPZooxw@mail.gmail.com Long paths for tablespace leads to uninterruptible hang in Windows]<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
=== Wire Protocol Changes / v4 Protocol ===<br />
{{TodoSubsection}}<br />
<br />
<br />
{{TodoItem<br />
|Allow dynamic character set handling}}<br />
<br />
{{TodoItem<br />
|Ensure the client can determine the encoding of messages sent early in the handshake<br />
* http://www.postgresql.org/message-id/541A5659.5050006@2ndquadrant.com}}<br />
<br />
{{TodoItem<br />
|Let the client indicate character encoding of database names, user names, passwords, and of pre-auth error messages returned by the server<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 />
<br />
{{TodoItem<br />
|Send numeric version to clients in fixed header<br />
* [https://commitfest.postgresql.org/10/752/ <nowiki>patch to send server_version_num in v3 protocol as GUC_REPORT</nowiki>]<br />
}}<br />
<br />
{{TodoItem<br />
|Add decoded type length/precision (i.e. typmod information)}}<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 />
|Use compression<br />
|Specify and implement wire protocol compression. If SSL transparent compression is used, hopefully avoid the overhead of key negotiation and encryption when SSL is configured only for compression. Note that [https://www.ietf.org/mail-archive/web/tls/current/msg11619.html compression is being removed from TLS 1.3] so we really need to do it ourselves.<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 (e.g. try client peer, fall back to md5)<br />
* http://www.postgresql.org/message-id/51A44185.5060306@2ndquadrant.com<br />
* http://www.postgresql.org/message-id/55192AFE.6080106@iki.fi<br />
* http://www.postgresql.org/message-id/54DB1D4E.8090700@vmware.com<br />
* https://commitfest.postgresql.org/6/320/<br />
}}<br />
<br />
{{TodoItem<br />
|Allow re-authentication<br />
|Let the client request re-authentication as a different user mid session, for connection pools that pass through the handshake.<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 />
{{TodoItem<br />
|Allow negotiation of encryption, <tt>STARTTLS</tt> style, rather than forcing client to decide on SSL or !SSL before connecting<br />
* http://www.postgresql.org/message-id/5406EAD3.7070002@2ndquadrant.com}}<br />
<br />
{{TodoItem<br />
|Permit lazy fetches of large values, at least out-of-line TOASTED values<br />
* http://www.postgresql.org/message-id/53FF0EF8.100@2ndquadrant.com<br />
}}<br />
<br />
{{TodoItem<br />
|Add session-level whitelisting of types for binary-mode transfer<br />
* http://www.postgresql.org/message-id/30470.1412055068@sss.pgh.pa.us<br />
}}<br />
<br />
{{TodoItem<br />
|Send client the xid when it is allocated<br />
|Lets the client later ask the server "did this commit or not?" after interterminate result due to crash or connection loss<br />
}}<br />
<br />
{{TodoItem<br />
|Report xlog position in commit message<br />
|Help enable client-side failover by providing a token clients can use to see if a commit has replayed to replicas yet<br />
* http://www.postgresql.org/message-id/53E2D346.9030806@2ndquadrant.com<br />
}}<br />
<br />
{{TodoItem<br />
|Changes to make cancellations more reliable and more secure<br />
* http://www.postgresql.org/message-id/CADT4RqAUd7wYYsM9D7GHJnZj3J79D4W%3Dved2kqM5mVt5cuGHgg@mail.gmail.com<br />
}}<br />
<br />
{{TodoItem<br />
|Clarify semantics of statement_timeout in extended query protocol<br />
|Batched and pipelined queries have unexpected behaviour with statement_timeout. Client needs to be able to specify statement boundary with protocol message.<br />
* https://www.postgresql.org/message-id/20160528.220442.1489791680347556026.t-ishii@sraoss.co.jp<br />
}}<br />
<br />
{{TodoItem<br />
|Create a more efficient way to handle out-of-line parameters<br />
* [https://www.postgresql.org/message-id/12500.1470002232%40sss.pgh.pa.us Re: Slowness of extended protocol]<br />
}}<br />
<br />
{{TodoItem<br />
|Separate transaction delineation from protocol error recovery (in v3 both are managed via the same Sync message)<br />
* https://www.postgresql.org/message-id/CADT4RqDdo9EcFbxwB_YO2H3BVZ0t-1qqZ%3D%2B%2BdVMnYaN6BpyUGQ%40mail.gmail.com<br />
* https://www.postgresql.org/message-id/CAMsr%2BYEgnJ8ZAWPLx5%3DBCbYYq9SNTdwbwvUcb7V-vYm5d5uhbQ%40mail.gmail.com<br />
}}<br />
<br />
{{TodoEndSubsection}}<br />
<br />
== Documentation ==<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 />
|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 />
* [http://www.postgresql.org/message-id/CAFj8pRD4OAXp2zp7dBRg5eo6X3rtT5MHTMVRN1e1kdK8xE6E4g@mail.gmail.com proposal: schema PL session variables]<br />
* [https://www.postgresql.org/message-id/CAFj8pRCfdTLeJbTSbAFOwhuS-aWaJ61w59XwKLcVYQFAVwfVCw%40mail.gmail.com proposal: session server side variables]<br />
* [https://www.postgresql.org/message-id/81060c9d-73df-2266-8857-d584164bb699%40commandprompt.com Packages: Again]<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 />
* [https://www.postgresql.org/message-id/ACF85C502E55A143AB9F4ECFE960660A17282D@mailserver2.local.mstarlabs.com Cluster wide option to control symbol case folding]<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 />
== 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 />
* [https://www.postgresql.org/message-id/942824238.20160712165757@bitec.ru One process per session lack of sharing]<br />
}}<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 />
{{TodoItem<br />
|On-disk bitmap indexes (not wanted)<br />
|The rigidity of on-disk bitmap indexes, and the existence of GIN and in-memory bitmaps make this undesirable.<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 <nowiki>bitmap indexes - performance</nowiki>]<br />
* [http://www.postgresql.org/message-id/20130914181424.GA24448@toroid.org <nowiki>[PATCH] bitmap indexes</nowiki>]<br />
}}<br />
<br />
[[Category:Todo]]</div>Saperhttps://wiki.postgresql.org/index.php?title=Homebrew&diff=34096Homebrew2019-09-14T09:37:28Z<p>Saper: pgsql->psql command utility, thanks to Nick Bauer-Levey <nickbl@gagosian.com></p>
<hr />
<div>[http://mxcl.github.com/homebrew/ Homebrew] is a package manager for Mac OS X that builds software from its source code. It includes a version of PostgreSQL packaged by what it refers to as a formula. This type of installation might be preferred by people who are comfortable using the command line to install programs, such as software developers.<br />
<br />
Typical use looks like<br />
<br />
<pre><br />
$ brew install postgresql<br />
</pre><br />
<br />
This install the command line console (psql) as well as the server, if you'd like to create your own databases locally.<br />
Run the following to start the server and login to it (it basically sets up a single "admin" user with your username, so that's who you'll be logged in as.<br />
<ore><br />
$ brew services start postgresql<br />
$ psql postgres<br />
</pre><br />
<br />
You can see what other versions are [https://apple.stackexchange.com/a/304025/25085 available] by running <br />
<br />
<pre><br />
$ brew search postgres<br />
</pre><br />
<br />
You can see which version the current latest will be by running<br />
<br />
<pre><br />
$ brew edit postgresql<br />
</pre><br />
<br />
Notes on Homebrew:<br />
<br />
* [http://exponential.io/blog/2015/02/21/install-postgresql-on-mac-os-x-via-brew/ Install PostgreSQL on Mac OS X via Brew]<br />
* [http://nextmarvel.net/blog/2011/09/brew-install-postgresql-on-os-x-lion/ Brew Install Postgresql on OS X Lion] - Resolving conflicts with the built-in PostgreSQL libraries shipping with OS X 10.7 'Lion'<br />
* [http://archives.postgresql.org/pgsql-hackers/2012-04/msg00010.php Discussion comparing Homebrew and EDB OS X installers]<br />
* [https://github.com/petere/homebrew-postgresql <code>petere/postgresql</code>] tap for installing multiple PostgreSQL versions in parallel<br />
<br />
[[Category:Tool]]</div>Saperhttps://wiki.postgresql.org/index.php?title=Lock_Monitoring&diff=33142Lock Monitoring2019-03-14T15:36:35Z<p>Saper: whitespace</p>
<hr />
<div>{{Languages}}<br />
= Online view current locks =<br />
== pg_locks view ==<br />
Looking at [http://www.postgresql.org/docs/current/static/view-pg-locks.html pg_locks] shows you what locks are granted and what processes are waiting for locks to be acquired. A good query to start looking for lock problems:<br />
<source lang="SQL"><br />
select relation::regclass, * from pg_locks where not granted;<br />
</source><br />
<br />
== pg_stat_activity view ==<br />
* Figuring out what the processes holding or waiting for locks is easier if you cross-reference against the information in [http://www.postgresql.org/docs/current/static/monitoring-stats.html pg_stat_activity]<br />
<br />
== Сombination of blocked and blocking activity ==<br />
The following query may be helpful to see what processes are blocking SQL statements (these only find row-level locks, not object-level locks).<br />
<br />
<source lang="SQL"><br />
SELECT blocked_locks.pid AS blocked_pid,<br />
blocked_activity.usename AS blocked_user,<br />
blocking_locks.pid AS blocking_pid,<br />
blocking_activity.usename AS blocking_user,<br />
blocked_activity.query AS blocked_statement,<br />
blocking_activity.query AS current_statement_in_blocking_process<br />
FROM pg_catalog.pg_locks blocked_locks<br />
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid<br />
JOIN pg_catalog.pg_locks blocking_locks <br />
ON blocking_locks.locktype = blocked_locks.locktype<br />
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database<br />
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation<br />
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page<br />
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple<br />
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid<br />
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid<br />
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid<br />
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid<br />
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid<br />
AND blocking_locks.pid != blocked_locks.pid<br />
<br />
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid<br />
WHERE NOT blocked_locks.granted;<br />
</source><br />
<br />
=== Here's an alternate view of that same data that includes application_name's ===<br />
<br />
Setting application_name variable in the begging of each transaction allows you to which logical process blocks another one. It can be information which source code line starts transaction or any other information that helps you to match application_name to your code.<br />
<source lang="SQL"><br />
SET application_name='%your_logical_name%';<br />
</source><br />
<br />
<source lang="SQL"><br />
SELECT blocked_locks.pid AS blocked_pid,<br />
blocked_activity.usename AS blocked_user,<br />
blocking_locks.pid AS blocking_pid,<br />
blocking_activity.usename AS blocking_user,<br />
blocked_activity.query AS blocked_statement,<br />
blocking_activity.query AS current_statement_in_blocking_process,<br />
blocked_activity.application_name AS blocked_application,<br />
blocking_activity.application_name AS blocking_application<br />
FROM pg_catalog.pg_locks blocked_locks<br />
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid<br />
JOIN pg_catalog.pg_locks blocking_locks <br />
ON blocking_locks.locktype = blocked_locks.locktype<br />
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE<br />
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation<br />
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page<br />
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple<br />
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid<br />
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid<br />
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid<br />
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid<br />
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid<br />
AND blocking_locks.pid != blocked_locks.pid<br />
<br />
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid<br />
WHERE NOT blocked_locks.GRANTED;<br />
</source><br />
<br />
Note: While this query will mostly work fine, it still has some correctness issues [https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=52f5d578d6c29bf254e93c69043b817d4047ca67], particularly on 9.6.<br />
<br />
=== Here's an alternate view of that same data that includes an idea how old the state is ===<br />
<br />
<source lang="SQL"><br />
SELECT a.datname,<br />
l.relation::regclass,<br />
l.transactionid,<br />
l.mode,<br />
l.GRANTED,<br />
a.usename,<br />
a.query,<br />
a.query_start,<br />
age(now(), a.query_start) AS "age",<br />
a.pid<br />
FROM pg_stat_activity a<br />
JOIN pg_locks l ON l.pid = a.pid<br />
ORDER BY a.query_start;<br />
</source><br />
<br />
For PostgreSQL older than 9.0:<br />
<br />
<source lang="SQL"><br />
select a.datname,<br />
c.relname,<br />
l.transactionid,<br />
l.mode,<br />
l.granted,<br />
a.usename,<br />
a.current_query, <br />
a.query_start,<br />
age(now(), a.query_start) as "age", <br />
a.procpid <br />
from pg_stat_activity a<br />
join pg_locks l on l.pid = a.procpid<br />
join pg_class c on c.oid = l.relation<br />
order by a.query_start;<br />
</source><br />
<br />
= Logging for later analysis =<br />
<br />
* If you suspect intermittent locks are causing problems only sometimes, but are having trouble catching them in one of these live views, setting the [http://www.postgresql.org/docs/current/static/runtime-config-logging.html#GUC-LOG-LOCK-WAITS log_lock_waits] and related [http://www.postgresql.org/docs/current/static/runtime-config-locks.html#GUC-DEADLOCK-TIMEOUT deadlock_timeout] parameters can be helpful. Then slow lock acquisition will appear in the database logs for later analysis.<br />
<br />
= See also =<br />
* [[Lock dependency information]]<br />
<br />
[[Category:Administration]]<br />
[[Category:Performance]]<br />
[[Category:Monitoring]]</div>Saperhttps://wiki.postgresql.org/index.php?title=Lock_Monitoring&diff=33141Lock Monitoring2019-03-14T15:35:39Z<p>Saper: /* Here's an alternate view of that same data that includes an idea how old the state is */ Update query - by Filip Rembiałkowski <filip.rembialkowski@gmail.com></p>
<hr />
<div>{{Languages}}<br />
= Online view current locks =<br />
== pg_locks view ==<br />
Looking at [http://www.postgresql.org/docs/current/static/view-pg-locks.html pg_locks] shows you what locks are granted and what processes are waiting for locks to be acquired. A good query to start looking for lock problems:<br />
<source lang="SQL"><br />
select relation::regclass, * from pg_locks where not granted;<br />
</source><br />
<br />
== pg_stat_activity view ==<br />
* Figuring out what the processes holding or waiting for locks is easier if you cross-reference against the information in [http://www.postgresql.org/docs/current/static/monitoring-stats.html pg_stat_activity]<br />
<br />
== Сombination of blocked and blocking activity ==<br />
The following query may be helpful to see what processes are blocking SQL statements (these only find row-level locks, not object-level locks).<br />
<br />
<source lang="SQL"><br />
SELECT blocked_locks.pid AS blocked_pid,<br />
blocked_activity.usename AS blocked_user,<br />
blocking_locks.pid AS blocking_pid,<br />
blocking_activity.usename AS blocking_user,<br />
blocked_activity.query AS blocked_statement,<br />
blocking_activity.query AS current_statement_in_blocking_process<br />
FROM pg_catalog.pg_locks blocked_locks<br />
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid<br />
JOIN pg_catalog.pg_locks blocking_locks <br />
ON blocking_locks.locktype = blocked_locks.locktype<br />
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database<br />
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation<br />
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page<br />
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple<br />
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid<br />
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid<br />
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid<br />
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid<br />
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid<br />
AND blocking_locks.pid != blocked_locks.pid<br />
<br />
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid<br />
WHERE NOT blocked_locks.granted;<br />
</source><br />
<br />
=== Here's an alternate view of that same data that includes application_name's ===<br />
<br />
Setting application_name variable in the begging of each transaction allows you to which logical process blocks another one. It can be information which source code line starts transaction or any other information that helps you to match application_name to your code.<br />
<source lang="SQL"><br />
SET application_name='%your_logical_name%';<br />
</source><br />
<br />
<source lang="SQL"><br />
SELECT blocked_locks.pid AS blocked_pid,<br />
blocked_activity.usename AS blocked_user,<br />
blocking_locks.pid AS blocking_pid,<br />
blocking_activity.usename AS blocking_user,<br />
blocked_activity.query AS blocked_statement,<br />
blocking_activity.query AS current_statement_in_blocking_process,<br />
blocked_activity.application_name AS blocked_application,<br />
blocking_activity.application_name AS blocking_application<br />
FROM pg_catalog.pg_locks blocked_locks<br />
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid<br />
JOIN pg_catalog.pg_locks blocking_locks <br />
ON blocking_locks.locktype = blocked_locks.locktype<br />
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE<br />
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation<br />
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page<br />
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple<br />
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid<br />
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid<br />
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid<br />
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid<br />
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid<br />
AND blocking_locks.pid != blocked_locks.pid<br />
<br />
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid<br />
WHERE NOT blocked_locks.GRANTED;<br />
</source><br />
<br />
Note: While this query will mostly work fine, it still has some correctness issues [https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=52f5d578d6c29bf254e93c69043b817d4047ca67], particularly on 9.6.<br />
<br />
=== Here's an alternate view of that same data that includes an idea how old the state is ===<br />
<source lang="SQL"><br />
SELECT a.datname,<br />
l.relation::regclass,<br />
l.transactionid,<br />
l.mode,<br />
l.GRANTED,<br />
a.usename,<br />
a.query,<br />
a.query_start,<br />
age(now(), a.query_start) AS "age",<br />
a.pid<br />
FROM pg_stat_activity a<br />
JOIN pg_locks l ON l.pid = a.pid<br />
ORDER BY a.query_start;<br />
</source><br />
<br />
For PostgreSQL older than 9.0:<br />
<br />
<source lang="SQL"><br />
select a.datname,<br />
c.relname,<br />
l.transactionid,<br />
l.mode,<br />
l.granted,<br />
a.usename,<br />
a.current_query, <br />
a.query_start,<br />
age(now(), a.query_start) as "age", <br />
a.procpid <br />
from pg_stat_activity a<br />
join pg_locks l on l.pid = a.procpid<br />
join pg_class c on c.oid = l.relation<br />
order by a.query_start;<br />
</source><br />
<br />
= Logging for later analysis =<br />
<br />
* If you suspect intermittent locks are causing problems only sometimes, but are having trouble catching them in one of these live views, setting the [http://www.postgresql.org/docs/current/static/runtime-config-logging.html#GUC-LOG-LOCK-WAITS log_lock_waits] and related [http://www.postgresql.org/docs/current/static/runtime-config-locks.html#GUC-DEADLOCK-TIMEOUT deadlock_timeout] parameters can be helpful. Then slow lock acquisition will appear in the database logs for later analysis.<br />
<br />
= See also =<br />
* [[Lock dependency information]]<br />
<br />
[[Category:Administration]]<br />
[[Category:Performance]]<br />
[[Category:Monitoring]]</div>Saperhttps://wiki.postgresql.org/index.php?title=User:Saper&diff=32730User:Saper2018-11-18T15:21:46Z<p>Saper: Created page with "Postgres since PostgreSQL 95!"</p>
<hr />
<div>Postgres since PostgreSQL 95!</div>Saperhttps://wiki.postgresql.org/index.php?title=User_talk:Jpurtzwiki&diff=32729User talk:Jpurtzwiki2018-11-18T15:21:05Z<p>Saper: Created page with "Hi Jürgen :) Vielen Dank für Dein Vortrag in Frankfurt! Kennst Du die Seiten von Wikimedia Commons? https://commons.wikimedia.org/wiki/Help:SVG ~~~~"</p>
<hr />
<div>Hi Jürgen :) Vielen Dank für Dein Vortrag in Frankfurt! Kennst Du die Seiten von Wikimedia Commons? https://commons.wikimedia.org/wiki/Help:SVG [[User:Saper|Saper]] ([[User talk:Saper|talk]]) 15:21, 18 November 2018 (UTC)</div>Saper