Andrew's PostgreSQL blog

I have a client who runs a PITR warm standby for their main production database. A couple of time recently it has fallen over, due to factors beyond the control of Postgres. The first time a data drive ran out of space, and the second time (today) a tablespace was created on the master that referred to a directory that didn't exist on the standby. Fixing this was really easy. Just remove the problem (clear space, create directory) and restart the standby. I'm really quite impressed by how robust the whole thing is. It's the sort of robustness that makes Postgres a joy to work with.

Productive as ever, Tom Lane seems to have taken a break from making his head hurt on replication issues, and instead fixed some long-standing problems we've had with the xml2 contrib module's memory management. My small contribution was to get it building correctly on Windows.

This means (among other things) that we can now use xslt_process to transform XML in various useful ways. Examples could include generating HTML (or XHTML) from it, or preparing XSL-FO output as a step towards turning it into PDFs, or just pretty printing it. I've just spent a little time verifying that things that were previously broken now work.

Here for example is a simple XML pretty printer function (it can probably be improved on, but this will do for a demo):

create or replace function xml_pretty_print(input xml) 
    returns xml
    language sql as
$func$

select xslt_process($1::text,
$$<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    version="1.0">
   <xsl:output method="xml" omit-xml-declaration="yes" indent="yes"/>
   <xsl:strip-space elements="*"/>
   <xsl:template match="@*|node()">
     <xsl:copy>
       <xsl:apply-templates select="@*|node()"/>
     </xsl:copy>
   </xsl:template>
 </xsl:stylesheet>
$$::text)::xml

$func$;

I have recently found myself doing a lot of XML for one reason or another, and the fact that Postgres just keeps getting better at it is good news.

The recent debate Bruce Momjian has started on the confusing mess that is the Python drivers for PostgreSQL made me feel very glad I just don't use any of them. As Just Another Perl Hacker, I am so glad that we have a good, well defined and fairly stable database API for Perl, namely DBI, and a pretty darn good implementation for PostgreSQL in DBD::Pg.

Add to these some of the higher end modules like DBIx::Class for use in frameworks and ORMs, and SQL::Translator for dealing with multiple database flavors, and Perl programmers doing database work are in pretty good shape.

Well, with some help from some comments on this blog and also from rereading this message from Aidan Van Dyk and understanding it a lot more this time, I think I have a handle on how the buildfarm should support Git repositories.

When the branch copy (which will be called [branchname]/pgsql, just as it is with CVS) exists, the buildfarm client will do an update by simply calling git pull. Where it does not exist, the client will create it by calling git clone followed by git checkout -t origin/[branchname] -b bf_[branchname].

This part is already working with my test buildfarm animal, in fact.

What I will add is support for an additional git_references config parameter, which can be used as part of the clone command. By default it will not be there, in which case the user would get a plain clone of the configured master repo. Setting up the references repo would be something documented but not done by the script.

One of the things that is mildly annoying from a developer perspective (it won't really affect buildfarm users) is that, as I understand it, when using Aidan's recommendation, getting updates into your working copy will require two commands rather than one (git fetch on your local master followed by git pull on your working copy). I guess that can be aliased, and it's something I will probably do pretty soon.

Today I made some small modifications to the buildfarm web app to support Git, and got a new buildfarm member quoll working with the new client code I have developed for building from Git. There are some wrinkles to work out, and no doubt there will be some bugs to fix, but I'm quite happy with the progress. I hope to release the new client code in a week or two.

One thing I have not been able to test (waiting on a new commit) is that the changed files list for git builds will point to commits on the git repository, like this. That will be a bit nicer than we've been able to do with CVS.

After a somewhat heated discussion, we seem to be approaching agreement on how to implement END{} blocks for PL/Perl. They will be done via callbacks registered using the on_proc_exit call. One significant use for this is to make PL/Perl profiling via Devel::NYTProf much less cumbersome.

The accompanying on_perl_init setting feature is still causing some angst, but there seems to be a bit of light at the end of the tunnel. I'm hopeful we can get some progress here without causing too much distaste.

I think the discussion has been instructive - when it has focused more on getting a way forward than on negativity it has been much more productive. There is often a tendency in our community to say "no". I am as bad about it as anybody. But it's worth remembering that people ask for things for a reason, and if we are going to say "no" or be skeptical, we need to make sure that we are still trying to address their needs.

At long last I have today returned to the task of getting the buildfarm to work with git. Actually, refactoring the code to remove most of the CVS-specific code from the main line is the big task in this. I don't want to have the code littered with code that tests the SCM type and takes action accordingly. Instead we'll have an scm object initialised with the right SCM type and method calls on the object. Once that is in place and working for CVS, implementing a new SCM object type for git will be comparatively simple. Unfortunately, the CVS-sprcific code is scattered all around, so it's proving to be a non-trivial task to hack it out.

On a related note, I'm a bit concerned that every git repo clone carries a copy of the whole base repo in it. That means that each repo copy comes in around 234Mb right now, as opposed to 80Mb for a current checkout of the CVS HEAD branch. Now I have four buildfarm animals regularly building some 15 branches, so that adds up to around 3.5Gb of disk space, nearly a 300% increase. I have it available, but that's a heck of a requirement. I'm certainly going to run those members against a local mirror of the repository, just as I do now with CVS.

I actually keep many more trees than this. I have a directory where I do development and committer work, and in there I have a pristine checkout of each stable branch that is alive, plus HEAD. For any non-trivial changes I create a copy of the relevant tree to work in. The reason for this is I'm often working on more than one thing at a time, and just cd'ing to the relevant directory makes switching from one task to another very easy. But if the trees become four times as big, that might start to become a bit impractical, and instead I'll have to start setting up my own local git branches. No doubt it's the way things are meant to be done, but it will take a bit of getting used to for me.

The Perl community have been great supporters of Postgres, and that's partly why PL/Perl is one of the most feature laden PLs around, as well as why DBI+DBD::Pg is one of the best client drivers around.

Now Tim Bunce, of DBI fame (among other things), has come along with some features and improvements for PL/Perl that will once again show that Perl + Postgres is just a great combination.

Already committed is his initial work on refactoring and tidying up the code, making it both easier to read and maintain, and more efficient in several respects. To go with this I have added some improved infrastructure that allows regression tests for both plperlu (the untrusted variety of PL/Plerl) and plperl+plperlu in combination. Up to now we only had provision for regression testing plperl, the trusted variety.

But there's much more coming. Tim has published a feature patch with all of the following:

• New GUC plperl.on_perl_init='...perl...' for admin use.


• New GUC plperl.on_trusted_init='...perl...' for plperl user use.


• New GUC plperl.on_untrusted_init='...perl...' for plperlu user use.


• END blocks now run at backend exit (fixes bug #5066).


• Stored procedure subs are now given names ($name__$oid).


• More error checking and reporting.


• Warnings no longer have an extra newline in the NOTICE text.


• Various minor optimizations like pre-growing data structures.


• New utility functions: quote_literal, quote_nullable, quote_ident,
  encode_bytea, decode_bytea, looks_like_number,
  encode_array_literal, encode_array_constructor.


• Enable plperl to "use"/"require" safely by redirecting the require
  opcode to code that dies if module not already loaded.

That's a lot of stuff, and maybe not all of it will make it into this new release, although I'm hopeful most of it will. The utility finctions will be both useful and a substantial efficiency gain. The initialization stuff, and the ability to preload certain modules (c.f. mod_perl) will make it much more attractive to create large applications using PL/Perl functions.

Currently, I use PL/Perl in two cases: first where I need to interact with something outside the database (the file system, another database system like SQLServer, atc.), or where I need some advanced facility Perl has that I can't easily get using PL/PGSQL. These changes will probably tip the balance more towards Pl/Perl.

I lost my temper a bit today (I know! hard to believe!). I was asked to do somre repair to a database where some schema changes had stopped my triggers working. So I looked at the two new tables. They were both junction tables, expressing many to many relationships. Such tables almost always have a very obvious non-surrogate key, namely the junction of the two primary keys from the other two tables. But did they have that? Not on your life. Instead they had a serial surrogate key on each table. Completely stupid and unnecessary.

For months we have been telling these developers that everything needs a primary key. Sadly, their reaction is to add a serial field to every new table, rather than actually exercising a few brain cells on working out what a sane primary key might be.

Josh Berkus is right. We need a Society for the Suppression of Surrogate Key Abuse. Sign me up.

I don't really take great joy in the turmoil in the MySQL world, but it's very tempting every time that Richard Stallman or Monty Widenius open their mouths on the subject not to cheer on the opposition.

This tragedy was eminently foreseeable (and indeed, was foreseen, by those not blinded by interest or ideology). Monty and Richard and a few others have laid the grounds for this to occur, so I'm not much impressed by their bleatings now.

And I don't appreciate misrepresentations and FUD concerning Postgres in Monty's latest rant. To buy up most of the major developers of Postgres, someone would have to acquire about six companies, all but one of which are closely held. Saying that the possibility of this makes Postgres vulnerable to the fate MySQL is now suffering is like saying that the possibility that the moon is made of green cheese means that all we know of astronomy might not be true. It's not going to happen, so raising it as a fear is just silly. And even if they did it would not put them in the position regarding Postgres that Richard and Monty have guaranteed to Oracle with their wretched dual licensing model.

Call me cynical, but maybe Monty has some interest in spreading some FUD regarding Postgres.

Also, it's not as hard to migrate MySQL apps to Postgres as he makes out. I have some experience in that area (e.g. see work I did on Bugzilla). Going the other way is much harder, because Postgres has a much richer, and extensible, type system.

I don't think Oracle will do MySQL much good, and I wouldn't trust them as far as I could kick them. But that doesn't mean we should just embrace any bad argument against the acquisition that comes along.

I came across this phrase many years ago in a wonderful book called Do You Sincerely Want To Be Rich? about the IOS financial scandal (no, nothing much has changed). It was used by IOS to tell sales people the attitude they needed to have when selling the (very badly managed, even fraudulent) mutual funds that were their business. The idea was that the sales person's ego could get in the way of making a sale.The sales person often had to put his/her ego aside in order to make the sale. They would be richer as a result.

It's a bit the same with PostgreSQL. Your ego can get in the way of your contribution. I try to remind myself of this frequently when an idea of mine is rejected or my code is criticised. Tempers flare and egos get bruised from time to time, even though in general the Postgres project is one of the nicest to work with that you can find in an open source project. I'm no better than anyone else in this respect. I get annoyed, and I can surely be as rude as anybody (well, almost anybody). But I have learned to take some deep breaths and laugh about things when I get annoyed. Life is too short to bear grudges or walk off in a huff.

There is a great story in Scott Adams' The Joy Of Work that goes like this:

Recently I attended an event where a well-known businessman was giving a talk. A member of the audience was acquainted with someone who once served on the board of directors with this businessman. He told me that the businessman was notorious for bringing ten new ideas to every meeting, of which at least nine were incredibly bad. The businessman was Ted Turner, billionaire founder of CNN.

So it seems to me likely that Ted Turner knows how to put his ego in his pocket. He doesn't mind rejection - he will just keep churning out ideas. (I suspect President Obama is another person with these characteristics.) Creativity is a percentage game. If even 5% of the ideas anyone has are good, we can all benefit. But only if the person who has those ideas is not afraid to communicate them, and is prepared for them to be rejected or fail. If you keep your ideas to yourself then they will certainly not benefit anyone, including yourself. And if you stop working on or thinking about something because your ideas are rejected then again you are depriving everybody, but yourself more than the rest.

I remind myself of this over and over again. Recently several ideas of mine for PostgreSQL have been rejected. In one case at least, ragged CSV import, I still think it was worth doing - the impact would have been very light indeed. But I have got over the annoyance and moved on.

I'm also very glad that KaiGai Kohei, author of SEPostgreSQL patches, has been so prepared to put his ego in his pocket. Not because I particularly enamoured of what he is doing, but because we need to value and nurture people who show his combination of technical competence and psychological maturity and persistence.

H/T Crad for this one

Last night, a rather rainy and unpleasant night in the North Carolina Triangle, I gave a talk to the Triangle Linux Users Group (trilug). The large conference room in the spiffy RedHat World HQ building on the NCSU campus was packed, rather to my surprise, as I talked for around two hours (my poor voice!) on some of the major features in the 8.4 release, as well as what's coming down the track in 8.5. I didn't cover the new features comprehensively, but focussed on things I thought important (and could talk about knowledgeably in some degree).

I don't know exactly how many people were there. It looked like maybe 100 or 150, and a larger crowd than at a couple of other meetings of the group I have been to.

The interest in Postgres is clearly significant and growing. We have a good message, and we just need to get it out there.

This is rather sad. I just made a client's query run 3 times faster, but it's not the nicest way to achieve a result.

Here's the faster version:

select source, max(datetime), 
	sum(case when days < 5 then 1 else 0 end)::integer as fivedays,
	sum(case when days < 10 then 1 else 0 end)::integer as tendays, 
	sum(case when days < 20 then 1 else 0 end)::integer as twentydays, 
	sum(case when days < 30 then 1 else 0 end)::integer as thirtydays, 
	sum(case when days < 60 then 1 else 0 end)::integer as sixtydays, 
	sum(case when days < 90 then 1 else 0 end)::integer as ninetydays 
from (
		SELECT source, datetime, 
		   extract(epoch from current_timestamp - datetime)::int/86400 as days 
		FROM a_big_table 
		offset 0
	) baserecords 
group by source 
order by source

Without that "offset 0" the query takes 30 seconds. With it it takes 9 seconds. I haven't profiled it, but I'm guessing that without it the query gets collapsed and "extract(epoch from current_timestamp - datetime)::int/86400" gets evaluated six times per row instead of once per row. As I understand it, offset 0 essentially forces the node for the inner query to be evaluated, rather than allowing the query to be collapsed.

Oh, well, the customer is happy. But it's as ugly as Cinderella's sisters.

We have a To Do item to allow use of named parameters in SQL functions. I wonder what it would take? I just tripped over myself badly though having to keep track of the right parameter numbers in a moderately complex SQL function.