Jump to content

PostgreSQL Open Source Database Blog

PostgreSQL Open Source Database for the Enterprise

Version Numbering

August 24th, 2010 by Robert Haas

Over the last few days, there’s been a debate raging on pgsql-hackers on the subject of version numbering. There are many thoughtful (and some less-thoughtful) opinions on the thread that you may wish to read, but I thought the most interesting was a link posted by Thom Brown to a blog post called The Golden Rules of Version Naming. If you haven’t seen it, it’s definitely worth a read.

Version Numbering




Anatomy of a FOR loop ? part 3

August 22nd, 2010 by Korry Douglas

In parts 1?and 2 of this series (see here and here), I described many of the (otherwise under-documented) features of the PL/pgSQL FOR loop. ?In this last installment, I’ll show you how to loop over things you may not have realized that you could loop over (my high-school grammar teacher is very offended at that [...]

Anatomy of a FOR loop ? part 3




Anatomy of a FOR loop ? part 2

August 22nd, 2010 by Korry Douglas

In the first part of this series (see here), I discussed the integer-based FOR loop and described some of the nitty-gritty details that you really should know to ensure that your code works properly in a world festering with NULL values. I promised that part 2 (this article) would describe how to loop through the [...]

Anatomy of a FOR loop ? part 2




Why We’re Conservative With PostgreSQL Minor Releases

August 16th, 2010 by Robert Haas

Last week, a PostgreSQL user filed bug #5611, complaining about a performance regression in PostgreSQL 8.4 as compared with PostgreSQL 8.2. The regression occurred because PostgreSQL 8.4 is capable of inlining SQL functions, while PostgreSQL 8.2 is not. The bug report was also surprising to me, because in my experience, inlining SQL queries has always improved performance, often dramatically. But this user managed unluckily hit a case where the opposite is true: inlining caused a function which had previously been evaluated just once to be evaluated multiple times. Fortunately, there is an easy workaround: writing the function using the “plpgsql” language rather than the “sql” language defeats inlining.

Although the bug itself is interesting (let’s face it, I’m a geek), what I found even more interesting was that I totally failed to appreciate the possibility that inlining an SQL function could ever fail to be a performance win. Prior to last week, if someone had asked me whether that was possible, I would have said that I didn’t think so, but you never know…

And that is why the PostgreSQL project maintains stable branches for each of our major releases for about five years. Stable branches don’t get new features; they don’t get performance enhancements; they don’t even get tweaks for things we wish we’d done differently or corrections to behavior of doubtful utility. What they do get is fixes for bugs (like: without this fix, your data might get corrupted; or, without this fix, the database might crash), security issues, and a smattering of documentation and translation updates. When we release a new major release (or actually about six months prior to when we actually release), development on that major release is over. Any further changes go into the next release.

On the other hand, we don’t abandon our releases once they’re out the door, either. We are just now in the process of ceasing to support PostgreSQL 7.4, which was released in November 2003. For nearly seven years, any serious bugs or security vulnerabilities which we have discovered either in that version or any newer version have been addressed by releasing a new version of PostgreSQL 7.4; the current release is 7.4.29. Absent a change in project policy, 7.4.30 will be the last 7.4.x release.

If you’re running PostgreSQL 8.3 or older, and particularly if you’re running PostgreSQL 8.2 or older, you should consider an upgrade, especially once PostgreSQL 9.0 comes out. Each release of PostgreSQL includes many exciting new features: new SQL constructions, sometimes new data types or built-in functions, and performance and manageability enhancements. Of course, before you upgrade to PostgreSQL 8.4 (or 9.0), you should carefully test your application to make sure that everything still works as you expect. For the most part, things tend to go pretty smoothly, but as bug #5611 demonstrates, not always.

Of course, this upgrade path is not for everyone. Application retesting can be difficult and time-consuming, especially for large installations. There is nothing wrong with staying on the major release of PostgreSQL that you are currently using. But it is very wise to upgrade regularly to the latest minor version available for that release. The upgrade process is generally as simple as installing the new binaries and restarting the server (but see the release notes for your version for details), and the PostgreSQL community is firmly committed to making sure that each of these releases represents an improvement to performance and stability rather than a step backwards.

Why We’re Conservative With PostgreSQL Minor Releases




Security Meeting in Maryland

August 14th, 2010 by Bruce Momjian

I have been invited to attend a Postgres security meeting at OmniTI in Maryland on September 8. The meeting will
include other local Postgres community members, government security experts, and contractors.

The goal of the meeting is for everyone to meet in person and to plan for necessary Postgres security additions and secure installation
guidelines. While this will be US-government specific, I expect all work products to be publicly released and available to benefit the
larger Postgres global community.

Security Meeting in Maryland




How I Hack on PostgreSQL

August 13th, 2010 by Robert Haas

Today’s post by Dimitri Fontaine gave me the idea of writing a blog posting about the tools I use for PostgreSQL development. I’m not saying that what I do is the best way of doing it (and it’s certainly not the only way of doing it), but it’s one way of doing it, and I’ve had good luck with it.

What commands do I use? The following list shows the ten commands that occur most frequently in my shell history.

[rhaas pgsql]$ history  | awk '{print $2}' | sort | uniq -c | sort -rn | head 250 git  57 vi  31 %%  25 cd  24 less  20 up  18 make  13 pg_ctl  10 ls   8 psql

Wow, that’s a lot of git. I didn’t realize that approximately half of all the commands I type are git commands. Let’s see some more details.

[rhaas pgsql]$ history  | awk '$2 == "git" { print $3}' | sort | uniq -c | sort -rn | head  93 diff  91 grep  15 log  10 commit   8 checkout   7 add   6 reset   5 clean   4 pull   3 branch

As you can see, I use git diff and git grep far more often than any other commands. The most common things I do with git diff are just plain git diff, which displays the unstaged changes in my working tree (so I can see what I’ve changed, or what a patch I’ve just applied has changed) and git diff master (which shows all the differences between my working tree and the master branch; this is because I frequently use git branches to hack on a patch I’m working on). A great deal of the work of writing a good patch – or reviewing one – consists in looking at the code over and over again and thinking about whether every change can be justified and proven correct.

git grep does a recursive grep starting at the current directory, but only examines files checked into git (not build products, for example). I use this as a way to find where a certain function is defined (by grepping for the name of the function at the start of a line) and as a way to find all occurrences of an identifier in the code (which is an absolutely essential step in verifying the correctness of your own patch, or someone else’s).

As you can also see, my preferred editor is vi (really vim). This might not be the best choice for everyone, but I’ve been using it for close to 20 years, so it’s probably too late to learn something else now. I think Dimitri Fontaine said it well in the post linked above: the best editor you can find is the one you master. Having said that, if you do even a small amount of programming, you’re likely to spend a lot of time in whatever editor you pick, so it’s probably worth the time it takes to learn a reasonably powerful one.

How I Hack on PostgreSQL




Why Is standard_conforming_strings Enabled in Postgres 9.1?

August 9th, 2010 by Bruce Momjian

‘depesz’ already wrote a clear blog
entry
explaining the effect of enabling standard_conforming_strings in Postgres 9.1. My blog entry explains why it will be changed in
Postgres 9.1.

To understand standard_conforming_strings, you have to know some Postgres history. Postgres is written in the
C language, and some C syntax was added to Postgres for convenience. For
example, Postgres supports the C syntax for not-equal, != , as well as the SQL-standard non-equal syntax, <>. This is a good addition
of C syntax because it does not affect SQL-standard-compliant behavior.

Continue Reading »

Why Is standard_conforming_strings Enabled in Postgres 9.1?




Speaking in New York City

August 8th, 2010 by Bruce Momjian

I am speaking at the New York PostgreSQL Meetup Group this Tuesday about the
new features in Postgres 9.0. (It will be similar to a webcast
I did a few months ago.)

I have a few interesting conferences coming up: SURGE (Baltimore),
OpenSQL Camp (Massachusetts),
PgWest 2010 (San Francisco), and perhaps PGDay Europe 2010
(Germany).

Speaking in New York City




Git is Coming to PostgreSQL

August 7th, 2010 by Robert Haas

As discussed at the PGCon 2010 Developer Meeting, PostgreSQL is scheduled to adopt git as its version control system some time in the next few weeks. Andrew Dunstan, who maintains the PostgreSQL build farm, has adapted the build farm code to work with either CVS or git; meanwhile, Magnus Hagander has done a trial conversion so that we can all see what the new repository will look like. My small contribution was to write some documentation for the PostgreSQL committers, which has subsequently been further edited by Heikki Linnakangas (the link here is to his personal web page, whose one complete sentence is one of the funnier things I’ve read on the Internet).

I don’t think the move to git is going to be radical change; indeed, we’re taking some pains to make sure that it isn’t. But it will make my life easier in several small ways. First, the existing git clone of the PostgreSQL CVS repository is flaky and unreliable. The back-branches have had severe problems in this area for some time (some don’t build), and the master branch (aka CVS HEAD) has periodic issues as well. At present, for example, the regression tests for contrib/dblink fail on a build from git, but pass on a build from CVS. While we might be able to fix (or minimize) these issues by fixing bugs in the conversion code, switching to git should eliminate them. Also, since I do my day-to-day PostgreSQL work using git, it will be nice to be able to commit that way also – it should be both faster (CVS is very slow by comparison) and less error-prone (no cutting and pasting the commit message, no forgetting to add a file in CVS that you already added in git).

Git is Coming to PostgreSQL




Database Reliability

August 4th, 2010 by Bruce Momjian

The database is usually a critical piece of infrastructure in an organization; when the database is down, many things stop working, so
database reliability is often a serious concern. While the reliability of database software is important, for Postgres it is often the
infrastructure that Postgres depends on that causes outages, not Postgres itself. We see this regularly on the Postgres email lists.

To get started, a fundamental assumption has to be discarded — that computers are abstract machines and always do what they are told.
While we often treat hardware as abstract devices, in reality they are physical, and are susceptible to failure just like any physical
entity.

Continue Reading »

Database Reliability