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?

Contributors: Jonathan Gardner jgardner at jonathangardner.net, elein at varlena.com

Level Numbering using Sequences [GENERAL] SERIAL type - auto-increment grouped by other field 28-Feb-2004

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');

     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:

1) create a sequence for each new level_one value that is inserted
2) set level_two to the next value of that sequence

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, then the create sequence statement could be precompiled in the plpgsql function.

     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');

     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.

Contributors: Paulovi? Michal , elein at varlena.com

Dropping Primary and Foreign Keys [GENERAL] How can I delete a primary or foreign key? 20-Feb-2004

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]

     -- does not work 
     ALTER TABLE mytable DROP PRIMARY KEY (id);
     ALTER TABLE SALESREPS
            DROP CONSTRAINT
     FOREIGN KEY (REP_OFFICE)
           REFERENCES OFFICES;

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    | 

Contributors: Tibor tiborh at mail.datanet.hu, Stephan Szabo sszabo at megazone.bigpanda.com, Joshua D. Drake jd at commandprompt.com, Richard Huxton dev at archonet.com