Re: Materialized View Patch File

Hi Dave,

On Thu, Jun 13, 2013 at 5:41 PM, Dave Page <dave(dot)page(at)enterprisedb(dot)com>wrote:

> Hi
>
> On Thu, Jun 13, 2013 at 9:23 AM, Neel Patel <neel(dot)patel(at)enterprisedb(dot)com>
> wrote:
> > Hi Dave,
> >
> > Please find the updated patch file for Materialized View with below
> changes.
> >
> > ---- Materialized View UI changes
> > ---- Added Option for WITH DATA
> > ---- Some of the bug fixes
>
> Much better :-)
>
> The only issue that I can find is that if I create a matview, and give
> it some custom vacuum properties, when I re-open the properties
> dialogue, I cannot edit any of the auto vacuum properties - they're
> all greyed out.
>
>
Yes it is an issue with index. Auto vacuum properties should be disable for
the Toast Table tab, not for the Table tab. It depends on the type of query
we are providing in definition. If it contains the ToastTable then Toast
Table tab will be enabled otherwise it will be disabled. Here in below case
query for "foo" and "ruletest" views has toast table so it will not disable
the tab but "gerp" view's query doen't have toast table so it will be
disable the toast table tab ( Currently it is disabling the Table tab which
has beed fixed with attached patch).

> *But*, it doesn't happen with all views - and in a (admittedly brief)
> test, I couldn't see what caused it. I see the problem with "gerp",
> but not "foo" or "ruletest":
>
> CREATE MATERIALIZED VIEW foo
> WITH (
> FILLFACTOR=12,
> autovacuum_enabled=true,
> autovacuum_analyze_threshold=10,
> toast.autovacuum_enabled=true
> ) AS
> SELECT pg_class.relname,
> pg_class.relnamespace,
> pg_class.reltype,
> pg_class.reloftype,
> pg_class.relowner,
> pg_class.relam,
> pg_class.relfilenode,
> pg_class.reltablespace,
> pg_class.relpages,
> pg_class.reltuples,
> pg_class.relallvisible,
> pg_class.reltoastrelid,
> pg_class.reltoastidxid,
> pg_class.relhasindex,
> pg_class.relisshared,
> pg_class.relpersistence,
> pg_class.relkind,
> pg_class.relnatts,
> pg_class.relchecks,
> pg_class.relhasoids,
> pg_class.relhaspkey,
> pg_class.relhasrules,
> pg_class.relhastriggers,
> pg_class.relhassubclass,
> pg_class.relispopulated,
> pg_class.relfrozenxid,
> pg_class.relminmxid,
> pg_class.relacl,
> pg_class.reloptions
> FROM pg_class
> WITH DATA;
>
> ALTER TABLE foo
> OWNER TO postgres;
>
> CREATE MATERIALIZED VIEW gerp
> WITH (
> FILLFACTOR=12,
> autovacuum_enabled=true,
> autovacuum_vacuum_threshold=40
> ) AS
> SELECT pg_class.oid,
> pg_class.relname
> FROM pg_class
> WITH DATA;
>
> ALTER TABLE gerp
> OWNER TO postgres;
>
> CREATE MATERIALIZED VIEW ruletest
> WITH (
> autovacuum_enabled=true,
> autovacuum_vacuum_threshold=23,
> autovacuum_vacuum_cost_delay=15,
> toast.autovacuum_enabled=true,
> toast.autovacuum_freeze_min_age=500000
> ) AS
> SELECT pg_class.relname,
> pg_class.relnamespace,
> pg_class.reltype,
> pg_class.reloftype,
> pg_class.relowner,
> pg_class.relam,
> pg_class.relfilenode,
> pg_class.reltablespace,
> pg_class.relpages,
> pg_class.reltuples,
> pg_class.relallvisible,
> pg_class.reltoastrelid,
> pg_class.reltoastidxid,
> pg_class.relhasindex,
> pg_class.relisshared,
> pg_class.relpersistence,
> pg_class.relkind,
> pg_class.relnatts,
> pg_class.relchecks,
> pg_class.relhasoids,
> pg_class.relhaspkey,
> pg_class.relhasrules,
> pg_class.relhastriggers,
> pg_class.relhassubclass,
> pg_class.relispopulated,
> pg_class.relfrozenxid,
> pg_class.relminmxid,
> pg_class.relacl,
> pg_class.reloptions
> FROM pg_class
> WITH DATA;
>
> ALTER TABLE ruletest
> OWNER TO postgres;
>
> Aside from that issue, I think it's just about done :-)
>
> --
> Dave Page
> Chief Architect, Tools & Installers
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>