PostgreSQL: the good, the bad, and the ugly

Did you know...? LWN.net is a subscriber-supported publication; we rely on subscribers to keep the entire operation going. Please help out by buying a subscription and keeping LWN on the net.

The PostgreSQL development community is working toward the 9.5 release, currently planned for the third quarter of this year. Development activity is at peak levels as the planned feature freeze for this release approaches. While this activity is resulting in the merging of some interesting functionality, including the long-awaited "upsert" feature (described below), it is also revealing some fault lines within the community. The fact that PostgreSQL lacks the review resources needed to keep up with its natural rate of change has been understood for years; many other projects suffer from the same problem. But the pressures on PostgreSQL seem to be becoming more acute, leading to concerns about fairness in the community and the durability of the project's cherished reputation for high-quality software.

The good

The news from PostgreSQL is certainly not all bad, though; the project continues to grow and to add interesting new features. One feature that was recently merged for the 9.5 release fills a longstanding gap: the lack of an "upsert" command.

The SQL standard provides two basic commands for placing data into a table. The INSERT command adds new rows, while UPDATE will modify the data stored in existing rows. What is missing is a way to modify a row if it exists, but to add a new row otherwise; that leads to the need for program logic to check for the existence of the row in question before choosing the correct command to make the change. Many relational database systems contain an "upsert" mechanism that combines INSERT and UPDATE in this way, but PostgreSQL has always lacked this feature.

Adding this functionality is harder than one might expect. One can start with the question of what it means for a row to be already present in a table. A simple answer is that the table has a primary key and that a row containing that primary key already exists, but one could imagine more subtle approaches. Once the problem of defining "presence" has been solved, there is the little problem of dealing with race conditions; the operation as a whole needs to be atomic or bad things will surely result. The challenges involved in getting this feature right have kept it out of PostgreSQL so far.

With 9.5, though, that situation will change — though there is still no UPSERT command. Instead, the new syntax, as implemented by Peter Geoghegan, looks like this:

    INSERT INTO table_name <insert stuff>
    ON CONFLICT conflict_target conflict_action;

The conflict_target is, in its simplest form, the name of a column for which a uniqueness requirement would be violated if the row were to be inserted directly. The conflict_action, instead, can take either of two forms: either DO NOTHING (which causes the insert operation for the conflicting row to be silently aborted) or DO UPDATE, which provides an UPDATE command to execute instead of the INSERT. There are, of course, numerous details that have been skipped over here; see the above-linked document for the full story.

This feature went through a number of cycles of review and change; it was finally committed to the PostgreSQL mainline on May 8. Once the 9.5 release is available, a famous gap in PostgreSQL functionality will be no more.

The bad

On May 9, PostgreSQL developer Robert Haas asked for suggestions toward the solution of some nasty data-corruption issues associated with the "multixact" feature, which is an internal mechanism supporting the use of multiple locks on any given row in order to increase concurrency. There are, it seems, a number details involved with the multixact feature that have not yet been thoroughly worked out. That might be acceptable for a feature planned for the 9.5 release, but, unfortunately, the relevant multixact changes were merged during the 9.3 development cycle. The 9.3 release happened in September 2013, but the fallout from that particular change is still being dealt with.

After Robert and others dealt with the latest round of problems, Bruce Momjian started a discussion on how this particular patch should have been dealt with. He suggested that perhaps the feature should have been reverted, but that didn't happen.

The ensuing discussion made it clear that there is no single thing that can be fixed to avoid the possibility of similar problems in the future. Bruce feels that there are not enough people jumping in to fix bugs when they come around. Joshua Drake pointed his finger at the PostgreSQL release cycle which is, he says, too short and too oriented toward the addition of new features. The one-year cycle used by the project is already long relative to many others, though. Tom Lane noted that the ratio of developers paid to fix bugs to those paid to add new features is too low, making it hard to get a problematic feature fixed. Robert attributed the bulk of the problems to the complexity of the particular patch involved.

Whatever the reason, there is concern within the PostgreSQL community that its well-earned reputation for low bug rates is at risk. That concern carried over into Robert's message on the remainder of the schedule for the 9.5 development cycle. While he is glad to see stuff being pushed in under the deadline, some of it worries him, too:

He noted that he is already worried about some of the patches, upsert among them, that have gone in for 9.5. Bruce, despite his concerns mentioned above, still seems to be more worried about the prospect that interesting features will be left out of 9.5; he has encouraged reviewers to push to get as many of them in as possible. But the mood of the community as a whole appears to be moving in the direction of letting questionable features wait rather than rushing them into the 9.5 release.

The ugly

That mood can be seen in Tom's posting of the remaining open items and his suggested disposition of each, but it turned a bit uglier as people read the list and saw what was proposed. In particular, Tom recommended that the grouping sets feature should be left out this time around. Grouping sets allow for more flexible grouping of results from a query; the feature has been under development for some time.

The specific problem in this case is that Tom had stated some months ago that he would be the one to shepherd that patch set through the review process. His role in the project is such that, once he lays claim to a patch in that way, other developers are unlikely to work on it; why bother, when the patch appears to be in the hands of one of the community's top developers? In this case, though, Tom never found the time to help get the patch into shape for merging; since nobody else did either, the patch languished. When he then said that the patch should not go into 9.5 because it was not ready, that led to charges of unfairness and calls to give the patch another chance.

This episode has made it clear that some developers, at least, are worried about how the development process looks to PostgreSQL contributors. As Josh Berkus put it:

Andres Freund observed that the grouping sets patch poses a relatively low risk to the stability of PostgreSQL as a whole; it is highly unlikely to cause data corruption problems. There have been a number of other patches, he said, that have gone into 9.5 with less review and in worse shape. Keeping this patch out, he said, is likely to worsen the perception of the development community as a whole:

In the end, Andres committed the grouping sets patch on May 16, so this feature will indeed be a part of the 9.5 release, but the incident seems to have left a bad taste in some developers' mouths. Nobody seems to believe that Tom intentionally stalled the patch, but they would have liked to see this story play out in a different way.

In general, it seems that the "commitfest" process used by the PostgreSQL community since the 8.4 release is showing some signs of age. Andres suggested that commitfests are frustrating for both reviewers and contributors while blocking good patches and failing to keep the bad ones out. He suggested a more direct "judgefest" process that would try to reach quick decisions on patches without allowing them to languish, but there has not been a lot of uptake for this idea on the list. This is not the first time the commitfest model has been called into question, of course, but the voices seem to be getting louder.

Development-process issues seem to come to the fore in just about every successful project at some point; a process that worked at one point in a project's evolution may well be unsuited to the next stage. In the end, a project is not just hacking the code; it must also hack the process by which it produces that code. That can make for some messy public discussions, but most projects come out of process-hacking phases as more smoothly functioning, better-tuned machines. PostgreSQL is not working badly now by any objective standard; with luck, its community will find a way to make it work even better.

---

(Log in to post comments)