Slony on win32 progressing nicely

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...

GiST concurrency

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

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!

Two Phase Commit

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 activitiy

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.

PostgreSQL web ftp browser symlink fix

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.

Timezone code finished?

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.

Datatyping with PL/Perl

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.)

Success with OLEDB

I can happily report that the PostgreSQL OLEDB driver works perfectly with Kerberos integrated login with Active Directory! Didn't even need any hackings at all.

Yay!

DOSing PostgreSQL :-(

During my PostgreSQL coding last weekend I found a fairly horrible DOS in a library that PostgreSQL can be compiled with. It's totally exploitable to a DOS (double-free) in a PostgreSQL environment, and the user doesn't need to be authenticated to do it. The crashed backend will cause the postmaster to kick out all other backends and restart then - not good!

The good news is that it's not compiled-in by default, and even if you compile it in, you also have to explicitly enable it to become vulnerable.

Also, the guys with the library responded really fast (hours) and I expect a complete advisory along with a patched version to be out sometime the end of this week or beginning of next. At which times full details will be available...

In summary, there is nothing wrong with PostgreSQL here - it's just a vector to exploit the vulnerability in the library. So there will be no PostgreSQL security patch...

Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

Past

Warsaw User Group
Jun 29, 2020
Virtual, Virtual
Postgres Vision
Jun 23-24, 2020
Online, Virtual
PGCon 2020
May 26-29, 2020
Online, Virtual
pgDay.paris 2020
Mar 26, 2020
Paris, France
Nordic PGDay 2020
Mar 24, 2020
Helsinki, Finland
More past conferences