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

01-Mar-2004 Issue: 64

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.

Materialized Views in PostgreSQL
[SQL] Materialized View Summary 24-Feb-2004

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

    1) create a sequence for each new level_one value that is inserted
    2) set level_two to the next value of that sequence
This function first creates the sequence name as a variable. This is only done for readability. Then we check to see if the sequence already exists. This is not completely safe. Another transaction may be creating the sequence at the same time. This is why this method is unwise for high concurrency inserts where inserts of new level one values are done often by different connections. If the sequence does not exist, then we create one to start at 2 and assign the level_two to 1.

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

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]
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    | 

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


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