PostgreSQL videos on Vimeo

There has been the occasional talk of PostgreSQL video content lately, and many will probably have found this already, but for those who haven't, check out the PostgreSQL channel on Vimeo, or subscribe to the RSS feed for a regular delivery of educational PostgreSQL presentations on video. Great work, keep it up.

Web browsers vs. debtags

So I wanted to see what web browsers are available in Debian. The first stop was http://packages.debian.org/. Going to the page of one browser package and clicking on "Browser" in the tags area only gives you the explanation of the tag, but not the list of other packages with that tag. Is that available somewhere?

So next try maybe grep-dctrl ... oh, grep-debtags appears to be the ticket.

$ grep-debtags -n -sPackage web::browser
arora
browser-history
caudium-dev
caudium-modules
caudium-perl
caudium-pixsl
caudium-ultralog
chimera2
conkeror
cookietool
dhelp
edbrowse
elinks
elinks-lite
elvis
elvis-console
epiphany-browser
epiphany-browser-dev
epiphany-extensions
epiphany-gecko
ezmlm-browse
galeon
galeon-common
gtkcookie
iceweasel
jsmath
junior-internet
kazehakase
konq-plugins
links
links2
lynx
lynx-cur
lynx-cur-wrapper
mozilla-firefox-adblock
mozilla-noscript
netrik
netsurf
saods9
stripclub
sugar-web-activity
surfraw
w3m
w3m-el
w3m-img
wapua
claws-mail-dillo-viewer
dillo
konqueror
midori

How many of those are actually web browsers? Probably about half of them. (Example: caudium (not listed) is a web server, caudium-dev is its development package, not very close to a web browser.)

This would actually be quite a useful interface if the tags had any relationship to reality. I was in fact looking for a lightweight graphical browser, so this is a plausible command:

$ grep-debtags -n -d -sPackage web::browser -a interface::x11 -a -! suite::gnome -a -! suite::kde

which gives me 15 hits, of which 8 or 9 are actual web browsers.

Well, my search for a lightweight browser stopped here:

iceweasel
lightweight web browser based on Mozilla

Yeah! ;-)

News on PostgreSQL source-code management

About a year ago, we started a Git hosting and mirroring service for the PostgreSQL project. Judging from feedback, comments, and the number of people who post obviously git generated patches, this service was widely welcome. Within the next few days, we will be moving this service onto a new backend infrastructure, which will allow even more people and projects to use this service, with decreased administration overhead and better performance. You will likely see a separate announcement when this is all set up and ready to use.

In the meantime, the committers of PostgreSQL have voted to stick with CVS as the master source code repository. This means that for the time frame of the development of version 8.4 + 1, there will be no project-wide move to another source-code management system for the master storage.

How does this make sense?

For those 6,769,383,230 of you out there who are not PostgreSQL committers, you can use any source-code management system you want, using various CVS conversion services. It's already possible and widely practiced to participate in PostgreSQL development day to day without using CVS at all. The PostgreSQL project system administrators are now putting more effort and resources into making sure that if Git is your choice, this choice is robustly supported. But there are other choices if Git is not your thing. (But read between the lines: More PostgreSQL hackers will be familiar with Git and CVS than any other system.) At the same time, the 15 committers will be able to continue to use the tool they are most comfortable with. Consider it rebasing in a weird way. So this way, it can be hoped that everyone wins, as they say.

PostgreSQL code snippets collection

PostgreSQL now has a code snippet hosting area for pieces of code that are not involved enough to warrant, say, founding a PgFoundry project, but are general and useful enough to be saved and maintained. This is run out of the wiki here: http://wiki.postgresql.org/wiki/Snippets. Browse, learn, extend, and enjoy!

Sun Set: Part 2

Today was my last day on the job at Sun Microsystems. After the PostgreSQL business there hasn't worked out as one might have hoped initially, I am leaving for another opportunity that fits my interests better.

Thanks and best regards to all the fine people whom I have had the pleasure of working with and who have helped me settle into (and out of) the job, especially Fabian, Jim, Jouni, Lenz, Liz, Magne, Martin, Masood, Pirkko, Robert, Sergei, Wei-Chen, Zdenek.

The next gig will be at F-Secure, starting on Monday. Yes, it will also involve PostgreSQL. And many more things. :-)

New Job at Sun

On July 22nd, 2008, I will be joining Sun Microsystems as PostgreSQL software engineer. Sun has been a valuable contributor to the PostgreSQL project for a number of years now, and I am looking forward to joining them in this effort. I am glad that I will be able to continue my personal role in the PostgreSQL project with the support of the great resources that Sun provides.

I would like to take this opportunity as well to thank my former colleagues at credativ GmbH for their support of the PostgreSQL project and my own career. I wish them much success in their continued development.

So, I expect that I will have more time to contribute to PostgreSQL development from now on, and both Sun and I have a sizeable backlog of projects and ideas that we would like to realize. Time to get started!

Schema design and ID fields

I'm sure everyone has created something like this once:

CREATE TABLE a (id int PRIMARY KEY, value text);

CREATE TABLE b (id int PRIMARY KEY, data text, a_id int REFERENCES a (id));

CREATE TABLE c (id int PRIMARY KEY, a_id int REFERENCES a (id), moredata text);

They all have id columns, but they are all different sets of IDs. Moreover, the same set of IDs is sometimes knows as "id", sometimes as "a_id". Some time ago I come to the conclusion that this naming scheme is bad. It makes sense locally within the table, because a.a_id would be redundant. But I think it has advantages to be redundant here anyway.

The first advantage is that you can use this join syntax:

a JOIN b USING (a_id) JOIN c USING (a_id)

If you build denormalized intermediate tables (materialized views) of this type

CREATE TABLE a_b AS SELECT * FROM a, b, c WHERE a.id = b.a_id AND a.id = c.a_id

it will fail because the join will have multiple columns named a_id. Unless using the USING join syntax, it won't know they are actually the same. So you would have to write out the column list instead of the asterisk in the select list.

Also, once your SQL queries become more complicated, say using table aliases and subselects, it is easy to lose track of what foo.id means in a particular context.

For all of these reasons, the general rule arises that a column name should be globally unique within a database schema. This should actually be checkable automatically if you allow duplicates only for columns connected by foreign key constraints.

There is another idea you can play with while developing your database schema. You start by creating (conceptually) one big table containing all the columns that are needed to store your data, which obviously requires globally unique column names. Then you use the decomposition algorithm to normalize this table down to the individual tables that you actually implement. Some might find this preferrable to converting ER diagrams.