Re: Materialized View Patch File

Hi

On Mon, Jul 1, 2013 at 1:00 PM, Neel Patel <neel.patel@enterprisedb.com> wrote:
> Hi Dave,
>
> Find the attached patch file ( it is on top of master branch ) for change in
> icon of materialized view in pgAdmin and also put the icon images attached
> in pgadmin/include/images folder.
>
> Please let me know in case of any issue.

A couple of issues:

- "Closed" (as in GetClosedIconId, smallClosedId and ClosedId) isn't
the right phrase in this context. Maybe s/Closed/Materialized/ ?

- Including a SELECT inside the loop over pgSet *views is a potential
performance killer if there are a lot of views. Please add a suitable
column to the outer query.

- I notice there are also 2 "if
(collection->GetConnection()->BackendMinimumVersion(9, 3))" tests
right next to each other in that outer query, one to get the vacuum
params, and one to get the fill factor. Can you please merge them
together?

Thanks.

>
> Thanks,
> Neel Patel
>
>
>
> On Wed, Jun 19, 2013 at 8:46 PM, Dave Page <dave.page@enterprisedb.com>
> wrote:
>>
>> Thanks, applied.
>>
>> On Wed, Jun 19, 2013 at 12:54 PM, Neel Patel
>> <neel.patel@enterprisedb.com> wrote:
>> > Hi Dave,
>> >
>> > Fixed the below bug.
>> >
>> > Bug:-
>> >
>> > When user creates the new schema and provides <schema_name>.<table_name>
>> > in
>> > the definition of the materialized view then it fails to display correct
>> > information in properties and SQL pane. Below are the steps to reproduce
>> > it.
>> >
>> > Step 1 :- Create new schema under database.
>> > Step 2:- Create new table under newly created schema
>> > Step 3:- Create new materialized view and give in definition
>> > <new_schema>.<new_table>
>> > Step 4:- Click on the materialized view and check properties and SQL
>> > Pane.
>> >
>> > Please find attached patch for fix.
>> >
>> > Thanks,
>> > Neel Patel
>> >
>> >
>> > On Tue, Jun 18, 2013 at 8:13 PM, Dave Page <dave.page@enterprisedb.com>
>> > wrote:
>> >>
>> >> Committed with a couple of minor changes:
>> >>
>> >> - Add the word "MATERIALIZED" to the COMMENT SQL where appropriate.
>> >>
>> >> - List the comment after all the other properties, per our standard.
>> >>
>> >> Thanks!!
>> >>
>> >> On Fri, Jun 14, 2013 at 7:02 AM, Neel Patel
>> >> <neel.patel@enterprisedb.com>
>> >> wrote:
>> >> > Hi Dave,
>> >> >
>> >> >
>> >> > On Thu, Jun 13, 2013 at 5:41 PM, Dave Page
>> >> > <dave.page@enterprisedb.com>
>> >> > wrote:
>> >> >>
>> >> >> Hi
>> >> >>
>> >> >> On Thu, Jun 13, 2013 at 9:23 AM, Neel Patel
>> >> >> <neel.patel@enterprisedb.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
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >> --
>> >> Dave Page
>> >> Chief Architect, Tools & Installers
>> >> EnterpriseDB: http://www.enterprisedb.com
>> >> The Enterprise PostgreSQL Company
>> >>
>> >> Blog: http://pgsnake.blogspot.com
>> >> Twitter: @pgsnake
>> >
>> >
>>
>>
>>
>> --
>> Dave Page
>> Chief Architect, Tools & Installers
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>
>



--
Dave Page
Chief Architect, Tools & Installers
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake