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