OK, I admit I carry around a laptop running windows/XP. So shoot me. Or, nevermind, since the ongoing torture is enough. In preparation for an upcoming business trip I decided to copy a database from my real computer to the PC.

Since I am a die-hard Un*x curmudgeon, I always have cygwin installed on my PCs to protect me from that other operating system. Prior to 8.0, I ran postgreSQL on the PC under cygwin. It was not speedy, but it did the job in a familiar environment when I needed it.

When 8.0 was released, I installed PostgreSQL from the pg_installer and immediately got wrapped up in user login problems. The login problems existed because I already had a postgres user in the system...I think. A short time later, I tried Pervasive's pg_installer and successfully got around the login issue and had a working PostgreSQL system.

So I dutifully dumped a 7.4 database using 8.0.1 pg_dump executable from my real machine because it is always good to use the latest dump executable, right? Then I moved the file over and prepared to load it on the PC. First I ran headlong into the problems getting PgAdminIII to restore an ASCII dump. OK, fine. It doesn't do that. I can run it from the command line. Using what was no doubt an old leftover version of a cygwin psql, I tried to load the database. No luck. There is no pl/perl. There is no pl/python. OK, I'll live with that. Then I ran into the COPY table (...) FROM stdin. It simply did not work and gave an error message on the first data line.

Aha, I thought, I'll upgrade PgAdminIII. Looking around I found it standalone but also noted that it was released with 8.0.1. Uh oh. I was running 8.0RC3 from Pervasive's site since I had downloaded it the day it was announced. I bit the bullet and decided to upgrade to 8.0.1.

I downloaded the pg_installer from one of the PostgreSQL Mirror sites, unzipped it and ran the update.bat since I had an old version of 8.0 on the system. A message told me to run the regular install file. ooooKay. I did that, filled out the forms and Next, Next, Next until it was ready to do an initdb. It refused to do an initdb because my file system was not an NTFS file system. There were no other options but to quit.

I don't know PC file systems well. I've got what I've got and it is a FAT32 and I do not know how to change it and get on with the task at hand. Remember? The task at hand was to load a database on the PC for a roadtrip.

So, I thought, well, I'll try Pervasive's pg_installer. It worked like a charm. I told it to go ahead and initdb, expecting to lose what I had and restart from scratch, but no, it cautiously did not do that. I had my half-baked database and the user I had added. Hmmm. Good choice. I dropped the bad database and was ready to roll.

Trying to get the PC experience, I called up PgAdminIII and tried to copy in my database from the ASCII dump. Ooops. There is no restore option. Hey, where'd it go? OK, I'm going to have to use psql. Uh oh.... No pl/perl. No pl/python. And lots of error messages on the data for COPY table FROM stdin. Still. Again.

Alright, I'll point my path to pick up the proper executables from the newly installed installation. I've got to sneak the PATH element in before cygwin's /usr/bin. And then invoking the installed psql.exe from cygwin which should work--cygwin can run DOS executables. It hangs.

A brilliant idea was that I just copy the backup file into PgAdminIII as an SQL script and run it. Darn. Back to the problem where "COPY table (....) FROM stdin;" does not work. Circles anyone?

Trying to think like a native, I thought I've give psql a chance from the DOS window. Voila! No pl/perl and no pl/python, but the data was loaded and I've got something to work with and demo from.

What did I learn? There is something funky about the different file system requirements which are different between the Pervasive and PostgreSQL versions of pg_installer. I hope it won't bite me later. (Note from reviewer: NTFS is more secure for dynamic data, FAT32 is for use with less dynamic data. For my purposes, FAT32 is fine. YMMV) Use DOS not cygwin windows for pg command line executables. Always upgrade whether you need it or not. There is more to getting pl/perl and pl/python than just installing PostgreSQL (but I knew that already). And try to think natively on foreign platforms.

Contributors: elein at varlena.com

Prevent Update of Keys No deletion of Keys 18-Mar-2005

Referential integrity of your data is easily maintained with proper foreign key definitions. However, it is often the case where you want a key column that is not a foreign key to also be non-updateable. Perhaps it is part of a constraint function or you simply do not want the keys updated after entry.

Suppose we have the following tables (For the table definitions, click on the image):

Foreign key constraints automatically disallow changing key columns that are referenced elsewhere, unless DELETE CASCADE or UPDATE CASCADE are used. To prevent update of column that is not part of a foreign key, you must create an update trigger which raises an error in the case where the column in question changes.

=# SELECT m.cust, m.odate, m.ono, d.dno, i.descr, i.units, d.qty, d.tot_price 
FROM master_order m 
JOIN detail_order d USING (ono) 
JOIN inventory i USING (ino);
       cust        |   odate    | ono | dno |     descr     |     units     | qty | tot_price
-------------------+------------+-----+-----+---------------+---------------+-----+-----------
 Sam's Snack Shack | 2005-03-18 |   1 |   1 | Paper Napkins | Bundle of 100 |   2 |      2.00
 Sam's Snack Shack | 2005-03-18 |   1 |   2 | Hot Cups      | Package of 50 |   1 |      8.00
 Burger Heaven     | 2005-03-18 |   2 |   3 | Paper Napkins | Bundle of 100 |  10 |     10.00
 Burger Heaven     | 2005-03-18 |   2 |   4 | Mustard       | 1 gallon      |   2 |     16.00
 Burger Heaven     | 2005-03-18 |   2 |   5 | Sauerkraut    | 1 gallon      |   2 |     14.00
 Dog y Dogs        | 2005-03-18 |   3 |   6 | Paper Napkins | Bundle of 100 |   3 |      3.00
 Dog y Dogs        | 2005-03-18 |   3 |   7 | Mustard       | 1 gallon      |   2 |     16.00
 Dog y Dogs        | 2005-03-18 |   3 |   8 | Sauerkraut    | 1 gallon      |   2 |     14.00

=# update master_order set ono=999 where ono=1;
ERROR:  update or delete on "master_order" violates foreign key constraint 
        "detail_order_ono_fkey" on "detail_order"
DETAIL:  Key (ono)=(1) is still referenced from table "detail_order".

=# update inventory set ino=999 where ino=1;
ERROR:  update or delete on "inventory" violates foreign key constraint 
        "detail_order_ino_fkey" on "detail_order"
DETAIL:  Key (ino)=(1) is still referenced from table "detail_order".

=# update detail_order set dno=999 where dno=1;
UPDATE 1

We will need a very simple trigger function and a trigger that checks to see if the detail number is updated and, if so, raise an exception

create or replace function detail_keycheck()
RETURNS TRIGGER AS
$$
BEGIN
        IF OLD.dno <> NEW.dno THEN
                RAISE EXCEPTION 'You cannot change the detail number';
        END IF;
        RETURN NEW;
END;
$$ language 'plpgsql';

create trigger noupd_detailkey before update on detail_order
for each row execute procedure detail_keycheck();

=# update detail_order set dno=1 where dno=999;
ERROR:  You cannot change the detail number

Contributors: elein at varlena.com

Understanding Open Source Projects Different Views of Open Source Organization 19-Mar-2005

In The 5 Types of Open Source Projects Josh Berkus categorizes the organizational structure of Open Source projects into five distinct categories, asserting that every open source project can be categorized "into one of only five categories, or as a hybrid of two." The category names for the types of projects are:

1. Solo
2. Monarchist
3. Community
4. Corporate
5. Foundation

Josh includes examples of open source projects which fit into the various categories. This helps people who know the projects to understand the description and it helps people who don't know the projects to understand the organization of specific projects better.

Josh asserts that PostgreSQL is a Community project under the control of the Core Group. What is not examined is that various aspects of the group use different organizational models. The actual technical (code) management is a loose Monarchist model. A few key developers have a few more votes and a lot more say so than a democracy. (This is a good thing!) And other aspects of the project, such as the web group, have their own committees with their own people with a few more votes.

This brings up the question as to whether blanket categorizations can be made for all aspects of a project. Reality is actually rather subtle. After discussing this with Josh, I do agree that PostgreSQL is a Community project, but I still assert that various aspects of the group do have slightly different organizational models which combine Community with Monarchy and other models where it is appropriate to do so. The ability to morph the individual aspects of a project to their most effective organization seems to me to be an attribute of a Community project.

Still there are many ways to look at things. Other people have looked at the organization of open source projects in various ways. The Open Source Maturity Model is one of these that takes a slightly different slant on the organization of open source projects. The purpose of the OSMM is to help companies evaluate whether or not to adopt certain open source products. It looks at different aspects of a project and sets up criteria to evaluate maturity. How PostgreSQL fits into the Open Source Maturity Model is summarized in the flyer Open Source Maturity Model and PostgreSQL.

In a paper also previously linked in GeneralBits, Karim Lakhani and Robert Wolf examine why open source projects are successful. Why Hackers Do What They Do: Understanding Motivation Effort in Free/Open Source Software Projects. (Note that one may want to fish around http://opensource.mit.edu/papers if you are interested in academic study of open source. You'll note that there is a little "pglists" directory there, too. PostgreSQL is one of the projects studied by various people including Karim Lakhani.)

I agree with the assumption that enthusiastic, motivated developers produce better quality code and that mature orgnization produces more stable code. But I know there is also a trade-off. Over organized and restricted projects lower enthusiasm. But then, so do anti-social and chaotic development environments.

In Josh's categorization of open source project models he cautiously steers a non-judgemental path with regards to which of these categories is "better." By expanding the categorization to different aspects of the projects and including the value of happy developers would enable us to see the organization, successes and benefits of various types of open source projects.

Contributors: elein at varlena.com, josh at agliodbs.com, lakhani at mit.edu