Dave's Postgres Blog

Updated PostgreSQL Download Infrastructure

2011-12-21T15:01:00.002Z

Back at the tail end of November, the PostgreSQL sysadmin/web team successfully migrated the PostgreSQL website to a new platform, based on PostgreSQL, Django, Lighttpd, and Varnish, to replace the old somewhat complex and messy PHP platform used in the past. Functionally and visually the website is almost identical to what it was, though the behind-the-scenes management interface is now vastly improved, as is the "Your Account" section which now offers users much more control over their submissions like news and events etc.

One other change that went largely unnoticed however was in the downloads section of the website, specifically the FTP area. This is a web interface over the content on the PostgreSQL FTP site, ftp.postgresql.org, that gives users a nice way to browse the files and directories on the site. On the old website, when the user clicked to download a file they would then be taken to a page of flags where they could select a mirror site to download the file from. The mirrors were all third party servers over which we had no control, aside from an automated system to ensure we only listed those which had content which was no more than 48 hours out of date. This arrangement made sense years ago when bandwidth was more precious, however with the bandwidth available to us these days it's really just clunky and inconvenient for users to have to choose one of 75 flags to reach a server that may not be entirely up to date.

To resolve this, as the new website infrastructure went live we also pushed a new download infrastructure into production. What you'll see now is direct links to files on download servers we run ourselves, from the website. This gives us a number of advantages:

The user has a much slicker experience when downloading, both in terms of the workflow, and often the speed of downloads (because some of the old mirrors were much faster than others).
We can push out files to the download servers in minutes, rather than days.
We can collect meaningful statistics to help us understand what users are downloading.
We can automatically (and invisibly) disable download servers in the event of problems, within minutes.

So what does this mean to the end user? Well, for a user coming from the website downloads take fewer clicks and avoid the "flags" page which could be daunting and potentially confusing. For those using the FTP site directly, there are a number of changes:

The site can be accessed at ftp://ftp.postgresql.org/, rather than using one of the individual mirror hostnames we used in the past.
Because there are no third party mirrors, there are no inconsistent paths to the content (one mirror in the past may have had content under /pub/mirrors/postgresql/ whilst another may have used /u/postgres/).
We can now offer downloads over HTTP using the same paths as FTP - you can use http://ftp.postgresql.org/pub/... or ftp://ftp.postgresql.org/pub/...
We've also opened up RSYNC access to all users, where previously only registered mirror sites could rsync the downloads from us: rsync://ftp.postgresql.org::pgsql-ftp.

Because there are lots of links to files on the old mirror network on the web, we've left the old mirror hostnames in place for the time being (though they are no longer being monitored), however they will be phased out over time.

Happy downloading!

To upgrade or not to upgrade? That is the question.

2011-10-11T15:38:00.000+01:00

On a fairly regular basis the Postgres community hear from users who are complaining of bugs in old versions of Postgres - they'll post a bug report or a request for help on one of the mailing lists saying something along the lines of having run into an issue with PostgreSQL 8.4.2 for example, and immediately be met with suggestions to upgrade to the latest version because there have been 37 bug fixes and 5 security issues resolved since 8.4.2 was released.

Generally this happens with one of two types of users. There are those that just don't bother to upgrade (who we can do little about), and those that don't upgrade because they are concerned that changes to PostgreSQL will break their application. This latter class of user is sometimes also restricted by what they can do by corporate policies in their workplace.

The Postgres developers are mindful of this issue and have practices in place to allow users to safely upgrade without significant risk of behavioural changes breaking their applications. The practice is really quite simple; minor upgrades of PostgreSQL never include new features.

What does this mean in practice? Well, PostgreSQL version numbers are in 3 parts, X.Y.Z (some packages such as the EnterpriseDB installers also add a build suffix):

X.Y: This is the major version number of the server, for example, 8.4, 9.0 or 9.1. New major versions may include new functionality, require upgrades to the database files on disk and generally require thorough testing of applications prior to deployment.

Z: This is the minor version number. This number is increased for bug fixes releases, also known as "point releases". These releases never include new features; only carefully applied bug fixes. Point releases are fully compatible with previous point releases of the same major version and should require minimal testing prior to deployment on existing installations.

Returning to our opening example, the user in this case is not being told to upgrade to 9.1.1 - the latest and greatest release at the time of writing, complete with a myriad of new features and changes from 8.4.2 - but to 8.4.9, the latest point release in the 8.4 series, which is functionally identical to 8.4.2. This numbering scheme and the processes behind it are specifically designed to allow users to safely and easily upgrade their database servers to minimise the number of known bugs in the software; in fact the PostgreSQL developers consider not upgrading to the latest point release to be riskier than upgrading.

So next time you're "certifying" your application with PostgreSQL, aim to certify it with a specific major version of the server, and avoid getting into a situation where you prevent yourself from updating to the latest point release as doing so can cause more problems than it can solve, and if you're a sysadmin or DBA rest assured that point releases won't introduce functionality changes and should be welcomed and installed as soon as possible!

StackBuilder Package Updates

2011-10-05T17:31:00.002+01:00

If you've ever used one of the PostgreSQL installers for v8.2 or above, either the old Windows MSI installer or the newer "one click" installers that also support Linux and Mac, you'll probably have come across StackBuilder. For those that haven't or those that never found the time, StackBuilder was introduced with the PostgreSQL 8.2 installer to allow us to distribute different components of PostgreSQL independently of the server itself. Originally in 8.0/8.1, the installer included lots of additional products, such as the ODBC, JDBC, OLEDB and .NET drivers, Slony and PostGIS. As you can imagine, this proved near impossible to maintain as we needed to try to coordinate the release of products from multiple independently run projects.

StackBuilder was the solution to this. The installers were cut down to essentially include just the PostgreSQL server, pgAdmin and StackBuilder, which allowed us to provide all the other components on independent release schedules. It also gave us a vehicle to encourage adoption of PostgreSQL by other Open Source projects, by including packages for them as well, so with a few mouse clicks a user could be up and running with a "stack" like Drupal, Apache, PHP and PostgreSQL on their OS of choice. We made a conscious decision to include software other than well known Open Source products in the StackBuilder catalog as well, with the aim of giving the user as much choice of product to use with PostgreSQL as possible - as a result we have products in the catalog from multiple vendors and projects, as well as the "PostgreSQL family" projects. If you're interested in having a PostgreSQL related product included in the catalog, please contact me to discuss how we can make that happen.

So, with the refresher course out of the way, todays blog post is prompted by the latest round of updates that I've been adding to the catalog - we've got 42 updates to the Open Source packages, as well as 18 new packages, including for the first time, pgBouncer, pgMemcache and Drupal 7.

Updates:

Apache/PHP 2.2.20-5.3.8: Linux32, Linux64, Mac, Win32
mediaWiki 1.17.0: Linux32, Linux64, Mac, Win32
pgJDBC 9.0-801: Linux32, Linux64, Mac, Win32
phpBB 9.0-801: Linux32, Linux64, Mac, Win32
PostGIS 1.3.6 for PG 8.3: Linux32, Linux64, Mac
PostGIS 1.4.2 for PG 8.4: Linux32, Linux64, Mac
PostGIS 1.5.3 for PG 9.0: Linux32, Linux64, Mac
psqlODBC 09.00.0310: Linux32, Linux64, Mac, Win32
Slony 1.2.22 for PG 8.3: Linux32, Linux64, Mac
Slony 2.0.7 for PG 8.4: Linux32, Linux64, Mac
Slony 2.0.7 for PG 9.0: Linux32, Linux64, Mac
Npgsql 2.0.11: Linux32, Linux64, Mac, Win32

New releases:

Drupal 7: Linux32, Linux64, Mac, Win32
pgBouncer: Linux32, Linux64
pgMemcache 2.0.1 for PG 9.0: Linux32, Linux64, Mac
pgMemcache 2.0.1 for PG 9.1: Linux32, Linux64, Mac
PostGIS 1.5.3 for PG 9.1: Linux32, Linux64, Mac
Slony 2.0.7 for PG 9.1: Linux32, Linux64, Mac

Note that the PostGIS community maintain the PostGIS installers for Windows which will be released as soon as they're available. Windows updates for Slony are still in development due to an issue found in QA. Mac and Windows builds of pgBouncer are on their way.

To download and install any of these packages, just run StackBuilder - if you don't have it already you can get it with the PostgreSQL Installers.

Enjoy :-)

PostgreSQL Conference Europe: Are you ready?

2011-10-04T09:11:00.003+01:00

PostgreSQL Conference Europe 2011 starts 2 weeks from today in the beautiful city of Amsterdam in the Netherlands. This is the fourth annual conference hosted by PostgreSQL Europe, following on from extremely successful events in Prato (Italy), Paris and Stuttgart, and is aimed at developers, DBAs, technologists and decision makers either using, or considering using the world's most advanced Open Source database.

This year we have four days on the schedule, with a kick-off day of training sessions hosted by respected PostgreSQL developers such as Greg Smith, Bruce Momjian, Magnus Hagander, Guillaume Lelarge and more. Topics will cover performance tuning, application development, database administration, replication & high availability and geospatial. The training sessions are available on their own, or as part of a regular conference attendance at additional - but very reasonable - cost.

We had a record number of talk proposals submitted this year but we've resisted the urge to host even more sessions in parallel - in fact we've reduced the number of parallel sessions to three as we all know how frustrating it can be when more than one that you want to see are at the same time. Instead we've extended the conference by a day to accomodate over 40 different sessions, which has the added bonus of allowing an additional night of social activities - always a great way to discuss the latest technologies, trends and ideas with other Postgres users over a beer or two.

We've got a great range of topics for this year, covering new features in PostgreSQL 9.1 and beyond, developing applications, running Postgres in the cloud, hacking PostgreSQL internals, tools and add-on products and managing large databases, presented by a wide cross-section of users and developers, including a number of this year's Google Summer of Code students who will talk about their work. You can view the complete schedule on the conference website.

Our opening keynote this year will be presented by Ram Mohan, EVP and CTO of Afilias who manage the .info, .org and .mobi top level domains using Postgres. Ram will be discussing the business decisions and strategy around their use of PostgreSQL. Our closing keynote will be presented by Ed Boyajian, President and CEO of EnterpriseDB who will discuss PostgreSQL's role in the post-Oracle era.

So, if you haven't done so already, head on over to the website to register as an attendee to avoid missing out on what promises to be an outstanding conference in an fantastic location. See you in Amsterdam!

PostgreSQL 9.1, meet MySQL

2011-08-01T16:57:00.007+01:00

So having spent the last few months with my head buried deep in a project at work, I finally managed to get back to my previous hacking on SQL/MED at the weekend after the kids went away for a week (good $DEITY it's quiet here)! Within a couple of hours, I had my half-baked Foreign Data Wrapper for MySQL up and running, and am now able to create foreign table objects in PostgreSQL 9.1 that map either directly to tables in a MySQL database, or to queries run on the MySQL server.

Here's an example:

-- Create the required functions for the FDW.
CREATE FUNCTION mysql_fdw_handler()
RETURNS fdw_handler
AS '$libdir/mysql_fdw'
LANGUAGE C STRICT;

CREATE FUNCTION mysql_fdw_validator(text[], oid)
RETURNS void
AS '$libdir/mysql_fdw'
LANGUAGE C STRICT;

-- Create the data wrapper or "transport".
CREATE FOREIGN DATA WRAPPER mysql_fdw
HANDLER mysql_fdw_handler
VALIDATOR mysql_fdw_validator;

-- Create the foreign server, a pointer to the MySQL server.
CREATE SERVER mysql_svr
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (address '127.0.0.1', port '3306');

-- Create one or more foreign tables on the MySQL server. The first of
-- these maps to a remote table, whilst the second uses an SQL query.
CREATE FOREIGN TABLE employees (
id integer,
name text,
address text)
SERVER mysql_svr
OPTIONS (table 'hr.employees');

CREATE FOREIGN TABLE ex_staff (
id integer,
name text,
address text)
SERVER mysql_svr
OPTIONS (query 'SELECT * FROM hr.employees WHERE date_left IS NOT NULL');

-- Create a user mapping to tell the FDW the username/password to

-- use to connect to MySQL, for PUBLIC. This could be done on a per-

-- role basis.

CREATE USER MAPPING FOR PUBLIC

SERVER mysql

OPTIONS (username 'dpage', password '');

So let's try it out. Here's the test table in MySQL:

mysql> SELECT * FROM employees;

+----+-------------+----------------------------+------------+

| id | name        | address                    | date_left  |

+----+-------------+----------------------------+------------+

|  1 | Dave Page   | 27 High Street, Georgetown | NULL       |

|  2 | Fred Bloggs | 46 Mill Road, Klasborough  | NULL       |

|  3 | Fred Bloggs | 24 The Wharf, Westhampton  | 2010-05-23 |

+----+-------------+----------------------------+------------+

3 rows in set (0.00 sec)

And here we are in PostgreSQL 9.1 beta 3:

postgres=# SELECT * FROM employees;

 id |    name     |          address           

----+-------------+----------------------------

  1 | Dave Page   | 27 High Street, Georgetown

  2 | Fred Bloggs | 46 Mill Road, Klasborough

  3 | Fred Bloggs | 24 The Wharf, Westhampton

(3 rows)

postgres=# SELECT * FROM ex_staff;

 id |    name     |          address          

----+-------------+---------------------------

  3 | Fred Bloggs | 24 The Wharf, Westhampton

(1 row)

For the curious, here's what the EXPLAIN output looks like:

postgres=# EXPLAIN SELECT * FROM employees;

                           QUERY PLAN                           

----------------------------------------------------------------

 Foreign Scan on employees  (cost=10.00..13.00 rows=3 width=68)

   Local server startup cost: 10

   MySQL query: SELECT * FROM hr.employees

(3 rows)

Pretty neat huh? There are a couple of limitations in the current implementation:

No attempt is currently made to push down quals (WHERE clauses) to the MySQL server, so every row MySQL finds is returned to PostgreSQL and filtered there. There's no defined API for this in PostgreSQL yet, and it's not immediately clear how to build something more complex than the simple example I used in my Redis FDW that would be required for a remote relational database. That said, you can build WHERE clauses into the foreign table definition of course.
The MySQL C API doesn't seem to offer a simple way to either randomly access a result set, or at least reset the cursor to the first row, unless you copy the entire resultset to the client (PostgreSQL in this case). Because we need to be able to return to the first row if PostgreSQL calls the Rescan function, we therefore currently copy the entire resultset, rather than reading it from the server, on demand.

Aside from minor tweaks, this is probably about as far as I'll take this little project for now. I'll be talking about it at both Postgres Open 2011 in Chicago in September, and PGBR 2011 in São Paulo in November - hopefully I'll see you there.

The MySQL FDW source code is available on Github, and uses the PostgreSQL licence.

Google Summer of Code 2011

2011-04-06T17:14:00.000+01:00

As a reminder, Google's Summer of Code 2011 program has started and PostgreSQL is participating! We are looking for students who are interested in hacking on pgAdmin or PostgreSQL all summer. You only have until April 8, 2011 at midnight Pacific time to submit a proposal.

If you are a student, or know a student, or are a professor, please urge your students to participate in Summer Of Code. Not only is it a fantastic opportunity to learn how open source code is really made, and get paid to do it, but it can also be career-building.

Read up on the PostgreSQL GSOC wiki page.

Tinkering with SQL/MED

2011-04-01T15:45:00.002+01:00

One of the cool new features in the upcoming PostgreSQL 9.1 release is support for SQL/MED, or Management of External Data. Essentially what this allows us to do is connect external data sources to PostgreSQL and query them as if they were tables. The initial support in 9.1 will offer read-only support for foreign tables, with the ability to include qualifier pushdown - i.e, the ability to push the applicable WHERE part of the SQL query to the remote server, to minimise data transfer. This optimisation is dependent on each individual Foreign Data Wrapper (FDW) - the driver used to connect to the remote data source - which may or may not implement it.

PostgreSQL 9.1 will ship with an FDW that allows access to files in formats that can be read by the existing COPY command, and Andrew Dunstan has been working on an FDW that will allow access to files with fixed-width data as well as one for accessing ragged CSV files.

I've been a big proponent of SQL/MED support, particularly at work, so was pleased to finally find some time to look at the work that's been done for 9.1 earlier in the week. I don't have much interest in reading external files (though clearly that is an important feature for some users) - I'm far more keen to see PostgreSQL have the ability to access the data in all those other databases you may not be able to migrate away from, or are better suited to particular workloads. So, with that in mind, I picked a NoSQL database more or less at random (though intentionally one that's a simple key/value store) and started hacking on an FDW for Redis.

The code is still pre-alpha quality, and there are a number of TODOs, including:

Add qual pushdown
Handle different Redis datatypes correctly
Figure out how to make Redis scans atomic (or at the least properly handle tuples that have been removed since the initial scan)
Handle Redis authentication

However it seems to work well (bar the known issues of course), and is looking like it will be simple to use as the basis for more complex FDWs which may well keep me amused for a while longer.

As always with Open Source, feel free to play with this if you think you might find it useful, but don't blame me if it breaks (which it likely will)!

Debunking the FUD: PostgreSQL for Microsoft Windows Payload Execution

2011-03-24T15:45:00.002Z

If you follow Twitter for keywords like "postgres" or "postgresql", you may well have seen a number of tweets over the last day or so regarding a so-called "Payload Execution" exploit in PostgreSQL. This supposed attack was apparently first described in this paper, and has hit Twitter after code to demonstrate the issue was added to Metasploit.

The "attack" works like this:

A user uploads a payload as a large object to the database. This can be done using the client side lo_create() and lo_import() functions.
The user then uses the server-side lo_export() function to export the payload to the server's filesystem.
The user then executes the payload, by creating an SQL level function to wrap the C function in the payload, eg:
CREATE FUNCTION do_bad_stuff() RETURNS int4 AS '$libdir/bad_stuff.dll' LANGUAGE C;
The user then executes the payload:
SELECT do_bad_stuff();

Here's the problem with the "exploit":

You need to be a superuser for it to work.

That's right - steps 2 and 3 above both require that you are a superuser - in other words, you already have complete, unfettered access to the database server.

But wait - that doesn't get you very far into the OS either! One oft-criticized feature of PostgreSQL (for users that unwisely like to do all their work as root) is that it refuses to run under an account with superuser privileges - in fact on Windows, we irrevocably remove unwanted privileges from our security token at startup. The reason for this, is to ensure that once our L337 H4X0r has uploaded his payload (using his existing superuser privileges), he cannot escalate those privileges to the operating system's superuser accounts and compromise other parts of the system.

As a side note - as superuser in most installations there are far easier ways of achieving the results above that avoid the need to know the architecture of the system or to write code in C - for example, you can simply create a function using one of the untrusted procedural languages, like pl/perlu.

So the bottom line is, this is not an exploit - it requires that you are already a database superuser with all the power that entails, and doesn't gain you anything you didn't already have the ability to do, or give you a way to gain OS level privileges beyond those already held by the low-privilege user account the server runs under.

Just 2 days left to register for PGDay.EU 2010

2010-11-24T11:38:00.000Z

Yup, you heard correctly - there are just two (and a bit) days left to register for the annual European PostgreSQL Conference, pgDay.EU 2010, being held in Stuttgart on December 6th and 7th, with a day of training sessions on the 8th.

With over 40 talks in a mix of English and German, this is an event not to be missed if you're a PostgreSQL user, developer, hobbyist, or are considering a deployment. There are a wide range of topics including talks on GIS, interoperability and migration, high availability and monitoring, business around PostgreSQL and case studies, as well as more academic topics.

On day three we have a number of training courses available, including a two part course on PostGIS, presented by one of the leading developers, Mark Cave-Ayland from Sirius, deployment of applications in the Cloud with Servoy presented by Robert Ivens from Roclasi, and a two part PostgreSQL administration course (in German) given by Andreas Scherbaum for EnterpriseDB.

Finally, as attendees from previous PGDay's will have come to expect, EnterpriseDB will be hosting a party for everyone on Monday night - definitely not one to miss!

So, talk to the boss, fill out those pesky travel requisition forms, and head on over to the registration page!

PGWest: Day 3

2010-11-06T05:54:00.000Z

PGWest; the final day. It started (for me at least) with Greg Smith giving another excellent talk on scaling PostgreSQL with Hot Standby. This was followed by Robert Hodges and Linas Virbalas talking about replication of data from MySQL to PostgreSQL using Continuent Tungsten.

The highlight/lowlight of the day - depending on your viewpoint - was Rob Wultsch's talk on MySQL. Rob primarily looked at some of the things MySQL does better than PostgreSQL, and also talked about the forks (or lack of them, if you discount patchsets - which I personally, do not) and the FUD. Now lets be honest here - Rob did make some perfectly valid points about MySQL; for example, the fact that it's replication is easy to setup. Now to take this example, I would argue that PostgreSQL isn't that hard to get going either - Robert Haas' tutorial illustrates that nicely - but MySQL is arguably better at the moment. For most of the points he raised, there are easy counter-arguments that can be made by PostgreSQL, as shown by JD who made a number of us cringe a little with an impromptu rebuttal session afterwards.

In my view, this whole session was doomed to failure. It's fine to point out some of the things that PostgreSQL can learn from MySQL, but the session as given glossed over everything that PostgreSQL can do but MySQL cannot - which, to someone unfamiliar with PostgreSQL could give an incorrect impression. For the end user who is selecting a database, it is important not to choose a product based on whether some features are better implemented in one database or another, but to choose based on the quality of the products, the reliability of them, and the availability of the features you actually need.

On reflection, I think the only way we can tackle this sort of comparison fairly in a talk session, is to have a proper, moderated debate between a PostgreSQL expert, and a MySQL expect. Who knows, maybe we can do that for PG East or PG Europe 2011.

Regardless of that - kudos to Rob for having the bottle to stand up and talk about MySQL in front of a room of PostgreSQL users. It was never going to be an easy crowd.

After lunch, I did a little work for a while, and then toddled off to Rob Treat's presentation on PostgreSQL 9.0: The other stuff. We joked the night before that no-one knew what The stuff was, never mind The other stuff, but I guess once he knuckled down to his slides, he realised it meant Hot Standby and Streaming Replication. The talk was pretty good in the end, though it did remind me a little of my talk in Brussels earlier this year, which could have been entitled PostgreSQL 9.0: The stuff, and the other stuff!

And that's where the conference ended for me, as we took the opportunity to hold an EnterpriseDB meeting whilst a we were in the same city. All in all, an excellent show, with a great turnout - and to top it off, The Register took notice of us with two different stories - which somehow makes it feel all worthwhile.

Booked for PGDay.EU 2010 yet?

2010-11-04T16:42:00.000Z

If you're planning on attending this year's European PostgreSQL conference (increasingly inaccurately known as PGDay!), then you might want to think about registering and booking your travel and accommodation now. It's just over a month until the conference, and isn't uncommon for the price of flights and trains to start to rise as the date gets nearer.

We have 42 sessions this year, with a wide variety of PostgreSQL talks in English and German, followed by a day of training sessions, covering PostgreSQL Administration, PostGIS, Hot Standby and using Servoy with PostgreSQL. Places on the training sessions are limited, and available on a first come, first served basis at a (relatively small) extra cost.

We have two guest keynote speakers: Simon Phipps is giving the opening keynote, and will be giving a talk entitled "Back To The Future of Open Source", looking issues around corporate involvement in Open Source projects, and what that means for the PostgreSQL community and contributors. Simon has been involved in many Open Source projects over the years, and was most recently known for his role at Sun Microsystems as Chief Open Source Officer.

The second, closing keynote will be given by Ed Boyajian, President and CEO of EnterpriseDB. Ed will be talking on "PostgreSQL's time to shine", and looking at how we as users and contributors to PostgreSQL have the opportunity to turn the $26B per year database industry on it's head.

This year's event will be held at the luxurious Millenium Hotel in Stuttgart, Germany, with the conference sessions on the 6th and 7th December, and training on the 8th December. We have a discount group rate for hotel bookings that includes internet access and breakfast.

Registration for the conference days and the training can be made online. We encourage you to register as early as possible to help us plan the event more effectively.

For more information on the conference, please visit the website.

PGWest: Day 1 and 2

2010-11-04T00:27:00.000Z

So my blogging of PGWest was a FAIL on the first day, as I never got around to following up my Day 0 post with anything, so with apologies, here's a quick roundup of day 1 and day 2.

Day 1

Started with breakfast with Magnus, Devrim and Guillaume before heading up to register on the mezzanine. The first half of the day was a number of three hour tutorials which were on some interesting topics, but none which particularly interested me, so I spent the time catching up with a number of colleagues who I haven't seen in a few months.

After lunch, my talk on "Securing your web application" was one of the first 'normal' talks to be given. It was intended as a wide but shallow look at some of the security issues to consider when building a web app - a completely new talk which unfortunately didn't work as well as I'd hoped and needs some tweaking should I give it again; trimming the length a little, and focusing a little more on the database end of the stack. Still, I think it covered most of the important points for new developers to consider.

After my talk, I spent more time with colleagues, including $BOSS who had arrived. I completely failed to see any more talks unfortunately.

In the evening, we had a quick drink (thanks SFPUG!) before heading off for dinner at an Irish pub, and then to the Starlight Room at the top of the hotel for a couple of drinks and dessert, coupled with lots of discussion on covering indexes (aka index-only scans), managing community workload and more.

Day 2

Day 2 started with JD herding a handful of us together at breakfast to go and give Josh Berkus some encouragement in his talk on the PostgreSQL Community. An excellent talk for the newcomers in the room, though a little wasted on those of us who have been around the community for 10+ years.

Highlight of the day was Scott McNealy's keynote, introduced by Ed Boyajian ($BIG_BOSS at EnterpriseDB). In case you've been living under a rock, you'll doubtless know that Scott was the founder and boss of Sun Microsystems until the Oracle takeover. The talk style was a little deadpan, but with a good stream of jokes that went down well with the audience. Interesting to hear Scott note that he was considered a good capitalist, whilst Larry Ellison is a great capitalist (who doesn't like to share)!

Scott's talk was followed up with JD's introduction to the conference (yeah, halfway through the schedule - really Josh?), and then lunch. After a quick EnterpriseDB training meeting, I caught Jimbo's talk on GridSQL, Magnus' on database driven cache invalidation with Varnish (which we'll be using with the new PostgreSQL website backend, so I figured I should learn how it works), and then finally Bruce's new talk, MVCC Unmasked. Pretty complex for the newbie I suspect, but a very well presented topic.

And on that note it's just about time to go back up to the Starlight Room, where the EnterpriseDB party is about to kick off with an open bar. I'll try not to get too hungover so I can write about day 3 tomorrow....

PGWest: Day 0...

2010-11-02T14:46:00.000Z

... or day 1, depending on how you look at it. Anyway, mostly yesterday, the day before the conference starts. Which is today. Obviously. $DEITY I hate jetlag-induced early mornings, especially when coupled with reminders for meetings on the East coast that go "ping" right as I'm finally drifting off to sleep again at 6AM.

Anyway, enough about that. The flight from LHR to SFO was mostly uneventful, barring an APU failure which meant the HVAC was barely working until we took off. It got a little hot, but otherwise everything was good, and being completely cut off from email, twitter and IM for nearly 11 hours meant that I managed to get a bunch of work done that's been piling up for ages and watch the A-Team.

Immigration at SFO was remarkably fast (I've only seen similar speeds in the past in Boston), which gave me plenty of time to queue up with a bunch of other passengers for over 10 minutes before a single cab showed up. What the *$£% is that about?

Made it to the hotel, which took a little longer than expected due to the World Series (I'm sure I won't be the first, or the last to note that that's a really misleading name) to find a whole gaggle of elephant herders in the lobby, with beer. Oh, and one dolphin botherer (hi Rob!). Dinner was at a small, but excellent Indonesian place on Post and Jones where Greg Stark showed up from work to join myself, Aurynn Shaw, Bruce Momjian, Magnus Hagander, Jim Nasby, Rob Wultsch and Guillaume Lelarge.

After dinner, a few of us stopped in the hotel bar for a night cap before heading off for some Zzzz's.

Today the conference starts in earnest of course. Breakfast in 20 minutes, and then at 9AM, a bunch of 3 hour tutorials. After lunch, my talk "Securing your web application" (and overview for beginners) is up, then I have the rest of the conference to see what I like.

So, there you have it. A couple of hundred largely useless words that spilled out of my brain having cleared out my "Starred Items" at 5AM leaving me nothing more interesting to do without committing more hours than I have. With any luck, tomorrow's roundup will be somewhat less of a waste of bytes, though someone did mention beer so I wouldn't hold your breath....

PGDay Europe 2010 schedule announced

2010-10-25T18:16:00.001+01:00

I'm pleased to be able to say that the schedule for this year's PGDay Europe conference in Stuttgart, Germany on the 6th - 8th December 2010 is now available.

We have a bumper crop of talks this year, with 13 sessions in German, 26 in English, 2 keynotes and a key signing party!

We're pleased to welcome Free and Open Source veteran, Simon Phipps as the presenter of our opening keynote. Simon is well known for his involvement with various organisations including GNOME, OpenSolaris and OpenJDK and will be talking about some of the changes we've been seeing in the Open Source world recently as projects evolve and corporate involvement changes. He is perhaps best known for his role as Chief Open Source Officer at Sun Microsystems where he oversaw the companies Open Source activities.

Of course, we also have some great technical talks lined up for developers, DBAs and people considering using or deploying Postgres, on topics such as PostGIS, application development, PostgreSQL internals, clustering and replication and more all in the comfortable surroundings of a top hotel.

For the first time we've also added a day of training sessions to the schedule this year (at extra cost). Training will be available on PostgreSQL administration, application development and PostGIS, with some courses in German and some in English.

As always, we reserve the right to adjust the schedule in the event of unforeseen circumstances.

So, if you haven't done so already, head on over to the website and register your attendance at the conference. You won't regret it!

See you in Stuttgart!

PGDay.EU 2010 Call for Papers ending soon!

2010-10-05T14:08:00.001+01:00

This years European PostgreSQL Conference is due to be held on the 6 - 8th December 2010 in Stuttgart Germany. Following on from last years extremely successful event in Paris, we hope to make the show bigger and better than before, with:

A 4 star hotel venue
A special guest keynote speaker (to be announced Real Soon Now :-) )
Multiple talk tracks in English and German
Talks for PostgreSQL hackers and users, as well as decision makers

Of course, to make that happen we need talks proposals from our community members and users, so if you can make it to Germany in December, please take a look at the call for papers page on the website, and send in your proposal before the 11th October!

See you in Stuttgart.

Cleaning up the Oracle mess

2010-07-30T09:50:00.001+01:00

I've been caught off-guard by the number of news stories that have been posted about Oracle turning off their Solaris boxes that participated in the PostgreSQL Buildfarm and the fact that I've been working to replace them. I think most of us in the community assumed it was going to happen sooner or later - Sun were contributors to PostgreSQL, but once they bought MySQL I think it was the beginning of the end of their contributions to the project. Being acquired by Oracle was the solid-gold nail in the coffin.

Anyway, just to clarify a few points in some of the articles I've seen:

My employer and corporate contributor to PostgreSQL, EnterpriseDB is providing the hardware for the new buildfarm members. We try to give back to the community wherever we can, and this is one thing that we can do quite easily.
So far I've enabled two Solaris SPARC buildfarm members, one running the Sun Studio compiler, and one using GCC. There are equivalent Solaris Intel members being setup at the moment, as well as a couple of Windows 7 ones using VC++ 2008 32 and 64 bit.
Contrary to reports and what might be inferred from the short bio on my blog, I actually work for EnterpriseDB on a full time basis. The "in my spare time I work for..." bit was my poor British attempt at humour. Sorry 'bout that - it won't happen again!

Well, that's about it I guess. We now return you to your scheduled programming.

Postgres, Passwords and Installers

2010-07-28T14:57:00.002+01:00

By far the most common issues we see reported with the "one-click" PostgreSQL installers that we build here at EnterpriseDB are password related. In this post I'll explain what the passwords are, why we need them, and how to reset them.

Superuser Password

The Superuser Password is used by Postgres to secure the built-in "postgres" superuser account in the database itself. This is the only account found in a fresh installation. The password is setup during the initial installation of the database server, and may be changed at any point in the future using pgAdmin, or by issuing an SQL query such as:

ALTER USER postgres WITH PASSWORD 'VeryVerySecret';

The superuser password is not required to upgrade Postgres to a newer version, however it will be required if you install certain StackBuilder modules, such as PostGIS or any of the PHP applications that are available. The password is required for these installers because they connect to the database server and create databases and other objects required to run the software.

Service Password

The service password is the one that tends to confuse people. Postgres runs under a special operating system user account for security reasons. This account is created on your machine when the installer runs, and unless overridden on the command line, it will be called "postgres". On Unix-like operating systems such as Linux and Mac OS X, the account is setup without a password and users generally never need to worry about it again.

On Windows however, things are slightly different. In a normal installation of Postgres we'll setup the service account on Windows to use the same password that we use for the superuser account (expert users can override this behaviour using command line options for the installer). We need to do this because Windows requires that service accounts have secured passwords.

Note: If you change the superuser password in the future, that does not change the service password as well.

When you upgrade Postgres on Windows, in order to re-install the service we need to know the service password which is why the installer will require that it be re-entered correctly.

If you have forgotten the password, you can reset it on the command line (there are GUI tools that can be used as well, but they are not available on all versions of Windows). Start a command prompt, and then enter a command like:

net user postgres *

You will be prompted to enter and confirm a new password for the user. If you do this, you must then also update the password in the service configuration for any Postgres or related services (such as pgAgent or pgBouncer) that may use the account, or expect them to fail to start at the next reboot. You can do this using the Services management console which can be found in the Administrative Tools folder. Just right-click each service, select Properties, and enter the new password and click OK.

We get a lot of people asking us to reset their service password, as they mistakenly think it's related to either their Postgres Community Login, or their account on the EnterpriseDB website. It's not - and we cannot change it for you!

Uninstallation

If you uninstall Postgres, the service account will not be removed from the operating system. This is because each individual uninstaller has no way of knowing if you are using the service account with other packages or installations. This isn't a huge problem unless you come to reinstall Postgres at a later time and realise you've completely forgotten what the password was set to - in which case you can reset it as shown above. If you prefer to remove the account, you can use a command like:

net user postgres /delete

Summary

So to recap, we have Superuser passwords which are used to secure the database server's superuser account, and on Windows a service password to secure the operating system service account used by Postgres. Accounts use the same username (postgres) and the same password in a normal installation, but either password can be changed independently of the other post-installation. Both passwords are specific to your own machine, and can be changed (or the service account deleted) as shown.

For a walkthrough of the installation process, please see this guide.

If you need any further assistance having read this post, please use the pgsql-general@postgresql.org mailing list, or the EnterpriseDB Forums.

Postgres Testimonials

2010-07-01T09:23:00.001+01:00

Those of you who attended PG East in Philadelphia earlier this year may recall that we were recording user testimonials on video. Well, we've finally got a bunch of those edited and ready for public consumption - you can find them on the EnterpriseDB YouTube channel.

These videos are ideal material to help persuade your boss or co-workers to adopt Postgres - for example, this video features Jim Nasby talking about how Enova Financial managed $1 billion worth of loans last year using Postgres, having previously scrapped MySQL after just a few months of use.

Check them out. Spread the word.

SQL Injection attacks

2010-07-01T09:13:00.000+01:00

There was another malicious attack targeting websites running Microsoft Internet Information Server (IIS) recently. I would imagine that the vast majority of website developers and DBAs are aware of SQL Injection attacks and how to avoid them - primarily, by using nothing but parameterised queries in your code - and a large percentage of those have probably seen the excellent XKCD comic strip that illustrates (yeah, yeah, pun intended) the point nicely.

It never hurts to have a belt-and-braces approach to these things though, which is why version 9.0 of our Postgres Plus Standard Server and version 8.4 of Advanced Server will include a new module called SQL/Protect. This is a relatively small plugin to Postgres prototyped by Jim Mlodgenski, the Chief Architect here at EnterpriseDB which helps protect against these sort of attacks. It uses a number of techniques, such as detection of tautology in WHERE clauses of your SQL to try to detect and prevent injection attacks. It can also run in a learning mode in which it will monitor your web app and learn the usage patterns of the database so it can prevent anything out of the ordinary being executed.

Look out for it in our upcoming releases.

Comparing VoltDB to Postgres

2010-05-27T21:04:00.002+01:00

I've been asked a few times recently for my opinion on VoltDB, the new database server architected by the 'father' of Postgres, Dr Michael Stonebraker so rather than repeating myself over and over again it seems like a good idea to write it all down.

VoltDB is an in-memory, lockless relational database that maintains ACID compliance, has a SQL interface and claims to offer massive performance increases and scalability over 'traditional' relational databases. If you take the time to visit the website, and download some of the docs or even the product itself, if you're a database geek like me you'll probably be pretty impressed. The technology is interesting - the ability to avoid locking seems like paradise, as does the linear scaling.

There are downsides though. In order to implement the key features of the DBMS, Stonebraker has had to design the system to work with a pretty narrow set of use-cases. Lets consider some of the pros and cons and compare them to Postgres.

In memory database

VoltDB is an in-memory database. This means it can be very fast. It also gives us two potential problems:

The database must fit into the available memory on the system. That means that with a single server with 4GB of RAM, a practical database size limit may be in the order of 3GB or so, once you allow 1GB for the OS and memory required to actually operate the database server.
Durability (the D in ACID) must be provided through replication of data to one or more secondary servers over the network, or through writing periodic snapshots to disk. Because replication is synchronous within the cluster and asynchronous between clusters, it is possible for a cluster-wide power failure to cause the loss of committed transactions.

Of course, in some circumstances these may be non-issues. VoltDB scales horizontally extremely well (near linearly in fact), so if your database is large, you can add more servers to get the storage you need. This won't suit people running multi-terabyte databases of course - RAM is cheap these days, but not that cheap - especially when you multiply by 2 or more for durability and redundancy!

In contrast, Postgres stores it's data on non-volatile storage - a direct attached hard disk, or SAN for example. The issues here are:

Since Postgres uses both a shared buffer cache and the kernel cache (and potentially Infinite Cache in EnterpriseDB's Postgres Plus Advanced Server), in a well-configured system - like VoltDB - it will read most if not all of its data from memory. Unlike VoltDB, Postgres will still have locking and buffer management overhead however and of course, any disk reads will be much slower.
Durability is achieved through the immediate logging of all transactions to a sequential transaction log, and later writing of updated pages to the heap. This is much slower than VoltDB's in-memory operations as we write everything to disk twice.

Summary: VoltDB is fast, because it's in-memory. This creates serious practical limits on database size though - how big is your budget? You'll also need at least two servers, with independent power supplies for any real durability. Postgres is slower, most noticeably when the working set doesn't fit into a cache, but you can store multi-terabyte databases on very cheap hard disks with full durability.

Lockless database

Traditional DBMSs allow for concurrent access by using granular locking and other techniques such as MVCC (Multi Version Concurrency Control). By locking only the smallest part of the database required to perform a specific operation, they ensure that other users can still access the rest of the database concurrently. Locks are held for as short a period as possible.

In contrast, VoltDB is a lockless database and therefore doesn't suffer from any of the complexities involved in lock management or MVCC snapshot management that Postgres does. This is especially important when scaling horizontally, as lock management and snapshot management are two of the most complex problems to solve when building clustered database systems based on Postgres.

VoltDB's solution isn't a panacea for these issues though. To achieve lockless operation, every request is serialised through each partition of the database. This means that if you have one partition (i.e. a single server), each database request will be run sequentially, with no parallelism at all. If you have partitioned data, then requests that affect different individual partitions may run in parallel, however any multi-partition requests must be run against the entire cluster on their own.

So what does this mean? Primarily, the effect is that performance is likely to tank if you try to run any complex queries on the system. Any long running, multi-partition query will block all other users of the database until it completes, which means that the system is only suitable for simple OLTP applications, with well thought out data structures. Don't be tempted to sneak any reports into your apps!

In contrast, Postgres can easily handle both OLTP and DW (data warehouse) workloads. MVCC ensures that 'readers never block writers', so you can run complex reports at the same time as tens or hundreds of OLTP operations are running concurrently. The downside is, that it is significantly harder to build multi-master clusters with Postgres.

Summary: VoltDB's lockless architecture makes it easy to scale horizontally, but limits concurrency. Postgres is harder to scale out, but offers excellent concurrency through fine-grained locking and MVCC.

Evolution and growth

The schema in a VoltDB database is defined in a runtime 'catalog'. This includes not just the schema, but also the details of the different nodes in the cluster, and the size of the database. Any changes that are required to the schema, to the configuration of the cluster, or to the size of the database currently require:

Dump of the data to non-volatile storage and shutdown of the VoltDB server on each node.
Reconfiguration of the runtime catalog.
Restart of the VoltDB server on each node, and reload of the data from storage.

In Postgres, database objects can be modified entirely 'on the fly', without shutting down the server or dumping or reloading data. The database size can grow to theoretically unlimited sizes, constrained only by the amount of storage available without the need to restart. Most of the clustering solutions for Postgres don't require a shutdown to install or reconfigure them.

Summary: VoltDB cannot offer high availability if the database grows beyond the predicted size, nor can changes to the schema (such as those that may be required by a software upgrade) be made without shutting down the system. Postgres allows your system to grow and evolve without requiring downtime.

Ecosystem, drivers and tools

VoltDB is a new project. At present, it doesn't have any real community, the only tools available are those shipping with the server, and the API is based on calling Java stored procedures. Actually, that's a bit awkward really. You essentially have to write a data access layer on the server in Java, which encapsulates all of the SQL queries you need to run. Your client code then calls those procedures directly.

Postgres has a long history. There is a large and vibrant community, with hundreds of tools, add-ons, utilities and so-on available. APIs to access the database are available for a host of languages including C, C++, .NET, Perl, Python, TCL, Ruby, PHP, Java and more. Stored procedures (functions) can be written in common languages including C, Perl, TCL, Ruby, Python PHP and Java.

Summary: The long Postgres history means that there are far more tools, utilities and interfaces available for it. VoltDB can catch up, but it will likely take many years. Further, the Java API limits how you can access the database - there is no way to connect via a standard ODBC or JDBC driver to allow you to use generic query tools for example.

Conclusion

VoltDB is an interesting product, but one with limited use-cases. If your database can fit in the memory of your server cluster, and you can architect your application to avoid any kind of complex query then it can offer vast performance advantages over traditional DBMSs like Postgres, and has potential to scale extremely well.

For most users though, concurrency and the ability to run complex queries are real issues, as is the ability to scale the database beyond sizes that are economical to keep in RAM, without having to dump and reload the data and restart the server to accommodate expansion and evolution of the system.

I can see some interesting use cases for VoltDB, and I'll be keeping an eye on its evolution and time permitting, trying it out for size. It's by no means a universal replacement for Postgres or any other similar DBMS (nor does it claim to be), but it could prove to be a very useful tool in the right situation.

PGCon 2010 summary

2010-05-25T13:31:00.000+01:00

I arrived home from PGCon 2010 yesterday. As always the conference was held in Ottawa, Canada to which many of the PostgreSQL developers now make an annual pilgrimage.

We started on Wednesday with the third annual developer meeting, this year held at Arc The Hotel for the second time. Many thanks to Nadine for ensuring everything went without a hitch. There were 29 developers present, working their way through a packed agenda in an extremely productive manner. There were some great ideas for 9.1, with lots of people claiming items to work on. If we get only half of that done, 9.1 will be an incredible release.

There are some photos from the meeting on my SmugMug page.

Thursday was the start of the conference proper, beginning with an excellent presentation from Gavin M. Roy comparing PostgreSQL with NoSQL databases. The talk can be heard here. I ducked in and out of a few more talks over the day, but spent much of my time working with Magnus and Stefan on the new infrastructure hosting platform that we're polishing off for the project's hosting.

On Thursday night we had the annual EnterpriseDB party, held at the Velvet Room. Good food, plenty of drink, and people discussing Postgres well into the early hours of Friday. Unfortunately I don't have any photos as I was too busy running around giving out beer tokens to remember to take any.

Friday was the second day of the main conference, starting at 10AM (thankfully) with David Fetter and Jonathan Leto kicking off my day talking about PL/Parrot. Dan hosted the closing session which consisted largely of an auction, the main purpose of which was to show how the PostgreSQL community can raise more money than the BSD community for charity. And we did. By quite some margin. The biggest earner being a PostgreSQL 9.0 sweatshirt signed by all of the developers at the developer meeting, which amid a circus of bidding between teams led by Jim Nasby and Gavin Roy ended up going for $1517, donated by both camps. Photos, are here.

The week was finished off with a short stop in Montreal with a number of other community members, before taking advantage of cheap Sunday flights to get back home for Monday morning!

Elected to the PostgreSQL Europe Board

2010-05-25T10:40:00.000+01:00

Magnus posted the results of the PostgreSQL Europe board elections yesterday, and I'm happy to have be one of the successful candidates, along with Guillaume Lelarge and Andreas Scherbaum. Everyone standing has made important contributions to PostgreSQL however and now that the election is over I can say that any of them would have been good choices in my opinion.

I'm looking forward to working on new initiatives to promote PostgreSQL within Europe, as well as the ongoing work to organise our 2010 conference in Amsterdam, so a big "thank you" goes to all those who voted for me.

A vaguely off-topic tale of frustration and nail biting: I hate flying!

2010-05-17T20:33:00.000+01:00

Well, in theory I love it. It's just become a nightmare over the last few trips.

In March there was PG East in Philadelphia. And the BA cabin crew decided to go on strike. Cue purchase of a (refundable) backup ticket at vast expense, and then days of nail biting until it became apparent that my original (on-refundable, but much cheaper) flights would go ahead just with reduced on-board service. So the US airways ticket got refunded, and I enjoyed a couple of salads for lunch. Not to my usual taste, but probably more healthy than what I normally favour!

In April I planned to take a trip to the EnterpriseDB Massachusetts office in Westford. Then Eyjafjallajökull erupted, and the ash came. Four more days of nail biting, before it's confirmed that my flight is canceled. Excellent service from BA though, and within 30 minutes I'm rebooked for the beginning of May. The office will just have to wait.

So in May we have attempt number two to get to Massachusetts. Which went without a hitch. Amazing. Well, except that the ash returned briefly whilst I was away causing another minor scare. Oh, and a mixup with my ride from the airport which quit waiting for me 22 hours before I arrived.

Second trip in May is PGCon. And late Saturday afternoon I hear on the radio that the ash is back. More nail biting. Constant refreshing of websites like the Met Office and NATS. On Sunday night, the Air Canada flight from Ottawa to London is delayed by hours due to overnight ash in the early hours of Monday morning, and by the time I wake up its been cancelled entirely which means that so is my flight as the plane is now in the wrong place. Luckily, I woke up at 5:30AM, by which time no-one else had noticed the flight cancellation and I managed to get a seat on the Tuesday flight (tomorrow). The ash is now gone, the CAA have relaxed the flying rules again and things are looking rosy.

So what's next? Well, in June I'm visiting our New Jersey office just after the planned second batch of BA cabin crew strikes are due to finish. Well, were due to finish. Now, BA have managed to get an injunction to prevent the strike going ahead as planned because the union screwed up the ballot (again)! I can already see what's going to happen; they'll take two weeks to re-run the ballot, then give BA a weeks notice of upcoming strikes, the second of which will coincide perfectly with my trip. D'oh!

Oh well. I still love being paid to work on Postgres and pgAdmin; it would just be nice if the travel were a little less stressful :-)

Postgres vs. SQL Server

2010-05-07T20:40:00.001+01:00

A colleague of mine (thanks Jimbo!) pointed me towards a benchmark comparison of Postgres 8.3.8 on RHEL 5.4 versus SQL Server 2008 R2 on Windows Server 2008 R2, performed and written up by Red Hat. I hadn't seen it before, so figured that maybe others hadn't either:

http://www.redhat.com/pdf/rhel/bmsql-postgres-sqlsrvr-v1.0-1.pdf

Can't wait for the result? The elephant wins :-)

pgAdmin demo video

2010-04-09T20:45:00.001+01:00

Embracing the whole Web 2.0 thing, one of my cow-orkers has been playing with his Mac and produced a nice video walkthrough of pgAdmin, showing how to get started and use a variety of the really cool features it has to offer Postgres users.

So, at risk of this being my shortest blog post ever, here's the video.

Thanks Gary!

Postgres 9.1 - Release Theme

2010-04-01T09:10:00.004+01:00

Following a great deal of discussion, I'm pleased to announce that the PostgreSQL Core team has decided that the major theme for the 9.1 release, due in 2011, will be 'NoSQL'.

There is a growing trend towards NoSQL databases, with major sites like Twitter and Facebook utilising them extensively. NoSQL databases often include multi-master replication, clustering and failover features that have long been requested in PostgresSQL, but have been extremely difficult to implement with SQL which has prevented us from advancing Postgree in the way that we'd like.

To address this, the intention is to remove SQL support from Postgres, and replace it with a language called 'QUEL'. This will provide us with the flexibility we need to implement the features of modern NoSQL databases. With no SQL support there will obviously be some differences in the query syntax that must be used to access your data. For example, the query:

select (e.salary/ (e.age - 18)) as comp from employee as e where e.name = "Jones"

would be rewritten as:

range of e is employee retrieve (comp = e.salary/ (e.age - 18)) where e.name = "Jones"

Aggregate syntax in QUEL is particularly powerful. For example, the query:

select dept,

avg(salary) as avg_salary,

sum(salary) as tot_salary

from

employees

group by

dept

may be written as:

range of e is employee

retrieve (e.dept,

avg_salary = avg(e.salary by e.dept),

tot_salary = sum(e.salary by e.dept)

)

Note that the grouped column can be specified for each individual aggregate.

We will be producing a comprehensive guide to the QUEL syntax to aid with application migration. We appreciate the difficulty that this change may cause some users, but feel we must embrace the NoSQL philosophy in order to remain "The world's most advanced Open Source

database"

"There's no question that, at 21 years old, the SQL standard is past its prime," said core developer and standards expert Peter Eisentraut. "It's time for us to switch to something fresher. I personally would have preferred XSLT, but QUEL is almost as good."

Project committer Heikki Linnakangas added: "By replacing SQL with QUEL not only will will be able to add new features to Postgres that were previously too difficult, but we'll also increase user loyalty as it'll be much harder for them to change to a different, SQL-based

database. That'll be pretty cool."

You may also notice that without SQL, the project name is somewhat misleading. To address that, the project name will be changed to 'PostgreQUEL' with the 9.1 release. We expect this will also put an end to the periodic debates on changing the project name.

Dave Page

On behalf of the PostgreSQL Core Team

Getting ready for PG East

2010-03-23T09:06:00.003Z

After a stressful few weeks keeping a close eye on the BA cabin crew strike, it seems that my flight is still scheduled so I will be able to get to Philadelphia for PG East 2010 without having to use my insanely expensive refundable backup ticket with US Airways.

This years conference is in a new format, spread over three days with a fourth day of tutorials on the schedule, all at the rather nice Radisson Plaza-Warwick Hotel in Philly, courtesy of my boss' budget here at EnterpriseDB (OK, I'm done with the shameless company plugs :-p ). The schedule includes a good mix of topics with varying levels of technical detail for geeks through IT/IS management.

I'll be presenting a new talk in which I'll describe the infrastructure behind postgresql.org, and how it's managed. If you've not been closely involved in a large Open Source project, you may be surprised at just how much there is - certainly far more than a webserver and mailing lists which are probably the most visible things.

Anyway - I'm speaking on Thursday, so if you can get to Philly, it'd be great to see you there.

PostgreSQL Europe opens for general membership

2010-02-22T19:28:00.002Z

After numerous emails, meetings in foreign lands and changes to the statutes, PostgreSQL Europe finally opened it's doors to new members today, of which I became the very first. Well, OK, technically I'm the fifth, as the elected board made up the first four, but I was the first non-elected, fee paying member.

So 'what's the point?' I hear you cry, and 'why should I part with my hard-earned cash?'. Well, joining PostgreSQL Europe costs €10 for 2 years membership, which is a fraction of what some other groups charge. You won't get a postgresql.eu email address for your money, but you do get the chance to vote for PostgreSQL Europe board members of your choosing, at the annual elections. Those four board members (who will become five after the upcoming elections) decide what events and activities the not-for-profit organisation will be involved in, and how to spend the money - what events should be sponsored, what swag should be purchased and so on.

Membership is open to anyone resident in geographical Europe (including all of Turkey and Russia), so if you want to join and play a part in the future of PostgreSQL, you can sign up on the postgresql.eu website. Don't leave it too long though - you need to be a member for at least a month to be eligible to vote in future elections.

PostgreSQL - now with added Open Source!

2010-02-18T15:58:00.003Z

Some of you may be aware that some time ago, Redhat's legal team changed the description of the PostgreSQL licence (yes, that is correct where I live - please don't email me to tell me I can't spell) from BSD to MIT. After doing some poking around (it helps when your boss's boss is an ex-Redhat exec) it transpired that they considered that our licence looked a lot more like the MIT licence than BSD.

It's true that ours doesn't look like the traditional BSD 3 or 4 clause licences - some people had already spotted that, but as it came from Berkeley we always just considered it to be a variant on the simplified BSD licence. The long and short of it was that lawyers said "no they're not the same", and we said "well it sure ain't MIT" and much discussion ensued.

The solution it seemed, was to try to get our licence recognised and approved by the Open Source Initiative. I originally tried to do it as an official BSD variant, but that wasn't accepted. I then resubmitted it as 'The PostgreSQL License" (yes, I know :-p), which was rejected as it wasn't a generic licence (OSI are trying to ensure that all new licences are resuable). Finally, I submitted it again with placeholders replacing the project name and copyright holder info, and nearly two months later, it's approved!

So there you have it ladies and gents. "The world's most advanced database with freely available source code" now actually is, officially, The world's most advanced Open Source database"!

Speaking at PG East

2010-02-18T13:24:00.003Z

If you've been involved in the PostgreSQL project at all in the last few years, you'll know that Josh Drake has been organising the PG East and PG West conference series since 2007. When we met in Paris at PGDay.EU, JD suggested that I submit a talk for East in March.

Which I did.

Which got accepted.

So now I'm travelling to Philadelphia in March :-)

Since we spoke though, the crack marketing team here at EnterpriseDB contacted JD and between them hatched a plan to make East the most impressive PostgreSQL conference to date. It'll be held over four days, at the Radisson Plaza - Warwick Hotel in Philadelphia, from March 25th to 28th and will include content for C-level execs and decision makers, as well as the regular crowd of database geeks like me. Of course, there will also be the traditional EnterpriseDB Party on the Friday night - if you haven't been to one of those yet, now's the perfect time to see what you've been missing out on!

So if you've been toying with the idea of going to a PostgreSQL conference, this is the perfect time to give into the urge and join us in Philly for database talk, beer and cheese-steak sandwiches. Mmmmm, cheese-steak....

FOSDEM 2010

2010-02-08T14:59:00.009Z

Well, FOSDEM 2010 is now over and it all seemed to go pretty well. The PostgreSQL Project was represented well (as one would hope for our second biggest annual European gathering), with the majority of the Hotel Agenda Louise seemingly occupied by database geeks.

On the Friday night we had a database dinner at Les Brasseurs de la Grand Place where we were joined by Sergey Petrunya and Kristian Nielsen from MariaDB. Good conversation, good beer and good food followed later by an aborted attempt to join the FOSDEM Beer Event at Cafe Delirium (it was just too busy) and a successful landing at the Irish pub a short walk from the Hotel.

Saturday morning was the start of the conference itself. We'd hoped to scrounge an extra table (much needed, with the number of people and the amount of swag we had), but unfortunately that didn't work out. Somehow, we managed to squeeze onto one.

The talks started at 1PM in our dev room (which we only had for Saturday afternoon unfortunately). Magnus and Jean-Paul started with report on the state of PostgreSQL Europe, and then it was my turn with my talk on "Developments in PostgreSQL 9.0". Slides and the full schedule can be found on the PostgreSQL Wiki. The dev room was basically packed solid with people sitting on the floor for every talk. Please FOSDEM organisers - give us a bigger room next year!

Saturday night was a late dinner at possibly the slowest restaurant in the world, who clearly weren't expecting too many diners. Food wasn't too bad though, when it finally arrived.

Sunday was spent mostly on the booth and (in my case) in lots of ad-hoc meetings on topics such as the new PostgreSQL project infrastructure thats in development, as well as PGDay.EU 2010 which is being planned in Amsterdam.

Most of us left at around 5PM, with Heikki, Greg, Magnus, Stefan and I heading for the airport. After pizza and a brief scare when Stefan lost his boarding pass, we left for home. Goodbye Brussels, see you next year.

More photos can be found here.

Kerberos support in PostgreSQL on Windows

2009-12-16T14:07:00.004Z

We recently received a report of some automated security scanning software red-flagging the Kerberos DLLs that ship with the PostgreSQL installers for Windows. This blog post is an analysis of the impact of know vulnerabilities in Kerberos, and how they relate to PostgreSQL, and what we're doing about them.

PostgreSQL 8.3.x and 8.4.x

PostgreSQL 8.3 and 8.4 are built using Kerberos for Windows (KfW) 3.2.2 which is based on the Kerberos 1.6.3 package. This is the latest version of Kerberos for Windows that is currently available from MIT.

The vulnerabilities that were reported by the security scanning tool were:

CVE-2008-0062 and CVE-2008-0063. These are bugs in the KDC server which are exposed if Kerberos 4 is enabled on a v5 KDC. As we don't ship the KDC software with PostgreSQL, these bugs do not apply.

CVE-2008-0947 and CVE-2008-0948. These are bugs in kadmind, the Kerberos Administration Server. We don't ship this either, so like the previous bugs, these do not apply to PostgreSQL.

What the scanning tool didn't report, was a fifth vulnerability which does potentially affect PostgreSQL users:

CVE-2009-0846. This issue is described as: The asn1_decode_generaltime() function, which decodes DER encodings of the ASN.1 type "GeneralizedTime", can free an uninitialized pointer. This can cause a Kerberos application to crash, or, under theoretically possible but unlikely circumstances, execute arbitrary malicious code.

As mentioned above, we currently ship the latest version of Kerberos with PostgreSQL. As soon as MIT update the Kerberos for Windows package to include Kerberos 1.6.4 (which does not have this issue), we will update the PostgreSQL build servers.

PostgreSQL 8.2.x

PostgreSQL 8.2 is built using Kerberos for Windows (KfW) 2.6.5 which is based on the Kerberos 1.3.5 package. This is the most recent version of Kerberos for Windows v2.6.x that is available from MIT and is no longer being maintained.

This version of Kerberos is believed to be vulnerable to the issue noted above (CVE-2009-0846), as well as CVE-2005-1689, which describes a double-free bug in the krb5_recvauth function (but was not noted by the scanning tool that started this exercise)!

Updating Kerberos for Windows to version 3.2.2 in the PostgreSQL 8.2 distribution is the only way we can work around this issue, however, this is not as simple as it might sound as the distribution has changed in structure thus requiring modifications to the PostgreSQL installer to accommodate additional DLLs as well as any application installers that our users may have built around their libpq-based applications.

Because of the potential disruption to users and software developers for the sake of a feature used by such a small percentage of users, we have decided not to update the PostgreSQL 8.2 installer with the newer Kerberos packages but instead to recommend users of PostgreSQL 8.2 on Windows who wish to use Kerberos plan to upgrade their installations to PostgreSQL 8.3 or 8.4 as soon as possible.

New PostgreSQL Committers

2009-12-07T10:50:00.003Z

Just a few minutes ago I posted the announcement below, telling the world that we've added some new committers to the PostgreSQL project. The project is extremely conservative when it comes to the source code as we're completely paranoid about breaking anything, however some have argued that we're perhaps too careful in this regard, and that our conservatism may actually be a bottleneck to the project.

Whilst the actual act of committing a change certainly isn't a bottleneck (after all, how long does it take to type 'cvs commit -m "Cool new feature from Joe"'?), the real bottleneck is in the review process, part of which involves one of our committers taking ownership of each patch, and guiding it through the final stages of the process. As patches become more and more complex, that can take more and more time - for (an extreme) example, Heikki has been reviewing Simon's Hot Standby patch for over a year now, as they refine the design and get it to a state where its ready to be committed to the main source tree. Of course, once a patch is committed, that's not necessarily the end. The committers will also take care of any post-commit cleanup, or other problems that may become apparent with any change, such as portability issues which may be highlighted by the buildfarm.

By increasing the pool of committers, we hope to ease that problem, and speed up the final stages involved in getting changes into PostgreSQL - and as all the new committers are experts with the PostgreSQL source code and work consistently to very high standards we're absolutely certain that the project's high standards will be maintained.

On behalf of the core team, I'm pleased to announce that the PostgreSQL Project has expanded it's team of "committers", those people who are able to make direct changes to the PostgreSQL source code respository. As the project is extremely conservative about any changes made to the source code to minimise the risk of introducing any bugs, commit access is only given to contributors who have consistently shown they work to a very high standard and have shown commitment to the project.

The new committers are:

Robert Haas: Robert developed the commitfest.postgresql.org website which is used to manage the process by which features are added to PostgreSQL. He has twice acted as commitfest manager, and submitted numerous patches such as join removal, auto-generation of headers & bki files and the TRUNCATE privilege.

Simon Riggs: Simon is well know for working on large enterprise features for PostgreSQL, including Point In Time Recovery and partitioning. Simon is currently working on allowing PITR slave servers to be used for read-only queries.

Greg Stark: Greg has worked on low-level features in PostgreSQL, including asynchronous pre-fetching of data and packed variable length data types. Greg was also responsible for the CREATE INDEX CONCURRENTLY feature.

ITAGAKI Takahiro: ITAGAKI-san has worked on countless patches for PostgreSQL, both fixing bugs and writing new features, recently including WHEN clauses for triggers, a buffer usage feature for EXPLAIN and a new implementation of VACUUM FULL.

Congratulations!

PostgreSQL Release Support Policy

2009-12-04T16:36:00.007Z

We finally came up with a support lifecycle policy for PostgreSQL. The 'official' version can be found on the wiki.

It's pretty straightforward though, and reads as follows:

The PostgreSQL project aims to fully support a major release for five years.

After a release falls out of full support, we may (at our committer's discretion) continue to apply further critical fixes to the source code, on a best-effort basis. No formal releases or binary packages will be produced by the project, but the updated source code will be available from our source code control system.

This policy will be followed on a best-effort basis. In extreme cases it may not be possible to support a release for the planned lifetime; for example if a serious bug is found that cannot be resolved in a given major version without significant risk to the stability of the code or loss of application compatibility. In such cases, early retirement of a major version may be required.

End Of Life (EOL) dates

Version	EOL Date
PostgreSQL 7.4	July 2010 (extended)
PostgreSQL 8.0	July 2010 (extended)
PostgreSQL 8.1	November 2010
PostgreSQL 8.2	December 2011
PostgreSQL 8.3	February 2013
PostgreSQL 8.4	July 2014

pgAdmin 1.10.1 released

2009-12-04T15:55:00.003Z

pgAdmin 1.10.1 has now been released. A source tarball, and builds for Windows and Mac OS X are now available in the downloads area of the website - expect additional distributions to become available over the next few days.

pgAdmin is the leading Open Source GUI interface to PostgreSQL, and can be used on Windows, Mac OS X, Linux, Solaris and FreeBSD.

This is a bug fix release, including the following changes:

Replace Alt-F4 with Ctrl-Q and Ctrl-W.
Prevent a crash if the edit grid is closed whilst it is loading data.
Don't attempt to remove rows in the edit grid if the user presses the delete key when the delete button is disabled.
Only offer valid server encodings for new databases.
Fix font dialogue on Snow Leopard.
Fix an issue with the ordering of the mappings in a text search configuration.
Fix a potential crash bug in the object browser.
Reverse engineer empty (not NULL) ACLs correctly.
Fix Greenplum support for column oriented partitions.
Ensure function variables get reset if the function is modified.
Fix cluster creation for Slony 2.0.
Reverse engineer function defaults values correctly.
Fix a potential crash in the edit grid.
Fix domain creation/modification for domains in non-default schemas.
Reverse engineer language privileges correctly.
Get rid of "No SQL query was generated." message dialog when no tables are selected in the GQB.
Hints files should be encoded in UTF-8.
Include comments on procedures in the reverse engineered SQL.
Fix debugger name resolution on 64 bit Solaris.
Fix Slony cluster creation on Solaris.
Fix foreign key creation on Solaris.
Fix an SQL syntax error when viewing the dependencies of a sequence.
Fix saving of macros.
Better fix for schedule and step dialogs.
Fix the menu entry in frmQuery.
Fix the dlgFunction handling of preload libraries.
Fix schedule and step dialogs.
Fix error thrown when examining a Slony 2.x cluster.

Happy upgrading!

PostgreSQL@FOSDEM 2010 - Call for talks

2009-12-03T13:18:00.000Z

FOSDEM is a major Free and Open Source event held annually in Brussels, Belgium, and attended by around 4000 people. As in recent years, the PostgreSQL project will have a devroom where we will be presenting a number of talks. The event will be held on the 6 - 7th February 2010.

We're looking for developers, users and contributors to submit talks for inclusion on the program. Any topic related to PostgreSQL is acceptable as long as it is non-commercial in nature. Suggested topics might include:

Migration of systems to PostgreSQL
Application development
Benchmarking and tuning
Spatial applications
Hacking the code
Data warehousing
New features
Tips and tricks
Replication
Case studies

We will have a number of 45 minutes slots, and may split one or more into 3 back-to-back 15 minute slots if we receive suitable proposals.

Please submit your proposals to:

fosdem@postgresql.eu

and include the following information:

Your name
The title of your talk (please be descriptive, as titles will be listed with ~250 from other projects)
A short abstract of one to two paragraphs
A short biography introducing yourself
Links to related websites/blogs etc.

The deadline for submissions is 22nd December 2009.

See you in Brussels!

JPUG 10th Anniversary conference pics

2009-11-28T10:50:00.006Z

I finally got my photos from the JPUG 10th Anniversary conference in Tokyo online. Most are actually from the events before and after the actual conference which involved some sightseeing, and lots of discussions of PostgreSQL on topics such as infrastructure issues and server virtualisation, how to be more supportive of new developers, and how to make MERGE work nicely with the PostgreSQL rules system.

After hitting the limits on my free Flickr account I figured it was time to move to Smugmug, so you can find the full set of pics over there.

Thanks again to all the JPUG folks for an exceptional conference!

Are you ready for PGDay.eu 2009?

2009-11-05T07:49:00.003Z

PGDay.eu 2009 starts tomorrow morning, at Telecom ParisTech in Paris, France. A few of us are here already to make sure the local bars and restaurants are suitable for the discerning PostgreSQL hacker or user, and in our spare time finishing our preparations for the great lineup of talks and and general 'get together' of Postgres people.

One thing that we haven't yet announced, is the almost traditional EnterpriseDB Party. This year it will be at Acrobates et Funambules, which is just a minutes walk from the conference venue at 204, Rue de Tolbiac, 75013 Paris, immediately following the conference on Friday. Due to the way the venue likes to operate, we'll be giving out tokens to exchange for drinks during the day at the conference. Hors d'oeuvres will also be served.

See you there!

PGDay.EU 2009 - registration deadline extended

2009-10-22T16:39:00.000+01:00

I'm pleased to announce that the registration deadline for PGDay.EU 2009 on November 6th & 7th in Paris has been extended to the 31st October, allowing attendees an extra eight days to benefit from the cheapest pricing. Attendees registering after the 31st October will be charged the 'on the door' price.

This is possible thanks to the generous support of our sponsors:

http://2009.pgday.eu/sponsors

For more information on the conference, including the talk schedule and registration and travel information, please visit the website at:

http://2009.pgday.eu/

See you in Paris!

PGDay.EU 2009 - approaching fast!

2009-10-20T12:15:00.000+01:00

PGDay.EU 2009 is approaching fast - have you registered yet?

Europe's premier PostgreSQL conference organised by PostgreSQL Europe and PostgreSQLfr will be held on November 6th and 7th at ParisTech Telecom in Paris, France. With an outstanding lineup of talks over the two days of the event, with tracks in English and French, this is the must-attend PostgreSQL conference this year!

http://2009.pgday.eu/start

Speakers will include well known community members and developers such as Simon Riggs, Gavin M. Roy, Gabriele Bartolini, Dimitri Fontaine, Joshua Drake and Guillaume Lelarge speaking on a wide range of topics. The full schedule can be seen at http://2009.pgday.eu/schedule

If you are planning on attending, please register as soon as possible at http://2009.pgday.eu/register. Early registration will help us ensure you get a T-Shirt and conference goodies!

Details of the venue and hotels in the local area can also be found on the conference website. If you have yet to book your accommodation, I would suggest doing so as soon as possible as Paris is quite busy at this time of year.

See you in Paris!

StackBuilder application updates (again!)

2009-08-23T12:36:00.002+01:00

Some more StackBuilder application updates, hot off the build servers:

First up is libpq64, which is a new installer offering a 64 bit build of libpq for use with Win64 applications. For various reasons, we don't currently have a Win64 port of Postgres, but this package allows you to interface with Postgres from your own 64 bit applications.

Secondly, we have updates to PostGIS. Leo & Regina from the PostGIS community have taken over maintenance of the PostGIS Windows installer from Mark Cave-Ayland and have built PostGIS 1.4.0 for PostgreSQL 8.3 and 8.4.

Finally, a PostGIS 1.4 installer for PostgreSQL 8.4 is also now available for Mac and Linux 32/64bit courtesy of the guys in the EnterpriseDB installer team.

As always, enjoy :-)

StackBuilder application updates

2009-08-10T10:46:00.003+01:00

Yikes, it's been a while since my last post. Well part of the reason for that is that I forgot to mention the last set of updates we published for StackBuilder at the end of last month. Sorry 'bout that. Anyway, hot on the heels of those, we have some more updates, fresh from the QA folks here at EnterpriseDB, so here's the combined list of updated packages:

ApachePHP 2.2.11-5.2.9-2
Drupal 6.12-1
MediaWiki 1.15.0-1
phpBB 3.0.5-1
EnterpriseDB Tuning Wizard for PostgreSQL 1.3-1
EnterpriseDB MySQL -> PostgreSQL Migration Wizard 1.1-2
PostgreSQL JDBC drivers 8.4-701-1
PostgreSQL ODBC drivers (psqlODBC) 08.04.0100-1
PostgreSQL .NET drivers (Npgsql) 2.0.5-1
Slony for PostgreSQL 8.4.x 2.0.2-1

As always, enjoy :-)

After the EDB party...

2009-05-22T07:36:00.002+01:00

Well, it's that time of year again, when I make an ill-advised post immediately following the EnterpriseDB party whilst full of beer at 2:30AM!

After some excellent talks today, as well as our keynote - which seemed to be well received, if not that polished, we held the usual conference party. As I mentioned in a previous post (which I can't be bothered to link to right now), we held it in the Velvet Room on the Byward Market again, this time without the dueling pianos. The venue staff laid on two buffets this year so we weren't all crammed in the room upstairs - they told me just before we left that we had 70 downstairs and 70 upstairs for dinner! That seems like a pretty good turnout to me :-)

Anyway, I need to get some sleep now, as much to Greg's disgust, it's breakfast at 9AM with Magnus & Selena. Laterz....

PGCon 2009 - Day 1

2009-05-21T12:20:00.002+01:00

Yesterday was the first full day for me at PGCon here in Ottawa. After quite a few beers upon arrival on Tuesday evening, the quiet serenity of the Developer Meeting seemed like it would be the perfect way to unwind. Following weeks of planning (mainly on Greg's part), the Novotel proved to be an excellent venue, with a nice room, AV equipment that just worked, and some interesting food (popcorn, fruit pizza or candyfloss anyone?). We had 24 hackers there in person, and were joined by Suzuki-san, Shimogaki-san and Takahiro-san (from NTT) on the phone - who were with us from something like 10PM to 6AM their time - and Simon Riggs via Skype. The minutes will be posted on the wiki as soon as Josh Berkus is able to write them up from his notes, but in short, we had a number of fruitful discussions on various topics. Certainly a successful meeting in my mind.

After we left the Novotel, Selena, Magnus and I headed to Don Cherries to work on our slides. I had a text from Magnus just before I left the UK, telling me we were doing the keynote. Of course, I put it down to too much beer on his art, but it turns out we did manage to get conned into doing the talk, so figured we'd better come up with something to talk about. It's only a short slot, so I shouldn't be able to embarress myself too much hopefully.

Finished off the evening with a quick beer with Denis, Jimbo and Scottie from EDB and Gavin, Jonah and Michael from MyYearbook.com, before heading off to Colonade Pizza for pizza and a couple more beers with a few of the usual suspects, courtesy of Paul, head honcho at the Pythian Group. Thanks Paul!

Oh well, that's enough for now - got that pesky keynote soon and need to get ready. More later, after this year's EnterpriseDB Party (assuming I survive)!

EnterpriseDB party at PGCon

2009-05-11T15:01:00.004+01:00

A few people have asked me if EnterpriseDB will be hosting a party at PGCon 2009, as we've done in previous years, and I'm pleased to announce that yes, we will!

As always, the party is open to all PGCon attendees, organisers and speakers and will include a dinner and booze (Mmmmm, beeeer). This year however, due to popular demand we have not booked the dueling pianos again!

The party will be at:

The Velvet Room
62 York Street
Ottawa, ON K1N 5T1
(613) 241-6810

[Map]

Doors open at 6:30PM on Thursday 21st May, and dinner will be served from around 7PM. See you there!

Goodbye

2009-04-01T08:27:00.000+01:00

Dear pgAdmin Community,

You may be aware that the pgAdmin project has been in existence for nearly 11 years now. During this time, the development team have spent thousands of hours writing hundreds of thousands of lines of code and documentation, engineering complex features and support for multiple versions of PostgreSQL, Postgres Plus and Greenplum, and providing support to thousands of users. As I'm sure you can understand, after so many years a group of the development team members have reached the point where we feel we've given as much as we can to the project.

Having received a offer from a very large and well known software company for ownership of our copyright to the source code, we have decided to close down the project, effective immediately. I cannot speak for all of my colleagues on the development team but personally I am looking forward to a complete change of lifestyle, having purchased a farm in New Zealand where my family and I will be raising sheep and I get to play thrash metal on my bass guitar as loudly as I like without annoying the neighbours! Magnus tells me he is looking into upgrading his yacht and taking a trip around the world, and Guillaume is going to spend his time drinking wine in his new vinyard at Château Margoux.

Please be aware that the mailing lists and website will be shutdown around 12PM today as the project transitions to its new owner who will be announcing availability of support contracts and professional services shortly.

I'd like to thank all of our users and contributors over the past 11 years - it's been an absolute pleasure working with all of you.

--
Dave Page
pgAdmin Project Lead

pgAdmin moves to the BSD licence

2009-03-27T11:58:00.003Z

After a huge effort over 18 months or more, involving lawyers, negotiations, tons of emails and new-found detective skills, we've finally changed the pgAdmin licence from Artistic v1.0 to the PostgreSQL variant of the BSD licence. The change is partly in response to criticism of the licence by the FSF who described it as "too vague; some passages are too clever for their own good, and their meaning is not clear.", but mostly because as a result of those comments (and a court case in the US), Red Hat dropped all Artistic 1.0 licenced packages from Fedora and RHEL.

So, from pgAdmin 1.10 onward the new licence is:

pgAdmin III

Copyright (c) 2002 - 2009, The pgAdmin Development Team

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE PGADMIN DEVELOPMENT TEAM BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE PGADMIN DEVELOPMENT TEAM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE PGADMIN DEVELOPMENT TEAM SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE PGADMIN DEVELOPMENT TEAM HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

Many thanks to all the past contributors who gave their consent to change the licence, to the rest of the pgAdmin Development Team for helping out with the grunt work, and to Karen from the Software Freedom Law Center for her sage advice.

StackBuilder updates - now with added .NET!!

2009-03-27T11:45:00.003Z

I finally got time to publish a round of StackBuilder package updates, as well as publish a new package - Npgsql 2.0.4. Npgsql is a mature and well maintained .NET data provider that works with Microsoft .NET and Mono. More information can be found on the project's pgFoundry page and website.

Npgsql 2.0.4 is available through StackBuilder for Windows, Linux 32/64bit and Mac OS X.

Other updates to existing packages for all four platforms are:

phpBB 3.0.4-1
Drupal 6.10-1
mediaWiki 1.14.0-1

Enjoy :-)

Sony game using Postgres technology

2009-03-25T16:37:00.007Z

Seeing as I've got a few minutes spare having just announced the next pgAdmin beta (checkout the Visual Tour), I figured it was about time for quick blog post to prove I am still alive :-p

Some people will certainly be aware that Sony Online Entertainment are a customer of EnterpriseDB and use our PostgreSQL-derived Postgres Plus Advanced Server product as the database behind a number of the services they offer - none of which I really knew anything about. Well for the first time I can actually point to one of their new games called Free Realms which is built on Advanced Server. I can't say I'm a gamer so I won't even try anything like a review, but it looks like some serious work has gone into it, and I can imagine my kids spending far too much time on it given half a chance!

For an open-source geek this is pretty cool stuff, right up there with Yahoo's use of pgAdmin with their massive Everest database - forget unseen financial systems, company CMSs or website shopping carts - this is code I (and many others) have hacked on and is being used to power fun and interesting stuff that potentially appeals to millions of users.

So, feel free to feed my geek ego and check it out :-)

The first conference of the year

2009-02-07T08:13:00.003Z

So after a traumatic journey to get the the Eurostar terminal at St. Pancras in London through the most snow we've seen in 20 years around my hometown, I finally arrived in Brussels with Greg for FOSDEM. After settling in to our hotel rooms, we met up with some of the usual suspects and wandered off to our planned dinner at Restobieres where we had a bunch of different beers, and dinner. Various other people joined us throughout the evening, including Magnus and Selena who met in Amsterdam and took the train here.

Which leads me to the point of the post. Well, as much of it as I can remember. After dinner a small group of us decided to go to Cafe Delirium where the FOSDEM beer night was being held. For reasons that now escape me, but were related in some way to us not knowing where on earth we were going, I promised to blame Selena for the fact that we had to follow a MySQL guy (yeah, I know, I know) to the square by the Cafe.

So, whatever the problem was at the time - Selena; it's all you fault.

There - don't say I don't keep a promise :-p

And now, breakfast, and off to day 1 of FOSDEM.

pgAdmin - change of licence

2009-02-04T09:44:00.003Z

Effective from the 26th February 2009, the pgAdmin Development Team intend to change the licence of pgAdmin III from the Artistic Licence v1.0 to the Artistic Licence v2.0 (http://www.opensource.org/licenses/artistic-license-2.0.php)

The change of licence is in response to criticism of its wording from the Free Software Foundation ("too vague; some passages are too clever for their own good, and their meaning is not clear."), as well as issues raised during legal proceedings in the US.

The licence change will apply to future releases of pgAdmin III; both official versions and updates released with PostgreSQL installers. Existing users may, at their option, use older versions under the terms of the new licence.

If you have contributed to the pgAdmin III project in the past and have any questions or objections regarding the intended licence change, please contact me immediately at dpage (at) pgadmin (dot) org.

PostgreSQL Early Experience Installers

2009-01-02T20:25:00.003Z

Hot on the heels of Devrim's Snapshot RPMs, I'm pleased to say we now have 'early experience' one-click installers available to download and play with the latest features committed to the PostgreSQL and pgAdmin source trees. We're aiming to update the installers periodically after interesting features are added - the first of which is Hitoshi Harada's SQL:2008 Window Functions patch for PostgreSQL.

You can download the installers for Linux 32 and 64 bit, Mac OS X 10.4+ and Windows.

Note that as development builds, these packages have had very little testing and should not be used on production systems.

PostgreSQL management on the iPhone

2008-12-16T19:54:00.006Z

Back in the summer Bob Zurek (CTO at EnterpriseDB) persuaded me to spend some time on a simple iPhone application for remote administration of PostgreSQL and Postgres Plus. I decided to write it as a web app as I didn't really have the spare time to learn Objective-C to the level required and besides, there are certain advantages to handling most of the processing server-side - for example, paging of large result sets from arbitrary user queries can be made more efficient. As is often the way, other projects took over and it fell by the wayside despite being more or less complete, but I finally managed to find the spare cycles to get Sachin from our installer team to package it up (thanks Sachin) - and I'm pleased to say that as of today it's finally available through StackBuilder for those of you with an iPhone or iPod touch.

So, what does it do? Not a massive amount admittedly - it's designed primarily for emergency maintenance (and frankly, who wants to do their day to day work on a phone?), but as can be seen from the main menu there are options to view server info, connections, GUC variables, locks, prepared transactions, and the ability to run arbitrary SQL queries.

The Info tool for example, displays various details of the server you're connected to. The installer will setup a connection to the local server, but you can add as many more as you like:

Probably the most useful feature is the ability to run arbitrary SQL queries. Batches of rows are summarised in a list, with the usual option at the end to get fetch the next batch. Selecting a row will display the complete set of values - the screenshot below captures the row details sliding across the display from the right:

If you want to have a go, just select the pgPhoneHome download in StackBuilder.

Oh... and while I think of it, we also updated the installer for the PostgreSQL JDBC driver to v8.3-604 at the same time.

Enjoy :-)

StackBuilder application updates

2008-12-05T12:28:00.003Z

I've just published a number of updates to various StackBuilder applications (if you're not sure what Stackbuilder is, see my previous blog post which included a brief description). The updates available are:

ApachePHP has been updated to Apache v2.2.10 & PHP v5.2.6 (Linux 32/64 bit, OS X and Windows).
Drupal has been updated to v6.6 (Linux 32/64 bit, OS X and Windows).
mediaWiki has been updated to 1.13.2 (Windows).
psqlODBC has been updated to 08.03.0400 (Linux 32/64 bit, OS X and Windows).
EnterpriseDB Tuning Wizard has been updated to 1.2 (Linux 32/64 bit and OS X).

So, fire up StackBuilder and start downloading those updates!

Going mobile with a Linux netbook

2008-12-04T09:59:00.003Z

A friend of mine bought a Linux based Acer Aspire One netbook, and asked me to help get it working with her Three (a UK 3G network) mobile broadband dongle. It's not entirely straightforward to figure out, but is pretty easy once you know how.

Initial setup

- Click on Settings and run the Aspire's Live Update program to update the operating system with the latest patches. Reboot when prompted. Keep doing this until Live Update reports that all updates have been installed.

- Shutdown the Aspire, and plug in the Three modem.

- Boot the Aspire, and select the Mobile Partner app on the Connect tab.

- Wait for Mobile Partner to detect the modem.

- Select Tools -> Options, and the click the New button.

- Give the profile a name, such as 'Three mobile broadband' and configure the following options:

APN type: Static

APN name: three.co.uk

Access number: *99#

Username: three

Password: three

Protocol: chap

- Save the profile.

Daily use

- Boot the Aspire, and plug in the modem. If a file browser window opens, close it.

- Run the Mobile Partner app on the Connect tab.

- Wait for Mobile Partner to detect the modem.

- Click the Connect button.

- When Mobile Partner has connected, minimise (don't close) it, fire up the web browser and away you go!

And what's the PostgreSQL angle you might ask? There isn't one. Sorry.

More StackBuilder apps available for Linux & Mac

2008-11-17T21:27:00.005Z

If you've ever used PostgreSQL 8.3 on Windows, you'll be aware of StackBuilder, the package management tool that's included for downloading and installing additional tools, drivers and applications to work with PostgreSQL. Designed to be really easy to use, you simply run StackBuilder, choose the applications you wish to install (any dependencies will be automatically installed as well), and it will download them and set them up to work with your PostgreSQL installation.

With the release of PostgreSQL 8.3.5 a few of weeks ago, we (the excellent team I work with at EnterpriseDB) added Linux and Mac ports of StackBuilder to the PostgreSQL 'one click' installers that we maintain, and as of Friday have an almost identical set of applications available via StackBuilder on Windows, Mac and Linux (32 and 64 bit).

So, currently available for all four platforms, we have:

PostgreSQL 8.3.5
psqlODBC
pgJDBC
PostGIS
Slony
Apache + PHP
Drupal*
mediaWiki
phpBB
phpPgAdmin
phpWiki

In addition, we also published our own Tuning Wizard and MySQL Migration Wizard apps. We're working on additional updates and new apps all the time, so fire up StackBuilder regularly to check for new packages.

* Note: The Drupal release for 64 bit Linux has been delayed due to a last minute issue found during testing.

Ladies and gentlemen, normal service has been resumed...

2008-11-17T14:16:00.000Z

As you may have heard, there was recently a major disaster with planetpostgresql.org. After a subtle nudge from Magnus, I figured I'd better sort out a new home for my blog as apparently I'm one of few remaining 404's on the PostgreSQL blog aggregator.

So, here we go. As a fairly heavy user of Google products already, it made sense to use Blogger, which despite the expected issues with the WYSIWYG editor and copy/paste on the Mac, so far seems pretty good. I've managed to scrape together most of the old posts thanks to the WayBack Machine which is good. A few have probably gone forever, but such is life.

Anyway, more later - I'll post a news update on some PostgreSQL work shortly...

LinuxLive Report

2008-10-27T13:06:00.000Z

PostgreSQL had a booth in the .Org Village at LinuxLive/MacLive on Thursday, Friday and Saturday of last week.

Greg Stark and I attended all three days, and brought badges, pins and shirts from the UK conference, as well as signs, flyers and demo hardware from our office. Mark Cave-Ayland split his time between our booth and his companies on all three days. Simon Riggs joined us on the Friday, during which time he was interviewed by the Ubuntu guys for their regular podcast. Gianni Ciolli joined us on the Saturday.

The show was pretty quiet, with both .Org and commercial stands commenting similarly. We did see a fairly steady stream of people throughout all three days however, with roughly a 50/50 split between existing users stopping for a chat, advice or to find out what's going to be in 8.4, and potential new users. By far the most popular question asked was 'why should I use PostgreSQL instead of MySQL', second to which were questions about replication (ie. when will we see PostgreSQL RAC? !!).

Things we should have taken but don't have include:

- PG vs. MySQL flyers
- PG.EU flyers. What is it, mailing lists, conferences etc.
- 'Famous users' poster
- 'What's new in 8.4' poster
- Usage example posters (eg. company Foo use PG + Slony to do whizz bang stuff).
- Elephant Juice (after seeing people getting merry on 'Drupal Drops'!)

Thanks to all those who helped out, and 'hi' to anyone that stopped by the booth, and to the guys from Debian, OpenBSD/OpenSSH, UKUUG, Bongo, OpenNMS and anyone else we were hanging out with!

Dodgy pics from the phone at http://www.flickr.com/photos/29058417@N03/

UK PostgreSQL Conference 2008

2008-02-21T14:04:00.000Z

So finally after years of on-off discussions at the pub, Simon Riggs and I have finally got the first ever UK PostgreSQL Conference organised! It will be on April the 2nd, in central Birmingham. The theme is 'PostgreSQL in the UK'.

So, to avoid disappointment, visit the conference website and book your place today!

Setting up Slony-I with pgAdmin

2007-09-27T13:48:00.001+01:00

pgAdmin has had the ability to create and manage Slony-I replication clusters for some time, however it is designed to allow the user to work directly with the lower-level Slony concepts such as listens and paths. This is pretty flexible, but isn't particularly user-friendly. We are hoping to include some wizards in the future to make it much easier to setup and modify clusters, but in the meantime here's a long overdue walkthrough using the existing features to create a 3 node replication cluster.

In this example, a master server is setup with two direct slaves. This example was written and tested using Slony-I v1.2.11 and PostgreSQL 8.2.5, running on a single Windows XP machine. The PostgreSQL pgbench utility is used to generate the test schema and workload.

1) Create 3 databases, master, slave1 and slave2 and ensure pl/pgsql is setup in each.

2) Create a pgbench schema in the master database:

> pgbench -i -U postgres master

3) Add a primary key called history_pkey to the history table on the tid, bid and aid columns.

4) Create a schema-only dump of the master database, and load it into slave1 and slave2:

> pg_dump -s -U postgres master > schema.sql
> psql -U postgres slave1 < psql -U postgres slave2

5) Create Slony config files for each slon engine (daemon on *nix). The files should contain just the following two lines:

cluster_name='pgbench'
conn_info='host=127.0.0.1 port=5432 user=postgres dbname=master'

Create a file for each database, adjusting the dbname parameter as required and adding any other connection options that may be needed. (Windows only) Install the Slony-I service: > slon -regservice Slony-I

6) Register each of the engines (this is only necessary on Windows - on *nix the slon daemons may be started individually and given the path to the config file on the command line using the -f option):

> slon -addengine Slony-I C:\slony\master.conf
> slon -addengine Slony-I C:\slony\slave1.conf
> slon -addengine Slony-I C:\slony\slave2.conf

7) In pgAdmin under the Replication node in the master database, create a new Slony-I cluster using the following options:

Join existing cluster: Unchecked
Cluster name: pgbench
Local node: 1 Master node
Admin node: 99 Admin node

8) Under the Replication node, create a Slony-I cluster in each of the slave databases using the following options:

Join existing cluster: Checked
Server: <Select the server containing the master database>
Database: master
Cluster name: pgbench
Local node: 10 Slave node 1
Admin node: 99 - Admin node

Join existing cluster: Checked
Server: <Select the server containing the master database>
Database: master
Cluster name: pgbench
Local node: 20 Slave node 2
Admin node: 99 - Admin node

9) Create Paths on the master to both slaves, and on each slave back to the master. Create the paths under each node on the master, using the connection strings specified in the slon config files. Note that future restructuring of the cluster may require additional paths to be defined.

10) Create a Replication Set on the master using the following settings:

ID: 1
Comment: pgbench set

11) Add the tables to the replication set using the following settings:

Table: public.accounts
ID: 1
Index: accounts_pkey

Table: public.branches
ID: 2
Index: branches_pkey

Table: public.history
ID: 3
Index: history_pkey

Table: public.tellers
ID: 4
Index: tellers_pkey

12) On the master node, create a new subscription for each slave using the following options:

Origin: 1
Provider: 1 - Master node
Receiver: 10 - Slave node 1

Origin: 1
Provider: 1 - Master node
Receiver: 20 - Slave node 2

13) Start the slon service (or daemons on *nix):

> net start Slony-I

14) Initial replication should begin and can be monitored on the statistics tab in pgAdmin for each node. The pgbench utility may be run against the master database to generate a test workload.

OpenSSL on Windows

2007-08-06T13:44:00.000+01:00

OK, to get the usual intro over and done with - blog, neglected, back now, very sorry, won't do it again.

And now onto business... Ben West reported a problem with pgAdmin III v.1.8.0 Beta 2 on Windows which seems to have gained a dependency on MSVCR71.DLL (that's the Visual C++ runtime library for VC++ 7.1). pgAdmin is built with VC++ 8 these days so we really don't want to ship the 7.1 runtimes. After a little investigation, it seems that the latest builds of OpenSSL from Shining Light Productions are built using VC++ 7.1

So, it seems we need to build OpenSSL ourselves now to avoid this issue. I struggled to find the details of how to do that, so after finding the relevant info elsewhere, I'll repeat it here for future reference...

So, here's how it's done. You will need something to unpack the OpenSSL source code (such as tar with gzip support), and a perl installation, as well as VC++.

Download the OpenSSL Source.

Start a Visual Studio command prompt for the version of Visual C++ you need to compile against (8.0 for us, otherwise known as 2005).

Unpack the OpenSSL source, and CD into the source directory.

Configure the source code:

C:\openssl-0.9.8e> perl configure VC-WIN32

Generate assembler code for speed:

C:\openssl-0.9.8e> ms\do_masm

Finally, the libraries can be built:

C:\openssl-0.9.8e> nmake -f ms\ntdll.mak

And that's it! The OpenSSL DLLs should be found in the out32dll directory.

Joining EnterpriseDB

2006-12-07T13:40:00.000Z

Once again I've been neglecting my blog - this time it's been months and months since my last entry which looking back I see was a drunken rambling following a conversation with (and about) Jim Nasby in the company of Magnus and an assortment of people from EnterpriseDB. Which is quite a coincidence really as the point of this blog entry is to announce to anyone that's interested that after more than 11 years at Vale Housing, I'll be joining EnterpriseDB in January.

So, from the new year I'll be braving the A34 on a daily basis to work primarily from the EnterpriseDB Performance Center in Bicester, near Oxford. PostgreSQL regulars may already know that means I'll be sharing an office with well known PostgreSQL hackers Simon Riggs, Heikki Linnakangas and Greg Stark.

As for pgAdmin and my other community work, EnterpriseDB have kindly agreed to allow me time to continue contributing to all the PostgreSQL projects I work on so I expect to dedicate as much time to pgAdmin development and the Windows PostgreSQL distribution as I've been able to do at Vale.

Anyway, enough rambling - if you want to know more about EnterpriseDB, checkout the website, or (for reasons that I still find somewhat baffling), the marketing guys decided to mention me in a press release.

Thought for the night...

2006-07-09T13:39:00.000+01:00

It seems I have been remise in writing up my experiences in Toronto as a number of the other hackers have been doing during the PostgreSQL 10th Anniversary Summit. So, to that end and in my current slightly drunken state I offer you this thought for the night that arose fromm a conversation with Jim Nasby, Denis Lussier, Simon Riggs, Magnus Hagander and Greg Stark:

What's the use-case for a Jim Nasby?

And though I'll probably think "what the hell did I write that for?" in the morning, I'll just hit 'Publish' and tootle off to bed...

pgAdmin gets plastic surgery

2006-07-05T13:37:00.001+01:00

Well, given the complete lack of activity on planetpostgresql/dpage you'd be forgiven for assuming I was dead or had been kidnapped by the Lizard Alliance to be used as a crash test dummy, but no, I've just been lazy and haven't written anything recently.

So, to put that to rights, here's a pgAdmin update. For the last few days I've been working on cosmetic and usability issues in the user interface, and consequently, 1.6.0 (when released) will sport a fancy new docking user interface on the main windows, allowing you to arrange elements as you like, detach panes, move toolbars around and display or hide individual elements. There are also popup docking hints to make it easy to re-dock elements exactly where you want them...

Other recent improvements include a host of tweaks to the Query Tool including regexp search and replace, error word highlighting and brace matching, as well as enhancements elsewhere such as the ability to display and set boolean columns to NULL in the Data Editor, an XML/XHTML reporting engine with a host of schema and data reporting options and initial support for the PostgreSQL 8.2-dev.

Anyhoo, that's enough for now. I'm leaving for the PostgreSQL Anniversary Conference early tomorrow morning so need to go and pack...

Random Ramblings

2006-03-15T13:36:00.000Z

Some random noise for the old blog...

First off, an article by Jason Gilmore discusses 5 outdated reasons why people don't choose PostgreSQL. pgAdmin gets a mention under the No professional development and administration tools section. The article was picked up and discussed in depth on Slashdot.

Second, (in a shameless plug) a small site I've been developing in spare five minute sessions for my wife Jo went live the other day.www.jo-jos-jewellery.co.uk is powered by PostgreSQL 8.1 (of course), running with Apache and PHP on FreeBSD. The more interesting part of the work was interfacing the site with Paypal who provide some great resources for developers, including an extremely useful sandbox version of their site for developers to test and experiment with.

'Nothing spectacular' I hear some of you cry - well, perhaps not. Certainly technically there's nothing particularly difficult in the site code, but coming from a decidedly non-commercial work environment as I do it made a very refreshing and welcome change to do something completely different. Now if only I could get my head round Google Adwords...

pgAdmin III v1.4.2 Released

2006-03-07T13:33:00.001Z

The pgAdmin Development Team are pleased to announce the release of version 1.4.2 of pgAdmin, the Open Source administration and development platform for PostgreSQL 7.3 and above. pgAdmin can be run on Linux, FreeBSD, Mac OSX, Solaris and Windows. For more information, please see the website at:

http://www.pgadmin.org/

pgAdmin can be downloaded free of charge from:

http://www.pgadmin.org/download/

This version is a bug fix release containing the following changes:

Fix a typo in the admin pack README [August Zajonc].
Fix serial column detection for 8.1 per Kris Jurka.
Mask the password when logging the connection string.
Properly escape single quotes in connection strings.
Re-arrange the Win32 dependencies so that iconv, libxml2 and wxwidgets are located in $PGADIR/../pgadmin3-deps/.
Properly enable the redo button in the Query Tool when appropriate, per PromoTrade.
Add missing files to the Unix tarball, per Andrus - xtra/wxbuild/*, src/utils/precomp.cpp, src/main/pgAdmin3.rc.
Read config files in the local encoding scheme.
Display the definition of set returning procedures correctly.
Fix drop procedure by including IN/OUT parameter flags in the name.
Fix role SQL - correctly define CREATEROLE/NOCREATEROLE per Andrus.
Honour the copy quoting setting properly in the SQL results pane.
Set the initial Unicode/Local charset options correctly in the Export dialogue.
Fix PK detection in EditGrid per Andrus Moor.
Fix Bitmap Index explain per Alexander Kirpa.
Use ELSIF not ELSEIF in pgagent.sql for compatibility with 7.x servers [Glen Sasek].
Exclude system schemas by name rather than OID, so that 'public' can be safely renamed.
fix slony node statistics.
Fix: Unsubscribe set on receiver.
Another wxComboBox(2.6.2) related fix.

Dave Page
pgAdmin Development Team

Speeding up the pgAdmin query tool

2006-02-28T13:27:00.001Z

The pgAdmin query tool (see the bottom two screenshots here) has caused people to mistakenly think their PostgreSQL queries have been overly slow a number of times in the past. In actual fact, PostgreSQL is normally running just fine, as is the query. Some users have then pointed their finger at the network, questioning whether SSL might be to blame, or the TCP/IP stack or firewall on their client machine. Well, sometimes the network is the cause, but in a number of cases what the user is actually seeing is the machine's native listview control taking it's time leisurely rendering the results.

We've actually been aware of this for a long time, and have noted the problem a few times on various mailing lists. We've even gone some way to alleviating the problem by showing two query time - if you look in the bottom right hand corner of the query tool you'll see that timings look like: 51243ms + 65343ms.

This is telling you that the query was executed by PostgreSQL, and data transferred to the client in 51.243 seconds, and it was then rendered in the grid in 65.343 seconds.

Edward Di Geronimo Jr recently stepped up and started woring on improving this - specifically to reduce that second figure that tends to throw people, and to provide enhanced copy/paste capabilities. After just three revisions to his original patch it is now looking ready to apply and is showing rendering time consistently reduced by about 75%! In addition, you can now copy data from arbitrary cells, columns, rows or blocks of cells. Nice.

So how does it work? Well, the original code ran the query, and asynchronously populated a wxListView control in a background thread from the result set once the query completed.

The first change in the new code replaces the wxListView control with a wxGrid control. The wxGrid is known to be more light-weight than the wxListView, but is less feature-rich and refined. This meant that special handling was needed to work around quirks such as the control not properly receiving focus when column or row headers were clicked.

The other major change was to create a new, extremely lightweight class (sqlResultTable) to store the query result itself, rather than relying on the grid. The grid is then populated from the result table.

So, the upshot is that the query tool is much faster than it was, and more convenient to copy data from - look out for it in pgAdmin III v1.6!!

And before I forget, many thanks to Ed for his hard work.

www.pgadmin.org gets a facelift

2006-01-17T13:27:00.001Z

Well, I figured it was about time to update the old blog with a new entry for 2006 (Happy New Year btw). A short entry this time - I've just finished a major update of the pgAdmin website.

Nothing spectacular, just:

an update of the colour scheme to match the current version of pgAdmin
a re-write of the framework code and CSS
a whole heap of reorganising of content in order to split some of the excessively long pages down into multiple smaller ones.
the addition of the 1.4 and development documentation, updated hourly from SVN

Currently it's all in English, but in a week or so once all the typos have been spotted I'll pass it on to the translators.

Anyhoo, check it out at http://www.pgadmin.org. Enjoy.

A jolly in Tokyo

2005-11-02T13:25:00.001Z

So, as part of our work porting Slony-I to Windows, and adding full support to pgAdmin, Andreas, Magnus and I were invited to present our work at a seminar in Tokyo, organised by Hiroshi Saito and his employer, SKC. We met in the departure lounge at London Heathrow from where we flew to Narita, Tokyo on Wednesday last week. Hiroshi met us at the airport, and brought us back to our hotel in Shimbashi.

The first evening we attended a welcome party with various members of the Japan PostgreSQL Users Group or JPUG for short. After a good meal and plenty of food it was off to bed to recover from the 30 hour first day!

On Friday we travelled by train to the seminar, and after an Italian lunch(!!) proceeded to give our talks to around 70 JPUG members. I spoke first on replication in general, and then on the Slony porting project and how we worked. An hour later, Magnus took to the stage and discussed the details of porting slon in more depth, and then gave a perfectly executed demonstration of Slony-I running on Windows. Finally, Andreas discussed the new management features added to pgAdmin.

[From left to right, Andreas Pflug, Magnus Hagander, myself and Hiroshi Saito]

After the presentation, we headed down to the restaurant in the building for and evening reception with beer, buffet and even a little bingo. Lot's of business cards were exchanged with a variety of PostgreSQL users and developers, and even one of the Firebird team!!

On Saturday, we spent a day sight-seeing, mainly in Kamakura where we visted a couple of temples and a very large cast iron Buddha. Lunch was Korean barbeque, following which we took a trip back to Tokyo station on the Shinkansen, or Bullet Train. A few beers in the 'Victorian Pub' across the courtyard from the hotel entrance, and it was off to bed before the journey back to the UK, Sweden and Germany in the morning.

All told, an excellent trip on which we were welcomed by our extremely hospitable Japanese friends. Many thanks in particular to Hiroshi Saito and SKC, not to mention the rest of the JPUG members.

Slony-I on Windows

2005-09-16T12:29:00.001+01:00

Magnus pinged me last night and was kind enough to point out how neglected my blog had become recently, so I figured I'd better write a new entry before the cobwebs become too thick to see through. In my defence, I've been incredibly busy recently with upcoming releases of psqlODBC, pgInstaller, pgAdmin and PostgreSQL, not to mention being busy in my day job as well!

So, in order to put things write I figured I'd mention the 'other' project I've been working on recently, the Windows port of Slony-I. A bunch of us (well, myself, Magnus Hagander and Andreas Pflug) were assembled into a team by Hiroshi Saito to port Slony-I to Windows, update the documentation, sort out the regression tests, and add full support to pgAdmin. Just before lunch, I posted the final patch (the documentation) of that little lot to the Slony mailing list for review, and finally, the end is in sight! Bar a couple of minor improvements to make, thats pretty much the project completed - so come the next major Slony release, you'll be able to replicate you PostgreSQL databases to and from your Windows boxen.

So, effort now moves to our presentation. Part of the deal with Hiroshi is that we present our work to the Japan PostgreSQL Users Group (JPUG), so at the end of October the three of us will be jetting off to Tokyo for a few days of trains, sushi and sake. I can't wait to get there

Hello Solaris, I'm pgAdmin III....

2005-08-29T13:22:00.001+01:00

For a while now we've had anecdotal reports of people compiling and running pgAdmin III on Solaris systems, a few successfully and a few unfortunately not. I was recently given the use of a Sparc box running Solaris 10 and asked to sort things out properly, so being the eager-to-please kind of chap that I am decided to do just that. The owner of the box had tried numerous combinations of configure options for wxWidgets (the cross platform API pgAdmin is built on), but had only managed to build an executable that crashed whenever a modal dialogue was closed.

So, after some investigation by myself and Andreas Pflug, I produced a reproduceable test case of the problem using the wxWidgets 'dialogs' sample, and emailled it to the wx developers list, along with details of some linking problems I'd also found. After some discussion, and speculation that the problems might have been caused by GTK being compiled by Sun's compiler and everything else by GNU's GCC, the problem was solved by Mike Wetherell, who committed a fix to the wxWidgets CVS, along with further fixes to resolve the linking issues.

So, using a wxWidgets snapshot, coupled with the latest pgAdmin snapshot (containing a couple of other compilation and linking fixes), pgAdmin sprang to life both on the Sparc box, and the x86 Solaris box I built in the office

'Enough of the yacking' I hear you cry, and 'how do I build it then'? Well, simple - here's what I installed:

PostgreSQL 8.0.3 configured with:

./configure --enable-thread-safety --with-openssl --without-readline

wxWidgets CVS snapshot from 2005-09-26 (or later, use the 'All' version) configured with:

./configure --with-gtk --enable-gtk2 --enable-unicode --enable-mimetype=no

pgAdmin SVN snapshot from 2005-09-27 (or later), configured with:

./configure

There you have it - enjoy

psqlODBC grows up

2005-07-22T12:28:00.000+01:00

If you've been following the development of psqlODBC at all, you'll probably know the Anoop Kumar from Pervasive Postgres has been working hard to update the PostgreSQL ODBC driver to use the standard libpq interface to communicate with PostgreSQL. The first version of the driver was released as a snapshot version about a week ago, and has been put through it's paces since then. A few memory leaks and other bugs have been fixed, and things are looking pretty darn good so far, considering how large these changes are.

There are immediate benefits to this change. First, psqlODBC now supports the v3 wire protocol now used by PostgreSQL, and when v4 is produced as it inevitabley will be, we'll automatically be able to support that as well. Second, we now benefit from other features that libpq provides - for example, support for environment variables like PGUSER and PGDATABASE and support for password files. It should also support authentication methods like Kerberos with little or no extra work.

One of the most requested features of psqlODBC is SSL support. Thanks to Anoop's hard work, I was able to add support for SSL to the driver in a little over an hour today - that will be included from the next snapshot release, 08.01.0003, which will probably be produced sometime next week.

Anyhoo, if you get a minute, grab the latest snapshot, and give it a whirl. Bug reports to pgsql-odbc@postgresql.org please!

It's Alive!

2005-05-25T12:27:00.001+01:00

Finally, after 2 weeks of frantic hacking between paid work, pgAgent is ready for testing! Anyone who follows the pgAdmin development work will already know this, however for those that don't, pgAgent is a job scheduler for PostgreSQL. Running as a service on windows and a daemon on *nix systems, it allows you to schedule jobs consisting of one or more SQL or shell/batch steps which run in sequence. Multi threading allows multiple jobs to run in parallel, and a nifty connection pooling mechanism ensures efficient resource usage on your server. Each job defined may have one or more schedules attached to it, each of which is based on an enhanced cron-style format. Management is from the pgAdmin console (of course), and the agents themselves can run on one or more machines at once.

Anyhoo, if you wanna give it a try, grab a snapshot from http://developer.pgadmin.org/snapshots/ and schedule away! Feedback is welcome of course - please send any to pgadmin-hackers@postgresql.org.

pgAdmin moves to Subversion

2005-05-05T12:26:00.001+01:00

So there I am, committing a whole heap of changes to the pgAdmin build system when Andreas Pflug emails me and suggest that whilst I'm at it, we could restructure the source tree to fix various issues that have cropped up since the project started. Good idea I think, however as anyone who has used it knows, you cannot move files and directories in CVS without losing the continuity of the history.

Subversion kept getting lots of good recommendations, and as it happens I was also replacing the main developement server, so after discussion and some playing with a test installation of Subversion for a while, we decide to migrate to it at the same time as we migrate to the new machine.

I won't write a full howto here, but I will include a warning about a gotcha that caught me out - to get the ViewCVS web interface runnng you need to install SWIG first, and then build and install the Subversion Python bindings - instructions for which are hidden away in subversion/bindings/swig/INSTALL in the Subversion source tarball. You will also need to use ViewCVS 1.0-dev, straight from the CVS.

So, yesterday it all went live, and so far, so good. I've made a heap of commits updating documentation and build scripts etc. and Andreas got in on the act with some source restructuring this afternoon. The only problems so far have been in ViewCVS. The first was my fault - diff's weren't working, and after 30 minutes or so of Googling and trying to understand the Python code (I don't speak Python yet), I realised it was because I hadn't installed GNU diffutils on the new dev box. D'oh! The second problem still needs investigation - the CVS Graph buttons are not showing up. It seems this is a known problem though.

Anyway, if you wanna take a look, head over to http://svn.pgadmin.org, or point your Subversion client at svn://svn.pgadmin.org/

By strange coincidence I see that the old "let's move to a new source control program" discussion is resurfacing on pgsql-hackers.... I wonder where it'll go this time?

Support rant - Dave goes 'off on one'!!!

2005-04-19T12:24:00.000+01:00

Warning: The following rant is not aimed at anyone in particular. It applies to a very small minority of the people I deal with on a daily basis who have unfortunately found a great way to cheese me off. I have been very careful to try not to make it sound like I don't respect end-users in general, however, if any of it does sound that way, it is entirely unintentional and I apologise in advance!

It's a fact of life that if you publically contribute to an Open Source project, then your inbox is going to bloat. Google currently has around 2000 hits for my primary email address in the web and groups databases, which means that that address is probably on a fair percentage of the spam address lists in the world- certainly it seems that way given that I can receive 200 or more junk messages on a bad day!

That's not what this rant is about though - I've long since given up complaining about spam and setup various filters instead that keep the majority of it from hitting my inbox at all. This rant is about support. Like many other people involved on OSS projects, I spend a great deal of time writing code and documentation, fixing bugs and helping users out, all for the fun of working on the project. What really irks me though, are the few users that cannot be bothered to take the time to help themselves and simply send a private email demanding immediate help with their 'problem'. Consider the following email, typical of a dozen or more I might receive in a week:

Dear Save,

I need Postgre for my prject. Tell me where I can download the Postgre and how to set it up for my project.

I expect you immediate attention in this matter.

Sincerly xyz

OK, first off, my name is Dave. Not Save but Dave with a 'D' for doughnut. Why should I be bothered to help you (for free) if you can't be bothered to get my name right? You would be amazed how often people do this - and it's not like Dave is a particularly unusual name.

Second, you got the project name wrong. OK, I'll grant you that we in the Open Source business have an annoying habit of giving projects cryptic names, but c'mon, Google has 5.75 million pages with PostgreSQL in them - surely that's a clue?

Right, third. Spelling & grammar. It's one thing if you're not an English speaker - heck, your English is probably far better than I'll ever manage to speak your language - but if you're going to demand help from someone, then it wouldn't hurt to check for at least the most glaring errors before hitting send. There really is no reason to spell the same word 14 different ways in the same message!

Next up, why are you mailing me directly? I'm one of a hundred or more people working on PostgreSQL, and nowhere is my email address listed as a support resource. OK, so I could maybe understand this more if it was a pgAdmin query given that I started the project (not that that makes it OK), but this is a PostgreSQL query.

Next one - downloading PostgreSQL. Really, how hard is it to go to the website and click 'Download'? A very wise PostgreSQL hacker recently said to me 'if someone cannot figure out that for themselves, what hope do they have of running a DBMS?'.

OK, number 6. 'Tell me how to set it up for my project'. WTF? I really do see this one quite often. How on earth am I expected to tell you anything about your project - and even if you had remembered to tell me what it was, why would I want to provide free consultancy? Bug fixing and a little guidance is one thing, but I'm not about to do your job as well as mine!

Finally, number 7. My immediate attention is expected? Yup, I'll give it my immediate attention - click, drag, drop, empty trash. Done. Next please.

OK, so it probably sounds like I've got a downer on everyone that asks for help - that is absolutely not the case. Although Eric Raymond puts it best in his paper How To Ask Questions The Smart Way, here's a few simple hints to avoid cheesing off the people you want a little help from:

Try to help yourself first. Check the website, the docs, Google Web/Google Groups. Above all, make sure you don't end up asking questions that are answered in easily accessible places such as FAQs, or worse, the project homepage.

Ask in the right place. Check the website for the project - there will almost certainly be support forums or other resources listed somewhere. Only contact the author directly as an absolute last resort.

Describe your problem clearly, and simply. Include system configuration details such as OS and software versions. If reporting a bug, try to include a procedure with which the bug may be reproduced.

Don't demand help. I personally take great exception to that, and I'm sure most other people do as well. Taking that sort of tone with me is a privilege reserved solely for my wife and my boss!

Don't expect your task to be done for you. Hackers and other users will usually help with problems, but not lazyness!

So there you go. How to piss off a hacker and how to get help in one simple rant!

This blog entry has been checked by an independent PostgreSQL hacker. Please send any objections to the sentiments held within to /dev/null

Err, OK then - a blog

2005-04-10T12:21:00.000+01:00

So there I am on a Saturday afternoon having just sent the kids off on a brief holiday with the in-laws, helping my brother sort out a DNS snafu with one of his sites (Endurance Life), when Devrim GUNDUZ pings me on ICQ.

"Morning Dave"

he says, followed by

"do you want to start a blog on PlanetPostgreSQL?".

My instant reaction is along the lines of:

"what's the point? Who'd want to read my drivel?"

and well, I'm still not convinced that anyone would! Still, Devrim pointed out that as one of the PostgreSQL webmasters, pgAdmin lead developer and a pgInstaller developer I might actually have something vaguely interesting to say, so for some reason that still completely escapes me, I said "OK, why not". I'm damn sure I'll get the mickey taken out of me by the anti-bloggers at work but who cares? So, anyway; I'm Dave Page and well, I guess this is my shiny new blog!