I have submitted a patch that runs Slony as a service on Win32. It supports multiple parallell engines (if you need to replicate more than one database) if needed.
Still needs a few things polished up (installation/uninstallation, registration of multiple engines etc) but I'd say win32 Slony is progressing very nicely. (Much of these things will probably be available in the installer and pgAdmin as well, but we'll need commandline interfaces as well)
As many PostgreSQL users are aware, PostgreSQL 8.0 does not fully support Unicode on win32 due to limitations in the API. Hopefully this will be fixed in 8.1, as we now have two different patches that solves this problem in different ways. The patches were posted a little over a week ago, but due to me sending it to the wrong list it didn't go through because of size. It has now been sent to -patches as well, and is available.
People interested in this are encouraged to apply one or both patches (the native patch is a whole lot easier than the ICU one, as it has much fewer dependencies) and test, test, test! You can grab the patches from the list archive. Let us know your results by a mail to -hackers-win32 or plain -hackers.
As of a couple of days ago, the Slony replication engine now runs on Windows.
First of all, this is far from finished work. It's just the initial part that has it up and running and doing simple replication. There are many details still left to iron out. The most noticable ones being support for running as a service and logging to eventlog, but there are others as well.
The changes so far have been smaller than I anticipated. Dave has done a bunch of work on the build system, and the slonik utility has also seen a couple of win32 specific patches not too long ago. But the slon engine is the toughest part, and so far things are looking promising for a patch that's not very disturbing to the rest of the code.
While far from being ready to put in production, if you are looking into deploying Slony on win32 in the future, now is a good time to start playing around with it. Get the latest cvs snapshot, build, and test. And the most important part, give us some feedback on how things are working...
Wow. This was pretty fast. On top of the WAL fixes, GiST concurrency fixes are now in, courtesy of Teodor and Oleg (and possibly others? Those are just the ones I know of..) With time to spare before 8.1. Great job guys!
I know at least one of my projects where this feature alone will make me upgrade to 8.1 the day it is released.
Midsummer coming up tomorrow, which means no PostgreSQL hacking for me for a couple of days. (Don't know what midsummer actually means? Visit Sweden for it once, and you'll know..) Once it's over, it's just a couple of days left to the feature freeze...
I got hold of Palle regarding the ICU patch, and it looks like it'll be ready in time for the freeze. Minor things left, and then I'll have to fix up the win32 build issues (which can be done, as I've done it hackishly before).
The issues of SIGTERM-to-a-backend are back up for discussion. Unsurprisingly, my very hackish idea for a pg_terminate_backend() function based on a flag in shared memory is not going in. Let's just hope that Tom (or someone else, but I'll bet it's going to be Tom eventually) figures out what's bad with the current behaviour (as lock table corruption has been reported) and can get a proper fix in before 8.1.
And I'll not even get into the discussion about the server instrumentation functions at this time...
As for the rest of the open items, I have no idea.. There's a lot of good stuff there, I hope it all gets in.
Happy Midsummer!
It's in. This is one of the big features for 8.1. I'm not sure of any project where I can use it right now myself, but it has been asked for a lot, so I'm sure it will be popular. I'll just have to figure out something to do with it myself.
Looks like we might have a different default database for tools to connect to in 8.1, to get around the "connected to template1, can't create a new database" problems that happen in previous versions.
On top of that, a generic database for tools like pgAdmin to store their config in - wether included by default in an installation or just an agreed-upon shared database name and structure that tools will autocreate.
Lots of activity in the PostgreSQL tree and on the lists lately affecting me directly or indirectly.
Seems we'll have GIST WAL Logging for 8.1. Hopefully we'll also have GIST concurrency. This is great - it'll bring tsearch2 to a whole new level /mha/templates/default/img/emoticons/smile.png alt=":-)" style="display: inline; vertical-align: bottom;" class="emoticon" / Yay!
It also seems the old autovaccuum-in-backend discussion is back up. Let's hope it gets in before the feature freeze this time. It's going to make life a lot easier for a lot of people - especially in the win32 camp where installation was previously a bit of a hassle. If it's done, we'll get it working with the MSI installer out of the box.
Andreas, of pgAdmin fame, is working on getting the backend instrumentation functions in this time - they were rejected for 8.0 because they were submitted too late. While a bit controversial to some people, let's hope they get in this time! Having to rely on contrib modules can be a pain sometimes.
I seem to be all out of pending patches. Both the pending timezone patches are in, and the kerberos work has been in for a while. Now perhaps I have time to look over that signaling patch, and to start digging into Slony.
Also seems the win32 build has been broken for a while. The fact that I didn't notice shows I haven't built on win32 enough lately.
I just finished off the docs part of my latest time zone patch. As Tom appeared at least reasonably positive, I'm hoping for this to go into the codebase shortly.
This patch in itself is fairly simple, and builds on the previous patch that was applied a couple of months back that removed the gobal timezone from the internal API.
I've just committed a fix to the PostgreSQL website ftp browser to fix handling of symbolic links in the ftp tree.
Previously, symbolic links were only read on creation of the browse pages. This made links to for example /ftp/win32 (which is a symbolic link to /ftp/binary/v8.0.3/win32) fail, since only the /ftp/index.html page was actually updated. Unfortunatly, just this link is present in the release notes for 8.0.3 - and was broken. Not good!
The new code generates Apache rewrite rules that does proper redirection of anything inside a symlinked directory.
PostgreSQL supports custom datatypes through either "full datatypes" or "DOMAIN"s. Doing full datatypes requires C-language coding that is loaded into the backend, whereas DOMAINs are SQL level. This makes DOMAINs a lot easier.
Next topic, procedural languages. PostgreSQL supports multiple procedural languages in the same installations - pl/pgsql which is "almost oracle pl/sql", pl/tcl, pl/python and... pl/perl. I like pl/perl mainly because it gives me the power of perl regexps :-)
Final topic before summary, Swedish SSNs (that's social security numbers, not nuclear attack submarines - we don't have those). All citizens in Sweden are given a "personnummer" (which is about the same as a SSN in the US) at birth, on the form YYMMDD-PPKX. Year, month, day should be self-explaining. PPK is a serial number, in which PP represents which area of the country the person is born, and K is even for females and odd for males. Finally, X is a checksum. You need a personnummer for almost everything. And of course, we need to store this in databases all the time.
Which brings me to the point of this post. Using a combination of DOMAIN and custom functions, you can create a simple datatype for personnummer that will automatically validate that the entered number is correct. Sure, if you have access you can validate it against a table of all valid personnummer, but that table is very restricted in availability. So we'll settle for validating the checksum. Here's how to do it:
CREATE OR REPLACE FUNCTION validate_pnr( text) RETURNS bool AS
$BODY$
# Basic validation
return 'f' unless ($_[0] =~ /^(\d{2})([01]\d)([0123]\d)-(\d|T)(\d|F)\d{2}$/);
return 'f' if ($2>12);
return 'f' if ($3>31);
# No checksum check for temporary numbers
return 't' if ($4 eq 'T');
# Checksum
my $s = substr($_[0],0,6) . substr($_[0],7,3);
my $m = 2;
my $c = 0;
while (length($s)) {
my $d = chop($s) * $m;
$c += ($d%2510)+int($d/10);
$m = ($m>1)?1:2;
}
$c = 10-($c %25 10);
return ($c == int(chop($_[0])))?'t':'f';
$BODY$
LANGUAGE 'plperl' IMMUTABLE STRICT;
CREATE DOMAIN persnr
AS varchar(11)
NOT NULL
CONSTRAINT persnr_check CHECK validate_pnr((VALUE)::text);
Simple as that. You will notice there is an exception for personnummer with the final four numbers as TFxx. These are used for temporary numbers for people who are not Swedish citizen but need to be registered in official databases that require a personnummer. Now we can use this like:
CREATE TABLE people (personnr persnr PRIMARY KEY, name varchar(128));
Or whatever. And the database will prevent us from putting in an invalid personnummer.
One could of course use the CHECK constraint on the table, but then we'd have to write that one once for each table. Using a DOMAINs we can re-use this information in multiple tables.
I'm sure this can be written in more efficient perl. If anybody wants to do it for me, please let me know and I'll happily update my installations /mha/templates/default/img/emoticons/smile.png alt=":-)" style="display: inline; vertical-align: bottom;" class="emoticon" /
(Sidenote to anybody blogging on worldpress (possibly only worldpress-pg? I'm not really sure): The thing eats backslashes in the editor. If you save multiple times, one level of backslashes is lost every time.. Also, the preview box is broken with this.)