Skip site navigation (1) Skip section navigation (2)

- GSoC - snapshot materialized view (work-in-progress) patch

From: Pavel Baroš <baros(dot)p(at)seznam(dot)cz>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>
Subject: - GSoC - snapshot materialized view (work-in-progress) patch
Date: 2010-07-08 22:29:23
Message-ID: 4C365143.9070706@seznam.cz (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi all,

here is my patch trying to implement snapshot materialized views. On 
http://github.com/pbaros/postgres can be seen too (or downloaded source 
code). But it is still in progress. Together with patch I enclosed sql 
script that shows what can be done with MV.

Description of patch:
1) can create MV, and is created uninitialized with data
    CREATE MATERIALIZED VIEW mvname AS SELECT ...

2) can refresh MV
    ALTER MATERIALIZED VIEW mvname REFRESH

3) MV cannot be modified by DML commands (INSERT, UPDATE and DELETE are 
not permitted)

4) index can be created and used with MV

5) pg_dump is repaired, in previous patch dump threw error, now dont, 
but it is sort of dummy, I want to reach state, where refreshing command 
will be posed after all COPY statements (when all data are in tables). 
In this patch REFRESH command is right behind CREATE MV command.

6) psql works too, new command \dm[S+] was added to the list
   \d[S+] [PATTERN]   - lists all db objects like tables, view, 
materialized view and sequences
   \dm[S+] [PATTERN]  - lists all materialized views

7) there are some docs too, but I guess it is not enough, at least my 
english will need to correct

8) some ALTER TABLE commands works, ie. RENAME TO, OWNER TO, SET SCHEMA, 
SET TABLESPACE

9) MV and columns can be commented

10) also some functions behave as expected, but if you know about some I 
did not mention and could fail when used with MV, I appreciate your hints
      pg_get_viewdef()
      pg_get_ruledef()
      pg_relation_filenode()
      pg_relation_filepath()
      pg_table_size()


In progress:
- regression tests
- behavior of various ALTER commands, ie SET STATISTIC, CLUSTER ON, 
ENABLE/DISABLE RULE, etc.


thanks for comments

Pavel Baros



Attachment: test_create.sql
Description: text/x-sql (2.7 KB)
Attachment: mv_v1.1.patch
Description: text/x-patch (73.6 KB)

Responses

pgsql-hackers by date

Next:From: KaiGai KoheiDate: 2010-07-09 00:20:17
Subject: Re: [v9.1] Add security hook on initialization of instance
Previous:From: Simon RiggsDate: 2010-07-08 21:09:58
Subject: Re: ALTER TABLE SET STATISTICS requires AccessExclusiveLock

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group