Yesterday I noticed that my trackbacks weren’t working in [http://blogs.ittoolbox.com/database/soup/ Josh’s blog]. I figured wasn’t much of a big deal, probably his blog provider was all busted. But then today I saw that someone else had actually trackbacked on him successfully, which meant that something was awry and it was up to me to figure out what. It turns out that Josh’s blog provider **is** busted, but also that there was something I could do about it. The problem is that Josh’s providor doesnt’t provide the trackback URL in the RDF metadata for each entry, so when [http://www.s9y.org s9y] tries to do it’s automated trackback pings, it fails miserably: • Checking http://blogs.ittoolbox.com/database/soup/ for possible trackbacks... • Trackback failed: No trackback-URI found. However you can trackback Josh’s blogs if you know how. The key is that you need to direct your trackback ping directly at the trackback url provided in the blog posting. [http://wordpress.org/support/topic/549 Wordpress] makes you do this type of thing all the time when you write a new post, but s9y doesn’t, since they figure it is easier to just do it automagically for you. Which it is. If it works. Which it didn’t. So after a quick conferance on [http://s9y.org/23.html #s9y], it was decided what I needed was the manual trackback plugin. Ok, simple enough I think. So I install s9y’s Sparticus plugin, which is designed to allow you to download plugins from the web and install them into s9y. A few clicks later and I have a nice list of plugins from the net, I pick my trackback plugin, hit install and got a whole mess of errors like: Trying to open URL serendipity_event_trackback.php?rev=1.9999... Fetched 8112 bytes from the URL above. Saving file as /apache/xzilla/plugins/serendipity_event_trackback/serendipity_event_trackback.php... Cannot write to file /apache/xzilla/plugins/serendipity_event_trackback/serendipity_event_trackback.php. Warning: mkdir(): SAFE MODE Restriction in effect. The script whose uid is 100 is not allowed to access /apache/serendipity-0.9.1/plugins/serendipity_event_trackback owned by uid 30 in /apache/serendipity-0.9.1/plugins/serendipity_event_spartacus/serendipity_event_spartacus.php on line 225 Cannot write to file /apache/xzilla//plugins/serendipity_event_trackback/UTF-8. Trying to open URL lang_ja.inc.php?rev=1.9999... Fetched 517 bytes from the URL above. Saving file as /apache/xzilla//plugins/serendipity_event_trackback/UTF-8/lang_ja.inc.php... Error: serendipity_event_trackback:67828dfe6253cda55cebe289e7ec3b11 (serendipity_event_trackback) DEBUG: Plugin serendipity_event_trackback:67828dfe6253cda55cebe289e7ec3b11 not an object: . Input: Array ( [adminModule] => plugins [pluginPath] => serendipity_event_trackback [install_plugin] => serendipity_event_trackback [spartacus_fetch] => event [action] => [adminAction] => ) . Fatal error: Call to a member function on a non-object in /apache/serendipity-0.9.1/include/admin/plugins.inc.php on line 797 Turns out sparticus doesn’t work in safe mode, and given the number of dba/admins involved in our project, you know they require we run the server in safe mode, so that’s kind of a non-starter. The good news is you can install the plugins manually if you want. Having spent the last few hours tripping over myself doing this, let me provide some tips: 1. Uninstall Sparticus -> you can’t use it, and it’s install mechanism conflicts with the manual install. Had I done this right away I would have saved myself a lot of time. 2. Swing on over to the [http://cvs.sourceforge.net/viewcvs.py/php-blog/additional_plugins/ s9y cvsweb interface], and go into the additional_plugins module and browse around till you find the plugin your looking for. In this case it is called “serendipity_event_trackback”. 3. Create a directory in your plugins that matches the plugin you want to install –> “serendipity_event_trackback” 4. Looking in that directory, you’ll see the files you need, so go ahead and download them into your new directory. 5. Make sure the files have the right name! I wasted some time on this too, since I used lwp-download to grab the files and it appended a .txt extension on the files I needed, so s9y couldn’t see them. 6. Make sure your webserver can read all of the files you have downloaded, chown is your friend here. 7. That’s pretty much it, you should be able to go into s9y and install the plugins now. It’s easy when you know how. [[image /xzilla/templates/default/img/emoticons/smile.png alt=”:-)” style=”display: inline; vertical-align: bottom;” class=”emoticon” /]]

Josh Berkus gripes about a common problem in todays software… abuse and misunderstanding of [http://en.wikipedia.org/wiki/Surrogate_key surrogate keys]. Given that I used a bunch of them in the [http://www.amazon.com/exec/obidos/tg/detail/-/1590595475/ref=pd_ts_b_241782/002-8194953-8241647?v=glance&s=books php/pg book], I better provide [http://blogs.ittoolbox.com/database/soup/archives/007327.asp?rss=1 a pointer to his article]. In my defense I did protest a little bit, but for example code it tends to make things easier to mess around with, so I didn’t really try to fight it. What is interesting is that in [http://www.postgresql.org PostgreSQL], the [http://www.postgresql.org/docs/faqs.FAQ.html#item4.12 OID] was really more of a true surrogate key than the [http://www.postgresql.org/docs/current/static/datatype.html#DATATYPE-SERIAL serial datatype], which has become more popular and powerful as PostgreSQL has started being used by more industry types rather than [http://www.dbdebunk.com/index.html academics] (for lack of a better word). But I guess that’s all water under the bridge now… what you can do though is go read his post and then at least add additional [http://www.postgresql.org/docs/8.1/interactive/ddl-constraints.html#AEN2016 unique constraints] to your tables for the columns that really do define a unique row.

[http://www.phparch.com/tek/tek_schedule.php php|tek] is happening in Orlando this year, only a couple hours from [http://local.live.com/default.aspx?v=2&cp=29.651985~-82.323184&style=h&lvl=13 my home], so I figured I ought to take a swing at presenting at the conference this year. Well, three talks suggested, three talks rejected. [http://www.m-w.com/cgi-bin/dictionary?va=phooey Phooey!] For those interested, the talks suggested were: 1) What’s new in PHP 5.1 and PostgreSQL -> A introduction to PHP’s PostgreSQL functionality, with a special look at the [http://people.planetpostgresql.org/devrim/index.php?/archives/24-New-PostgreSQL-functions-in-PHP-5.1.html new functionality Chris added] for PHP 5.1, including code samples for each function. 2) Introduction to [http://projects.commandprompt.com/projects/public/plphp PL/PHP] -> A tour on installation and use of the PL/PHP function language, covering all the major areas like useing php functionality, makeing use of spi, and writing triggers. 3) Better Object/Relational Mapping Through Advanced Database Tricks -> Basically an overview of how to simplify PHP object models through the use of updatable views in postgresql, with real code in PHP showing how it is used. I thought these would be interesting topics, but so much for that. Guess I might as well [http://people.planetpostgresql.org/xzilla/index.php?/archives/129-4-Days-On-Rails-On-PG-of-Course!.html keep playing with ruby] instead. [[image /xzilla/templates/default/img/emoticons/smile.png alt=”:-)” style=”display: inline; vertical-align: bottom;” class=”emoticon” /]]

I’m in the process of moving from 7.3 to 8.1 on one of the systems I work with. I’ve been experimenting with different settings for the postgresql.conf to help speed up data loads. After a couple quick mis-starts, I took a swing with the following conf changes: This got it down to about 10 hours to do a 30GB databases, which quite frankly seemed awfully slow. More to the point, it was really beyond the amount of downtime we could accept for this app, especially when you consider we still had to allow for time to dump out the old database. We were actually considering using Slony at one point, but that seemed like a dubious path for this system since it would require upgrading the database (7.3.4 on Red Hat 7.3) via (questionably available) rpms and then compiling Slony itself on that server to install it (which would be tricky since it has no C compiler on it). So rather than open that can of worms up, I instead decided to sniff around the docs looking for whatever I had overlooked, and finally a couple things jumped out at me: Hard to believe I overlooked shared_buffers, which is the default setting to change for good performance. Stats_collector was at least counter-intuitive, since I normally try to run with that setting on in production. It’s currently loading up now so I don’t have final numbers, but this will get it down to under 2 hours, which will fit into our downtime window. Yay! If you’re doing database loads, I’d suggest changing these settings first. Hope this helps someone, if you have any other suggestions please post it.

Apparently tech editors are [http://it.slashdot.org/article.pl?sid=05/12/23/0153254&tid=221 struggling for stories] this time of year. From [http://www.techworld.com/security/news/index.cfm?NewsID=5117 techworld’s Matthew Broersma] we get an “article” about a recent [http://securitytracker.com/alerts/2006/Jan/1015446.html mod_auth_pgsql exploit] with this over the top headline and opening sentence: **Apache shot with security holes** “Companies running Apache and a PostgreSQL database are at risk from serious Internet intrusion.” Is this guy a direct disciple of [http://en.wikipedia.org/wiki/J._Jonah_Jameson J. Jonah Jameson]? To be honest I don’t even **know** of anyone running [http://freshmeat.net/projects/mod_auth_pgsql/ mod_auth_pgsql]; I’m sure it is an important piece of software and the folks running it surely do need to upgrade, but I fail to see it as the doom and gloom scenario that Broersma tries to paint with those type of headlines. This probably wouldn’t bother me if there weren’t [http://www.cve.mitre.org/cgi-bin/cvename.cgi?name=2006-0105 real security issues] and actual news items about [http://www.postgresql.org/about/news.456 releases that people really do need to be worrying about]. </rant>

I’ve made a few changes to the blog based on Jakob Nielsen’s [http://www.useit.com/alertbox/weblogs.html Weblog Usability: The Top Ten Design Mistakes]. I’ve always been a big supporter of Nielsen, and in fact using his teachings got me a job at [http://www.kforce.com kforce] as a usability designer. Changes include [http://people.planetpostgresql.org/xzilla/index.php?/archives/127-Fix-for-Serendiptity-s9y-quicksearch.html fixing the search box], dropping the calendar (lmk if you want it back), and tossing up a photo and mini-bio.

I’ve been going through some of the [http://www.ruby-lang.org/en/ ruby]/[http://api.rubyonrails.com/ ruby on rails] [http://www.onlamp.com/pub/a/onlamp/2005/01/20/rails.html tutorials] and have to say it’s very interesting how easily they convert over to [http://www.postgresql.org postgresql]. In fact there are virtually no code changes neccessary (a far cry from your [http://www.awtrey.com/tutorials/dbeweb/ average php tutorial]); in each case so far I pretty much just redefined my database.yml file to point to postgresql and then went about my business. When the tutorials hit you with some oddball mysql schema: CREATE TABLE `categories` ( `id` smallint(5) unsigned NOT NULL auto_increment, `category` varchar(20) NOT NULL default '', `created_on` timestamp(14) NOT NULL, `updated_on` timestamp(14) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `category_key` (`category`) ) TYPE=[http://sql-info.de/mysql/gotchas.html MyISAM] COMMENT='List of categories'; you just write it up in the proper postgresql syntax and you’re good to go: CREATE TABLE categories ( id serial PRIMARY KEY, category varchar(20) DEFAULT '' UNIQUE NOT NULL, created_on timestamptz DEFAULT now() NOT NULL, updated_on timestamptz DEFAULT now() NOT NULL ); You can do these simple schemas in your head, and the supplied ruby code just works, making the tutorials pretty easy to go through. Plus the “models” files still make sense since they force the application to adhere to constraints you’ve laid out in your database. For those postgresql users who want to give it a try, here is the full sql schema, “converted” to postgresql for the [http://rails.homelinux.org/ Four Days on Rails] tutorial. I did this on-the-fly, but it should work fine in case you get stuck.

Turns out there is a bug in [http://www.s9y.org s9y]’s quicksearch code that causes entries to be duplicated in the results display, and subsequently throws off the total number of entries from the result count that is returned with the results. The good news (in a way) is that it only manifests itself if you run [http://www.postgresql.org postgresql] and you tag your entries under multiple [http://www.s9y.org/69.html multiple categories]. Given that this broke searches on my site, I figured I ought to fix it, and with a couple of pointers from [http://www.supergarv.de/serendipity/ garvin] on [http://www.s9y.org/23.html #s9y] I did yesterday. Here’s a diff if you want to fix it on your site now, rather than waiting for the [http://svn.berlios.de/wsvn/serendipity/branches/0.9/docs/NEWS?op=file&rev=814&sc=1 next release]; it is really a very simple fix once you know how.

Nice little blog post about [http://www.xerratus.com/2005/12/21/WhySSNShouldNOTBeStoredAsAnIntegerInADatabase.aspx why storing Social Security Numbers (SSN) as integers is a bad idea]. The best part is the guy who comments that the SSN is really an integer, and that preceeding zero’s are simply a “display issue”, and that the “validator” function isn’t doing it’s job. OK, here’s my validator function:

 rms=# CREATE DOMAIN ssn AS text CHECK (VALUE like '_-_-___') ;

CREATE DOMAIN

Now let’s see about inserting crappy data:

rms=# CREATE TABLE peeps (name text, social ssn);

CREATE TABLE

First the “validator strips preceeding 0’s cause it thinks it’s an int” problem, which my friend Amber would suffer from :

rms=# INSERT INTO peeps VALUES ('hambre','50422112');

ERROR:  value for domain ssn violates check constraint "ssn_check"

No problem there. Ok, what about my pal Devrim, he doesn’t have a SSN:

rms=# INSERT INTO peeps VALUES ('Devrim','');

ERROR:  value for domain ssn violates check constraint "ssn_check"

Oh look, more busted app code… I said he didn’t have a SSN, not that he had a blank SSN [[image /xzilla/templates/default/img/emoticons/smile.png alt=”:-)” style=”display: inline; vertical-align: bottom;” class=”emoticon” /]]

rms=# INSERT INTO peeps VALUES ('Devrim',NULL);

INSERT 0 1

Ok, that worked. Lastly, let’s look at my pal David, who does have a SSN:

rms=# INSERT INTO peeps VALUES ('fett-dawg','415422112');

ERROR:  value for domain ssn violates check constraint "ssn_check"

See, my domain validator is quite draconian… I’m gonna require the “-” in the number. On the upside, you won’t have to worry about anyone displaying the number incorrectly.

rms=# INSERT INTO peeps VALUES ('fett-dawg','415-42-2112');

INSERT 0 1

Even if you don’t like **my** definition of what a SSN domain should look like, the point here is that if you use a domain, there will be no question what a SSN will be defined at within your application; no matter if your application developers disagree, get replaced, or work on different interfaces to the data and never even speak to each other; in every case your safe from getting munged data. Aaaah! I love the smell of data integrity in the morning! One last note… Magnus thinks my example is not complicated enough, but hopefully it still get’s the point across.

I’m not sure if this is top secret info, but I happened across an IRC conversation where [http://www.gunduz.org/ Devrim Gunduz] mentioned that he had recently finished up work on a PostgreSQL book written in Turkish, and that he was going to start looking for a publisher. This reminded me of an old [http://donxml.com/grokthis/archive/2004/05/07/686.aspx DonXml blog post], “Why Shouldn’t Authors Self-Publish?”, which is certainly worth a read. It perticularly interested me since the post directly references [http://blogs.apress.com/ Apress books] directly, a company I have been involved with in [http://apress.com/book/bookDisplay.html?bID=424 a couple] [http://apress.com/book/bookDisplay.html?bID=10016 of projects] and has shown me to currently be the most PostgreSQL friendly of the technical book publishers. The main thrust of the post seems to be that you can make more money if you self publish, with the main counter argument being that your publisher should help you with things like editing and reviewing and generally making a better book. I certainly stand behind that latter point; the trickiest part of writing a book seems to be how to get good feedback loops, and if you can get good editors and reviewers, it certainly helps. One idea might be to just put the [http://www.paulgraham.com/writing44.html whole thing online], but for technical books this is certainly a scary prospect for most authors (where people will actually want to read your book near the computer and probably want to copy/paste the code). OTOH, the thing that makes publishing on demand seem viable is that most publishers generally prefer authors who can bring in thier own market, so if you can do that then I guess self-publishing is worth looking at. The reason I find this interesting at all is that there seems to be a general consensus that the postgresql community would like to see more books on thier [http://www.postgresql.org favorite database], but the numbers I have seen don’t seem to bear out a large postgresql specific book market. This makes me skeptical that even a friendly publisher is going to be excited to take up books that are niche postgresql specific topics, like perhaps developing in [http://www.postgresql.org/docs/8.1/interactive/plpgsql.html plpgsql], or [http://www.revsys.com/writings/postgresql-performance.html query and performance tuning]. Both of these topics could probably command a good 200 - 300 pages, which isn’t enough for most book publishers to get excited about. I’m actually skeptical that the postgresql community would support these sufficiently; I [http://people.planetpostgresql.org/xzilla/index.php?/archives/32-Intro-to-8.1-Developer-Series.html asked about interest] in any 8.1 specific articles and really didn’t see any signifigant demand, but maybe publishing on demand makes these types of books viable?