Thread: Implicit rule created for materialized views
Hi
In playing with materialized views, I noticed that they still seem to
have an _RETURN rule implicitly created like a regular view. This
doesn't seem right to me - is there a reason?
viper:~ dpage$ /usr/local/pgsql-9.3/bin/psql -p 5433 -U postgres
psql (9.3beta1)
Type "help" for help.
postgres=# CREATE MATERIALIZED VIEW ruletest AS SELECT * FROM pg_class;
SELECT 298
postgres=# SELECT pg_get_ruledef(oid) FROM pg_rewrite WHERE ev_class =
'ruletest'::regclass; pg_get_ruledef
-----------------------------------------------------------------CREATE RULE "_RETURN" AS
+ ON SELECT TO ruletest DO INSTEAD 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;
(1 row)
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Mon, Jun 3, 2013 at 1:06 PM, Kevin Grittner <kevin.grittner@enterprisedb.com> wrote: > Yes, that is currently used for REFRESH, and will be used to drive the > incremental maintenance when that is added. Without it, CREATE MATERIALIZED > VIEW wouldn't be different from CREATE TABLE AS. OK. > A materialized view is pretty much like a view, but with the results > materialized. Yeah, I get that, but what is confusing is that this now seems to be a special kind of relation where there is an ON SELECT DO INSTEAD rule which isn't actually executed on SELECTs from the view but at some arbitrary time in the future. > On Mon, Jun 3, 2013 at 6:58 AM, Dave Page <dpage@pgadmin.org> wrote: >> >> Hi >> >> In playing with materialized views, I noticed that they still seem to >> have an _RETURN rule implicitly created like a regular view. This >> doesn't seem right to me - is there a reason? >> >> viper:~ dpage$ /usr/local/pgsql-9.3/bin/psql -p 5433 -U postgres >> psql (9.3beta1) >> Type "help" for help. >> >> postgres=# CREATE MATERIALIZED VIEW ruletest AS SELECT * FROM pg_class; >> SELECT 298 >> postgres=# SELECT pg_get_ruledef(oid) FROM pg_rewrite WHERE ev_class = >> 'ruletest'::regclass; >> pg_get_ruledef >> ----------------------------------------------------------------- >> CREATE RULE "_RETURN" AS + >> ON SELECT TO ruletest DO INSTEAD 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; >> (1 row) >> >> >> -- >> Dave Page >> Blog: http://pgsnake.blogspot.com >> Twitter: @pgsnake >> >> EnterpriseDB UK: http://www.enterprisedb.com >> The Enterprise PostgreSQL Company > > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Dave Page <dpage@pgadmin.org> writes:
> On Mon, Jun 3, 2013 at 1:06 PM, Kevin Grittner
> <kevin.grittner@enterprisedb.com> wrote:
>> A materialized view is pretty much like a view, but with the results
>> materialized.
> Yeah, I get that, but what is confusing is that this now seems to be a
> special kind of relation where there is an ON SELECT DO INSTEAD rule
> which isn't actually executed on SELECTs from the view but at some
> arbitrary time in the future.
There is that. I wondered before if it would be worth the trouble to
invent a distinct pg_rewrite.ev_type value for these things, ie the rule
would be something like "ON REFRESH DO INSTEAD ...". On balance that
seems like it would force a lot of additional code changes for
questionable benefit, though.
regards, tom lane
Dave Page <dpage@pgadmin.org> wrote: > Kevin Grittner <kevin.grittner@enterprisedb.com> wrote: >> Dave Page <dpage@pgadmin.org> wrote: >>> In playing with materialized views, I noticed that they still >>> seem to have an _RETURN rule implicitly created like a regular >>> view. >> A materialized view is pretty much like a view, but with the >> results materialized. > > Yeah, I get that, but what is confusing is that this now seems to > be a special kind of relation where there is an ON SELECT DO > INSTEAD rule which isn't actually executed on SELECTs from the > view but at some arbitrary time in the future. Perhaps this way of looking at it will allow it to make sense: It generates values which will be returned by SELECT -- it just does that in advance and caches them on disk for quicker return when queried. As a practical matter, a materialized view needs to store exactly the same information about its query, in the same form, as a regular view. To add a new table to store this in a different place, with references and such maintained in the same way, would have multiplied the size of the patch with a lot of copy/pasted code. I'm pretty sure the result would have been something which was harder to review and maintain. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, Jun 3, 2013 at 3:59 PM, Kevin Grittner <kgrittn@ymail.com> wrote: > Dave Page <dpage@pgadmin.org> wrote: >> Kevin Grittner <kevin.grittner@enterprisedb.com> wrote: >>> Dave Page <dpage@pgadmin.org> wrote: > >>>> In playing with materialized views, I noticed that they still >>>> seem to have an _RETURN rule implicitly created like a regular >>>> view. > >>> A materialized view is pretty much like a view, but with the >>> results materialized. >> >> Yeah, I get that, but what is confusing is that this now seems to >> be a special kind of relation where there is an ON SELECT DO >> INSTEAD rule which isn't actually executed on SELECTs from the >> view but at some arbitrary time in the future. > > Perhaps this way of looking at it will allow it to make sense: It > generates values which will be returned by SELECT -- it just does > that in advance and caches them on disk for quicker return when > queried. That perspective certainly makes it clearer. > As a practical matter, a materialized view needs to store exactly > the same information about its query, in the same form, as a > regular view. To add a new table to store this in a different > place, with references and such maintained in the same way, would > have multiplied the size of the patch with a lot of copy/pasted > code. I'm pretty sure the result would have been something which > was harder to review and maintain. Yeah, I have no desire for that to be done. I'm just trying to understand what looked like some weirdness in the way it all worked. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Yes, that is currently used for REFRESH, and will be used to drive the incremental maintenance when that is added. Without it, CREATE MATERIALIZED VIEW wouldn't be different from CREATE TABLE AS.
A materialized view is pretty much like a view, but with the results materialized.
-Kevin
On Mon, Jun 3, 2013 at 6:58 AM, Dave Page <dpage@pgadmin.org> wrote:
Hi
In playing with materialized views, I noticed that they still seem to
have an _RETURN rule implicitly created like a regular view. This
doesn't seem right to me - is there a reason?
viper:~ dpage$ /usr/local/pgsql-9.3/bin/psql -p 5433 -U postgres
psql (9.3beta1)
Type "help" for help.
postgres=# CREATE MATERIALIZED VIEW ruletest AS SELECT * FROM pg_class;
SELECT 298
postgres=# SELECT pg_get_ruledef(oid) FROM pg_rewrite WHERE ev_class =
'ruletest'::regclass;
pg_get_ruledef
-----------------------------------------------------------------
CREATE RULE "_RETURN" AS +
ON SELECT TO ruletest DO INSTEAD 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;
(1 row)
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company