I ported a fairly small webapp from Microsoft SQL Server to PostgreSQL today. Fairly small, but it still went pretty darn fast - took me just about an hour!
One thing that helped a lot compared to previous times I've done this is the new lastval() function in version 8.1. It takes away having to rewrite all those pieces of code that rely on the @@IDENTITY variable or SCOPE_IDENTITY() function in MSSQL. Thank you Dennis!
Apart from that there were two small things to do - get rid of the datediff() calls (and replace them with much much nicer PostgreSQL equivalents) and change JOINs to FROM on multi-table updates. Finally, I replaced the Full Text Indexes with tsearch2. Using the snowball stemmer in Swedish that also gives a lot better result than the old one, which was a nice bonus :-)
The Serendipity blog software on people.planetpostgresql.org has been upgraded to 0.9.1 for all registered blogs.
It took a lot more time and effort than it should have, because somebody seems to think it's a good idea to require the webserver to have write permissions on the php files and the directories where they live in order to do an upgrade, which is obviously a horrible idea from any kind of security perspective. It also had some bad conflicts with the safe mode in PHP, which I still don't really know what it was - took a lot of hacking before it finally worked. I'm just hoping it's a long time until the next major upgrade...
For now - enjoy your new version!
Ok, so better late than never. Or hey, at least it's late. But since Dave beat me to it anyway, I got kind of sidetracked. So to make it easy, start by reading Daves post, and I won't repeat, but just add a couple of quick notes instead.
So to summarise, I'll have to start repeating Dave anyway. It was a great trip, and a fantastic experience. Many thanks to Hiroshi, SKC and the JPUG. And of course also to Dave and Andreas!
This is actually a bit old news, but I've been out of town.
About a week ago, we got a patch from Qingqing Zhou (and changed around in several different steps my me and Tom Lane) applied to PostgreSQL 8.1 that changes the way win32 polls for the "fake signals" used to emulate Unix behaviour and eliminates a kernel call in a very active codepath. The end result of this: Very nice speedups on Windows. In some isolated test-cases up to 40%25!!! In general cases, it's going to be much less than that of course, but it is still a significan speedup. It appears especially efficient in scenarios with lots of concurrent access, bringing win32 performance much closer to what we have on Linux.
Way to go Qingqing, and thanks also to Merlin and Andrew for quick testing work.
My own update from the Japan trip coming up in a bit...
The first thing to notice is that I certainly didn't have time to write any blog posts during the event :-) I didn't have internet access from my hotel, just from the convention area, which also contributed to that.
In general, OSCON Europe was good. A few sessions were of "a bit lesser quality", but in most were very good. Most sessions were technically oriented, which is what I was looking for, but a couple were a bit more on the marketing side. And it's hard to get into deep technical stuff if you only have a 50 minute slot.
From a PostgreSQL perspective, it was very good. I got the definite feel that PostgreSQL was a lot better represented than MySQL when you looked at what people were interested in. We had a BOF, which they didn't. It wasn't a huge number of people, but there were some 10-12 of us at least, and some interesting discussions.
In general there were a whole lot of interesting people to talk to. Bruce and Simon from the PostgreSQL project of course, but a lot of others as well - both PostgreSQL users and people doing completely other things.
The most asked question from db users: What will happen with MySQL after the Innobase thing?
The most made-fun-of-in-talks-incident: Oracle buys Innobase.
Nobody really knows what will happen, and the MySQL guys didn't answer any questions about it. At least not in public. The MySQL guy (David) also managed to insult their community pretty good in his talk. I don't think it was intentional, but this was actually the second time I hear things along that line from them. The quote was basically "we get contributions from the community, but they are never good enough. We may take the idea and rewrite it from scratch". Ironically, his collegue Kaj had a talk the next day titled "taking the community to the next level"...
Finally, about my own talk delivered together with Simon Riggs. I think it went reasonably well. If you were there and want to let me know what you thought, please do so. I'll put the presentation slides up in a couple of days.
So I got here, so some quick first impressions:
No PostgreSQL talks scheduled until wednesday, but there's at least Bruce to chat about pg stuff with. And if there are others around, do show yourselves if you can figure out where we are :-)
/mha/uploads/oscon-dead-languages.jpg alt="" align="right" Now for the mandatory first day picture. Daiman Conway delivering his talk on dead languages, and actually showing LISP code (and that wasn't the worst!) I thought I'd never have to live through watching a presentation about LISP again... (pardon the layout - the wireless connection here (hotel) is very flaky, so I have to click things quickly before it goes offline)
Today I found out (the hard way - customer call!) that I had created a whole bunch of databases on a server in UNICODE encoding, when the locale picked only supported LATIN1. There were also a bunch of other dbs that were actually in LATIN1 that had no problems.
To solve this, I wrote a quick-n-dirty-n-hacky script to batch-convert all the databases from UNICODE to LATIN1. Luckily none of them were very large :-) I'll post it here in the hope that it'll help someone - but consider it mainly something to build from. Specifically the errorchecking should perhaps be better than just an echo of the exitcode.
It solved my problem. YMMV.
#!/bin/sh
/usr/local/pgsql/bin/psql template1 postgres -A -t -c "SELECT datname FROM pg_database WHERE datallowconn AND encoding=6 AND NOT datname ~ '.*_s'" | while read D ; do
echo "Converting $D"
echo "-- start" > $D.dump
echo "ALTER DATABASE $D RENAME TO ${D}_s;" >> $D.dump
echo "CREATE DATABASE $D ENCODING='LATIN1' TEMPLATE=template0;" >> $D.dump
echo "\\connect $D" >> $D.dump
/usr/local/pgsql/bin/pg_dump -U postgres $D >> $D.dump
/usr/local/pgsql/bin/psql -v ON_ERROR_STOP=1 -U postgres template1 -f $D.dump
echo Exitcode $?
done
Finally got around to testing the new ODBC version for Kerberos support, and I'm happy to report it works perfectly and requires no hacks to install.
To use it, make sure you have a libpq with Kerberos support compiled in (yeah, this means manual build - we're looking into this for 8.1, but no promises yet). Then just set up your data source as you normally would, and skip specifying the password (you must specify a username though). As long as your pg_hba.conf is properly set up, that's all you need.
I have an application with the need to synchronize apache passwords for basic auth (using DBM files) from a postgresql table. Using mod_auth_pgsql simply not an option - there may be other cases where it's an option but not wanted, in this case there it was out from the beginning.
So. pl/perl once again to the rescue, along with the CPAN module Apache::Htpasswd. Pretty easy to do with a trigger. Hit the link to see the code.
CREATE TABLE users (
userid character varying(32) NOT NULL PRIMARY KEY,
"password" character varying(32) DEFAULT randpwd() NOT NULL
);
CREATE FUNCTION sync_user_list() RETURNS "trigger"
AS $_$
use Apache::Htpasswd;
use strict;
my %25userlist;
open(HT,"/data/webusers/users") || elog(ERROR,"Could not read password file: $!");
while (<HT>) {
my ($uid,$pwd) = split /:/;
$userlist{$uid}=1;
}
close(HT);
my $ht = new Apache::Htpasswd("/data/webusers/users") || elog(ERROR,"Cannot open password file: $!");
my $r = spi_exec_query("SELECT userid,password FROM users");
my %25users;
for (my $i = 0; $i < $r->{processed}; $i++) {
my $u = $r->{rows}[$i]->{userid};
my $p = $r->{rows}[$i]->{password};
$users{$u} = 1;
if (!$ht->fetchPass($u)) {
$ht->htpasswd($u,$p) || elog(ERROR,"Could not add user $u: " . $ht->error());
}
elsif (!$ht->htCheckPassword($u,$p)) {
$ht->htpasswd($u,$p,1) || elog(ERROR,"Could not add/set password for $u: " . $ht->error());
}
}
foreach my $u (keys %25userlist) {
if ($users{$u} != 1) {
$ht->htDelete($u);
}
}
return;
$_$
LANGUAGE plperlu;
CREATE TRIGGER trg_sync_user_list
AFTER INSERT OR DELETE OR UPDATE ON users
FOR EACH STATEMENT
EXECUTE PROCEDURE sync_user_list();
Code has now been committed to pginstaller to move user creation to the execute sequence. This means it can be used from silent installs, and also that the user won't be created until you have filled in all dialogs - in 8.0 we create it as soon as you leave the service config screen.
If you need this, or really anybody else as well, please grab latest cvs, test and let us know how it works. If you can't compile yourself, wait for the next beta which hopefully won't be too far off. But as this is a fairly major change, we'd appreciate a lot of testing.
A couple of minor fixes have also gone in, as we're nearing release. Not a lot of bug/feature-requests still pending now.