varlena
varlena
PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

16-Aug-2004 Issue: 82

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see www.PostgreSQL.org.

Linux World 2004
Linux World San Francisco 08-Aug-2004

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

After almost 9 months of development, the PostgreSQL Global Development Group is proud to announce that development on PostgreSQL 8.0.0 has now finished, and is ready for some serious testing.

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
For a complete list of changes/improvements since 7.4.0 was released, please see:
http://developer.postgresql.org/beta-history.txt
That said, and without further ado, Beta 1 is currently available for download on all mirrors (look under source/v8.0.0beta):
http://www.postgresql.org/mirrors-ftp.html
And, thanks to David Fetter, the Beta is also available via BitTorrent at:
http://bt.postgresql.org
As with all releases, the success of this release falls in the your hands ... to go from Beta to Release, we need as many people out there to put it through her paces as possible, on as many platforms as possible. We urge anyone, and everyone, to download a copy and run her through her regression tests, and report any/all problems, and bugs, to
pgsql-bugs@postgresql.org
The more bugs we can find, and eliminate, during Beta, the more successful the Release will be ...

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
	);
Notice that we have made a decision that when the people record is deleted, then then corresponding addresses record is also deleted. This is the ON DELETE CASCADE directive.

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);
This view is implemented via the view definition as a SELECT RULE which looks like this.
	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;
These Rules show the basic construction of simple view updates. But what happens when you want something more complex to occur? For example, you may want the people_full view to enable updates to the address. In this case, you want to have the rule perform two statements--one to update the people table and one to update the addresses table. Because there is a one to one correspondence between addresses and people, the update RULE is very straightforward: just update both tables based on the existing primary key. We will first drop the old INSERT and UPDATE rules before we change them.
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;
);
The INSERT RULE is similar. Records in both tables are updated. The additional feature in this case is calling nextval() to get the next available person_id and using the same value via currval() to update the addresses table.
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


Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

Top
Google
Search General Bits & varlena.com Search WWW