We have Pictures from Linux World, too!
Official Press ReleaseAfter 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:
email@example.comThe 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 ...
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 flyers sponsored by Varlena, LLC are also available at General PostgreSQL Flyer (pdf) and PostgreSQL and the Open Source Maturity Model (pdf).
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.
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