Viewing entries tagged with postgresql. Return to full view.

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:


# 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';




  AS varchar(11)
    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.


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

The evil that is RPM

I just returned from yet another really bad experience with RPM. Scenario: RedHat AS 3.0, freshly installed this morning. Not a single package installed from anywhere other than the RHAS CDs. Needed to change the smarthost in sendmail. This required some 10-15 new RPMs to be installed. One of which was "glibc-2.3.2-95.30-i386". So I install it, and bang, RPM stops working with broken libraries. Turns out I should have installed the -i686 version, even though the system told me to install -i386.

The solution? Copy the i686 version over to a slackware machine, run rpm2tgz, copy it back and just untar it in the root. System back alive. Then run some rpm -i --force to make it understand it was there, and now pray it keeps working. It seems good.

No offense to the pg RPM people, but I would never risk using RPM to install my database. PostgreSQL goes in from source every time. (On Unix that is - on Win32 I use the installer. Because frankly, I've never seen this level of problems on a Windows machine as long as you don't install "weird software")

Now, I wouldn't run RedHat on the box if it was my choice either - slackware is my distro of choice for servers. But for some reason people who say "we support Linux" really only mean "we support RedHat". Which sucks really bad, but that's a different story...

Oh, and in about 1 hour of doing simple "rpm -i" on all the required packages, the RPM database was corrupted four times. Perhaps it's time for these people to put their data in a PostgreSQL database...

Finally, thanks to Devrim for attempting to help me solve this in a less hackish way.

Open source and support

So people keep telling me that you can't get good support for open source. At least, you can't get it unless you pay for it. At least not if you need actual code changes. At least not.. etc etc.

Yesterday I opened a bug with Mono, because it crashed (segfault) whenever I tried to execute anything on the Graphics class. In about 30 minutes I had my first response, asking for some more information. Another 20 minutes later and I had a solution - I was missing a scalable sserif font on my system (it's a server after all). And 5 more minutes confirming that a better error message will appear in a later version.

This is the second time I had to file a bug with Mono. The first time, it was XML-related, and an actual code bug. In this case I had a patch to apply to my installation within 48 hours, and it was included in the next release (in the meantime, I had a workaround).

In contrast, I've had an ipsec issue open with Microsoft since last week, without an actual solution in sight. Granted, this is a more complex issue than the ones above. And don't get me wrong, the guy(s?) working on it from the MS are doing a good job. But it generally takes more time. And more than once I've had cases closed with "no resolution, issue will not be fixed".

There are of course cases when things aren't fixed, but a (good or bad) workaround is provided in the open source world as well. But the argument that support is worse there just doesn't stand. In my experience, it's usually about as good.

Unless you are in a position where you can hire someone (or have someoen on your staff) that can actually fix the broken code. In this case, open source is a winner because that is possible to do this. That's just not possible in closed source - I can have an army of good coders, they still can't fix a bug in Windows.

Nice article about the installer

Seems at least somebody think we did a good job with the MSI installer for PostgreSQL on Win32 (and the db, but the installer was the focus). PostgreSQL vs MySQL vs Oracle on Win32, and PostgreSQL won the total score. And in the point specific about the installer, it was 10/10 for PostgreSQL, 5/10 for MySQL and 7/10 for Oracle.

Oh. The article.

Postgresql vs Active Directory

My project to integrate PostgreSQL with Active Directory is progressing nicely.

(Yes, this is a good thing. MS SQL Server has "Integrated Security" which means it leverages the exsting Windows login to automatically access the database without a separate password. In a domain environment (read corporate environment), this is usually a very good thing. Having similar functionality in PostgreSQL helps make migration easier.)

Once the patches that were included in 8.0.2 got in, the remaining job wasn't very hard. Getting basic kerberos interoperability working was a lot easier now than last time I tried it, lots of progress made on the kerberos distributions there.

So far it only works if your server runs on Linux (or any unix should work - the point is that the win32 native server currently does not work). The clients can run either Windows or Unix. It requires the clients to use libpq (which means perhaps it works with the OLE DB driver since it's based on libpq - I need to test that).

It also requires the server and libpq to be recompiled with a different compile option. I'm going to be working on a patch for 8.1 to solve that.

A HOWTO document will be written once I've ironed out the last parts of the process. What I have now is enough for me to deploy to a set of about 30 users for 10dbs, but the build instructions are not exactly clear ATM.

Oh, and big thanks to Dave and the pgAdmin team for putting out 1.2.1 so quickly which had a fix required for Kerberos authentication to work.

Windows interop weirdness plus torrent news

Hit something really weird in the windows vs linux interop department yesterday. Consider this:

Format a disk FAT32 in Windows. Mount it in linux, copy some files, reboot into Windows XP setup and proceed to install Windows. Works just fine.

Format a disk FAT32 in Linux. Mount in linux, copy some files. Reboot into Windows XP setup and proceed to install Windows. Works just fine - up to a point. After reboot (after Windows Setup has copied all files from the network to the local disk), the machine no longer boots - reports "Disk error" in the boot sector.

So much for interop. Solution? Do a disk image of a FAT32 partition formatted in Windows (empty - the image ends up around 10K for a 3Gb partition), and then restore that disk image from linux. Then it works. Hmmmm..

To make this post a little more PostgreSQL related, I got the new bittorrent stuff pushed out for yesterday. Now torrent downloads are automated, and they are automatically listed in the ftp browser. Should make it easier to find the torrents, and easier to make them track reality around new releases.

Getting started

So I guess Devrim talked me into starting one of these as well - I think he wants to create an army or something. We'll see where it ends. There should certainly be some PostgreSQL stuff I can rant about...


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


Mar 5-8, 2020
Pasadena, USA
Nordic PGDay 2020
Mar 24, 2020
Helsinki, Finland 2020
Mar 26, 2020
Paris, France
PGCon 2020
May 26-29, 2020
Ottawa, Canada
PGConf.EU 2020
Oct 20-23, 2020
Berlin, Germany


Jan 31-Feb 2, 2020
Brussels, Belgium
PGDaySF 2020
Jan 21, 2020
San Francisco, USA
Dec 5, 2019
Saarbr├╝cken, Germany
Stockholm PostgreSQL Meetup
Dec 3, 2019
Stockholm, Sweden
Berlin PostgreSQL Meetup
Nov 21, 2019
Berlin, Germany
More past conferences