Monday, March 25

Author: craig.ringer

Help us make a better PostgreSQL 9.3!

Craig's PlanetPostgreSQL
As interest in PostgreSQL grows, so does the rate at which new patches are proposed. To maintain the high level of quality in PostgreSQL it is important that all patches be checked and reviewed, so that what gets added to the codebase is good quality. Some of this evaluation requires a lot of expertise in the PostgreSQL core code, but most of it requires little development experience at all. The more initial checking and review gets done before patches get evaluated by the experts, the less work those experts have to do. So: please step up and review a patch. There are patch review guidelines on the wiki, and it's quite an accessible process. Step right up if you want to contribute a little back to the software you use and rely on, or if there's a particular enhancement you want to make (more…)

Testers needed for proposed 9.3 SEPostgreSQL enhancements

Craig's PlanetPostgreSQL
SELinux / SEPostgreSQL users: There are some proposed improvements in the 2013-01 commitfest that might go into PostgreSQL 9.3 - but only if you help. Interested users are needed to try out the following patches and report back with their experiences if you want to see these changes in 9.3: The patches are: Add a new event type of object_access_hook named OAT_POST_ALTER. This allows extensions to catch controls just after system catalogs are updated. Patch also adds sepgsql permission check capability on some ALTER commands, but not all. This patch adds sepgsql support for permission checks equivalent to the existing SCHEMA USE privilege: This patch (more…)

PostgreSQL regression tests hanging on Windows? Check path depth.

Craig's PlanetPostgreSQL
I just confirmed the cause an extremely weird problem that's been frustrating me for days. I want to share it so nobody else has to waste their time on this. It appears that - at least on my build machine, a Windows 7 SP1 x64 box with Windows SDK 7.1, Visual Studio 2010 Express SP1 and Visual Studio Express 2012 on it - vcregress check will hang indefinitely with a postgres.exe process sitting at 100% cpu if the regression tests are run in a path that is too deep. This seems to happen with both x64 and x86 builds. git.exe seems to have a similar problem, where a git clean -fdx in a deep directory tree will sit at 100% cpu forever, making no progress. In both git.exe's and postgres.exe's cases, Process Monitor shows a steam of QueryNameInformationFile events with result BUFFER OVERFLOW. QueryNameInformationFile is the IRP_MJ_QUERY_INFORMATION operation of ZwQueryInformationFile as documented in MSDN here. It's a kernel-level operation. I'm yet to determine the root cause of the issue. To work around the problem, build in a shallower directory tree. I've included a bunch of details after the cut, primarily to help anyone else with this problem find this post. (more…)

Simplifying compilation of PostgreSQL on Windows

Craig's PlanetPostgreSQL
As part of some internal continuous integration and testing work, I've put together some scripts to simplify the compilation of PostgreSQL on Windows. PostgreSQL its self is pretty easy to compile on Windows. You download and install ActiveState Perl and Visual Studio or the Microsoft Windows SDK, unpack a PostgreSQL source tree, copy to src\tools\msvc\, edit it to reflect your environment, open an SDK command prompt for your Windows SDK version / Visual Studio version, cd to the PostgreSQL directory, and run src\tools\msvc\ Not too bad. The trouble is getting the dependencies built, and that's what I'm working on improving. The scripts I've published at are a step toward that. I've written some NMake (more…)

During installation, cluster initialisation fails with the message “No error” on Windows

Craig's PlanetPostgreSQL
Today I ran into another strange issue with PostgreSQL installation on Windows. It turned out not to be a problem with the installer; instead it was a form of broken Windows installation that I hadn't seen before, so I thought I'd write it up. The installer already contains checks for several kinds of broken Windows install. For example, it tests to make sure that %TEMP% is writeable, and to makes sure the vbscript interpreter actually works. These were both the cause of frequent problem reports to the mailing lists in the past. This is a less common issue, though it's clearly turned up in the wild before, as shown by this report and this one. It turns out that some - probably rare - Windows installs have an incorrect %COMSPEC% environment variable. This causes popen to fail with the wonderfully useful error message: "No error" when initdb tries to execute the PostgreSQL backend. The message displayed to the user is: Problem running post-install step. Installation may not complete correctly. The database cluster initialisation failed. ... which can be caused by several different issues, of which this is only one. (more…)

Improving PostgreSQL performance on AWS EC2

Craig's PlanetPostgreSQL
Questions periodically come up on the PostgreSQL mailing list regarding Amazon EC2 and how to get PostgreSQL to perform well on it. The general feeling on the list appears to have been that EC2 performs very poorly for database workloads, at least with PostgreSQL, and it doesn't seem to be taken particularly seriously as a platform. I certainly thought of it as a last resort myself, for when other constraints prevent you from using a proper VPS or real hardware. I had the chance to meet with a high level AWS support engineer last week. It's prompted me to write up the basics of configuring EC2 instances for decent PostgreSQL performance. I haven't had the chance to back the following advice with hard numbers and benchmarks yet, so remember: Always test everything with a simulation of (more…)