We have Pictures from Linux World, too!

Elein
One of the interesting things about Linux World was the number of people enquiring about the company. Of course there is no one company. We are community based. This concept was new to many entrepreneurs in spite of the fact that this was a Linux conference.

The Linux World conference was a clash of Open Source and commercial with commercial entities dominating. There is a growing distinction between community based open source projects and commercial based open source projects, which for us is highlighted by the confusion between MySQL and PostgreSQL licenses and projects.

IBM's announcement to open source Cloudscape was very welcome. Rumor has it that the people on the project at IBM will remain very gainfully employed and are happy about the change. (Cloudscape is a java embedded Object Relational database system.)

Traffic at the PostgreSQL booth was constant. We usually had three of us at a time answering questions about every level of postgreSQL. We had MySQL and IBM'ers and other database people checking us out and asking questions. We had a lot of people asking the usual FAQs and grabbing up the ever popular CDs. Time flew by.

Josh Berkus
Well, for my part let me say that I would *not* like to do 2 conventions back-to-back again. It makes it very hard to prepare for LWE-SF. However, I think that overall it was very good for us and gave helped us in pushing significantly heightened presence in the OSS world.

Let me thank all of the volunteers who pitched in on this one, most especially David Fetter who drove up from Palo Alto at 70mph when I had to take a client emergency, and Roman, Myra and Githogori who all helped out despite never having been to an SFPUG event before.

As a special delight, a woman from the Japan PostgreSQL User's Group attended the conference and worked two shifts with us at the booth. Since we had 2 Japanese companies send reps over on Thursday morning, this was very useful!

We gave out between 700 and 800 CDs by 4pm Wednesday afternoon, meaning that we probably talked to 400 to 500 people (some people just grabbed and ran). Probably the best part of doing this show was meeting all of the people who use PG but don't speak up on the mailing lists; some of the uses to which our database is put are fascinating. For example, we found out that the Venezuelan government uses PostgreSQL in some departments.

The BOF on Wednesday night had 25 to 40 attendees. I briefly went over some of the expected features in 8.0, and then Mark Wong of OSDL went over their current project testing PostgreSQL on Linux against TPC-style workloads. For example, Mark revealed that using the new Table spaces patch for 8.0 to put all of the major tables and logs of a database on separate volumes (in a 42-disk machine) could boost performance by 13%. He also did a comparison of file systems.

After the presentation, we had a general discussion about 8.0, performance, and PostgreSQL PR.

David Reese
It's the first time I've gone to Linux World, and I got to be an exhibitor, which is very exciting.

Postgres opinions.
Good - Lots of interest in Postgres from many business industries. Received feedback about migration tools. Someone mentioned an XML based data conversion tool, but I don't know anything about it. The people who use Postgres have a lot of passion for it. In order for postgres to succeed it needs to have continued presence at different shows (macworld, javaone, others do they sponsor .org booths?)

Bad - we ran out of postgres boot disks. We need numerical data on specific Postgres information. That is, describing in detail with specifics at least the following:

• Time to load 100 MB, 1 GB and 1 TB database.
• Transaction throughput - a matrix of x many transactions / second given
• specific client/server configuration.
• Latency of replication technology (two phase commit?)
• Time it takes to convert an existing database given x tables, indexes,
• constraints, foreign keys, custom functions.
• Replication strategies (hardware, software)
• Case studies (migration study, return on investment, startup)

Anyway, the Bad section is due to my limited knowledge of Postgres. I can answer most of those questions verbally, but written down may have more help for others.

Contributors: Josh Berkus josh at agliodbs.com David Reese dmreese at comcast.net elein at varlena.com

PostgreSQL 8.0 Goes Beta! PostgreSQL 8.0.0 Officially Goes Beta 9-Aug-2004

Official Press Release

For those wondering about the 8.0.0 designation on this release, there have been several very large features included in this release that we felt warranted the jump. As with all of our releases, we aim to have this one as rock solid as possible, but at least one of the features added to this release involved such changes that may warrant a bit extra testing post-release before deploying it in production.

Although the list of new features in 8.0.0 is extensive, with both SMB (Win32 Native Support) and Enterprise (Nested Transactions and Point in Time Recovery) features being added, there is one thing that hasn't been included as part of the core distribution, and that is a Windows Installer, which can be found at:

http://pgfoundry.org/projects/pginstaller

http://developer.postgresql.org/beta-history.txt

http://www.postgresql.org/mirrors-ftp.html

http://bt.postgresql.org

pgsql-bugs@postgresql.org

On behalf of all of the developers, Happy Bug Hunting ... Contributors: press at postgresql.org

O'Reilly Open Source Conference More Coming 01-Aug-2004

More Pictures from OSCON have been added and more of the talks from the conference have been added to Tidbits. I expect a few more to come in slowly (people are busy with 8.0!) so check back to see more.

The PostgreSQL and Slony-1 CD is available via bt.postgresql.org. It was created by Robert Bernier and funded by Affilias,

The flyers sponsored by Varlena, LLC are also available at General PostgreSQL Flyer (pdf) and PostgreSQL and the Open Source Maturity Model (pdf).

Editor: elein at varlena.com

Updateable views How to create updateable views 15-Aug-2004

Although, there are several examples within different articles of General Bits covering updateable views, it seems from questions I've received that a consolidated example would be helpful.

In theory, all views are updateable if the rules are created. Simple single table views are the easiest and this includes views where all of the joins are only relational lookups.

Updateable views are implemented using RULES. A rule is evaluated once and only once when a command is issues. The command is re-written based on the directives of the rule system. This is different from a TRIGGER which is executed for each row selected by the original command.

In this example, we have people and a lookup table of addresses for the people. Also we have a lookup table of states for the address table.

	CREATE table people (
	   person_id   SERIAL PRIMARY KEY,
	   inits       text,
	   fname       text
	);
	CREATE table states (
	   state text PRIMARY KEY,
	   state_long  text
	);
	CREATE table addresses (
	   person_id   int REFERENCES people (person_id)
	      ON DELETE CASCADE,
	   city  text,
	   state text REFERENCES states (state),
	   country  text,
	   zip   text
	);

A full view of these values would look like this:

	CREATE VIEW people_full AS
	SELECT p.person_id, p.inits, p.fname, a.city,
	   a.state, s.state_long, a.country, a.zip
	FROM people p JOIN addresses a USING (person_id)
	   JOIN states s USING (state);

	CREATE RULE "_RETURN" AS ON
	SELECT TO people_full DO INSTEAD 
	SELECT p.person_id, p.inits, p.fname, a.city, a.state, s.state_long, 
	   a.country, a.zip FROM 
	   ((people p JOIN addresses a USING (person_id)) JOIN states s USING (state));

When creating UPDATE, INSERT and DELETE RULES, you have also access to NEW.column and/or OLD.column. The columns are the target list from the view. An INSERT only has NEW values; a DELETE only has OLD values and an UPDATE has them both.

To create an UPDATE RULE you must first decide what do do when various fields change. In the simple case, you can focus on the primary table in the view and ignore any other changes. This means that when any column in the people table is changed it should be written to the people table. Any changes in columns from the address or state fields should be ignored. It is safest to disallow the primary key does to changes on update. That RULE looks like this:

	CREATE RULE "upd_people_full" AS ON
	UPDATE TO people_full DO INSTEAD
	UPDATE people set inits=NEW.inits, fname=NEW.fname WHERE person_id=OLD.person_id;

An INSERT RULE would do the corresponding action for INSERT.

	CREATE RULE "ins_people_full" AS ON
	INSERT TO people_full DO INSTEAD
	INSERT INTO people (person_id, inits, fname) VALUES (NEW.person_id, NEW.inits, NEW.fname);

A DELETE RULE may be simpler or may be more complex. To keep things simple leverage the ON DELETE CASCADE and only delete from the people record. Then to DELETE the people record via the people_full view, the RULE would look like this.

	CREATE RULE "del_people_full" AS ON
	DELETE TO people_full DO INSTEAD
	DELETE FROM people WHERE person_id=OLD.person_id;

DROP RULE "upd_people_full" on people_full;
DROP RULE "ins_people_full" on people_full;
CREATE RULE "upd_people_full" as ON
UPDATE TO people_full DO INSTEAD
(
	UPDATE people set inits=NEW.inits, fname=NEW.fname 
	   WHERE person_id=OLD.person_id;
	UPDATE addresses set city=NEW.city, state=NEW.state, zip=NEW.zip 
	   WHERE person_id=OLD.person_id;
);

CREATE RULE "ins_people_full" as ON
INSERT TO people_full DO INSTEAD
(
	INSERT INTO people (person_id, inits, fname) 
	   VALUES (nextval('people_person_id_seq'),NEW.inits, NEW.fname);
   INSERT INTO addresses (person_id,city, state, zip) 
	   VALUES (currval('people_person_id_seq'), NEW.city, NEW.state, NEW.zip );
);

An exercise left for you is to see how the rules might change if you decided to allow a change in the state_long name in the state table.

A different DELETE RULE is not necessary since both tables were handled in the original RULE and via ON DELETE CASCADE.

PostgreSQL does not assume to know what you want with regards to the semantics of updateable views. The views PostgreSQL supports can be extremely complex with any number of subselects and JOINs.

The important idea with updateable views, is that you must know what exactly you want to happen when you update a view. Writing the rules is trivial, but thinking out the repercussions to the relations and semantics of your data is something only you can do clearly enough.

The SQL for this example is also available.

Contributors: elein at varlena.com