A materialized view is a technique to "cache" the results of a query when its result set is large or the query is very slow and that query is used very often. A materialized view is a set of query results stored in a table. Usually this means the results of a selection of a view. Queries involving aggregation are a good reason to have a materialized view. Usually a slow query is not a good reason to have a materialized view unless you have exhausted the best advice from pgsql-general and pgsql-performance.
When an ordinary view is selected, the view is expanded into its underlying query and that query is executed in full for each statement.
A materialized view is a managed table. It is populated when the underlying tables of its base query are updated so that it always exists.
There are several techniques for creating materialized views which are described in Jonathan Gardner's paper Materialized Views in PostgreSQL.
It is interesting to note that the materialized views' issues are very similar to the issues for replication. The copy of the data is done either from a database table to another database table for replication or an executed view into a copy of the data into the same database for materialized views. Decisions with regards to how to and when to update the data copy are common to both sets of problems. In fact any denormalization or data copies will have the same basic question: How up to date is the copy of the data and how "expensive" is it to keep it updated?
An outline numbering scheme can be very handy. By outline numbering, I mean "1.1, 1.2, 1.3, 2.1...". There are several ways to do this type of numbering and I'd suspect that 7.5 may have some wrapped up functions to do this. In the meanwhile, in simple plpgsql here is a one solution.
There is a caveat to this solution and that is that it is possible for two updates to both try to assign levels n, 1. If the table has many concurrent inserts, you do not want to use this method. The exact problem will be discussed a bit later.
In our outline table, we have two fields for the grouping of the numbers, level_one and level_two. This example only shows two levels, but could be expanded to any fixed number of levels.
The first level is an ordinary serial type and behaves as you would expect; it auto-increments with each new insertion of the "value" default.
The second level of the outline will be managed with a trigger. The insert statement would be:
insert into outline2 values (default, NULL, 'data value');Because we want a primary key made up of the combination of level_one and level_two we need to set a non-NULL default to fool the parser at input time. This will allow us to insert NULL values in level_two and have them automatically updated by the trigger. (This practice is a little bit questionable since one shouldn't try to work around safety checks.)
create table outline2 ( level_one serial, level_two integer default 888, data text, PRIMARY KEY (level_one, level_two) );
Now we want a trigger function that can:
Since the sequence will not actually be created until the statement is complete, the special case of the first assignment is necessary. If the sequence already exists, then we just make the simple assignment of the next value of the sequence.
The creation of a new sequence must be executed dynamically because
there is a value which may change for each iteration of the function.
That value is the sequence name. If the sequence name were a constant,
create function ins_outline2( ) returns trigger as ' DECLARE seqname text; BEGIN seqname := ''seq_outline2_'' || NEW.level_one ; -- for readability -- This is NOT bulletproof if not exists ( select 1 from pg_class where relname = seqname) then -- execute is required because the name of the sequence -- is dynamically created execute ''create sequence '' || seqname || '' start 2''; NEW.level_two := 1; else NEW.level_two := nextval( seqname ); end if; return NEW; END; ' language 'plpgsql';
The trigger definition is simple after the creation of the function.
create trigger insupd_outline2 before insert on outline2 for each row execute procedure insupd_outline2();
To demonstrate that this works, let us insert the following records:
insert into outline2 values (default, NULL, 'one_one'); insert into outline2 values (1, NULL, 'one_two'); insert into outline2 values (1, NULL, 'one_three'); insert into outline2 values (default, NULL, 'two_one'); insert into outline2 values (2, NULL, 'two_two'); insert into outline2 values (3, NULL, 'three_one');This is the result:
elein=# select * from outline2; level_one | level_two | data -----------+-----------+----------- 1 | 1 | one_one 1 | 2 | one_two 1 | 3 | one_three 2 | 1 | two_one 2 | 2 | two_two 3 | 1 | three_one (6 rows)
This idea can be expanded to more than two columns if necessary by handling additional columns similarly. Creation of the first of any new sequence is the tricky part.
The full SQL script to recreate this test case is available.
Primary keys and foreign keys are constraints on tables. It is possible to use ALTER TABLE to drop these constraints.
ALTER TABLE tablename DROP CONSTRAINT constraint_name [RESTRICT | CASCADE]There was some confusion with regards to what the constraint name is for a primary or foreign key. A book was said to suggest that the following statements should drop those keys, however this does not work in PostgreSQL. PostgreSQL, like the SQL standards requires the unique name of the table constraint.
-- does not work ALTER TABLE mytable DROP PRIMARY KEY (id); ALTER TABLE SALESREPS DROP CONSTRAINT FOREIGN KEY (REP_OFFICE) REFERENCES OFFICES;To find the proper constraint name use the \d in psql. In the following case, the primary key constraint is a64_pkey and the foreign key constraint is "$1" (with the quotes).
elein=# \d a64 Table "public.a64" Column | Type | Modifiers --------+---------+----------- id | integer | not null xid | integer | a_data | text | Indexes: "a64_pkey" primary key, btree (id) Foreign-key constraints: "$1" FOREIGN KEY (xid) REFERENCES b64(id)
Using the correct constraint names, you can drop the primary and foreign keys for your table.
elein=# ALTER TABLE a64 DROP CONSTRAINT a64_pkey; ALTER TABLE elein=# \d a64 Table "public.a64" Column | Type | Modifiers --------+---------+----------- id | integer | not null xid | integer | a_data | text | Foreign-key constraints: "$1" FOREIGN KEY (xid) REFERENCES b64(id) elein=# ALTER TABLE a64 DROP CONSTRAINT "$1"; ALTER TABLE elein=# \d a64 Table "public.a64" Column | Type | Modifiers --------+---------+----------- id | integer | not null xid | integer | a_data | text |
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