*** a/contrib/oid2name/oid2name.c --- b/contrib/oid2name/oid2name.c *************** *** 494,500 **** sql_exec_dumpalltables(PGconn *conn, struct options * opts) " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " " LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database()," " pg_catalog.pg_tablespace t " ! "WHERE relkind IN ('r'%s%s) AND " " %s" " t.oid = CASE" " WHEN reltablespace <> 0 THEN reltablespace" --- 494,500 ---- " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " " LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database()," " pg_catalog.pg_tablespace t " ! "WHERE relkind IN ('r', 'm'%s%s) AND " " %s" " t.oid = CASE" " WHEN reltablespace <> 0 THEN reltablespace" *************** *** 565,571 **** sql_exec_searchtables(PGconn *conn, struct options * opts) " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace \n" " LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),\n" " pg_catalog.pg_tablespace t \n" ! "WHERE relkind IN ('r', 'i', 'S', 't') AND \n" " t.oid = CASE\n" " WHEN reltablespace <> 0 THEN reltablespace\n" " ELSE dattablespace\n" --- 565,571 ---- " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace \n" " LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),\n" " pg_catalog.pg_tablespace t \n" ! "WHERE relkind IN ('r', 'm', 'i', 'S', 't') AND \n" " t.oid = CASE\n" " WHEN reltablespace <> 0 THEN reltablespace\n" " ELSE dattablespace\n" *** a/contrib/pg_upgrade/info.c --- b/contrib/pg_upgrade/info.c *************** *** 282,288 **** get_rel_infos(ClusterInfo *cluster, DbInfo *dbinfo) "CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid " "FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n " " ON c.relnamespace = n.oid " ! "WHERE relkind IN ('r', 'i'%s) AND " /* exclude possible orphaned temp tables */ " ((n.nspname !~ '^pg_temp_' AND " " n.nspname !~ '^pg_toast_temp_' AND " --- 282,288 ---- "CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid " "FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n " " ON c.relnamespace = n.oid " ! "WHERE relkind IN ('r', 'm', 'i'%s) AND " /* exclude possible orphaned temp tables */ " ((n.nspname !~ '^pg_temp_' AND " " n.nspname !~ '^pg_toast_temp_' AND " *** a/contrib/pg_upgrade/pg_upgrade.c --- b/contrib/pg_upgrade/pg_upgrade.c *************** *** 457,464 **** set_frozenxids(void) PQclear(executeQueryOrDie(conn, "UPDATE pg_catalog.pg_class " "SET relfrozenxid = '%u' " ! /* only heap and TOAST are vacuumed */ ! "WHERE relkind IN ('r', 't')", old_cluster.controldata.chkpnt_nxtxid)); PQfinish(conn); --- 457,464 ---- PQclear(executeQueryOrDie(conn, "UPDATE pg_catalog.pg_class " "SET relfrozenxid = '%u' " ! /* only heap, materialized view, and TOAST are vacuumed */ ! "WHERE relkind IN ('r', 'm', 't')", old_cluster.controldata.chkpnt_nxtxid)); PQfinish(conn); *** a/contrib/pg_upgrade/version_old_8_3.c --- b/contrib/pg_upgrade/version_old_8_3.c *************** *** 145,151 **** old_8_3_check_for_tsquery_usage(ClusterInfo *cluster) "FROM pg_catalog.pg_class c, " " pg_catalog.pg_namespace n, " " pg_catalog.pg_attribute a " ! "WHERE c.relkind = 'r' AND " " c.oid = a.attrelid AND " " NOT a.attisdropped AND " " a.atttypid = 'pg_catalog.tsquery'::pg_catalog.regtype AND " --- 145,151 ---- "FROM pg_catalog.pg_class c, " " pg_catalog.pg_namespace n, " " pg_catalog.pg_attribute a " ! "WHERE c.relkind in ('r', 'm') AND " " c.oid = a.attrelid AND " " NOT a.attisdropped AND " " a.atttypid = 'pg_catalog.tsquery'::pg_catalog.regtype AND " *************** *** 323,329 **** old_8_3_rebuild_tsvector_tables(ClusterInfo *cluster, bool check_mode) "FROM pg_catalog.pg_class c, " " pg_catalog.pg_namespace n, " " pg_catalog.pg_attribute a " ! "WHERE c.relkind = 'r' AND " " c.oid = a.attrelid AND " " NOT a.attisdropped AND " " a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " --- 323,329 ---- "FROM pg_catalog.pg_class c, " " pg_catalog.pg_namespace n, " " pg_catalog.pg_attribute a " ! "WHERE c.relkind in ('r', 'm') AND " " c.oid = a.attrelid AND " " NOT a.attisdropped AND " " a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " *************** *** 343,349 **** old_8_3_rebuild_tsvector_tables(ClusterInfo *cluster, bool check_mode) "FROM pg_catalog.pg_class c, " \ " pg_catalog.pg_namespace n, " \ " pg_catalog.pg_attribute a " \ ! "WHERE c.relkind = 'r' AND " \ " c.oid = a.attrelid AND " \ " NOT a.attisdropped AND " \ " a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " \ --- 343,349 ---- "FROM pg_catalog.pg_class c, " \ " pg_catalog.pg_namespace n, " \ " pg_catalog.pg_attribute a " \ ! "WHERE c.relkind in ('r', 'm') AND " \ " c.oid = a.attrelid AND " \ " NOT a.attisdropped AND " \ " a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " \ *** a/contrib/pgstattuple/pgstattuple.c --- b/contrib/pgstattuple/pgstattuple.c *************** *** 216,221 **** pgstat_relation(Relation rel, FunctionCallInfo fcinfo) --- 216,222 ---- switch (rel->rd_rel->relkind) { case RELKIND_RELATION: + case RELKIND_MATVIEW: case RELKIND_TOASTVALUE: case RELKIND_SEQUENCE: return pgstat_heap(rel, fcinfo); *** a/contrib/sepgsql/dml.c --- b/contrib/sepgsql/dml.c *************** *** 191,196 **** check_relation_privileges(Oid relOid, --- 191,197 ---- switch (relkind) { case RELKIND_RELATION: + case RELKIND_MATVIEW: result = sepgsql_avc_check_perms(&object, SEPG_CLASS_DB_TABLE, required, *************** *** 226,232 **** check_relation_privileges(Oid relOid, /* * Only columns owned by relations shall be checked */ ! if (relkind != RELKIND_RELATION) return true; /* --- 227,233 ---- /* * Only columns owned by relations shall be checked */ ! if (relkind != RELKIND_RELATION && relkind != RELKIND_MATVIEW) return true; /* *** a/contrib/sepgsql/label.c --- b/contrib/sepgsql/label.c *************** *** 764,769 **** exec_object_restorecon(struct selabel_handle * sehnd, Oid catalogId) --- 764,771 ---- objtype = SELABEL_DB_SEQUENCE; else if (relForm->relkind == RELKIND_VIEW) objtype = SELABEL_DB_VIEW; + else if (relForm->relkind == RELKIND_MATVIEW) + objtype = SELABEL_DB_TABLE; else continue; /* no need to assign security label */ *************** *** 782,788 **** exec_object_restorecon(struct selabel_handle * sehnd, Oid catalogId) case AttributeRelationId: attForm = (Form_pg_attribute) GETSTRUCT(tuple); ! if (get_rel_relkind(attForm->attrelid) != RELKIND_RELATION) continue; /* no need to assign security label */ objtype = SELABEL_DB_COLUMN; --- 784,791 ---- case AttributeRelationId: attForm = (Form_pg_attribute) GETSTRUCT(tuple); ! if (get_rel_relkind(attForm->attrelid) != RELKIND_RELATION && ! get_rel_relkind(attForm->attrelid) != RELKIND_MATVIEW) continue; /* no need to assign security label */ objtype = SELABEL_DB_COLUMN; *** a/contrib/sepgsql/relation.c --- b/contrib/sepgsql/relation.c *************** *** 54,61 **** sepgsql_attribute_post_create(Oid relOid, AttrNumber attnum) Form_pg_attribute attForm; /* ! * Only attributes within regular relation have individual security ! * labels. */ if (get_rel_relkind(relOid) != RELKIND_RELATION) return; --- 54,61 ---- Form_pg_attribute attForm; /* ! * Only attributes within regular relation can have ALTER to add columns ! * with individual security labels. */ if (get_rel_relkind(relOid) != RELKIND_RELATION) return; *************** *** 159,165 **** sepgsql_attribute_relabel(Oid relOid, AttrNumber attnum, ObjectAddress object; char *audit_name; ! if (get_rel_relkind(relOid) != RELKIND_RELATION) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("cannot set security label on non-regular columns"))); --- 159,166 ---- ObjectAddress object; char *audit_name; ! if (get_rel_relkind(relOid) != RELKIND_RELATION && ! get_rel_relkind(relOid) != RELKIND_MATVIEW) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("cannot set security label on non-regular columns"))); *************** *** 252,257 **** sepgsql_relation_post_create(Oid relOid) --- 253,259 ---- switch (classForm->relkind) { case RELKIND_RELATION: + case RELKIND_MATVIEW: tclass = SEPG_CLASS_DB_TABLE; tclass_text = "table"; break; *************** *** 301,310 **** sepgsql_relation_post_create(Oid relOid) SetSecurityLabel(&object, SEPGSQL_LABEL_TAG, rcontext); /* ! * We also assigns a default security label on columns of the new regular ! * tables. */ ! if (classForm->relkind == RELKIND_RELATION) { Relation arel; ScanKeyData akey; --- 303,313 ---- SetSecurityLabel(&object, SEPGSQL_LABEL_TAG, rcontext); /* ! * We also assign a default security label on columns of new regular ! * tables and materialized views. */ ! if (classForm->relkind == RELKIND_RELATION || ! classForm->relkind == RELKIND_MATVIEW) { Relation arel; ScanKeyData akey; *************** *** 378,383 **** sepgsql_relation_drop(Oid relOid) --- 381,387 ---- switch (relkind) { case RELKIND_RELATION: + case RELKIND_MATVIEW: tclass = SEPG_CLASS_DB_TABLE; break; case RELKIND_SEQUENCE: *************** *** 489,499 **** sepgsql_relation_relabel(Oid relOid, const char *seclabel) tclass = SEPG_CLASS_DB_SEQUENCE; else if (relkind == RELKIND_VIEW) tclass = SEPG_CLASS_DB_VIEW; else ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("cannot set security labels on relations except " ! "for tables, sequences or views"))); object.classId = RelationRelationId; object.objectId = relOid; --- 493,505 ---- tclass = SEPG_CLASS_DB_SEQUENCE; else if (relkind == RELKIND_VIEW) tclass = SEPG_CLASS_DB_VIEW; + else if (relkind == RELKIND_MATVIEW) + tclass = SEPG_CLASS_DB_TABLE; else ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("cannot set security labels on relations except " ! "for tables, sequences, views, or materialized views"))); object.classId = RelationRelationId; object.objectId = relOid; *************** *** 536,541 **** sepgsql_relation_setattr(Oid relOid) --- 542,548 ---- switch (get_rel_relkind(relOid)) { case RELKIND_RELATION: + case RELKIND_MATVIEW: tclass = SEPG_CLASS_DB_TABLE; break; case RELKIND_SEQUENCE: *** a/contrib/sepgsql/sepgsql.h --- b/contrib/sepgsql/sepgsql.h *************** *** 32,37 **** --- 32,39 ---- /* * Internally used code of object classes + * + * NOTE: Materialized views are treated as tables for now. */ #define SEPG_CLASS_PROCESS 0 #define SEPG_CLASS_FILE 1 *** a/contrib/vacuumlo/vacuumlo.c --- b/contrib/vacuumlo/vacuumlo.c *************** *** 209,215 **** vacuumlo(const char *database, const struct _param * param) strcat(buf, " AND a.atttypid = t.oid "); strcat(buf, " AND c.relnamespace = s.oid "); strcat(buf, " AND t.typname in ('oid', 'lo') "); ! strcat(buf, " AND c.relkind = 'r'"); strcat(buf, " AND s.nspname !~ '^pg_'"); res = PQexec(conn, buf); if (PQresultStatus(res) != PGRES_TUPLES_OK) --- 209,215 ---- strcat(buf, " AND a.atttypid = t.oid "); strcat(buf, " AND c.relnamespace = s.oid "); strcat(buf, " AND t.typname in ('oid', 'lo') "); ! strcat(buf, " AND c.relkind in ('r', 'm')"); strcat(buf, " AND s.nspname !~ '^pg_'"); res = PQexec(conn, buf); if (PQresultStatus(res) != PGRES_TUPLES_OK) *** a/doc/src/sgml/catalogs.sgml --- b/doc/src/sgml/catalogs.sgml *************** *** 1597,1604 **** The catalog pg_class catalogs tables and most everything else that has columns or is otherwise similar to a table. This includes indexes (but see also ! pg_index), sequences, views, composite types, ! and TOAST tables; see relkind. Below, when we mean all of these kinds of objects we speak of relations. Not all columns are meaningful for all relation types. --- 1597,1604 ---- The catalog pg_class catalogs tables and most everything else that has columns or is otherwise similar to a table. This includes indexes (but see also ! pg_index), sequences, views, materialized ! views, composite types, and TOAST tables; see relkind. Below, when we mean all of these kinds of objects we speak of relations. Not all columns are meaningful for all relation types. *************** *** 1789,1796 **** r = ordinary table, i = index, ! S = sequence, v = view, c = ! composite type, t = TOAST table, f = foreign table --- 1789,1797 ---- r = ordinary table, i = index, ! S = sequence, v = view, ! m = materialized view, ! c = composite type, t = TOAST table, f = foreign table *************** *** 1863,1868 **** --- 1864,1879 ---- + relisvalid + boolean + + + Relation is valid and can be scanned. Currently, this is used only for + materialized views; for other relation types, it will always be true. + + + + relfrozenxid xid *** a/doc/src/sgml/ref/allfiles.sgml --- b/doc/src/sgml/ref/allfiles.sgml *************** *** 21,26 **** Complete list of usable sgml source files in this directory. --- 21,27 ---- + *************** *** 62,67 **** Complete list of usable sgml source files in this directory. --- 63,69 ---- + *************** *** 101,106 **** Complete list of usable sgml source files in this directory. --- 103,109 ---- + *************** *** 135,140 **** Complete list of usable sgml source files in this directory. --- 138,144 ---- + *** a/doc/src/sgml/ref/alter_extension.sgml --- b/doc/src/sgml/ref/alter_extension.sgml *************** *** 39,44 **** ALTER EXTENSION name DROP object_name | FOREIGN TABLE object_name | FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) | + MATERIALIZED VIEW object_name OPERATOR operator_name (left_type, right_type) | OPERATOR CLASS object_name USING index_method | OPERATOR FAMILY object_name USING index_method | *** /dev/null --- b/doc/src/sgml/ref/alter_materialized_view.sgml *************** *** 0 **** --- 1,152 ---- + + + + + ALTER MATERIALIZED VIEW + 7 + SQL - Language Statements + + + + ALTER MATERIALIZED VIEW + change the definition of a materialized view + + + + ALTER MATERIALIZED VIEW + + + + + ALTER MATERIALIZED VIEW [ IF EXISTS ] name OWNER TO new_owner + ALTER MATERIALIZED VIEW [ IF EXISTS ] name RENAME TO new_name + ALTER MATERIALIZED VIEW [ IF EXISTS ] name SET SCHEMA new_schema + ALTER MATERIALIZED VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] ) + ALTER MATERIALIZED VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] ) + + + + + Description + + + ALTER MATERIALIZED VIEW changes various auxiliary + properties of a materialized view. + + + + You must own the materialized view to use ALTER MATERIALIZED + VIEW. To change a materailized view's schema, you must also have + CREATE privilege on the new schema. + To alter the owner, you must also be a direct or indirect member of the new + owning role, and that role must have CREATE privilege on + the materialized view's schema. (These restrictions enforce that altering + the owner doesn't do anything you couldn't do by dropping and recreating the + materialized view. However, a superuser can alter ownership of any view + anyway.) + + + + + Parameters + + + + name + + + The name (optionally schema-qualified) of an existing materialized view. + + + + + + IF EXISTS + + + Do not throw an error if the materialized view does not exist. A notice + is issued in this case. + + + + + + new_owner + + + The user name of the new owner of the materialized view. + + + + + + new_name + + + The new name for the materialized view. + + + + + + new_schema + + + The new schema for the materialized view. + + + + + + view_option_name + + + The name of a materialized view option to be set or reset. + + + + + + view_option_name + + + The new value for a view option. + + + + + + + + Examples + + + To rename the materialized view foo to + bar: + + ALTER MATERIALIZED VIEW foo RENAME TO bar; + + + + + Compatibility + + + ALTER MATERIALIZED VIEW is a + PostgreSQL extension. + + + + + See Also + + + + + + + + *** a/doc/src/sgml/ref/comment.sgml --- b/doc/src/sgml/ref/comment.sgml *************** *** 38,43 **** COMMENT ON --- 38,44 ---- FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) | INDEX object_name | LARGE OBJECT large_object_oid | + MATERIALIZED VIEW object_name OPERATOR operator_name (left_type, right_type) | OPERATOR CLASS object_name USING index_method | OPERATOR FAMILY object_name USING index_method | *************** *** 279,284 **** COMMENT ON FUNCTION my_function (timestamp) IS 'Returns Roman Numeral'; --- 280,286 ---- COMMENT ON INDEX my_index IS 'Enforces uniqueness on employee ID'; COMMENT ON LANGUAGE plpython IS 'Python support for stored procedures'; COMMENT ON LARGE OBJECT 346344 IS 'Planning document'; + COMMENT ON MATERIALIZED VIEW my_matview IS 'Summary of order history'; COMMENT ON OPERATOR ^ (text, text) IS 'Performs intersection of two texts'; COMMENT ON OPERATOR - (NONE, integer) IS 'Unary minus'; COMMENT ON OPERATOR CLASS int4ops USING btree IS '4 byte integer operators for btrees'; *** /dev/null --- b/doc/src/sgml/ref/create_materialized_view.sgml *************** *** 0 **** --- 1,174 ---- + + + + + CREATE MATERIALIZED VIEW + 7 + SQL - Language Statements + + + + CREATE MATERIALIZED VIEW + define a new materialized view + + + + CREATE MATERIALIZED VIEW + + + + + CREATE [ UNLOGGED ] MATERIALIZED VIEW table_name + [ (column_name [, ...] ) ] + [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ] + [ TABLESPACE tablespace_name ] + AS query + [ WITH [ NO ] DATA ] + + + + + Description + + + CREATE MATERIALIZED VIEW defines a materialized view of + a query. The query is executed and used to populate the view at the time + the command is issued (unless WITH NO DATA is used) and may be + refreshed later using REFRESH MATERIALIZED VIEW. + + + + CREATE MATERIALIZED VIEW is similar to + CREATE TABLE AS, except that it also remembers the query used + to initialize the view, so that it can be refreshed later upon demand. + + + + + Parameters + + + + UNLOGGED + + + If specified, the materialized view will be unlogged. + Refer to for details. + + + + + + table_name + + + The name (optionally schema-qualified) of the materialized view to be + created. + + + + + + column_name + + + The name of a column in the new materialized view. If column names are + not provided, they are taken from the output column names of the query. + + + + + + WITH ( storage_parameter [= value] [, ... ] ) + + + This clause specifies optional storage parameters for the new + materialized view; see for more + information. The WITH clause + can also include OIDS=TRUE (or just OIDS) + to specify that rows of the new table + should have OIDs (object identifiers) assigned to them, or + OIDS=FALSE to specify that the rows should not have OIDs. + See for more information. + + + + + + WITH OIDS + WITHOUT OIDS + + + These are obsolescent syntaxes equivalent to WITH (OIDS) + and WITH (OIDS=FALSE), respectively. If you wish to give + both an OIDS setting and storage parameters, you must use + the WITH ( ... ) syntax; see above. + + + + + + TABLESPACE tablespace_name + + + The tablespace_name is the name + of the tablespace in which the new materialized view is to be created. + If not specified, is consulted. + + + + + + query + + + A , TABLE, or + command, or an command that runs a + prepared SELECT, TABLE, or + VALUES query. + + + + + + WITH [ NO ] DATA + + + This clause specifies whether or not the materialized view should be + populated at creation time. If not, the materialized view will be + flagged as invalid and cannot be queried until REFRESH + MATERIALIZED VIEW is used. + + + + + + + + + Compatibility + + + CREATE MATERIALIZED VIEW is a + PostgreSQL extension. + + + + + See Also + + + + + + + + + + + *** a/doc/src/sgml/ref/create_table_as.sgml --- b/doc/src/sgml/ref/create_table_as.sgml *************** *** 340,345 **** CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS --- 340,346 ---- See Also + *** a/doc/src/sgml/ref/create_view.sgml --- b/doc/src/sgml/ref/create_view.sgml *************** *** 352,357 **** CREATE VIEW name [ ( See Also + *** /dev/null --- b/doc/src/sgml/ref/drop_materialized_view.sgml *************** *** 0 **** --- 1,114 ---- + + + + + DROP MATERIALIZED VIEW + 7 + SQL - Language Statements + + + + DROP MATERIALIZED VIEW + remove a materialized view + + + + DROP MATERIALIZED VIEW + + + + + DROP MATERIALIZED VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] + + + + + Description + + + DROP MATERIALIZED VIEW drops an existing materialized + view. To execute this command you must be the owner of the materialized + view. + + + + + Parameters + + + + IF EXISTS + + + Do not throw an error if the materialized view does not exist. A notice + is issued in this case. + + + + + + name + + + The name (optionally schema-qualified) of the materialized view to + remove. + + + + + + CASCADE + + + Automatically drop objects that depend on the materialized view (such as + other materialized views, or regular views). + + + + + + RESTRICT + + + Refuse to drop the materialized view if any objects depend on it. This + is the default. + + + + + + + + Examples + + + This command will remove the materialized view called + order_summary: + + DROP MATERIALIZED VIEW order_summary; + + + + + Compatibility + + + DROP MATERIALIZED VIEW is a + PostgreSQL extension. + + + + + See Also + + + + + + + + + *** /dev/null --- b/doc/src/sgml/ref/refresh_materialized_view.sgml *************** *** 0 **** --- 1,83 ---- + + + + + REFRESH MATERIALIZED VIEW + 7 + SQL - Language Statements + + + + REFRESH MATERIALIZED VIEW + refresh a materialized view + + + + REFRESH MATERIALIZED VIEW + + + + + REFRESH MATERIALIZED VIEW name + + + + + Description + + + REFRESH MATERIALIZED VIEW refreshes the contents of a + materialized view by re-executing the backing query. + + + + + Parameters + + + + name + + + The name (optionally schema-qualified) of the materialized view to + refresh. + + + + + + + + Examples + + + This command will refresh the materialized view called + order_summary: + + REFRESH MATERIALIZED VIEW order_summary; + + + + + Compatibility + + + REFRESH MATERIALIZED VIEW is a + PostgreSQL extension. + + + + + See Also + + + + + + + + + *** a/doc/src/sgml/ref/security_label.sgml --- b/doc/src/sgml/ref/security_label.sgml *************** *** 32,37 **** SECURITY LABEL [ FOR provider ] ON --- 32,38 ---- FOREIGN TABLE object_name FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) | LARGE OBJECT large_object_oid | + MATERIALIZED VIEW object_name [ PROCEDURAL ] LANGUAGE object_name | ROLE object_name | SCHEMA object_name | *** a/doc/src/sgml/reference.sgml --- b/doc/src/sgml/reference.sgml *************** *** 49,54 **** --- 49,55 ---- &alterIndex; &alterLanguage; &alterLargeObject; + &alterMaterializedView; &alterOperator; &alterOperatorClass; &alterOperatorFamily; *************** *** 90,95 **** --- 91,97 ---- &createGroup; &createIndex; &createLanguage; + &createMaterializedView; &createOperator; &createOperatorClass; &createOperatorFamily; *************** *** 129,134 **** --- 131,137 ---- &dropGroup; &dropIndex; &dropLanguage; + &dropMaterializedView; &dropOperator; &dropOperatorClass; &dropOperatorFamily; *************** *** 163,168 **** --- 166,172 ---- &prepare; &prepareTransaction; &reassignOwned; + &refreshMaterializedView; &reindex; &releaseSavepoint; &reset; *** a/src/backend/access/common/reloptions.c --- b/src/backend/access/common/reloptions.c *************** *** 791,796 **** extractRelOptions(HeapTuple tuple, TupleDesc tupdesc, Oid amoptions) --- 791,797 ---- case RELKIND_RELATION: case RELKIND_TOASTVALUE: case RELKIND_VIEW: + case RELKIND_MATVIEW: options = heap_reloptions(classForm->relkind, datum, false); break; case RELKIND_INDEX: *************** *** 1191,1196 **** heap_reloptions(char relkind, Datum reloptions, bool validate) --- 1192,1198 ---- } return (bytea *) rdopts; case RELKIND_RELATION: + case RELKIND_MATVIEW: return default_reloptions(reloptions, validate, RELOPT_KIND_HEAP); case RELKIND_VIEW: return default_reloptions(reloptions, validate, RELOPT_KIND_VIEW); *** a/src/backend/access/heap/heapam.c --- b/src/backend/access/heap/heapam.c *************** *** 2112,2118 **** heap_prepare_insert(Relation relation, HeapTuple tup, TransactionId xid, * If the new tuple is too big for storage or contains already toasted * out-of-line attributes from some other relation, invoke the toaster. */ ! if (relation->rd_rel->relkind != RELKIND_RELATION) { /* toast table entries should never be recursively toasted */ Assert(!HeapTupleHasExternal(tup)); --- 2112,2119 ---- * If the new tuple is too big for storage or contains already toasted * out-of-line attributes from some other relation, invoke the toaster. */ ! if (relation->rd_rel->relkind != RELKIND_RELATION && ! relation->rd_rel->relkind != RELKIND_MATVIEW) { /* toast table entries should never be recursively toasted */ Assert(!HeapTupleHasExternal(tup)); *************** *** 2657,2663 **** l1: * because we need to look at the contents of the tuple, but it's OK to * release the content lock on the buffer first. */ ! if (relation->rd_rel->relkind != RELKIND_RELATION) { /* toast table entries should never be recursively toasted */ Assert(!HeapTupleHasExternal(&tp)); --- 2658,2665 ---- * because we need to look at the contents of the tuple, but it's OK to * release the content lock on the buffer first. */ ! if (relation->rd_rel->relkind != RELKIND_RELATION && ! relation->rd_rel->relkind != RELKIND_MATVIEW) { /* toast table entries should never be recursively toasted */ Assert(!HeapTupleHasExternal(&tp)); *************** *** 3016,3022 **** l2: * We need to invoke the toaster if there are already any out-of-line * toasted values present, or if the new tuple is over-threshold. */ ! if (relation->rd_rel->relkind != RELKIND_RELATION) { /* toast table entries should never be recursively toasted */ Assert(!HeapTupleHasExternal(&oldtup)); --- 3018,3025 ---- * We need to invoke the toaster if there are already any out-of-line * toasted values present, or if the new tuple is over-threshold. */ ! if (relation->rd_rel->relkind != RELKIND_RELATION && ! relation->rd_rel->relkind != RELKIND_MATVIEW) { /* toast table entries should never be recursively toasted */ Assert(!HeapTupleHasExternal(&oldtup)); *** a/src/backend/access/heap/tuptoaster.c --- b/src/backend/access/heap/tuptoaster.c *************** *** 353,362 **** toast_delete(Relation rel, HeapTuple oldtup) bool toast_isnull[MaxHeapAttributeNumber]; /* ! * We should only ever be called for tuples of plain relations --- ! * recursing on a toast rel is bad news. */ ! Assert(rel->rd_rel->relkind == RELKIND_RELATION); /* * Get the tuple descriptor and break down the tuple into fields. --- 353,363 ---- bool toast_isnull[MaxHeapAttributeNumber]; /* ! * We should only ever be called for tuples of plain relations or ! * materialized views --- recursing on a toast rel is bad news. */ ! Assert(rel->rd_rel->relkind == RELKIND_RELATION || ! rel->rd_rel->relkind == RELKIND_MATVIEW); /* * Get the tuple descriptor and break down the tuple into fields. *************** *** 443,449 **** toast_insert_or_update(Relation rel, HeapTuple newtup, HeapTuple oldtup, * We should only ever be called for tuples of plain relations --- * recursing on a toast rel is bad news. */ ! Assert(rel->rd_rel->relkind == RELKIND_RELATION); /* * Get the tuple descriptor and break down the tuple(s) into fields. --- 444,451 ---- * We should only ever be called for tuples of plain relations --- * recursing on a toast rel is bad news. */ ! Assert(rel->rd_rel->relkind == RELKIND_RELATION || ! rel->rd_rel->relkind == RELKIND_MATVIEW); /* * Get the tuple descriptor and break down the tuple(s) into fields. *** a/src/backend/catalog/aclchk.c --- b/src/backend/catalog/aclchk.c *************** *** 765,770 **** objectsInSchemaToOids(GrantObjectType objtype, List *nspnames) --- 765,772 ---- objects = list_concat(objects, objs); objs = getRelationsInNamespace(namespaceId, RELKIND_VIEW); objects = list_concat(objects, objs); + objs = getRelationsInNamespace(namespaceId, RELKIND_MATVIEW); + objects = list_concat(objects, objs); objs = getRelationsInNamespace(namespaceId, RELKIND_FOREIGN_TABLE); objects = list_concat(objects, objs); break; *** a/src/backend/catalog/dependency.c --- b/src/backend/catalog/dependency.c *************** *** 3024,3029 **** getRelationDescription(StringInfo buffer, Oid relid) --- 3024,3033 ---- appendStringInfo(buffer, _("view %s"), relname); break; + case RELKIND_MATVIEW: + appendStringInfo(buffer, _("materialized view %s"), + relname); + break; case RELKIND_COMPOSITE_TYPE: appendStringInfo(buffer, _("composite type %s"), relname); *** a/src/backend/catalog/heap.c --- b/src/backend/catalog/heap.c *************** *** 778,783 **** InsertPgClassTuple(Relation pg_class_desc, --- 778,784 ---- values[Anum_pg_class_relhasrules - 1] = BoolGetDatum(rd_rel->relhasrules); values[Anum_pg_class_relhastriggers - 1] = BoolGetDatum(rd_rel->relhastriggers); values[Anum_pg_class_relhassubclass - 1] = BoolGetDatum(rd_rel->relhassubclass); + values[Anum_pg_class_relisvalid - 1] = BoolGetDatum(rd_rel->relisvalid); values[Anum_pg_class_relfrozenxid - 1] = TransactionIdGetDatum(rd_rel->relfrozenxid); if (relacl != (Datum) 0) values[Anum_pg_class_relacl - 1] = relacl; *************** *** 833,838 **** AddNewRelationTuple(Relation pg_class_desc, --- 834,840 ---- switch (relkind) { case RELKIND_RELATION: + case RELKIND_MATVIEW: case RELKIND_INDEX: case RELKIND_TOASTVALUE: /* The relation is real, but as yet empty */ *************** *** 856,861 **** AddNewRelationTuple(Relation pg_class_desc, --- 858,864 ---- /* Initialize relfrozenxid */ if (relkind == RELKIND_RELATION || + relkind == RELKIND_MATVIEW || relkind == RELKIND_TOASTVALUE) { /* *************** *** 879,884 **** AddNewRelationTuple(Relation pg_class_desc, --- 882,888 ---- new_rel_reltup->relowner = relowner; new_rel_reltup->reltype = new_type_oid; new_rel_reltup->reloftype = reloftype; + new_rel_reltup->relisvalid = true; new_rel_desc->rd_att->tdtypeid = new_type_oid; *************** *** 1057,1064 **** heap_create_with_catalog(const char *relname, if (IsBinaryUpgrade && OidIsValid(binary_upgrade_next_heap_pg_class_oid) && (relkind == RELKIND_RELATION || relkind == RELKIND_SEQUENCE || ! relkind == RELKIND_VIEW || relkind == RELKIND_COMPOSITE_TYPE || ! relkind == RELKIND_FOREIGN_TABLE)) { relid = binary_upgrade_next_heap_pg_class_oid; binary_upgrade_next_heap_pg_class_oid = InvalidOid; --- 1061,1068 ---- if (IsBinaryUpgrade && OidIsValid(binary_upgrade_next_heap_pg_class_oid) && (relkind == RELKIND_RELATION || relkind == RELKIND_SEQUENCE || ! relkind == RELKIND_VIEW || relkind == RELKIND_MATVIEW || ! relkind == RELKIND_COMPOSITE_TYPE || relkind == RELKIND_FOREIGN_TABLE)) { relid = binary_upgrade_next_heap_pg_class_oid; binary_upgrade_next_heap_pg_class_oid = InvalidOid; *************** *** 1084,1089 **** heap_create_with_catalog(const char *relname, --- 1088,1094 ---- { case RELKIND_RELATION: case RELKIND_VIEW: + case RELKIND_MATVIEW: case RELKIND_FOREIGN_TABLE: relacl = get_user_default_acl(ACL_OBJECT_RELATION, ownerid, relnamespace); *************** *** 1127,1132 **** heap_create_with_catalog(const char *relname, --- 1132,1138 ---- */ if (IsUnderPostmaster && (relkind == RELKIND_RELATION || relkind == RELKIND_VIEW || + relkind == RELKIND_MATVIEW || relkind == RELKIND_FOREIGN_TABLE || relkind == RELKIND_COMPOSITE_TYPE)) new_array_oid = AssignTypeArrayOid(); *************** *** 1304,1310 **** heap_create_with_catalog(const char *relname, if (relpersistence == RELPERSISTENCE_UNLOGGED) { ! Assert(relkind == RELKIND_RELATION || relkind == RELKIND_TOASTVALUE); heap_create_init_fork(new_rel_desc); } --- 1310,1317 ---- if (relpersistence == RELPERSISTENCE_UNLOGGED) { ! Assert(relkind == RELKIND_RELATION || relkind == RELKIND_MATVIEW || ! relkind == RELKIND_TOASTVALUE); heap_create_init_fork(new_rel_desc); } *************** *** 1330,1341 **** heap_create_init_fork(Relation rel) --- 1337,1396 ---- { RelationOpenSmgr(rel); smgrcreate(rel->rd_smgr, INIT_FORKNUM, false); + + /* + * Create a special init fork for a materialized view, so that on its + * initial reference it can be flagged as invalid. + */ + if (rel->rd_rel->relkind == RELKIND_MATVIEW) + { + Page page; + + page = (Page) palloc(BLCKSZ); + PageInit(page, BLCKSZ, 1); + smgrextend(rel->rd_smgr, INIT_FORKNUM, 0, (char *) page, true); + } + if (XLogIsNeeded()) log_smgrcreate(&rel->rd_smgr->smgr_rnode.node, INIT_FORKNUM); smgrimmedsync(rel->rd_smgr, INIT_FORKNUM); } /* + * Check whether the first page of a materialized view looks like it came from + * its init fork, which would mean that the MV should be flagged as invalid + * for querying. + * + * The check here must match what is set up in heap_create_init_fork(). + */ + bool + heap_is_matview_init_fork(Relation rel) + { + Page page; + bool isInitFork; + + Assert(rel->rd_rel->relkind == RELKIND_MATVIEW); + + RelationOpenSmgr(rel); + if (!smgrexists(rel->rd_smgr, MAIN_FORKNUM)) + return false; + if (smgrnblocks(rel->rd_smgr, MAIN_FORKNUM) < 1) + return false; + page = (Page) palloc(BLCKSZ); + smgrread(rel->rd_smgr, MAIN_FORKNUM, 0, (char *) page); + isInitFork = (((PageHeader) page)->pd_special > 0 && + ((PageHeader) page)->pd_special < BLCKSZ); + pfree(page); + + if (isInitFork) + { + smgrtruncate(rel->rd_smgr, MAIN_FORKNUM, 0); + } + + return isInitFork; + } + + /* * RelationRemoveInheritance * * Formerly, this routine checked for child relations and aborted the *** a/src/backend/catalog/objectaddress.c --- b/src/backend/catalog/objectaddress.c *************** *** 442,447 **** get_object_address(ObjectType objtype, List *objname, List *objargs, --- 442,448 ---- case OBJECT_SEQUENCE: case OBJECT_TABLE: case OBJECT_VIEW: + case OBJECT_MATVIEW: case OBJECT_FOREIGN_TABLE: address = get_relation_by_qualified_name(objtype, objname, *************** *** 814,819 **** get_relation_by_qualified_name(ObjectType objtype, List *objname, --- 815,827 ---- errmsg("\"%s\" is not a view", RelationGetRelationName(relation)))); break; + case OBJECT_MATVIEW: + if (relation->rd_rel->relkind != RELKIND_MATVIEW) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a materialized view", + RelationGetRelationName(relation)))); + break; case OBJECT_FOREIGN_TABLE: if (relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, *************** *** 1071,1076 **** check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address, --- 1079,1085 ---- case OBJECT_SEQUENCE: case OBJECT_TABLE: case OBJECT_VIEW: + case OBJECT_MATVIEW: case OBJECT_FOREIGN_TABLE: case OBJECT_COLUMN: case OBJECT_RULE: *** a/src/backend/catalog/toasting.c --- b/src/backend/catalog/toasting.c *************** *** 84,93 **** BootstrapToastTable(char *relName, Oid toastOid, Oid toastIndexOid) rel = heap_openrv(makeRangeVar(NULL, relName, -1), AccessExclusiveLock); ! if (rel->rd_rel->relkind != RELKIND_RELATION) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("\"%s\" is not a table", relName))); /* create_toast_table does all the work */ --- 84,94 ---- rel = heap_openrv(makeRangeVar(NULL, relName, -1), AccessExclusiveLock); ! if (rel->rd_rel->relkind != RELKIND_RELATION && ! rel->rd_rel->relkind != RELKIND_MATVIEW) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("\"%s\" is not a table or materialized view", relName))); /* create_toast_table does all the work */ *** a/src/backend/commands/Makefile --- b/src/backend/commands/Makefile *************** *** 16,22 **** OBJS = aggregatecmds.o alter.o analyze.o async.o cluster.o comment.o \ collationcmds.o constraint.o conversioncmds.o copy.o createas.o \ dbcommands.o define.o discard.o dropcmds.o \ event_trigger.o explain.o extension.o foreigncmds.o functioncmds.o \ ! indexcmds.o lockcmds.o operatorcmds.o opclasscmds.o \ portalcmds.o prepare.o proclang.o \ schemacmds.o seclabel.o sequence.o tablecmds.o tablespace.o trigger.o \ tsearchcmds.o typecmds.o user.o vacuum.o vacuumlazy.o \ --- 16,22 ---- collationcmds.o constraint.o conversioncmds.o copy.o createas.o \ dbcommands.o define.o discard.o dropcmds.o \ event_trigger.o explain.o extension.o foreigncmds.o functioncmds.o \ ! indexcmds.o lockcmds.o matview.o operatorcmds.o opclasscmds.o \ portalcmds.o prepare.o proclang.o \ schemacmds.o seclabel.o sequence.o tablecmds.o tablespace.o trigger.o \ tsearchcmds.o typecmds.o user.o vacuum.o vacuumlazy.o \ *** a/src/backend/commands/alter.c --- b/src/backend/commands/alter.c *************** *** 112,117 **** ExecRenameStmt(RenameStmt *stmt) --- 112,118 ---- case OBJECT_TABLE: case OBJECT_SEQUENCE: case OBJECT_VIEW: + case OBJECT_MATVIEW: case OBJECT_INDEX: case OBJECT_FOREIGN_TABLE: return RenameRelation(stmt); *************** *** 162,167 **** ExecAlterObjectSchemaStmt(AlterObjectSchemaStmt *stmt) --- 163,169 ---- case OBJECT_SEQUENCE: case OBJECT_TABLE: case OBJECT_VIEW: + case OBJECT_MATVIEW: return AlterTableNamespace(stmt); case OBJECT_DOMAIN: *** a/src/backend/commands/analyze.c --- b/src/backend/commands/analyze.c *************** *** 205,215 **** analyze_rel(Oid relid, VacuumStmt *vacstmt, BufferAccessStrategy bstrategy) } /* ! * Check that it's a plain table or foreign table; we used to do this in ! * get_rel_oids() but seems safer to check after we've locked the ! * relation. */ ! if (onerel->rd_rel->relkind == RELKIND_RELATION) { /* Regular table, so we'll use the regular row acquisition function */ acquirefunc = acquire_sample_rows; --- 205,216 ---- } /* ! * Check that it's a plain table, materialized view, or foreign table; we ! * used to do this in get_rel_oids() but seems safer to check after we've ! * locked the relation. */ ! if (onerel->rd_rel->relkind == RELKIND_RELATION || ! onerel->rd_rel->relkind == RELKIND_MATVIEW) { /* Regular table, so we'll use the regular row acquisition function */ acquirefunc = acquire_sample_rows; *** a/src/backend/commands/comment.c --- b/src/backend/commands/comment.c *************** *** 83,97 **** CommentObject(CommentStmt *stmt) case OBJECT_COLUMN: /* ! * Allow comments only on columns of tables, views, composite ! * types, and foreign tables (which are the only relkinds for ! * which pg_dump will dump per-column comments). In particular we ! * wish to disallow comments on index columns, because the naming ! * of an index's columns may change across PG versions, so dumping ! * per-column comments could create reload failures. */ if (relation->rd_rel->relkind != RELKIND_RELATION && relation->rd_rel->relkind != RELKIND_VIEW && relation->rd_rel->relkind != RELKIND_COMPOSITE_TYPE && relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, --- 83,99 ---- case OBJECT_COLUMN: /* ! * Allow comments only on columns of tables, views, materialized ! * views, composite types, and foreign tables (which are the only ! * relkinds for which pg_dump will dump per-column comments). In ! * particular we wish to disallow comments on index columns, ! * because the naming of an index's columns may change across PG ! * versions, so dumping per-column comments could create reload ! * failures. */ if (relation->rd_rel->relkind != RELKIND_RELATION && relation->rd_rel->relkind != RELKIND_VIEW && + relation->rd_rel->relkind != RELKIND_MATVIEW && relation->rd_rel->relkind != RELKIND_COMPOSITE_TYPE && relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, *** a/src/backend/commands/copy.c --- b/src/backend/commands/copy.c *************** *** 1428,1433 **** BeginCopyTo(Relation rel, --- 1428,1439 ---- errmsg("cannot copy from view \"%s\"", RelationGetRelationName(rel)), errhint("Try the COPY (SELECT ...) TO variant."))); + else if (rel->rd_rel->relkind == RELKIND_MATVIEW) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot copy from materialized view \"%s\"", + RelationGetRelationName(rel)), + errhint("Try the COPY (SELECT ...) TO variant."))); else if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), *************** *** 1935,1940 **** CopyFrom(CopyState cstate) --- 1941,1951 ---- (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("cannot copy to view \"%s\"", RelationGetRelationName(cstate->rel)))); + else if (cstate->rel->rd_rel->relkind == RELKIND_MATVIEW) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot copy to materialized view \"%s\"", + RelationGetRelationName(cstate->rel)))); else if (cstate->rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), *** a/src/backend/commands/createas.c --- b/src/backend/commands/createas.c *************** *** 2,7 **** --- 2,9 ---- * * createas.c * Execution of CREATE TABLE ... AS, a/k/a SELECT INTO + * Since CREATE MATERIALIZED VIEW shares syntax and most behaviors, + * implement that here, too. * * We implement this by diverting the query's normal output to a * specialized DestReceiver type. *************** *** 29,34 **** --- 31,38 ---- #include "commands/createas.h" #include "commands/prepare.h" #include "commands/tablecmds.h" + #include "commands/view.h" + #include "parser/analyze.h" #include "parser/parse_clause.h" #include "rewrite/rewriteHandler.h" #include "storage/smgr.h" *************** *** 43,48 **** typedef struct --- 47,53 ---- { DestReceiver pub; /* publicly-known function pointers */ IntoClause *into; /* target relation specification */ + Query *query; /* the query which defines/populates data */ /* These fields are filled by intorel_startup: */ Relation rel; /* relation to write to */ CommandId output_cid; /* cmin to insert in output tuples */ *************** *** 57,62 **** static void intorel_destroy(DestReceiver *self); --- 62,122 ---- /* + * Common setup needed by both normal execution and EXPLAIN ANALYZE. + */ + Query * + SetupForCreateTableAs(Query *query, IntoClause *into, const char *queryString, + ParamListInfo params, DestReceiver *dest) + { + List *rewritten; + + Assert(query->commandType == CMD_SELECT); + + if (into->relkind == RELKIND_MATVIEW) + query = (Query *) parse_analyze((Node *) copyObject(query), + queryString, NULL, 0)->utilityStmt; + + /* + * Parse analysis was done already, but we still have to run the rule + * rewriter. We do not do AcquireRewriteLocks: we assume the query either + * came straight from the parser, or suitable locks were acquired by + * plancache.c. + * + * Because the rewriter and planner tend to scribble on the input, we make + * a preliminary copy of the source querytree. This prevents problems in + * the case that CTAS is in a portal or plpgsql function and is executed + * repeatedly. (See also the same hack in EXPLAIN and PREPARE.) + */ + rewritten = QueryRewrite((Query *) copyObject(query)); + + /* SELECT should never rewrite to more or less than one SELECT query */ + if (list_length(rewritten) != 1) + elog(ERROR, "unexpected rewrite result for CREATE TABLE AS SELECT"); + query = (Query *) linitial(rewritten); + + Assert(query->commandType == CMD_SELECT); + + /* Save the query after rewrite but before planning. */ + ((DR_intorel *) dest)->query = query; + ((DR_intorel *) dest)->into = into; + + if (into->relkind == RELKIND_MATVIEW) + { + /* + * A materialized view would either need to save parameters for use in + * maintaining or loading the data or prohibit them entirely. The + * latter seems safer and more sane. + */ + if (params != NULL && params->numParams > 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("materialized views may not be defined using bound parameters"))); + } + + return query; + } + + /* * ExecCreateTableAs -- execute a CREATE TABLE AS command */ void *************** *** 66,72 **** ExecCreateTableAs(CreateTableAsStmt *stmt, const char *queryString, Query *query = (Query *) stmt->query; IntoClause *into = stmt->into; DestReceiver *dest; - List *rewritten; PlannedStmt *plan; QueryDesc *queryDesc; ScanDirection dir; --- 126,131 ---- *************** *** 90,115 **** ExecCreateTableAs(CreateTableAsStmt *stmt, const char *queryString, return; } - Assert(query->commandType == CMD_SELECT); ! /* ! * Parse analysis was done already, but we still have to run the rule ! * rewriter. We do not do AcquireRewriteLocks: we assume the query either ! * came straight from the parser, or suitable locks were acquired by ! * plancache.c. ! * ! * Because the rewriter and planner tend to scribble on the input, we make ! * a preliminary copy of the source querytree. This prevents problems in ! * the case that CTAS is in a portal or plpgsql function and is executed ! * repeatedly. (See also the same hack in EXPLAIN and PREPARE.) ! */ ! rewritten = QueryRewrite((Query *) copyObject(stmt->query)); ! ! /* SELECT should never rewrite to more or less than one SELECT query */ ! if (list_length(rewritten) != 1) ! elog(ERROR, "unexpected rewrite result for CREATE TABLE AS SELECT"); ! query = (Query *) linitial(rewritten); ! Assert(query->commandType == CMD_SELECT); /* plan the query */ plan = pg_plan_query(query, 0, params); --- 149,156 ---- return; } ! query = SetupForCreateTableAs(query, into, queryString, params, dest); /* plan the query */ plan = pg_plan_query(query, 0, params); *************** *** 299,310 **** intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) if (lc != NULL) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), ! errmsg("CREATE TABLE AS specifies too many column names"))); /* * Actually create the target table */ ! intoRelationId = DefineRelation(create, RELKIND_RELATION, InvalidOid); /* * If necessary, create a TOAST table for the target table. Note that --- 340,377 ---- if (lc != NULL) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), ! errmsg("too many column names are specified"))); ! ! /* ! * Enforce validations needed for materialized views only. ! */ ! if (into->relkind == RELKIND_MATVIEW) ! { ! /* ! * Prohibit a data-modifying CTE in the query used to create a ! * materialized view. It's not sufficiently clear what the user would ! * want to happen if the MV is refreshed or incrementally maintained. ! */ ! if (myState->query->hasModifyingCTE) ! ereport(ERROR, ! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("materialized views must not use data-modifying statements in WITH"))); ! ! /* ! * Check whether any temporary database objects are used in the ! * creation query. It would be hard to refresh data or incrementally ! * maintain it if a source disappeared. ! */ ! if (isQueryUsingTempRelation(myState->query)) ! ereport(ERROR, ! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("materialized views must not use temporary tables or views"))); ! } /* * Actually create the target table */ ! intoRelationId = DefineRelation(create, into->relkind, InvalidOid); /* * If necessary, create a TOAST table for the target table. Note that *************** *** 330,335 **** intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) --- 397,415 ---- intoRelationDesc = heap_open(intoRelationId, AccessExclusiveLock); /* + * Create the "view" part of a materialized view. + */ + if (into->relkind == RELKIND_MATVIEW) + { + StoreViewQuery(intoRelationId, myState->query, false); + if (into->skipData) + { + CommandCounterIncrement(); + SetRelationIsValid(intoRelationId, false); + } + } + + /* * Check INSERT permission on the constructed table. * * XXX: It would arguably make sense to skip this check if into->skipData *************** *** 338,344 **** intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) rte = makeNode(RangeTblEntry); rte->rtekind = RTE_RELATION; rte->relid = intoRelationId; ! rte->relkind = RELKIND_RELATION; rte->requiredPerms = ACL_INSERT; for (attnum = 1; attnum <= intoRelationDesc->rd_att->natts; attnum++) --- 418,425 ---- rte = makeNode(RangeTblEntry); rte->rtekind = RTE_RELATION; rte->relid = intoRelationId; ! rte->relkind = into->relkind; ! rte->isResultRel = true; rte->requiredPerms = ACL_INSERT; for (attnum = 1; attnum <= intoRelationDesc->rd_att->natts; attnum++) *** a/src/backend/commands/event_trigger.c --- b/src/backend/commands/event_trigger.c *************** *** 67,72 **** static event_trigger_support_data event_trigger_support[] = { --- 67,73 ---- { "FUNCTION", true }, { "INDEX", true }, { "LANGUAGE", true }, + { "MATERIALIZED VIEW", true }, { "OPERATOR", true }, { "OPERATOR CLASS", true }, { "OPERATOR FAMILY", true }, *************** *** 216,221 **** check_ddl_tag(const char *tag) --- 217,223 ---- */ if (pg_strcasecmp(tag, "CREATE TABLE AS") == 0 || pg_strcasecmp(tag, "SELECT INTO") == 0 || + pg_strcasecmp(tag, "REFRESH MATERIALIZED VIEW") == 0 || pg_strcasecmp(tag, "ALTER DEFAULT PRIVILEGES") == 0 || pg_strcasecmp(tag, "ALTER LARGE OBJECT") == 0) return EVENT_TRIGGER_COMMAND_TAG_OK; *** a/src/backend/commands/explain.c --- b/src/backend/commands/explain.c *************** *** 47,53 **** explain_get_index_name_hook_type explain_get_index_name_hook = NULL; #define X_NOWHITESPACE 4 static void ExplainOneQuery(Query *query, IntoClause *into, ExplainState *es, ! const char *queryString, ParamListInfo params); static void report_triggers(ResultRelInfo *rInfo, bool show_relname, ExplainState *es); static double elapsed_time(instr_time *starttime); --- 47,53 ---- #define X_NOWHITESPACE 4 static void ExplainOneQuery(Query *query, IntoClause *into, ExplainState *es, ! const char *queryString, DestReceiver *dest, ParamListInfo params); static void report_triggers(ResultRelInfo *rInfo, bool show_relname, ExplainState *es); static double elapsed_time(instr_time *starttime); *************** *** 218,224 **** ExplainQuery(ExplainStmt *stmt, const char *queryString, foreach(l, rewritten) { ExplainOneQuery((Query *) lfirst(l), NULL, &es, ! queryString, params); /* Separate plans with an appropriate separator */ if (lnext(l) != NULL) --- 218,224 ---- foreach(l, rewritten) { ExplainOneQuery((Query *) lfirst(l), NULL, &es, ! queryString, None_Receiver, params); /* Separate plans with an appropriate separator */ if (lnext(l) != NULL) *************** *** 299,310 **** ExplainResultDesc(ExplainStmt *stmt) */ static void ExplainOneQuery(Query *query, IntoClause *into, ExplainState *es, ! const char *queryString, ParamListInfo params) { /* planner will not cope with utility statements */ if (query->commandType == CMD_UTILITY) { ! ExplainOneUtility(query->utilityStmt, into, es, queryString, params); return; } --- 299,312 ---- */ static void ExplainOneQuery(Query *query, IntoClause *into, ExplainState *es, ! const char *queryString, DestReceiver *dest, ! ParamListInfo params) { /* planner will not cope with utility statements */ if (query->commandType == CMD_UTILITY) { ! ExplainOneUtility(query->utilityStmt, into, es, ! queryString, dest, params); return; } *************** *** 319,325 **** ExplainOneQuery(Query *query, IntoClause *into, ExplainState *es, plan = pg_plan_query(query, 0, params); /* run it (if needed) and produce output */ ! ExplainOnePlan(plan, into, es, queryString, params); } } --- 321,327 ---- plan = pg_plan_query(query, 0, params); /* run it (if needed) and produce output */ ! ExplainOnePlan(plan, into, es, queryString, dest, params); } } *************** *** 336,342 **** ExplainOneQuery(Query *query, IntoClause *into, ExplainState *es, */ void ExplainOneUtility(Node *utilityStmt, IntoClause *into, ExplainState *es, ! const char *queryString, ParamListInfo params) { if (utilityStmt == NULL) return; --- 338,345 ---- */ void ExplainOneUtility(Node *utilityStmt, IntoClause *into, ExplainState *es, ! const char *queryString, DestReceiver *dest, ! ParamListInfo params) { if (utilityStmt == NULL) return; *************** *** 349,361 **** ExplainOneUtility(Node *utilityStmt, IntoClause *into, ExplainState *es, * contained parsetree another time, but let's be safe. */ CreateTableAsStmt *ctas = (CreateTableAsStmt *) utilityStmt; ! List *rewritten; Assert(IsA(ctas->query, Query)); ! rewritten = QueryRewrite((Query *) copyObject(ctas->query)); ! Assert(list_length(rewritten) == 1); ! ExplainOneQuery((Query *) linitial(rewritten), ctas->into, es, ! queryString, params); } else if (IsA(utilityStmt, ExecuteStmt)) ExplainExecuteQuery((ExecuteStmt *) utilityStmt, into, es, --- 352,366 ---- * contained parsetree another time, but let's be safe. */ CreateTableAsStmt *ctas = (CreateTableAsStmt *) utilityStmt; ! Query *query = (Query *) ctas->query; ! ! dest = CreateIntoRelDestReceiver(into); Assert(IsA(ctas->query, Query)); ! ! query = SetupForCreateTableAs(query, ctas->into, queryString, params, dest); ! ! ExplainOneQuery(query, ctas->into, es, queryString, dest, params); } else if (IsA(utilityStmt, ExecuteStmt)) ExplainExecuteQuery((ExecuteStmt *) utilityStmt, into, es, *************** *** 396,404 **** ExplainOneUtility(Node *utilityStmt, IntoClause *into, ExplainState *es, */ void ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, ! const char *queryString, ParamListInfo params) { - DestReceiver *dest; QueryDesc *queryDesc; instr_time starttime; double totaltime = 0; --- 401,408 ---- */ void ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, ! const char *queryString, DestReceiver *dest, ParamListInfo params) { QueryDesc *queryDesc; instr_time starttime; double totaltime = 0; *************** *** 422,436 **** ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, PushCopiedSnapshot(GetActiveSnapshot()); UpdateActiveSnapshotCommandId(); - /* - * Normally we discard the query's output, but if explaining CREATE TABLE - * AS, we'd better use the appropriate tuple receiver. - */ - if (into) - dest = CreateIntoRelDestReceiver(into); - else - dest = None_Receiver; - /* Create a QueryDesc for the query */ queryDesc = CreateQueryDesc(plannedstmt, queryString, GetActiveSnapshot(), InvalidSnapshot, --- 426,431 ---- *** a/src/backend/commands/indexcmds.c --- b/src/backend/commands/indexcmds.c *************** *** 355,361 **** DefineIndex(IndexStmt *stmt, relationId = RelationGetRelid(rel); namespaceId = RelationGetNamespace(rel); ! if (rel->rd_rel->relkind != RELKIND_RELATION) { if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) --- 355,362 ---- relationId = RelationGetRelid(rel); namespaceId = RelationGetNamespace(rel); ! if (rel->rd_rel->relkind != RELKIND_RELATION && ! rel->rd_rel->relkind != RELKIND_MATVIEW) { if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) *************** *** 1835,1841 **** ReindexDatabase(const char *databaseName, bool do_system, bool do_user) { Form_pg_class classtuple = (Form_pg_class) GETSTRUCT(tuple); ! if (classtuple->relkind != RELKIND_RELATION) continue; /* Skip temp tables of other backends; we can't reindex them at all */ --- 1836,1843 ---- { Form_pg_class classtuple = (Form_pg_class) GETSTRUCT(tuple); ! if (classtuple->relkind != RELKIND_RELATION && ! classtuple->relkind != RELKIND_MATVIEW) continue; /* Skip temp tables of other backends; we can't reindex them at all */ *** /dev/null --- b/src/backend/commands/matview.c *************** *** 0 **** --- 1,322 ---- + /*------------------------------------------------------------------------- + * + * matview.c + * materialized view support + * + * Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * + * IDENTIFICATION + * src/backend/commands/matview.c + * + *------------------------------------------------------------------------- + */ + #include "postgres.h" + + #include "access/relscan.h" + #include "access/xact.h" + #include "catalog/catalog.h" + #include "catalog/namespace.h" + #include "commands/cluster.h" + #include "commands/matview.h" + #include "commands/tablecmds.h" + #include "executor/executor.h" + #include "miscadmin.h" + #include "storage/lmgr.h" + #include "storage/smgr.h" + #include "tcop/tcopprot.h" + #include "utils/snapmgr.h" + + + typedef struct + { + DestReceiver pub; /* publicly-known function pointers */ + Oid transientoid; /* OID of new heap into which to store */ + /* These fields are filled by transientrel_startup: */ + Relation transientrel; /* relation to write to */ + CommandId output_cid; /* cmin to insert in output tuples */ + int hi_options; /* heap_insert performance options */ + BulkInsertState bistate; /* bulk insert state */ + } DR_transientrel; + + static void transientrel_startup(DestReceiver *self, int operation, TupleDesc typeinfo); + static void transientrel_receive(TupleTableSlot *slot, DestReceiver *self); + static void transientrel_shutdown(DestReceiver *self); + static void transientrel_destroy(DestReceiver *self); + static void refresh_matview(Oid matviewOid, Oid tableSpace, bool isWithOids, + Query *dataQuery, const char *queryString); + + /* + * ExecRefreshMatView -- execute a REFRESH MATERIALIZED VIEW command + */ + void + ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString, + ParamListInfo params, char *completionTag) + { + Oid matviewOid; + Relation matviewRel; + RewriteRule *rule; + List *actions; + Query *dataQuery; + Oid tableSpace; + bool isWithOids; + + /* + * Get a lock until end of transaction. + */ + matviewOid = RangeVarGetRelidExtended(stmt->relation, + AccessExclusiveLock, false, false, + RangeVarCallbackOwnsTable, NULL); + matviewRel = heap_open(matviewOid, NoLock); + + /* Make sure it is a materialized view. */ + if (matviewRel->rd_rel->relkind != RELKIND_MATVIEW) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("\"%s\" is not a materialized view", + RelationGetRelationName(matviewRel)))); + + /* + * We're not using materialized views in the system catalogs. + */ + Assert(!IsSystemRelation(matviewRel)); + + /* + * Check that everything is correct for a refresh. Problems at this point + * are internal errors, so elog is sufficient. + */ + if (matviewRel->rd_rel->relhasrules == false || + matviewRel->rd_rules->numLocks < 1) + elog(ERROR, + "materialized view \"%s\" is missing rewrite information", + RelationGetRelationName(matviewRel)); + + if (matviewRel->rd_rules->numLocks > 1) + elog(ERROR, + "materialized view \"%s\" has too many rules", + RelationGetRelationName(matviewRel)); + + rule = matviewRel->rd_rules->rules[0]; + if (rule->event != CMD_SELECT || !(rule->isInstead)) + elog(ERROR, + "the rule for materialized view \"%s\" is not a SELECT INSTEAD OF rule", + RelationGetRelationName(matviewRel)); + + actions = rule->actions; + if (list_length(actions) != 1) + elog(ERROR, + "the rule for materialized view \"%s\" is not a single action", + RelationGetRelationName(matviewRel)); + + /* + * The stored query was rewritten at the time of the MV definition, but + * has not been scribbled on by the planner. + */ + dataQuery = (Query *) linitial(actions); + Assert(IsA(dataQuery, Query)); + + /* + * Check for active uses of the relation in the current transaction, such + * as open scans. + * + * NB: We count on this to protect us against problems with refreshing the + * data using HEAP_INSERT_FROZEN. + */ + CheckTableNotInUse(matviewRel, "REFRESH MATERIALIZED VIEW"); + + tableSpace = matviewRel->rd_rel->reltablespace; + isWithOids = matviewRel->rd_rel->relhasoids; + + heap_close(matviewRel, NoLock); + + refresh_matview(matviewOid, tableSpace, isWithOids, dataQuery, queryString); + } + + /* + * refresh_matview + * + * This refreshes the materialized view by creating a new table and swapping + * the relfilenodes of the new table and the old materialized view, so the OID + * of the original materialized view is preserved. Thus we do not lose GRANT + * nor references to this materialized view. + * + * Indexes are rebuilt too, via REINDEX. Since we are effectively bulk-loading + * the new heap, it's better to create the indexes afterwards than to fill them + * incrementally while we load. + * + * If the materialized view was flagged with relisvalid == false, success of + * this command will change it to true. + */ + static void + refresh_matview(Oid matviewOid, Oid tableSpace, bool isWithOids, + Query *dataQuery, const char *queryString) + { + Oid OIDNewHeap; + PlannedStmt *plan; + DestReceiver *dest; + QueryDesc *queryDesc; + List *rtable; + + /* Check for user-requested abort. */ + CHECK_FOR_INTERRUPTS(); + + /* + * Kludge here to allow refresh of a materialized view which is invalid + * (that is, it was created WITH NO DATA or was TRUNCATED). We flag the + * first two RangeTblEntry list elements, which were added to the front + * of the rewritten Query to keep the rules system happy, with the + * isResultRel flag to indicate that it is OK if they are flagged as + * invalid. + */ + rtable = dataQuery->rtable; + ((RangeTblEntry *) linitial(rtable))->isResultRel = true; + ((RangeTblEntry *) lsecond(rtable))->isResultRel = true; + + /* Plan the query which will generate data for the refresh. */ + plan = pg_plan_query(dataQuery, 0, NULL); + + /* Create the transient table that will receive the regenerated data. */ + OIDNewHeap = make_new_heap(matviewOid, tableSpace); + dest = CreateTransientRelDestReceiver(OIDNewHeap); + + /* + * Use a snapshot with an updated command ID to ensure this query sees + * results of any previously executed queries. (This could only matter if + * the planner executed an allegedly-stable function that changed the + * database contents, but let's do it anyway to be safe.) + */ + PushCopiedSnapshot(GetActiveSnapshot()); + UpdateActiveSnapshotCommandId(); + + /* Create a QueryDesc, redirecting output to our tuple receiver */ + queryDesc = CreateQueryDesc(plan, queryString, + GetActiveSnapshot(), InvalidSnapshot, + dest, NULL, 0); + + /* call ExecutorStart to prepare the plan for execution */ + ExecutorStart(queryDesc, + isWithOids ? EXEC_FLAG_WITH_OIDS : EXEC_FLAG_WITHOUT_OIDS); + + /* run the plan */ + ExecutorRun(queryDesc, ForwardScanDirection, 0L); + + /* and clean up */ + ExecutorFinish(queryDesc); + ExecutorEnd(queryDesc); + + FreeQueryDesc(queryDesc); + + PopActiveSnapshot(); + + /* + * Swap the physical files of the target and transient tables, then + * rebuild the target's indexes and throw away the transient table. + */ + finish_heap_swap(matviewOid, OIDNewHeap, false, false, false, RecentXmin); + + SetRelationIsValid(matviewOid, true); + } + + DestReceiver * + CreateTransientRelDestReceiver(Oid transientoid) + { + DR_transientrel *self = (DR_transientrel *) palloc0(sizeof(DR_transientrel)); + + self->pub.receiveSlot = transientrel_receive; + self->pub.rStartup = transientrel_startup; + self->pub.rShutdown = transientrel_shutdown; + self->pub.rDestroy = transientrel_destroy; + self->pub.mydest = DestTransientRel; + self->transientoid = transientoid; + + return (DestReceiver *) self; + } + + /* + * transientrel_startup --- executor startup + */ + static void + transientrel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) + { + DR_transientrel *myState = (DR_transientrel *) self; + Relation transientrel; + + transientrel = heap_open(myState->transientoid, NoLock); + + /* + * Fill private fields of myState for use by later routines + */ + myState->transientrel = transientrel; + myState->output_cid = GetCurrentCommandId(true); + + /* + * We can skip WAL-logging the insertions, unless PITR or streaming + * replication is in use. We can skip the FSM in any case. + */ + myState->hi_options = HEAP_INSERT_SKIP_FSM | HEAP_INSERT_FROZEN; + if (!XLogIsNeeded()) + myState->hi_options |= HEAP_INSERT_SKIP_WAL; + myState->bistate = GetBulkInsertState(); + + /* Not using WAL requires smgr_targblock be initially invalid */ + Assert(RelationGetTargetBlock(transientrel) == InvalidBlockNumber); + } + + /* + * transientrel_receive --- receive one tuple + */ + static void + transientrel_receive(TupleTableSlot *slot, DestReceiver *self) + { + DR_transientrel *myState = (DR_transientrel *) self; + HeapTuple tuple; + + /* + * get the heap tuple out of the tuple table slot, making sure we have a + * writable copy + */ + tuple = ExecMaterializeSlot(slot); + + /* + * force assignment of new OID (see comments in ExecInsert) + */ + if (myState->transientrel->rd_rel->relhasoids) + HeapTupleSetOid(tuple, InvalidOid); + + heap_insert(myState->transientrel, + tuple, + myState->output_cid, + myState->hi_options, + myState->bistate); + + /* We know this is a newly created relation, so there are no indexes */ + } + + /* + * transientrel_shutdown --- executor end + */ + static void + transientrel_shutdown(DestReceiver *self) + { + DR_transientrel *myState = (DR_transientrel *) self; + + FreeBulkInsertState(myState->bistate); + + /* If we skipped using WAL, must heap_sync before commit */ + if (myState->hi_options & HEAP_INSERT_SKIP_WAL) + heap_sync(myState->transientrel); + + /* close transientrel, but keep lock until commit */ + heap_close(myState->transientrel, NoLock); + myState->transientrel = NULL; + } + + /* + * transientrel_destroy --- release DestReceiver object + */ + static void + transientrel_destroy(DestReceiver *self) + { + pfree(self); + } *** a/src/backend/commands/prepare.c --- b/src/backend/commands/prepare.c *************** *** 665,673 **** ExplainExecuteQuery(ExecuteStmt *execstmt, IntoClause *into, ExplainState *es, PlannedStmt *pstmt = (PlannedStmt *) lfirst(p); if (IsA(pstmt, PlannedStmt)) ! ExplainOnePlan(pstmt, into, es, query_string, paramLI); else ! ExplainOneUtility((Node *) pstmt, into, es, query_string, paramLI); /* No need for CommandCounterIncrement, as ExplainOnePlan did it */ --- 665,673 ---- PlannedStmt *pstmt = (PlannedStmt *) lfirst(p); if (IsA(pstmt, PlannedStmt)) ! ExplainOnePlan(pstmt, into, es, query_string, None_Receiver, paramLI); else ! ExplainOneUtility((Node *) pstmt, into, es, query_string, None_Receiver, paramLI); /* No need for CommandCounterIncrement, as ExplainOnePlan did it */ *** a/src/backend/commands/seclabel.c --- b/src/backend/commands/seclabel.c *************** *** 101,111 **** ExecSecLabelStmt(SecLabelStmt *stmt) /* * Allow security labels only on columns of tables, views, ! * composite types, and foreign tables (which are the only ! * relkinds for which pg_dump will dump labels). */ if (relation->rd_rel->relkind != RELKIND_RELATION && relation->rd_rel->relkind != RELKIND_VIEW && relation->rd_rel->relkind != RELKIND_COMPOSITE_TYPE && relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, --- 101,112 ---- /* * Allow security labels only on columns of tables, views, ! * materialized views, composite types, and foreign tables (which ! * are the only relkinds for which pg_dump will dump labels). */ if (relation->rd_rel->relkind != RELKIND_RELATION && relation->rd_rel->relkind != RELKIND_VIEW && + relation->rd_rel->relkind != RELKIND_MATVIEW && relation->rd_rel->relkind != RELKIND_COMPOSITE_TYPE && relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, *** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *************** *** 215,220 **** static const struct dropmsgstrings dropmsgstringarray[] = { --- 215,226 ---- gettext_noop("view \"%s\" does not exist, skipping"), gettext_noop("\"%s\" is not a view"), gettext_noop("Use DROP VIEW to remove a view.")}, + {RELKIND_MATVIEW, + ERRCODE_UNDEFINED_TABLE, + gettext_noop("materialized view \"%s\" does not exist"), + gettext_noop("materialized view \"%s\" does not exist, skipping"), + gettext_noop("\"%s\" is not a materialized view"), + gettext_noop("Use DROP MATERIALIZED VIEW to remove a materialized view.")}, {RELKIND_INDEX, ERRCODE_UNDEFINED_OBJECT, gettext_noop("index \"%s\" does not exist"), *************** *** 246,254 **** struct DropRelationCallbackState /* Alter table target-type flags for ATSimplePermissions */ #define ATT_TABLE 0x0001 #define ATT_VIEW 0x0002 ! #define ATT_INDEX 0x0004 ! #define ATT_COMPOSITE_TYPE 0x0008 ! #define ATT_FOREIGN_TABLE 0x0010 static void truncate_check_rel(Relation rel); static List *MergeAttributes(List *schema, List *supers, char relpersistence, --- 252,261 ---- /* Alter table target-type flags for ATSimplePermissions */ #define ATT_TABLE 0x0001 #define ATT_VIEW 0x0002 ! #define ATT_MATVIEW 0x0004 ! #define ATT_INDEX 0x0008 ! #define ATT_COMPOSITE_TYPE 0x0010 ! #define ATT_FOREIGN_TABLE 0x0020 static void truncate_check_rel(Relation rel); static List *MergeAttributes(List *schema, List *supers, char relpersistence, *************** *** 397,402 **** static void RangeVarCallbackForDropRelation(const RangeVar *rel, Oid relOid, --- 404,411 ---- static void RangeVarCallbackForAlterRelation(const RangeVar *rv, Oid relid, Oid oldrelid, void *arg); + static bool isQueryUsingTempRelation_walker(Node *node, void *context); + /* ---------------------------------------------------------------- * DefineRelation *************** *** 733,739 **** DropErrorMsgWrongType(const char *relname, char wrongkind, char rightkind) /* * RemoveRelations * Implements DROP TABLE, DROP INDEX, DROP SEQUENCE, DROP VIEW, ! * DROP FOREIGN TABLE */ void RemoveRelations(DropStmt *drop) --- 742,748 ---- /* * RemoveRelations * Implements DROP TABLE, DROP INDEX, DROP SEQUENCE, DROP VIEW, ! * DROP MATERIALIZED VIEW, DROP FOREIGN TABLE */ void RemoveRelations(DropStmt *drop) *************** *** 785,790 **** RemoveRelations(DropStmt *drop) --- 794,803 ---- relkind = RELKIND_VIEW; break; + case OBJECT_MATVIEW: + relkind = RELKIND_MATVIEW; + break; + case OBJECT_FOREIGN_TABLE: relkind = RELKIND_FOREIGN_TABLE; break; *************** *** 1153,1158 **** ExecuteTruncate(TruncateStmt *stmt) --- 1166,1178 ---- heap_relid = RelationGetRelid(rel); toast_relid = rel->rd_rel->reltoastrelid; + /* This makes a materialized view invalid for use. */ + if (rel->rd_rel->relkind == RELKIND_MATVIEW && + rel->rd_rel->relisvalid) + { + SetRelationIsValid(heap_relid, false); + } + /* * The same for the toast table, if any. */ *************** *** 1216,1226 **** truncate_check_rel(Relation rel) { AclResult aclresult; ! /* Only allow truncate on regular tables */ ! if (rel->rd_rel->relkind != RELKIND_RELATION) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("\"%s\" is not a table", RelationGetRelationName(rel)))); /* Permissions checks */ --- 1236,1247 ---- { AclResult aclresult; ! /* Only allow truncate on regular tables and materialized views. */ ! if (rel->rd_rel->relkind != RELKIND_RELATION && ! rel->rd_rel->relkind != RELKIND_MATVIEW) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("\"%s\" is not a table or materialized view", RelationGetRelationName(rel)))); /* Permissions checks */ *************** *** 2041,2046 **** SetRelationHasSubclass(Oid relationId, bool relhassubclass) --- 2062,2100 ---- } /* + * SetRelationIsValid + * Set the value of the relation's relisvalid field in pg_class. + * + * NOTE: caller must be holding an appropriate lock on the relation. + * ShareUpdateExclusiveLock is sufficient. + */ + void + SetRelationIsValid(Oid relationId, bool relisvalid) + { + Relation relationRelation; + HeapTuple tuple; + Form_pg_class classtuple; + + /* + * Fetch a modifiable copy of the tuple, modify it, update pg_class. + */ + relationRelation = heap_open(RelationRelationId, RowExclusiveLock); + tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relationId)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for relation %u", relationId); + classtuple = (Form_pg_class) GETSTRUCT(tuple); + + if (classtuple->relisvalid != relisvalid) + { + classtuple->relisvalid = relisvalid; + heap_inplace_update(relationRelation, tuple); + } + + heap_freetuple(tuple); + heap_close(relationRelation, RowExclusiveLock); + } + + /* * renameatt_check - basic sanity checks before attribute rename */ static void *************** *** 2062,2073 **** renameatt_check(Oid myrelid, Form_pg_class classform, bool recursing) */ if (relkind != RELKIND_RELATION && relkind != RELKIND_VIEW && relkind != RELKIND_COMPOSITE_TYPE && relkind != RELKIND_INDEX && relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("\"%s\" is not a table, view, composite type, index, or foreign table", NameStr(classform->relname)))); /* --- 2116,2128 ---- */ if (relkind != RELKIND_RELATION && relkind != RELKIND_VIEW && + relkind != RELKIND_MATVIEW && relkind != RELKIND_COMPOSITE_TYPE && relkind != RELKIND_INDEX && relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("\"%s\" is not a table, view, materialized view, composite type, index, or foreign table", NameStr(classform->relname)))); /* *************** *** 2984,2995 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, break; case AT_SetOptions: /* ALTER COLUMN SET ( options ) */ case AT_ResetOptions: /* ALTER COLUMN RESET ( options ) */ ! ATSimplePermissions(rel, ATT_TABLE | ATT_INDEX | ATT_FOREIGN_TABLE); /* This command never recurses */ pass = AT_PASS_MISC; break; case AT_SetStorage: /* ALTER COLUMN SET STORAGE */ ! ATSimplePermissions(rel, ATT_TABLE); ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode); /* No command-specific prep needed */ pass = AT_PASS_MISC; --- 3039,3050 ---- break; case AT_SetOptions: /* ALTER COLUMN SET ( options ) */ case AT_ResetOptions: /* ALTER COLUMN RESET ( options ) */ ! ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW | ATT_INDEX | ATT_FOREIGN_TABLE); /* This command never recurses */ pass = AT_PASS_MISC; break; case AT_SetStorage: /* ALTER COLUMN SET STORAGE */ ! ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW); ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode); /* No command-specific prep needed */ pass = AT_PASS_MISC; *************** *** 3002,3008 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, pass = AT_PASS_DROP; break; case AT_AddIndex: /* ADD INDEX */ ! ATSimplePermissions(rel, ATT_TABLE); /* This command never recurses */ /* No command-specific prep needed */ pass = AT_PASS_ADD_INDEX; --- 3057,3063 ---- pass = AT_PASS_DROP; break; case AT_AddIndex: /* ADD INDEX */ ! ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW); /* This command never recurses */ /* No command-specific prep needed */ pass = AT_PASS_ADD_INDEX; *************** *** 3049,3055 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, break; case AT_ClusterOn: /* CLUSTER ON */ case AT_DropCluster: /* SET WITHOUT CLUSTER */ ! ATSimplePermissions(rel, ATT_TABLE); /* These commands never recurse */ /* No command-specific prep needed */ pass = AT_PASS_MISC; --- 3104,3110 ---- break; case AT_ClusterOn: /* CLUSTER ON */ case AT_DropCluster: /* SET WITHOUT CLUSTER */ ! ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW); /* These commands never recurse */ /* No command-specific prep needed */ pass = AT_PASS_MISC; *************** *** 3076,3082 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, pass = AT_PASS_DROP; break; case AT_SetTableSpace: /* SET TABLESPACE */ ! ATSimplePermissions(rel, ATT_TABLE | ATT_INDEX); /* This command never recurses */ ATPrepSetTableSpace(tab, rel, cmd->name, lockmode); pass = AT_PASS_MISC; /* doesn't actually matter */ --- 3131,3137 ---- pass = AT_PASS_DROP; break; case AT_SetTableSpace: /* SET TABLESPACE */ ! ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW | ATT_INDEX); /* This command never recurses */ ATPrepSetTableSpace(tab, rel, cmd->name, lockmode); pass = AT_PASS_MISC; /* doesn't actually matter */ *************** *** 3084,3090 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, case AT_SetRelOptions: /* SET (...) */ case AT_ResetRelOptions: /* RESET (...) */ case AT_ReplaceRelOptions: /* reset them all, then set just these */ ! ATSimplePermissions(rel, ATT_TABLE | ATT_INDEX | ATT_VIEW); /* This command never recurses */ /* No command-specific prep needed */ pass = AT_PASS_MISC; --- 3139,3145 ---- case AT_SetRelOptions: /* SET (...) */ case AT_ResetRelOptions: /* RESET (...) */ case AT_ReplaceRelOptions: /* reset them all, then set just these */ ! ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_MATVIEW | ATT_INDEX); /* This command never recurses */ /* No command-specific prep needed */ pass = AT_PASS_MISC; *************** *** 3197,3203 **** ATRewriteCatalogs(List **wqueue, LOCKMODE lockmode) { AlteredTableInfo *tab = (AlteredTableInfo *) lfirst(ltab); ! if (tab->relkind == RELKIND_RELATION) AlterTableCreateToastTable(tab->relid, (Datum) 0); } } --- 3252,3259 ---- { AlteredTableInfo *tab = (AlteredTableInfo *) lfirst(ltab); ! if (tab->relkind == RELKIND_RELATION || ! tab->relkind == RELKIND_MATVIEW) AlterTableCreateToastTable(tab->relid, (Datum) 0); } } *************** *** 3929,3934 **** ATSimplePermissions(Relation rel, int allowed_targets) --- 3985,3993 ---- case RELKIND_VIEW: actual_target = ATT_VIEW; break; + case RELKIND_MATVIEW: + actual_target = ATT_MATVIEW; + break; case RELKIND_INDEX: actual_target = ATT_INDEX; break; *************** *** 3975,3992 **** ATWrongRelkindError(Relation rel, int allowed_targets) case ATT_TABLE: msg = _("\"%s\" is not a table"); break; - case ATT_TABLE | ATT_INDEX: - msg = _("\"%s\" is not a table or index"); - break; case ATT_TABLE | ATT_VIEW: msg = _("\"%s\" is not a table or view"); break; case ATT_TABLE | ATT_FOREIGN_TABLE: msg = _("\"%s\" is not a table or foreign table"); break; case ATT_TABLE | ATT_COMPOSITE_TYPE | ATT_FOREIGN_TABLE: msg = _("\"%s\" is not a table, composite type, or foreign table"); break; case ATT_VIEW: msg = _("\"%s\" is not a view"); break; --- 4034,4060 ---- case ATT_TABLE: msg = _("\"%s\" is not a table"); break; case ATT_TABLE | ATT_VIEW: msg = _("\"%s\" is not a table or view"); break; + case ATT_TABLE | ATT_VIEW | ATT_MATVIEW | ATT_INDEX: + msg = _("\"%s\" is not a table, view, materialized view, or index"); + break; + case ATT_TABLE | ATT_MATVIEW: + msg = _("\"%s\" is not a table or materialized view"); + break; + case ATT_TABLE | ATT_MATVIEW | ATT_INDEX: + msg = _("\"%s\" is not a table, materialized view, or index"); + break; case ATT_TABLE | ATT_FOREIGN_TABLE: msg = _("\"%s\" is not a table or foreign table"); break; case ATT_TABLE | ATT_COMPOSITE_TYPE | ATT_FOREIGN_TABLE: msg = _("\"%s\" is not a table, composite type, or foreign table"); break; + case ATT_TABLE | ATT_MATVIEW | ATT_INDEX | ATT_FOREIGN_TABLE: + msg = _("\"%s\" is not a table, materialized view, composite type, or foreign table"); + break; case ATT_VIEW: msg = _("\"%s\" is not a view"); break; *************** *** 4139,4145 **** find_composite_type_dependencies(Oid typeOid, Relation origRelation, rel = relation_open(pg_depend->objid, AccessShareLock); att = rel->rd_att->attrs[pg_depend->objsubid - 1]; ! if (rel->rd_rel->relkind == RELKIND_RELATION) { if (origTypeName) ereport(ERROR, --- 4207,4214 ---- rel = relation_open(pg_depend->objid, AccessShareLock); att = rel->rd_att->attrs[pg_depend->objsubid - 1]; ! if (rel->rd_rel->relkind == RELKIND_RELATION || ! rel->rd_rel->relkind == RELKIND_MATVIEW) { if (origTypeName) ereport(ERROR, *************** *** 4967,4977 **** ATPrepSetStatistics(Relation rel, const char *colName, Node *newValue, LOCKMODE * allowSystemTableMods to be turned on. */ if (rel->rd_rel->relkind != RELKIND_RELATION && rel->rd_rel->relkind != RELKIND_INDEX && rel->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("\"%s\" is not a table, index, or foreign table", RelationGetRelationName(rel)))); /* Permissions checks */ --- 5036,5047 ---- * allowSystemTableMods to be turned on. */ if (rel->rd_rel->relkind != RELKIND_RELATION && + rel->rd_rel->relkind != RELKIND_MATVIEW && rel->rd_rel->relkind != RELKIND_INDEX && rel->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("\"%s\" is not a table, materialized view, index, or foreign table", RelationGetRelationName(rel)))); /* Permissions checks */ *************** *** 8078,8083 **** ATExecChangeOwner(Oid relationOid, Oid newOwnerId, bool recursing, LOCKMODE lock --- 8148,8154 ---- { case RELKIND_RELATION: case RELKIND_VIEW: + case RELKIND_MATVIEW: case RELKIND_FOREIGN_TABLE: /* ok to change owner */ break; *************** *** 8234,8244 **** ATExecChangeOwner(Oid relationOid, Oid newOwnerId, bool recursing, LOCKMODE lock tuple_class->relkind == RELKIND_COMPOSITE_TYPE); /* ! * If we are operating on a table, also change the ownership of any ! * indexes and sequences that belong to the table, as well as the ! * table's toast table (if it has one) */ if (tuple_class->relkind == RELKIND_RELATION || tuple_class->relkind == RELKIND_TOASTVALUE) { List *index_oid_list; --- 8305,8316 ---- tuple_class->relkind == RELKIND_COMPOSITE_TYPE); /* ! * If we are operating on a table or materialized view, also change ! * the ownership of any indexes and sequences that belong to the ! * relation, as well as its toast table (if it has one). */ if (tuple_class->relkind == RELKIND_RELATION || + tuple_class->relkind == RELKIND_MATVIEW || tuple_class->relkind == RELKIND_TOASTVALUE) { List *index_oid_list; *************** *** 8254,8260 **** ATExecChangeOwner(Oid relationOid, Oid newOwnerId, bool recursing, LOCKMODE lock list_free(index_oid_list); } ! if (tuple_class->relkind == RELKIND_RELATION) { /* If it has a toast table, recurse to change its ownership */ if (tuple_class->reltoastrelid != InvalidOid) --- 8326,8333 ---- list_free(index_oid_list); } ! if (tuple_class->relkind == RELKIND_RELATION || ! tuple_class->relkind == RELKIND_MATVIEW) { /* If it has a toast table, recurse to change its ownership */ if (tuple_class->reltoastrelid != InvalidOid) *************** *** 8524,8529 **** ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation, --- 8597,8603 ---- case RELKIND_RELATION: case RELKIND_TOASTVALUE: case RELKIND_VIEW: + case RELKIND_MATVIEW: (void) heap_reloptions(rel->rd_rel->relkind, newOptions, true); break; case RELKIND_INDEX: *************** *** 8532,8538 **** ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation, default: ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("\"%s\" is not a table, index, or TOAST table", RelationGetRelationName(rel)))); break; } --- 8606,8612 ---- default: ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("\"%s\" is not a table, view, materialized view, index, or TOAST table", RelationGetRelationName(rel)))); break; } *************** *** 9815,9822 **** AlterTableNamespace(AlterObjectSchemaStmt *stmt) } /* ! * The guts of relocating a table to another namespace: besides moving ! * the table itself, its dependent objects are relocated to the new schema. */ void AlterTableNamespaceInternal(Relation rel, Oid oldNspOid, Oid nspOid, --- 9889,9897 ---- } /* ! * The guts of relocating a table or materialized view to another namespace: ! * besides moving the relation itself, its dependent objects are relocated to ! * the new schema. */ void AlterTableNamespaceInternal(Relation rel, Oid oldNspOid, Oid nspOid, *************** *** 9837,9843 **** AlterTableNamespaceInternal(Relation rel, Oid oldNspOid, Oid nspOid, nspOid, false, false, objsMoved); /* Fix other dependent stuff */ ! if (rel->rd_rel->relkind == RELKIND_RELATION) { AlterIndexNamespaces(classRel, rel, oldNspOid, nspOid, objsMoved); AlterSeqNamespaces(classRel, rel, oldNspOid, nspOid, --- 9912,9919 ---- nspOid, false, false, objsMoved); /* Fix other dependent stuff */ ! if (rel->rd_rel->relkind == RELKIND_RELATION || ! rel->rd_rel->relkind == RELKIND_MATVIEW) { AlterIndexNamespaces(classRel, rel, oldNspOid, nspOid, objsMoved); AlterSeqNamespaces(classRel, rel, oldNspOid, nspOid, *************** *** 10242,10251 **** AtEOSubXact_on_commit_actions(bool isCommit, SubTransactionId mySubid, /* * This is intended as a callback for RangeVarGetRelidExtended(). It allows ! * the table to be locked only if (1) it's a plain table or TOAST table and ! * (2) the current user is the owner (or the superuser). This meets the ! * permission-checking needs of both CLUSTER and REINDEX TABLE; we expose it ! * here so that it can be used by both. */ void RangeVarCallbackOwnsTable(const RangeVar *relation, --- 10318,10328 ---- /* * This is intended as a callback for RangeVarGetRelidExtended(). It allows ! * the relation to be locked only if (1) it's a plain table, materialized ! * view, or TOAST table and (2) the current user is the owner (or the ! * superuser). This meets the permission-checking needs of CLUSTER, REINDEX ! * TABLE, and REFRESH MATERIALIZED VIEW; we expose it here so that it can be ! * used by all. */ void RangeVarCallbackOwnsTable(const RangeVar *relation, *************** *** 10265,10274 **** RangeVarCallbackOwnsTable(const RangeVar *relation, relkind = get_rel_relkind(relId); if (!relkind) return; ! if (relkind != RELKIND_RELATION && relkind != RELKIND_TOASTVALUE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("\"%s\" is not a table", relation->relname))); /* Check permissions */ if (!pg_class_ownercheck(relId, GetUserId())) --- 10342,10352 ---- relkind = get_rel_relkind(relId); if (!relkind) return; ! if (relkind != RELKIND_RELATION && relkind != RELKIND_TOASTVALUE && ! relkind != RELKIND_MATVIEW) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("\"%s\" is not a table or materialized view", relation->relname))); /* Check permissions */ if (!pg_class_ownercheck(relId, GetUserId())) *************** *** 10350,10355 **** RangeVarCallbackForAlterRelation(const RangeVar *rv, Oid relid, Oid oldrelid, --- 10428,10438 ---- (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("\"%s\" is not a view", rv->relname))); + if (reltype == OBJECT_MATVIEW && relkind != RELKIND_MATVIEW) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a materialized view", rv->relname))); + if (reltype == OBJECT_FOREIGN_TABLE && relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), *************** *** 10386,10394 **** RangeVarCallbackForAlterRelation(const RangeVar *rv, Oid relid, Oid oldrelid, * Don't allow ALTER TABLE .. SET SCHEMA on relations that can't be moved * to a different schema, such as indexes and TOAST tables. */ ! if (IsA(stmt, AlterObjectSchemaStmt) &&relkind != RELKIND_RELATION ! && relkind != RELKIND_VIEW && relkind != RELKIND_SEQUENCE ! && relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("\"%s\" is not a table, view, sequence, or foreign table", --- 10469,10477 ---- * Don't allow ALTER TABLE .. SET SCHEMA on relations that can't be moved * to a different schema, such as indexes and TOAST tables. */ ! if (IsA(stmt, AlterObjectSchemaStmt) && relkind != RELKIND_RELATION ! && relkind != RELKIND_VIEW && relkind != RELKIND_MATVIEW ! && relkind != RELKIND_SEQUENCE && relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("\"%s\" is not a table, view, sequence, or foreign table", *************** *** 10396,10398 **** RangeVarCallbackForAlterRelation(const RangeVar *rv, Oid relid, Oid oldrelid, --- 10479,10529 ---- ReleaseSysCache(tuple); } + + /* + * Returns true iff any relation underlying this query is a temporary database + * object (table, view, or materialized view). + * + */ + bool + isQueryUsingTempRelation(Query *query) + { + return isQueryUsingTempRelation_walker((Node *) query, NULL); + } + + static bool + isQueryUsingTempRelation_walker(Node *node, void *context) + { + if (node == NULL) + return false; + + if (IsA(node, Query)) + { + Query *query = (Query *) node; + ListCell *rtable; + + foreach(rtable, query->rtable) + { + RangeTblEntry *rte = lfirst(rtable); + + if (rte->rtekind == RTE_RELATION) + { + Relation rel = heap_open(rte->relid, AccessShareLock); + char relpersistence = rel->rd_rel->relpersistence; + + heap_close(rel, AccessShareLock); + if (relpersistence == RELPERSISTENCE_TEMP) + return true; + } + } + + return query_tree_walker(query, + isQueryUsingTempRelation_walker, + context, + QTW_IGNORE_JOINALIASES); + } + + return expression_tree_walker(node, + isQueryUsingTempRelation_walker, + context); + } *** a/src/backend/commands/typecmds.c --- b/src/backend/commands/typecmds.c *************** *** 2781,2787 **** get_rels_with_domain(Oid domainOid, LOCKMODE lockmode) format_type_be(domainOid)); /* Otherwise we can ignore views, composite types, etc */ ! if (rel->rd_rel->relkind != RELKIND_RELATION) { relation_close(rel, lockmode); continue; --- 2781,2788 ---- format_type_be(domainOid)); /* Otherwise we can ignore views, composite types, etc */ ! if (rel->rd_rel->relkind != RELKIND_RELATION && ! rel->rd_rel->relkind != RELKIND_MATVIEW) { relation_close(rel, lockmode); continue; *** a/src/backend/commands/vacuum.c --- b/src/backend/commands/vacuum.c *************** *** 340,362 **** get_rel_oids(Oid relid, const RangeVar *vacrel) } else { ! /* Process all plain relations listed in pg_class */ Relation pgclass; HeapScanDesc scan; HeapTuple tuple; - ScanKeyData key; - - ScanKeyInit(&key, - Anum_pg_class_relkind, - BTEqualStrategyNumber, F_CHAREQ, - CharGetDatum(RELKIND_RELATION)); pgclass = heap_open(RelationRelationId, AccessShareLock); ! scan = heap_beginscan(pgclass, SnapshotNow, 1, &key); while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL) { /* Make a relation list entry for this guy */ oldcontext = MemoryContextSwitchTo(vac_context); oid_list = lappend_oid(oid_list, HeapTupleGetOid(tuple)); --- 340,365 ---- } else { ! /* ! * Process all plain relations and materialized views listed in ! * pg_class ! */ Relation pgclass; HeapScanDesc scan; HeapTuple tuple; pgclass = heap_open(RelationRelationId, AccessShareLock); ! scan = heap_beginscan(pgclass, SnapshotNow, 0, NULL); while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL) { + Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple); + + if (classForm->relkind != RELKIND_RELATION && + classForm->relkind != RELKIND_MATVIEW) + continue; + /* Make a relation list entry for this guy */ oldcontext = MemoryContextSwitchTo(vac_context); oid_list = lappend_oid(oid_list, HeapTupleGetOid(tuple)); *************** *** 706,711 **** vac_update_datfrozenxid(void) --- 709,715 ---- * InvalidTransactionId in relfrozenxid anyway.) */ if (classForm->relkind != RELKIND_RELATION && + classForm->relkind != RELKIND_MATVIEW && classForm->relkind != RELKIND_TOASTVALUE) continue; *************** *** 983,988 **** vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound) --- 987,993 ---- * relation. */ if (onerel->rd_rel->relkind != RELKIND_RELATION && + onerel->rd_rel->relkind != RELKIND_MATVIEW && onerel->rd_rel->relkind != RELKIND_TOASTVALUE) { ereport(WARNING, *** a/src/backend/commands/view.c --- b/src/backend/commands/view.c *************** *** 36,92 **** static void checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc); - static bool isViewOnTempTable_walker(Node *node, void *context); - - /*--------------------------------------------------------------------- - * isViewOnTempTable - * - * Returns true iff any of the relations underlying this view are - * temporary tables. - *--------------------------------------------------------------------- - */ - static bool - isViewOnTempTable(Query *viewParse) - { - return isViewOnTempTable_walker((Node *) viewParse, NULL); - } - - static bool - isViewOnTempTable_walker(Node *node, void *context) - { - if (node == NULL) - return false; - - if (IsA(node, Query)) - { - Query *query = (Query *) node; - ListCell *rtable; - - foreach(rtable, query->rtable) - { - RangeTblEntry *rte = lfirst(rtable); - - if (rte->rtekind == RTE_RELATION) - { - Relation rel = heap_open(rte->relid, AccessShareLock); - char relpersistence = rel->rd_rel->relpersistence; - - heap_close(rel, AccessShareLock); - if (relpersistence == RELPERSISTENCE_TEMP) - return true; - } - } - - return query_tree_walker(query, - isViewOnTempTable_walker, - context, - QTW_IGNORE_JOINALIASES); - } - - return expression_tree_walker(node, - isViewOnTempTable_walker, - context); - } /*--------------------------------------------------------------------- * DefineVirtualRelation --- 36,41 ---- *************** *** 506,512 **** DefineView(ViewStmt *stmt, const char *queryString) */ view = copyObject(stmt->view); /* don't corrupt original command */ if (view->relpersistence == RELPERSISTENCE_PERMANENT ! && isViewOnTempTable(viewParse)) { view->relpersistence = RELPERSISTENCE_TEMP; ereport(NOTICE, --- 455,461 ---- */ view = copyObject(stmt->view); /* don't corrupt original command */ if (view->relpersistence == RELPERSISTENCE_PERMANENT ! && isQueryUsingTempRelation(viewParse)) { view->relpersistence = RELPERSISTENCE_TEMP; ereport(NOTICE, *************** *** 530,535 **** DefineView(ViewStmt *stmt, const char *queryString) --- 479,495 ---- */ CommandCounterIncrement(); + StoreViewQuery(viewOid, viewParse, stmt->replace); + + return viewOid; + } + + /* + * Use the rules system to store the query for the view. + */ + void + StoreViewQuery(Oid viewOid, Query *viewParse, bool replace) + { /* * The range table of 'viewParse' does not contain entries for the "OLD" * and "NEW" relations. So... add them! *************** *** 539,545 **** DefineView(ViewStmt *stmt, const char *queryString) /* * Now create the rules associated with the view. */ ! DefineViewRules(viewOid, viewParse, stmt->replace); ! ! return viewOid; } --- 499,503 ---- /* * Now create the rules associated with the view. */ ! DefineViewRules(viewOid, viewParse, replace); } *** a/src/backend/executor/execMain.c --- b/src/backend/executor/execMain.c *************** *** 490,495 **** ExecutorRewind(QueryDesc *queryDesc) --- 490,536 ---- /* + * ExecCheckRelationsValid + * Check that relations which are to be accessed are flagged as valid. + * + * If not, throw error. For a materialized view, suggest refresh. + */ + static void + ExecCheckRelationsValid(List *rangeTable) + { + ListCell *l; + + foreach(l, rangeTable) + { + RangeTblEntry *rte = (RangeTblEntry *) lfirst(l); + + if (rte->rtekind != RTE_RELATION) + continue; + + if (!RelationIsFlaggedAsValid(rte->relid)) + { + if (rte->relkind == RELKIND_MATVIEW) + { + /* It is OK to replace the contents of an invalid matview. */ + if (rte->isResultRel) + continue; + + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("materialized view \"%s\" has not been populated", + get_rel_name(rte->relid)), + errhint("Use the REFRESH MATERIALIZED VIEW command."))); + } + else + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("relation \"%s\" is flagged as invalid", + get_rel_name(rte->relid)))); + } + } + } + + /* * ExecCheckRTPerms * Check access permissions for all relations listed in a range table. * *************** *** 724,729 **** InitPlan(QueryDesc *queryDesc, int eflags) --- 765,775 ---- ExecCheckRTPerms(rangeTable, true); /* + * Ensure that all referrenced relations are flagged as valid. + */ + ExecCheckRelationsValid(rangeTable); + + /* * initialize the node's execution state */ estate->es_range_table = rangeTable; *************** *** 990,995 **** CheckValidResultRel(Relation resultRel, CmdType operation) --- 1036,1047 ---- break; } break; + case RELKIND_MATVIEW: + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot change materialized view \"%s\"", + RelationGetRelationName(resultRel)))); + break; case RELKIND_FOREIGN_TABLE: ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), *************** *** 1040,1045 **** CheckValidRowMarkRel(Relation rel, RowMarkType markType) --- 1092,1104 ---- errmsg("cannot lock rows in view \"%s\"", RelationGetRelationName(rel)))); break; + case RELKIND_MATVIEW: + /* Should not get here */ + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot lock rows in materialized view \"%s\"", + RelationGetRelationName(rel)))); + break; case RELKIND_FOREIGN_TABLE: /* Perhaps we can support this someday, but not today */ ereport(ERROR, *** a/src/backend/executor/spi.c --- b/src/backend/executor/spi.c *************** *** 2050,2055 **** _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI, --- 2050,2062 ---- if (((CreateTableAsStmt *) stmt)->is_select_into) res = SPI_OK_SELINTO; } + else if (IsA(stmt, RefreshMatViewStmt)) + { + Assert(strncmp(completionTag, + "REFRESH MATERIALIZED VIEW ", 23) == 0); + _SPI_current->processed = strtoul(completionTag + 23, + NULL, 10); + } else if (IsA(stmt, CopyStmt)) { Assert(strncmp(completionTag, "COPY ", 5) == 0); *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** *** 1032,1037 **** _copyIntoClause(const IntoClause *from) --- 1032,1038 ---- COPY_SCALAR_FIELD(onCommit); COPY_STRING_FIELD(tableSpaceName); COPY_SCALAR_FIELD(skipData); + COPY_SCALAR_FIELD(relkind); return newnode; } *************** *** 1969,1974 **** _copyRangeTblEntry(const RangeTblEntry *from) --- 1970,1976 ---- COPY_SCALAR_FIELD(rtekind); COPY_SCALAR_FIELD(relid); COPY_SCALAR_FIELD(relkind); + COPY_SCALAR_FIELD(isResultRel); COPY_NODE_FIELD(subquery); COPY_SCALAR_FIELD(security_barrier); COPY_SCALAR_FIELD(jointype); *************** *** 3226,3236 **** _copyCreateTableAsStmt(const CreateTableAsStmt *from) --- 3228,3249 ---- COPY_NODE_FIELD(query); COPY_NODE_FIELD(into); + COPY_SCALAR_FIELD(relkind); COPY_SCALAR_FIELD(is_select_into); return newnode; } + static RefreshMatViewStmt * + _copyRefreshMatViewStmt(const RefreshMatViewStmt *from) + { + RefreshMatViewStmt *newnode = makeNode(RefreshMatViewStmt); + + COPY_NODE_FIELD(relation); + + return newnode; + } + static CreateSeqStmt * _copyCreateSeqStmt(const CreateSeqStmt *from) { *************** *** 4301,4306 **** copyObject(const void *from) --- 4314,4322 ---- case T_CreateTableAsStmt: retval = _copyCreateTableAsStmt(from); break; + case T_RefreshMatViewStmt: + retval = _copyRefreshMatViewStmt(from); + break; case T_CreateSeqStmt: retval = _copyCreateSeqStmt(from); break; *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** *** 124,129 **** _equalIntoClause(const IntoClause *a, const IntoClause *b) --- 124,130 ---- COMPARE_SCALAR_FIELD(onCommit); COMPARE_STRING_FIELD(tableSpaceName); COMPARE_SCALAR_FIELD(skipData); + COMPARE_SCALAR_FIELD(relkind); return true; } *************** *** 1523,1534 **** _equalCreateTableAsStmt(const CreateTableAsStmt *a, const CreateTableAsStmt *b) --- 1524,1544 ---- { COMPARE_NODE_FIELD(query); COMPARE_NODE_FIELD(into); + COMPARE_SCALAR_FIELD(relkind); COMPARE_SCALAR_FIELD(is_select_into); return true; } static bool + _equalRefreshMatViewStmt(const RefreshMatViewStmt *a, const RefreshMatViewStmt *b) + { + COMPARE_NODE_FIELD(relation); + + return true; + } + + static bool _equalCreateSeqStmt(const CreateSeqStmt *a, const CreateSeqStmt *b) { COMPARE_NODE_FIELD(sequence); *************** *** 2221,2226 **** _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b) --- 2231,2237 ---- COMPARE_SCALAR_FIELD(rtekind); COMPARE_SCALAR_FIELD(relid); COMPARE_SCALAR_FIELD(relkind); + COMPARE_SCALAR_FIELD(isResultRel); COMPARE_NODE_FIELD(subquery); COMPARE_SCALAR_FIELD(security_barrier); COMPARE_SCALAR_FIELD(jointype); *************** *** 2788,2793 **** equal(const void *a, const void *b) --- 2799,2807 ---- case T_CreateTableAsStmt: retval = _equalCreateTableAsStmt(a, b); break; + case T_RefreshMatViewStmt: + retval = _equalRefreshMatViewStmt(a, b); + break; case T_CreateSeqStmt: retval = _equalCreateSeqStmt(a, b); break; *** a/src/backend/nodes/outfuncs.c --- b/src/backend/nodes/outfuncs.c *************** *** 893,898 **** _outIntoClause(StringInfo str, const IntoClause *node) --- 893,899 ---- WRITE_ENUM_FIELD(onCommit, OnCommitAction); WRITE_STRING_FIELD(tableSpaceName); WRITE_BOOL_FIELD(skipData); + WRITE_CHAR_FIELD(relkind); } static void *************** *** 2350,2355 **** _outRangeTblEntry(StringInfo str, const RangeTblEntry *node) --- 2351,2357 ---- case RTE_RELATION: WRITE_OID_FIELD(relid); WRITE_CHAR_FIELD(relkind); + WRITE_BOOL_FIELD(isResultRel); break; case RTE_SUBQUERY: WRITE_NODE_FIELD(subquery); *** a/src/backend/nodes/readfuncs.c --- b/src/backend/nodes/readfuncs.c *************** *** 395,400 **** _readIntoClause(void) --- 395,401 ---- READ_ENUM_FIELD(onCommit, OnCommitAction); READ_STRING_FIELD(tableSpaceName); READ_BOOL_FIELD(skipData); + READ_CHAR_FIELD(relkind); READ_DONE(); } *************** *** 1189,1194 **** _readRangeTblEntry(void) --- 1190,1196 ---- case RTE_RELATION: READ_OID_FIELD(relid); READ_CHAR_FIELD(relkind); + READ_BOOL_FIELD(isResultRel); break; case RTE_SUBQUERY: READ_NODE_FIELD(subquery); *** a/src/backend/optimizer/plan/planner.c --- b/src/backend/optimizer/plan/planner.c *************** *** 3375,3381 **** plan_cluster_use_sort(Oid tableOid, Oid indexOid) rte = makeNode(RangeTblEntry); rte->rtekind = RTE_RELATION; rte->relid = tableOid; ! rte->relkind = RELKIND_RELATION; rte->lateral = false; rte->inh = false; rte->inFromCl = true; --- 3375,3381 ---- rte = makeNode(RangeTblEntry); rte->rtekind = RTE_RELATION; rte->relid = tableOid; ! rte->relkind = RELKIND_RELATION; /* Don't be too picky. */ rte->lateral = false; rte->inh = false; rte->inFromCl = true; *** a/src/backend/optimizer/util/plancat.c --- b/src/backend/optimizer/util/plancat.c *************** *** 409,414 **** estimate_rel_size(Relation rel, int32 *attr_widths, --- 409,415 ---- { case RELKIND_RELATION: case RELKIND_INDEX: + case RELKIND_MATVIEW: case RELKIND_TOASTVALUE: /* it has storage, ok to call the smgr */ curpages = RelationGetNumberOfBlocks(rel); *** a/src/backend/parser/analyze.c --- b/src/backend/parser/analyze.c *************** *** 190,195 **** transformTopLevelStmt(ParseState *pstate, Node *parseTree) --- 190,196 ---- ctas->query = parseTree; ctas->into = stmt->intoClause; + ctas->relkind = OBJECT_TABLE; ctas->is_select_into = true; /* *************** *** 324,329 **** analyze_requires_snapshot(Node *parseTree) --- 325,335 ---- result = true; break; + case T_RefreshMatViewStmt: + /* yes, because the SELECT from pg_rewrite must be analyzed */ + result = true; + break; + default: /* other utility statements don't have any real parse analysis */ result = false; *************** *** 2117,2123 **** transformExplainStmt(ParseState *pstate, ExplainStmt *stmt) /* * transformCreateTableAsStmt - ! * transform a CREATE TABLE AS (or SELECT ... INTO) Statement * * As with EXPLAIN, transform the contained statement now. */ --- 2123,2130 ---- /* * transformCreateTableAsStmt - ! * transform a CREATE TABLE AS, SELECT ... INTO, or CREATE MATERIALIZED VIEW ! * Statement * * As with EXPLAIN, transform the contained statement now. */ *************** *** 2126,2131 **** transformCreateTableAsStmt(ParseState *pstate, CreateTableAsStmt *stmt) --- 2133,2156 ---- { Query *result; + /* + * Set relkind in IntoClause based on statement relkind. These are + * different types, because the parser users the ObjectType enumeration + * and the executor uses RELKIND_* defines. + */ + switch (stmt->relkind) + { + case (OBJECT_TABLE): + stmt->into->relkind = RELKIND_RELATION; + break; + case (OBJECT_MATVIEW): + stmt->into->relkind = RELKIND_MATVIEW; + break; + default: + elog(ERROR, "unrecognized object relkind: %d", + (int) stmt->relkind); + } + /* transform contained query */ stmt->query = (Node *) transformStmt(pstate, stmt->query); *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** *** 121,126 **** typedef struct PrivTarget --- 121,133 ---- #define CAS_NOT_VALID 0x10 #define CAS_NO_INHERIT 0x20 + /* + * In the IntoClause structure there is a char value which will eventually be + * set to RELKIND_RELATION or RELKIND_MATVIEW based on the relkind field in + * the statement-level structure, which is an ObjectType. Define the default + * here, which should always be overridden later. + */ + #define INTO_CLAUSE_RELKIND_DEFAULT '\0' #define parser_yyerror(msg) scanner_yyerror(msg, yyscanner) #define parser_errposition(pos) scanner_errposition(pos, yyscanner) *************** *** 247,252 **** static void processCASbits(int cas_bits, int location, const char *constrType, --- 254,260 ---- DeallocateStmt PrepareStmt ExecuteStmt DropOwnedStmt ReassignOwnedStmt AlterTSConfigurationStmt AlterTSDictionaryStmt + CreateMatViewStmt RefreshMatViewStmt %type select_no_parens select_with_parens select_clause simple_select values_clause *************** *** 350,356 **** static void processCASbits(int cas_bits, int location, const char *constrType, %type fdw_option %type OptTempTableName ! %type into_clause create_as_target %type createfunc_opt_item common_func_opt_item dostmt_opt_item %type func_arg func_arg_with_default table_func_column --- 358,364 ---- %type fdw_option %type OptTempTableName ! %type into_clause create_as_target create_mv_target %type createfunc_opt_item common_func_opt_item dostmt_opt_item %type func_arg func_arg_with_default table_func_column *************** *** 359,364 **** static void processCASbits(int cas_bits, int location, const char *constrType, --- 367,373 ---- %type opt_trusted opt_restart_seqs %type OptTemp + %type OptNoLog %type OnCommitOption %type for_locking_item *************** *** 555,561 **** static void processCASbits(int cas_bits, int location, const char *constrType, LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P ! MAPPING MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE NAME_P NAMES NATIONAL NATURAL NCHAR NEXT NO NONE NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF --- 564,570 ---- LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P ! MAPPING MATCH MATERIALIZED MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE NAME_P NAMES NATIONAL NATURAL NCHAR NEXT NO NONE NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF *************** *** 570,576 **** static void processCASbits(int cas_bits, int location, const char *constrType, QUOTE ! RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE --- 579,585 ---- QUOTE ! RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE *************** *** 743,748 **** stmt : --- 752,758 ---- | CreateForeignTableStmt | CreateFunctionStmt | CreateGroupStmt + | CreateMatViewStmt | CreateOpClassStmt | CreateOpFamilyStmt | AlterOpFamilyStmt *************** *** 788,793 **** stmt : --- 798,804 ---- | IndexStmt | InsertStmt | ListenStmt + | RefreshMatViewStmt | LoadStmt | LockStmt | NotifyStmt *************** *** 1694,1702 **** DiscardStmt: /***************************************************************************** * ! * ALTER [ TABLE | INDEX | SEQUENCE | VIEW ] variations * ! * Note: we accept all subcommands for each of the four variants, and sort * out what's really legal at execution time. *****************************************************************************/ --- 1705,1713 ---- /***************************************************************************** * ! * ALTER [ TABLE | INDEX | SEQUENCE | VIEW | MATERIALIZED VIEW ] variations * ! * Note: we accept all subcommands for each of the five variants, and sort * out what's really legal at execution time. *****************************************************************************/ *************** *** 1773,1778 **** AlterTableStmt: --- 1784,1807 ---- n->missing_ok = true; $$ = (Node *)n; } + | ALTER MATERIALIZED VIEW qualified_name alter_table_cmds + { + AlterTableStmt *n = makeNode(AlterTableStmt); + n->relation = $4; + n->cmds = $5; + n->relkind = OBJECT_MATVIEW; + n->missing_ok = false; + $$ = (Node *)n; + } + | ALTER MATERIALIZED VIEW IF_P EXISTS qualified_name alter_table_cmds + { + AlterTableStmt *n = makeNode(AlterTableStmt); + n->relation = $6; + n->cmds = $7; + n->relkind = OBJECT_MATVIEW; + n->missing_ok = true; + $$ = (Node *)n; + } ; alter_table_cmds: *************** *** 3153,3158 **** CreateAsStmt: --- 3182,3188 ---- CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt); ctas->query = $6; ctas->into = $4; + ctas->relkind = OBJECT_TABLE; ctas->is_select_into = false; /* cram additional flags into the IntoClause */ $4->rel->relpersistence = $2; *************** *** 3171,3176 **** create_as_target: --- 3201,3207 ---- $$->onCommit = $4; $$->tableSpaceName = $5; $$->skipData = false; /* might get changed later */ + $$->relkind = INTO_CLAUSE_RELKIND_DEFAULT; } ; *************** *** 3184,3189 **** opt_with_data: --- 3215,3278 ---- /***************************************************************************** * * QUERY : + * CREATE MATERIALIZED VIEW relname AS SelectStmt + * + *****************************************************************************/ + + CreateMatViewStmt: + CREATE OptNoLog MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data + { + CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt); + ctas->query = $7; + ctas->into = $5; + ctas->relkind = OBJECT_MATVIEW; + ctas->is_select_into = false; + /* cram additional flags into the IntoClause */ + $5->rel->relpersistence = $2; + $5->skipData = !($8); + $$ = (Node *) ctas; + } + ; + + create_mv_target: + qualified_name opt_column_list OptWith OptTableSpace + { + $$ = makeNode(IntoClause); + $$->rel = $1; + $$->colNames = $2; + $$->options = $3; + $$->onCommit = ONCOMMIT_NOOP; + $$->tableSpaceName = $4; + $$->skipData = false; /* might get changed later */ + $$->relkind = INTO_CLAUSE_RELKIND_DEFAULT; + } + ; + + OptNoLog: UNLOGGED { $$ = RELPERSISTENCE_UNLOGGED; } + | /*EMPTY*/ { $$ = RELPERSISTENCE_PERMANENT; } + ; + + + /***************************************************************************** + * + * QUERY : + * REFRESH MATERIALIZED VIEW qualified_name + * + *****************************************************************************/ + + RefreshMatViewStmt: + REFRESH MATERIALIZED VIEW qualified_name + { + RefreshMatViewStmt *n = makeNode(RefreshMatViewStmt); + n->relation = $4; + $$ = (Node *) n; + } + ; + + + /***************************************************************************** + * + * QUERY : * CREATE SEQUENCE seqname * ALTER SEQUENCE seqname * *************** *** 3698,3703 **** AlterExtensionContentsStmt: --- 3787,3801 ---- n->objname = $6; $$ = (Node *)n; } + | ALTER EXTENSION name add_drop MATERIALIZED VIEW any_name + { + AlterExtensionContentsStmt *n = makeNode(AlterExtensionContentsStmt); + n->extname = $3; + n->action = $4; + n->objtype = OBJECT_MATVIEW; + n->objname = $7; + $$ = (Node *)n; + } | ALTER EXTENSION name add_drop FOREIGN TABLE any_name { AlterExtensionContentsStmt *n = makeNode(AlterExtensionContentsStmt); *************** *** 5024,5029 **** DropStmt: DROP drop_type IF_P EXISTS any_name_list opt_drop_behavior --- 5122,5128 ---- drop_type: TABLE { $$ = OBJECT_TABLE; } | SEQUENCE { $$ = OBJECT_SEQUENCE; } | VIEW { $$ = OBJECT_VIEW; } + | MATERIALIZED VIEW { $$ = OBJECT_MATVIEW; } | INDEX { $$ = OBJECT_INDEX; } | FOREIGN TABLE { $$ = OBJECT_FOREIGN_TABLE; } | EVENT TRIGGER { $$ = OBJECT_EVENT_TRIGGER; } *************** *** 5090,5096 **** opt_restart_seqs: * EXTENSION | ROLE | TEXT SEARCH PARSER | * TEXT SEARCH DICTIONARY | TEXT SEARCH TEMPLATE | * TEXT SEARCH CONFIGURATION | FOREIGN TABLE | ! * FOREIGN DATA WRAPPER | SERVER | EVENT TRIGGER ] | * AGGREGATE (arg1, ...) | * FUNCTION (arg1, arg2, ...) | * OPERATOR (leftoperand_typ, rightoperand_typ) | --- 5189,5196 ---- * EXTENSION | ROLE | TEXT SEARCH PARSER | * TEXT SEARCH DICTIONARY | TEXT SEARCH TEMPLATE | * TEXT SEARCH CONFIGURATION | FOREIGN TABLE | ! * FOREIGN DATA WRAPPER | SERVER | EVENT TRIGGER | ! * MATERIALIZED VIEW] | * AGGREGATE (arg1, ...) | * FUNCTION (arg1, arg2, ...) | * OPERATOR (leftoperand_typ, rightoperand_typ) | *************** *** 5264,5269 **** comment_type: --- 5364,5370 ---- | DOMAIN_P { $$ = OBJECT_DOMAIN; } | TYPE_P { $$ = OBJECT_TYPE; } | VIEW { $$ = OBJECT_VIEW; } + | MATERIALIZED VIEW { $$ = OBJECT_MATVIEW; } | COLLATION { $$ = OBJECT_COLLATION; } | CONVERSION_P { $$ = OBJECT_CONVERSION; } | TABLESPACE { $$ = OBJECT_TABLESPACE; } *************** *** 5365,5370 **** security_label_type: --- 5466,5472 ---- | TABLESPACE { $$ = OBJECT_TABLESPACE; } | TYPE_P { $$ = OBJECT_TYPE; } | VIEW { $$ = OBJECT_VIEW; } + | MATERIALIZED VIEW { $$ = OBJECT_MATVIEW; } ; security_label: Sconst { $$ = $1; } *************** *** 6907,6912 **** RenameStmt: ALTER AGGREGATE func_name aggr_args RENAME TO name --- 7009,7034 ---- n->missing_ok = true; $$ = (Node *)n; } + | ALTER MATERIALIZED VIEW qualified_name RENAME TO name + { + RenameStmt *n = makeNode(RenameStmt); + n->renameType = OBJECT_MATVIEW; + n->relation = $4; + n->subname = NULL; + n->newname = $7; + n->missing_ok = false; + $$ = (Node *)n; + } + | ALTER MATERIALIZED VIEW IF_P EXISTS qualified_name RENAME TO name + { + RenameStmt *n = makeNode(RenameStmt); + n->renameType = OBJECT_MATVIEW; + n->relation = $6; + n->subname = NULL; + n->newname = $9; + n->missing_ok = true; + $$ = (Node *)n; + } | ALTER INDEX qualified_name RENAME TO name { RenameStmt *n = makeNode(RenameStmt); *************** *** 7314,7319 **** AlterObjectSchemaStmt: --- 7436,7459 ---- n->missing_ok = true; $$ = (Node *)n; } + | ALTER MATERIALIZED VIEW qualified_name SET SCHEMA name + { + AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); + n->objectType = OBJECT_MATVIEW; + n->relation = $4; + n->newschema = $7; + n->missing_ok = false; + $$ = (Node *)n; + } + | ALTER MATERIALIZED VIEW IF_P EXISTS qualified_name SET SCHEMA name + { + AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); + n->objectType = OBJECT_MATVIEW; + n->relation = $6; + n->newschema = $9; + n->missing_ok = true; + $$ = (Node *)n; + } | ALTER FOREIGN TABLE relation_expr SET SCHEMA name { AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); *************** *** 8468,8473 **** ExplainableStmt: --- 8608,8615 ---- | DeleteStmt | DeclareCursorStmt | CreateAsStmt + | CreateMatViewStmt + | RefreshMatViewStmt | ExecuteStmt /* by default all are $$=$1 */ ; *************** *** 8552,8557 **** ExecuteStmt: EXECUTE name execute_param_clause --- 8694,8700 ---- n->params = $8; ctas->query = (Node *) n; ctas->into = $4; + ctas->relkind = OBJECT_TABLE; ctas->is_select_into = false; /* cram additional flags into the IntoClause */ $4->rel->relpersistence = $2; *************** *** 9098,9103 **** into_clause: --- 9241,9247 ---- $$->onCommit = ONCOMMIT_NOOP; $$->tableSpaceName = NULL; $$->skipData = false; + $$->relkind = INTO_CLAUSE_RELKIND_DEFAULT; } | /*EMPTY*/ { $$ = NULL; } *************** *** 12562,12567 **** unreserved_keyword: --- 12706,12712 ---- | LOCK_P | MAPPING | MATCH + | MATERIALIZED | MAXVALUE | MINUTE_P | MINVALUE *************** *** 12606,12611 **** unreserved_keyword: --- 12751,12757 ---- | RECHECK | RECURSIVE | REF + | REFRESH | REINDEX | RELATIVE_P | RELEASE *** a/src/backend/parser/parse_utilcmd.c --- b/src/backend/parser/parse_utilcmd.c *************** *** 646,651 **** transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla --- 646,652 ---- if (relation->rd_rel->relkind != RELKIND_RELATION && relation->rd_rel->relkind != RELKIND_VIEW && + relation->rd_rel->relkind != RELKIND_MATVIEW && relation->rd_rel->relkind != RELKIND_COMPOSITE_TYPE && relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, *** a/src/backend/postmaster/autovacuum.c --- b/src/backend/postmaster/autovacuum.c *************** *** 1962,1983 **** do_autovacuum(void) * Scan pg_class to determine which tables to vacuum. * * We do this in two passes: on the first one we collect the list of plain ! * relations, and on the second one we collect TOAST tables. The reason ! * for doing the second pass is that during it we want to use the main ! * relation's pg_class.reloptions entry if the TOAST table does not have ! * any, and we cannot obtain it unless we know beforehand what's the main ! * table OID. * * We need to check TOAST tables separately because in cases with short, * wide tables there might be proportionally much more activity in the * TOAST table than in its parent. */ ! ScanKeyInit(&key, ! Anum_pg_class_relkind, ! BTEqualStrategyNumber, F_CHAREQ, ! CharGetDatum(RELKIND_RELATION)); ! ! relScan = heap_beginscan(classRel, SnapshotNow, 1, &key); /* * On the first pass, we collect main tables to vacuum, and also the main --- 1962,1978 ---- * Scan pg_class to determine which tables to vacuum. * * We do this in two passes: on the first one we collect the list of plain ! * relations and materialized views, and on the second one we collect ! * TOAST tables. The reason for doing the second pass is that during it we ! * want to use the main relation's pg_class.reloptions entry if the TOAST ! * table does not have any, and we cannot obtain it unless we know ! * beforehand what's the main table OID. * * We need to check TOAST tables separately because in cases with short, * wide tables there might be proportionally much more activity in the * TOAST table than in its parent. */ ! relScan = heap_beginscan(classRel, SnapshotNow, 0, NULL); /* * On the first pass, we collect main tables to vacuum, and also the main *************** *** 1993,1998 **** do_autovacuum(void) --- 1988,1997 ---- bool doanalyze; bool wraparound; + if (classForm->relkind != RELKIND_RELATION && + classForm->relkind != RELKIND_MATVIEW) + continue; + relid = HeapTupleGetOid(tuple); /* Fetch reloptions and the pgstat entry for this table */ *************** *** 2378,2383 **** extract_autovac_opts(HeapTuple tup, TupleDesc pg_class_desc) --- 2377,2383 ---- AutoVacOpts *av; Assert(((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_RELATION || + ((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_MATVIEW || ((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_TOASTVALUE); relopts = extractRelOptions(tup, pg_class_desc, InvalidOid); *** a/src/backend/postmaster/pgstat.c --- b/src/backend/postmaster/pgstat.c *************** *** 1561,1566 **** pgstat_initstats(Relation rel) --- 1561,1567 ---- /* We only count stats for things that have storage */ if (!(relkind == RELKIND_RELATION || + relkind == RELKIND_MATVIEW || relkind == RELKIND_INDEX || relkind == RELKIND_TOASTVALUE || relkind == RELKIND_SEQUENCE)) *** a/src/backend/rewrite/rewriteDefine.c --- b/src/backend/rewrite/rewriteDefine.c *************** *** 257,262 **** DefineQueryRewrite(char *rulename, --- 257,263 ---- * Verify relation is of a type that rules can sensibly be applied to. */ if (event_relation->rd_rel->relkind != RELKIND_RELATION && + event_relation->rd_rel->relkind != RELKIND_MATVIEW && event_relation->rd_rel->relkind != RELKIND_VIEW) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), *************** *** 353,359 **** DefineQueryRewrite(char *rulename, */ checkRuleResultList(query->targetList, RelationGetDescr(event_relation), ! true); /* * ... there must not be another ON SELECT rule already ... --- 354,361 ---- */ checkRuleResultList(query->targetList, RelationGetDescr(event_relation), ! event_relation->rd_rel->relkind != ! RELKIND_MATVIEW); /* * ... there must not be another ON SELECT rule already ... *************** *** 411,417 **** DefineQueryRewrite(char *rulename, * business of converting relations to views is just a kluge to allow * loading ancient pg_dump files.) */ ! if (event_relation->rd_rel->relkind != RELKIND_VIEW) { HeapScanDesc scanDesc; --- 413,420 ---- * business of converting relations to views is just a kluge to allow * loading ancient pg_dump files.) */ ! if (event_relation->rd_rel->relkind != RELKIND_VIEW && ! event_relation->rd_rel->relkind != RELKIND_MATVIEW) { HeapScanDesc scanDesc; *** a/src/backend/rewrite/rewriteHandler.c --- b/src/backend/rewrite/rewriteHandler.c *************** *** 1168,1174 **** rewriteTargetListUD(Query *parsetree, RangeTblEntry *target_rte, const char *attrname; TargetEntry *tle; ! if (target_relation->rd_rel->relkind == RELKIND_RELATION) { /* * Emit CTID so that executor can find the row to update or delete. --- 1168,1175 ---- const char *attrname; TargetEntry *tle; ! if (target_relation->rd_rel->relkind == RELKIND_RELATION || ! target_relation->rd_rel->relkind == RELKIND_MATVIEW) { /* * Emit CTID so that executor can find the row to update or delete. *************** *** 1591,1596 **** fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown) --- 1592,1607 ---- rel = heap_open(rte->relid, NoLock); /* + * Skip materialized view expansion when resultRelation is set. + */ + if (rel->rd_rel->relkind == RELKIND_MATVIEW && + rel->rd_rel->relisvalid) + { + heap_close(rel, NoLock); + break; + } + + /* * Collect the RIR rules that we must apply */ rules = rel->rd_rules; *** a/src/backend/storage/lmgr/predicate.c --- b/src/backend/storage/lmgr/predicate.c *************** *** 460,472 **** static void OnConflict_CheckForSerializationFailure(const SERIALIZABLEXACT *read /* * Does this relation participate in predicate locking? Temporary and system ! * relations are exempt. */ static inline bool PredicateLockingNeededForRelation(Relation relation) { return !(relation->rd_id < FirstBootstrapObjectId || ! RelationUsesLocalBuffers(relation)); } /* --- 460,473 ---- /* * Does this relation participate in predicate locking? Temporary and system ! * relations are exempt, as are materialized views. */ static inline bool PredicateLockingNeededForRelation(Relation relation) { return !(relation->rd_id < FirstBootstrapObjectId || ! RelationUsesLocalBuffers(relation) || ! relation->rd_rel->relkind == RELKIND_MATVIEW); } /* *** a/src/backend/tcop/dest.c --- b/src/backend/tcop/dest.c *************** *** 32,37 **** --- 32,38 ---- #include "access/xact.h" #include "commands/copy.h" #include "commands/createas.h" + #include "commands/matview.h" #include "executor/functions.h" #include "executor/tstoreReceiver.h" #include "libpq/libpq.h" *************** *** 125,130 **** CreateDestReceiver(CommandDest dest) --- 126,134 ---- case DestSQLFunction: return CreateSQLFunctionDestReceiver(); + + case DestTransientRel: + return CreateTransientRelDestReceiver(InvalidOid); } /* should never get here */ *************** *** 157,162 **** EndCommand(const char *commandTag, CommandDest dest) --- 161,167 ---- case DestIntoRel: case DestCopyOut: case DestSQLFunction: + case DestTransientRel: break; } } *************** *** 198,203 **** NullCommand(CommandDest dest) --- 203,209 ---- case DestIntoRel: case DestCopyOut: case DestSQLFunction: + case DestTransientRel: break; } } *************** *** 241,246 **** ReadyForQuery(CommandDest dest) --- 247,253 ---- case DestIntoRel: case DestCopyOut: case DestSQLFunction: + case DestTransientRel: break; } } *** a/src/backend/tcop/utility.c --- b/src/backend/tcop/utility.c *************** *** 37,42 **** --- 37,43 ---- #include "commands/event_trigger.h" #include "commands/explain.h" #include "commands/extension.h" + #include "commands/matview.h" #include "commands/lockcmds.h" #include "commands/portalcmds.h" #include "commands/prepare.h" *************** *** 110,115 **** CheckRelationOwnership(RangeVar *rel, bool noCatalogs) --- 111,137 ---- ReleaseSysCache(tuple); } + /* + * Tells whether a relation is flagged as valid. The caller must be holding a + * lock on the relation. + */ + bool + RelationIsFlaggedAsValid(Oid relid) + { + HeapTuple tuple; + Form_pg_class classtup; + bool result; + + tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(tuple)) + return false; /* concurrently dropped */ + classtup = (Form_pg_class) GETSTRUCT(tuple); + result = classtup->relisvalid; + ReleaseSysCache(tuple); + + return result; + } + /* * CommandIsReadOnly: is an executable query read-only? *************** *** 202,207 **** check_xact_readonly(Node *parsetree) --- 224,230 ---- case T_CreateSeqStmt: case T_CreateStmt: case T_CreateTableAsStmt: + case T_RefreshMatViewStmt: case T_CreateTableSpaceStmt: case T_CreateTrigStmt: case T_CompositeTypeStmt: *************** *** 682,687 **** standard_ProcessUtility(Node *parsetree, --- 705,711 ---- case OBJECT_TABLE: case OBJECT_SEQUENCE: case OBJECT_VIEW: + case OBJECT_MATVIEW: case OBJECT_FOREIGN_TABLE: RemoveRelations((DropStmt *) parsetree); break; *************** *** 1137,1142 **** standard_ProcessUtility(Node *parsetree, --- 1161,1173 ---- queryString, params, completionTag); break; + case T_RefreshMatViewStmt: + if (isCompleteQuery) + EventTriggerDDLCommandStart(parsetree); + ExecRefreshMatView((RefreshMatViewStmt *) parsetree, + queryString, params, completionTag); + break; + case T_VariableSetStmt: ExecSetVariableStmt((VariableSetStmt *) parsetree); break; *************** *** 1263,1268 **** standard_ProcessUtility(Node *parsetree, --- 1294,1300 ---- ReindexIndex(stmt->relation); break; case OBJECT_TABLE: + case OBJECT_MATVIEW: ReindexTable(stmt->relation); break; case OBJECT_DATABASE: *************** *** 1482,1490 **** QueryReturnsTuples(Query *parsetree) * We assume it is invoked only on already-parse-analyzed statements * (else the contained parsetree isn't a Query yet). * ! * In some cases (currently, only EXPLAIN of CREATE TABLE AS/SELECT INTO), ! * potentially Query-containing utility statements can be nested. This ! * function will drill down to a non-utility Query, or return NULL if none. */ Query * UtilityContainsQuery(Node *parsetree) --- 1514,1523 ---- * We assume it is invoked only on already-parse-analyzed statements * (else the contained parsetree isn't a Query yet). * ! * In some cases (currently, only EXPLAIN of CREATE TABLE AS/SELECT INTO and ! * CREATE MATERIALIZED VIEW), potentially Query-containing utility statements ! * can be nested. This function will drill down to a non-utility Query, or ! * return NULL if none. */ Query * UtilityContainsQuery(Node *parsetree) *************** *** 1628,1633 **** AlterObjectTypeCommandTag(ObjectType objtype) --- 1661,1669 ---- case OBJECT_VIEW: tag = "ALTER VIEW"; break; + case OBJECT_MATVIEW: + tag = "ALTER MATERIALIZED VIEW"; + break; default: tag = "???"; break; *************** *** 1825,1830 **** CreateCommandTag(Node *parsetree) --- 1861,1869 ---- case OBJECT_VIEW: tag = "DROP VIEW"; break; + case OBJECT_MATVIEW: + tag = "DROP MATERIALIZED VIEW"; + break; case OBJECT_INDEX: tag = "DROP INDEX"; break; *************** *** 2086,2095 **** CreateCommandTag(Node *parsetree) break; case T_CreateTableAsStmt: ! if (((CreateTableAsStmt *) parsetree)->is_select_into) ! tag = "SELECT INTO"; ! else ! tag = "CREATE TABLE AS"; break; case T_VariableSetStmt: --- 2125,2148 ---- break; case T_CreateTableAsStmt: ! switch (((CreateTableAsStmt *) parsetree)->relkind) ! { ! case OBJECT_TABLE: ! if (((CreateTableAsStmt *) parsetree)->is_select_into) ! tag = "SELECT INTO"; ! else ! tag = "CREATE TABLE AS"; ! break; ! case OBJECT_MATVIEW: ! tag = "CREATE MATERIALIZED VIEW"; ! break; ! default: ! tag = "???"; ! } ! break; ! ! case T_RefreshMatViewStmt: ! tag = "REFRESH MATERIALIZED VIEW"; break; case T_VariableSetStmt: *************** *** 2622,2627 **** GetCommandLogLevel(Node *parsetree) --- 2675,2684 ---- lev = LOGSTMT_DDL; break; + case T_RefreshMatViewStmt: + lev = LOGSTMT_DDL; + break; + case T_VariableSetStmt: lev = LOGSTMT_ALL; break; *** a/src/backend/utils/adt/dbsize.c --- b/src/backend/utils/adt/dbsize.c *************** *** 718,723 **** pg_relation_filenode(PG_FUNCTION_ARGS) --- 718,724 ---- switch (relform->relkind) { case RELKIND_RELATION: + case RELKIND_MATVIEW: case RELKIND_INDEX: case RELKIND_SEQUENCE: case RELKIND_TOASTVALUE: *************** *** 766,771 **** pg_relation_filepath(PG_FUNCTION_ARGS) --- 767,773 ---- switch (relform->relkind) { case RELKIND_RELATION: + case RELKIND_MATVIEW: case RELKIND_INDEX: case RELKIND_SEQUENCE: case RELKIND_TOASTVALUE: *** a/src/backend/utils/adt/xml.c --- b/src/backend/utils/adt/xml.c *************** *** 2285,2291 **** schema_get_xml_visible_tables(Oid nspid) StringInfoData query; initStringInfo(&query); ! appendStringInfo(&query, "SELECT oid FROM pg_catalog.pg_class WHERE relnamespace = %u AND relkind IN ('r', 'v') AND pg_catalog.has_table_privilege (oid, 'SELECT') ORDER BY relname;", nspid); return query_to_oid_list(query.data); } --- 2285,2291 ---- StringInfoData query; initStringInfo(&query); ! appendStringInfo(&query, "SELECT oid FROM pg_catalog.pg_class WHERE relnamespace = %u AND relkind IN ('r', 'm', 'v') AND pg_catalog.has_table_privilege (oid, 'SELECT') ORDER BY relname;", nspid); return query_to_oid_list(query.data); } *************** *** 2311,2317 **** static List * database_get_xml_visible_tables(void) { /* At the moment there is no order required here. */ ! return query_to_oid_list("SELECT oid FROM pg_catalog.pg_class WHERE relkind IN ('r', 'v') AND pg_catalog.has_table_privilege (pg_class.oid, 'SELECT') AND relnamespace IN (" XML_VISIBLE_SCHEMAS ");"); } --- 2311,2317 ---- database_get_xml_visible_tables(void) { /* At the moment there is no order required here. */ ! return query_to_oid_list("SELECT oid FROM pg_catalog.pg_class WHERE relkind IN ('r', 'm', 'v') AND pg_catalog.has_table_privilege (pg_class.oid, 'SELECT') AND relnamespace IN (" XML_VISIBLE_SCHEMAS ");"); } *** a/src/backend/utils/cache/relcache.c --- b/src/backend/utils/cache/relcache.c *************** *** 36,41 **** --- 36,42 ---- #include "access/htup_details.h" #include "access/xact.h" #include "catalog/catalog.h" + #include "catalog/heap.h" #include "catalog/index.h" #include "catalog/indexing.h" #include "catalog/namespace.h" *************** *** 54,59 **** --- 55,61 ---- #include "catalog/pg_type.h" #include "catalog/schemapg.h" #include "catalog/storage.h" + #include "commands/tablecmds.h" #include "commands/trigger.h" #include "miscadmin.h" #include "optimizer/clauses.h" *************** *** 376,381 **** RelationParseRelOptions(Relation relation, HeapTuple tuple) --- 378,384 ---- case RELKIND_TOASTVALUE: case RELKIND_INDEX: case RELKIND_VIEW: + case RELKIND_MATVIEW: break; default: return; *************** *** 942,947 **** RelationBuildDesc(Oid targetRelId, bool insertIt) --- 945,967 ---- if (insertIt) RelationCacheInsert(relation); + /* flag unlogged matview invalid if its heap looks like the init fork */ + if (insertIt && + relation->rd_rel->relkind == RELKIND_MATVIEW && + relation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED) + { + /* prevent race conditions on this part of the test */ + LWLockAcquire(UnloggedMatViewInitLock, LW_EXCLUSIVE); + if (relation->rd_rel->relisvalid && + heap_is_matview_init_fork(relation)) + { + SetRelationIsValid(relid, false); + RelationOpenSmgr(relation); + smgrtruncate(relation->rd_smgr, MAIN_FORKNUM, 0); + } + LWLockRelease(UnloggedMatViewInitLock); + } + /* It's fully valid */ relation->rd_isvalid = true; *************** *** 1432,1437 **** formrdesc(const char *relationName, Oid relationReltype, --- 1452,1458 ---- relation->rd_rel->reltuples = 0; relation->rd_rel->relallvisible = 0; relation->rd_rel->relkind = RELKIND_RELATION; + relation->rd_rel->relisvalid = true; relation->rd_rel->relhasoids = hasoids; relation->rd_rel->relnatts = (int16) natts; *************** *** 2555,2560 **** RelationBuildLocalRelation(const char *relname, --- 2576,2582 ---- rel->rd_rel->relhasoids = rel->rd_att->tdhasoid; rel->rd_rel->relnatts = natts; rel->rd_rel->reltype = InvalidOid; + rel->rd_rel->relisvalid = true; /* needed when bootstrapping: */ rel->rd_rel->relowner = BOOTSTRAP_SUPERUSERID; *** a/src/bin/initdb/initdb.c --- b/src/bin/initdb/initdb.c *************** *** 2076,2082 **** setup_privileges(void) static char *privileges_setup[] = { "UPDATE pg_class " " SET relacl = E'{\"=r/\\\\\"$POSTGRES_SUPERUSERNAME\\\\\"\"}' " ! " WHERE relkind IN ('r', 'v', 'S') AND relacl IS NULL;\n", "GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n", "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n", "REVOKE ALL ON pg_largeobject FROM PUBLIC;\n", --- 2076,2082 ---- static char *privileges_setup[] = { "UPDATE pg_class " " SET relacl = E'{\"=r/\\\\\"$POSTGRES_SUPERUSERNAME\\\\\"\"}' " ! " WHERE relkind IN ('r', 'v', 'm', 'S') AND relacl IS NULL;\n", "GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n", "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n", "REVOKE ALL ON pg_largeobject FROM PUBLIC;\n", *** a/src/bin/pg_dump/common.c --- b/src/bin/pg_dump/common.c *************** *** 272,278 **** flagInhTables(TableInfo *tblinfo, int numTables, { /* Sequences and views never have parents */ if (tblinfo[i].relkind == RELKIND_SEQUENCE || ! tblinfo[i].relkind == RELKIND_VIEW) continue; /* Don't bother computing anything for non-target tables, either */ --- 272,279 ---- { /* Sequences and views never have parents */ if (tblinfo[i].relkind == RELKIND_SEQUENCE || ! tblinfo[i].relkind == RELKIND_VIEW || ! tblinfo[i].relkind == RELKIND_MATVIEW) continue; /* Don't bother computing anything for non-target tables, either */ *************** *** 317,323 **** flagInhAttrs(TableInfo *tblinfo, int numTables) /* Sequences and views never have parents */ if (tbinfo->relkind == RELKIND_SEQUENCE || ! tbinfo->relkind == RELKIND_VIEW) continue; /* Don't bother computing anything for non-target tables, either */ --- 318,325 ---- /* Sequences and views never have parents */ if (tbinfo->relkind == RELKIND_SEQUENCE || ! tbinfo->relkind == RELKIND_VIEW || ! tbinfo->relkind == RELKIND_MATVIEW) continue; /* Don't bother computing anything for non-target tables, either */ *** a/src/bin/pg_dump/pg_backup_archiver.c --- b/src/bin/pg_dump/pg_backup_archiver.c *************** *** 2909,2915 **** _getObjectDescription(PQExpBuffer buf, TocEntry *te, ArchiveHandle *AH) const char *type = te->desc; /* Use ALTER TABLE for views and sequences */ ! if (strcmp(type, "VIEW") == 0 || strcmp(type, "SEQUENCE") == 0) type = "TABLE"; /* objects named by a schema and name */ --- 2909,2916 ---- const char *type = te->desc; /* Use ALTER TABLE for views and sequences */ ! if (strcmp(type, "VIEW") == 0 || strcmp(type, "SEQUENCE") == 0|| ! strcmp(type, "MATERIALIZED VIEW") == 0) type = "TABLE"; /* objects named by a schema and name */ *************** *** 3141,3146 **** _printTocEntry(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt, bool isDat --- 3142,3148 ---- strcmp(te->desc, "TABLE") == 0 || strcmp(te->desc, "TYPE") == 0 || strcmp(te->desc, "VIEW") == 0 || + strcmp(te->desc, "MATERIALIZED VIEW") == 0 || strcmp(te->desc, "SEQUENCE") == 0 || strcmp(te->desc, "FOREIGN TABLE") == 0 || strcmp(te->desc, "TEXT SEARCH DICTIONARY") == 0 || *** a/src/bin/pg_dump/pg_dump.c --- b/src/bin/pg_dump/pg_dump.c *************** *** 194,199 **** static void dumpAttrDef(Archive *fout, AttrDefInfo *adinfo); --- 194,200 ---- static void dumpSequence(Archive *fout, TableInfo *tbinfo); static void dumpSequenceData(Archive *fout, TableDataInfo *tdinfo); static void dumpIndex(Archive *fout, IndxInfo *indxinfo); + static void dumpMatViewIndex(Archive *fout, IndxInfo *indxinfo); static void dumpConstraint(Archive *fout, ConstraintInfo *coninfo); static void dumpTableConstraintComment(Archive *fout, ConstraintInfo *coninfo); static void dumpTSParser(Archive *fout, TSParserInfo *prsinfo); *************** *** 1042,1050 **** expand_table_name_patterns(Archive *fout, "SELECT c.oid" "\nFROM pg_catalog.pg_class c" "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace" ! "\nWHERE c.relkind in ('%c', '%c', '%c', '%c')\n", RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW, ! RELKIND_FOREIGN_TABLE); processSQLNamePattern(GetConnection(fout), query, cell->val, true, false, "n.nspname", "c.relname", NULL, "pg_catalog.pg_table_is_visible(c.oid)"); --- 1043,1051 ---- "SELECT c.oid" "\nFROM pg_catalog.pg_class c" "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace" ! "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c')\n", RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW, ! RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE); processSQLNamePattern(GetConnection(fout), query, cell->val, true, false, "n.nspname", "c.relname", NULL, "pg_catalog.pg_table_is_visible(c.oid)"); *************** *** 1605,1610 **** dumpTableData(Archive *fout, TableDataInfo *tdinfo) --- 1606,1649 ---- } /* + * refreshMatViewData - + * load or refresh the contents of a single materialized view + * + * Actually, this just makes an ArchiveEntry for the REFRESH MATERIALIZED VIEW + * statement. + */ + static void + refreshMatViewData(Archive *fout, TableDataInfo *tdinfo) + { + TableInfo *tbinfo = tdinfo->tdtable; + PQExpBuffer q; + + q = createPQExpBuffer(); + appendPQExpBuffer(q, "REFRESH MATERIALIZED VIEW %s;\n", + fmtId(tbinfo->dobj.name)); + + ArchiveEntry(fout, + tdinfo->dobj.catId, /* catalog ID */ + tdinfo->dobj.dumpId, /* dump ID */ + tbinfo->dobj.name, /* Name */ + tbinfo->dobj.namespace->dobj.name, /* Namespace */ + NULL, /* Tablespace */ + tbinfo->rolname, /* Owner */ + false, /* with oids */ + "MATERIALIZED VIEW DATA", /* Desc */ + SECTION_POST_DATA, /* Section */ + q->data, /* Create */ + "", /* Del */ + NULL, /* Copy */ + &(tbinfo->dobj.dumpId), /* Deps */ + 1, /* # Deps */ + NULL, /* Dumper */ + NULL); /* Dumper Arg */ + + destroyPQExpBuffer(q); + } + + /* * getTableData - * set up dumpable objects representing the contents of tables */ *************** *** 1655,1664 **** makeTableDataInfo(TableInfo *tbinfo, bool oids) tbinfo->dobj.catId.oid)) return; /* OK, let's dump it */ tdinfo = (TableDataInfo *) pg_malloc(sizeof(TableDataInfo)); ! tdinfo->dobj.objType = DO_TABLE_DATA; /* * Note: use tableoid 0 so that this object won't be mistaken for --- 1694,1710 ---- tbinfo->dobj.catId.oid)) return; + /* An invalid materialized view does not generate data. */ + if (!(tbinfo->relisvalid)) + return; + /* OK, let's dump it */ tdinfo = (TableDataInfo *) pg_malloc(sizeof(TableDataInfo)); ! if (tbinfo->relkind == RELKIND_MATVIEW) ! tdinfo->dobj.objType = DO_REFRESH_MATVIEW; ! else ! tdinfo->dobj.objType = DO_TABLE_DATA; /* * Note: use tableoid 0 so that this object won't be mistaken for *************** *** 3920,3925 **** getTables(Archive *fout, int *numTables) --- 3966,3972 ---- int i_toastoid; int i_toastfrozenxid; int i_relpersistence; + int i_relisvalid; int i_owning_tab; int i_owning_col; int i_reltablespace; *************** *** 3964,3970 **** getTables(Archive *fout, int *numTables) "c.relhasindex, c.relhasrules, c.relhasoids, " "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " ! "c.relpersistence, " "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " --- 4011,4017 ---- "c.relhasindex, c.relhasrules, c.relhasoids, " "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " ! "c.relpersistence, c.relisvalid, " "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " *************** *** 3978,3990 **** getTables(Archive *fout, int *numTables) "d.objsubid = 0 AND " "d.refclassid = c.tableoid AND d.deptype = 'a') " "LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid) " ! "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c') " "ORDER BY c.oid", username_subquery, RELKIND_SEQUENCE, RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW, RELKIND_COMPOSITE_TYPE, ! RELKIND_FOREIGN_TABLE); } else if (fout->remoteVersion >= 90000) { --- 4025,4037 ---- "d.objsubid = 0 AND " "d.refclassid = c.tableoid AND d.deptype = 'a') " "LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid) " ! "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c') " "ORDER BY c.oid", username_subquery, RELKIND_SEQUENCE, RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW, RELKIND_COMPOSITE_TYPE, ! RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE); } else if (fout->remoteVersion >= 90000) { *************** *** 4000,4006 **** getTables(Archive *fout, int *numTables) "c.relhasindex, c.relhasrules, c.relhasoids, " "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " ! "'p' AS relpersistence, " "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " --- 4047,4053 ---- "c.relhasindex, c.relhasrules, c.relhasoids, " "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " ! "'p' AS relpersistence, 't'::bool as relisvalid, " "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " *************** *** 4035,4041 **** getTables(Archive *fout, int *numTables) "c.relhasindex, c.relhasrules, c.relhasoids, " "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " ! "'p' AS relpersistence, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " --- 4082,4088 ---- "c.relhasindex, c.relhasrules, c.relhasoids, " "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " ! "'p' AS relpersistence, 't'::bool as relisvalid, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " *************** *** 4070,4076 **** getTables(Archive *fout, int *numTables) "c.relhasindex, c.relhasrules, c.relhasoids, " "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " ! "'p' AS relpersistence, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " --- 4117,4123 ---- "c.relhasindex, c.relhasrules, c.relhasoids, " "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " ! "'p' AS relpersistence, 't'::bool as relisvalid, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " *************** *** 4106,4112 **** getTables(Archive *fout, int *numTables) "0 AS relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " ! "'p' AS relpersistence, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " --- 4153,4159 ---- "0 AS relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " ! "'p' AS relpersistence, 't'::bool as relisvalid, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " *************** *** 4141,4147 **** getTables(Archive *fout, int *numTables) "0 AS relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " ! "'p' AS relpersistence, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " --- 4188,4194 ---- "0 AS relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " ! "'p' AS relpersistence, 't'::bool as relisvalid, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " *************** *** 4172,4178 **** getTables(Archive *fout, int *numTables) "0 AS relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " ! "'p' AS relpersistence, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " --- 4219,4225 ---- "0 AS relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " ! "'p' AS relpersistence, 't'::bool as relisvalid, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " *************** *** 4198,4204 **** getTables(Archive *fout, int *numTables) "0 AS relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " ! "'p' AS relpersistence, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " --- 4245,4251 ---- "0 AS relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " ! "'p' AS relpersistence, 't'::bool as relisvalid, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " *************** *** 4234,4240 **** getTables(Archive *fout, int *numTables) "0 as relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " ! "'p' AS relpersistence, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " --- 4281,4287 ---- "0 as relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " ! "'p' AS relpersistence, 't'::bool as relisvalid, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " *************** *** 4282,4287 **** getTables(Archive *fout, int *numTables) --- 4329,4335 ---- i_toastoid = PQfnumber(res, "toid"); i_toastfrozenxid = PQfnumber(res, "tfrozenxid"); i_relpersistence = PQfnumber(res, "relpersistence"); + i_relisvalid = PQfnumber(res, "relisvalid"); i_owning_tab = PQfnumber(res, "owning_tab"); i_owning_col = PQfnumber(res, "owning_col"); i_reltablespace = PQfnumber(res, "reltablespace"); *************** *** 4323,4328 **** getTables(Archive *fout, int *numTables) --- 4371,4377 ---- tblinfo[i].hasrules = (strcmp(PQgetvalue(res, i, i_relhasrules), "t") == 0); tblinfo[i].hastriggers = (strcmp(PQgetvalue(res, i, i_relhastriggers), "t") == 0); tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0); + tblinfo[i].relisvalid = (strcmp(PQgetvalue(res, i, i_relisvalid), "t") == 0); tblinfo[i].frozenxid = atooid(PQgetvalue(res, i, i_relfrozenxid)); tblinfo[i].toast_oid = atooid(PQgetvalue(res, i, i_toastoid)); tblinfo[i].toast_frozenxid = atooid(PQgetvalue(res, i, i_toastfrozenxid)); *************** *** 4518,4525 **** getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) { TableInfo *tbinfo = &tblinfo[i]; ! /* Only plain tables have indexes */ ! if (tbinfo->relkind != RELKIND_RELATION || !tbinfo->hasindex) continue; /* Ignore indexes of tables not to be dumped */ --- 4567,4577 ---- { TableInfo *tbinfo = &tblinfo[i]; ! /* Only plain tables and materialized views have indexes. */ ! if (tbinfo->relkind != RELKIND_RELATION && ! tbinfo->relkind != RELKIND_MATVIEW) ! continue; ! if (!tbinfo->hasindex) continue; /* Ignore indexes of tables not to be dumped */ *************** *** 4733,4739 **** getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) { char contype; ! indxinfo[j].dobj.objType = DO_INDEX; indxinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid)); indxinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid)); AssignDumpId(&indxinfo[j].dobj); --- 4785,4794 ---- { char contype; ! if (tbinfo->relkind == RELKIND_MATVIEW) ! indxinfo[j].dobj.objType = DO_MATVIEW_INDEX; ! else ! indxinfo[j].dobj.objType = DO_INDEX; indxinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid)); indxinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid)); AssignDumpId(&indxinfo[j].dobj); *************** *** 5101,5112 **** getRules(Archive *fout, int *numRules) if (ruleinfo[i].ruletable) { /* ! * If the table is a view, force its ON SELECT rule to be sorted ! * before the view itself --- this ensures that any dependencies ! * for the rule affect the table's positioning. Other rules are ! * forced to appear after their table. */ ! if (ruleinfo[i].ruletable->relkind == RELKIND_VIEW && ruleinfo[i].ev_type == '1' && ruleinfo[i].is_instead) { addObjectDependency(&ruleinfo[i].ruletable->dobj, --- 5156,5169 ---- if (ruleinfo[i].ruletable) { /* ! * If the table is a view or materialized view, force its ON ! * SELECT rule to be sorted before the view itself --- this ! * ensures that any dependencies for the rule affect the table's ! * positioning. Other rules are forced to appear after their ! * table. */ ! if ((ruleinfo[i].ruletable->relkind == RELKIND_VIEW || ! ruleinfo[i].ruletable->relkind == RELKIND_MATVIEW) && ruleinfo[i].ev_type == '1' && ruleinfo[i].is_instead) { addObjectDependency(&ruleinfo[i].ruletable->dobj, *************** *** 7312,7317 **** dumpDumpableObject(Archive *fout, DumpableObject *dobj) --- 7369,7380 ---- case DO_INDEX: dumpIndex(fout, (IndxInfo *) dobj); break; + case DO_REFRESH_MATVIEW: + refreshMatViewData(fout, (TableDataInfo *) dobj); + break; + case DO_MATVIEW_INDEX: + dumpMatViewIndex(fout, (IndxInfo *) dobj); + break; case DO_RULE: dumpRule(fout, (RuleInfo *) dobj); break; *************** *** 12351,12367 **** dumpTable(Archive *fout, TableInfo *tbinfo) } /* * dumpTableSchema * write the declaration (not data) of one user-defined table or view */ static void dumpTableSchema(Archive *fout, TableInfo *tbinfo) { - PQExpBuffer query = createPQExpBuffer(); PQExpBuffer q = createPQExpBuffer(); PQExpBuffer delq = createPQExpBuffer(); PQExpBuffer labelq = createPQExpBuffer(); - PGresult *res; int numParents; TableInfo **parents; int actual_atts; /* number of attrs in this CREATE statement */ --- 12414,12485 ---- } /* + * Create the AS clause for a view or materialized view. The semicolon is + * stripped because a materialized view must add a WITH NO DATA clause. + * + * This returns a new buffer which must be freed by the caller. + */ + static PQExpBuffer + createViewAsClause(Archive *fout, TableInfo *tbinfo) + { + PQExpBuffer query = createPQExpBuffer(); + PQExpBuffer result = createPQExpBuffer(); + PGresult *res; + int len; + + /* Fetch the view definition */ + if (fout->remoteVersion >= 70300) + { + /* Beginning in 7.3, viewname is not unique; rely on OID */ + appendPQExpBuffer(query, + "SELECT pg_catalog.pg_get_viewdef('%u'::pg_catalog.oid) AS viewdef", + tbinfo->dobj.catId.oid); + } + else + { + appendPQExpBuffer(query, "SELECT definition AS viewdef " + "FROM pg_views WHERE viewname = "); + appendStringLiteralAH(query, tbinfo->dobj.name, fout); + appendPQExpBuffer(query, ";"); + } + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + if (PQntuples(res) != 1) + { + if (PQntuples(res) < 1) + exit_horribly(NULL, "query to obtain definition of view \"%s\" returned no data\n", + tbinfo->dobj.name); + else + exit_horribly(NULL, "query to obtain definition of view \"%s\" returned more than one definition\n", + tbinfo->dobj.name); + } + + len = PQgetlength(res, 0, 0); + + if (len == 0) + exit_horribly(NULL, "definition of view \"%s\" appears to be empty (length zero)\n", + tbinfo->dobj.name); + + /* Strip off the trailing semicolon so that other things may follow. */ + appendBinaryPQExpBuffer(result, PQgetvalue(res, 0, 0), len - 1); + + PQclear(res); + destroyPQExpBuffer(query); + + return result; + } + + /* * dumpTableSchema * write the declaration (not data) of one user-defined table or view */ static void dumpTableSchema(Archive *fout, TableInfo *tbinfo) { PQExpBuffer q = createPQExpBuffer(); PQExpBuffer delq = createPQExpBuffer(); PQExpBuffer labelq = createPQExpBuffer(); int numParents; TableInfo **parents; int actual_atts; /* number of attrs in this CREATE statement */ *************** *** 12382,12425 **** dumpTableSchema(Archive *fout, TableInfo *tbinfo) /* Is it a table or a view? */ if (tbinfo->relkind == RELKIND_VIEW) { ! char *viewdef; reltypename = "VIEW"; - /* Fetch the view definition */ - if (fout->remoteVersion >= 70300) - { - /* Beginning in 7.3, viewname is not unique; rely on OID */ - appendPQExpBuffer(query, - "SELECT pg_catalog.pg_get_viewdef('%u'::pg_catalog.oid) AS viewdef", - tbinfo->dobj.catId.oid); - } - else - { - appendPQExpBuffer(query, "SELECT definition AS viewdef " - "FROM pg_views WHERE viewname = "); - appendStringLiteralAH(query, tbinfo->dobj.name, fout); - appendPQExpBuffer(query, ";"); - } - - res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); - - if (PQntuples(res) != 1) - { - if (PQntuples(res) < 1) - exit_horribly(NULL, "query to obtain definition of view \"%s\" returned no data\n", - tbinfo->dobj.name); - else - exit_horribly(NULL, "query to obtain definition of view \"%s\" returned more than one definition\n", - tbinfo->dobj.name); - } - - viewdef = PQgetvalue(res, 0, 0); - - if (strlen(viewdef) == 0) - exit_horribly(NULL, "definition of view \"%s\" appears to be empty (length zero)\n", - tbinfo->dobj.name); - /* * DROP must be fully qualified in case same name appears in * pg_catalog --- 12500,12509 ---- /* Is it a table or a view? */ if (tbinfo->relkind == RELKIND_VIEW) { ! PQExpBuffer result; reltypename = "VIEW"; /* * DROP must be fully qualified in case same name appears in * pg_catalog *************** *** 12436,12484 **** dumpTableSchema(Archive *fout, TableInfo *tbinfo) appendPQExpBuffer(q, "CREATE VIEW %s", fmtId(tbinfo->dobj.name)); if (tbinfo->reloptions && strlen(tbinfo->reloptions) > 0) appendPQExpBuffer(q, " WITH (%s)", tbinfo->reloptions); ! appendPQExpBuffer(q, " AS\n %s\n", viewdef); appendPQExpBuffer(labelq, "VIEW %s", fmtId(tbinfo->dobj.name)); - - PQclear(res); } else { ! if (tbinfo->relkind == RELKIND_FOREIGN_TABLE) { ! int i_srvname; ! int i_ftoptions; ! ! reltypename = "FOREIGN TABLE"; ! ! /* retrieve name of foreign server and generic options */ ! appendPQExpBuffer(query, ! "SELECT fs.srvname, " ! "pg_catalog.array_to_string(ARRAY(" ! "SELECT pg_catalog.quote_ident(option_name) || " ! "' ' || pg_catalog.quote_literal(option_value) " ! "FROM pg_catalog.pg_options_to_table(ftoptions) " ! "ORDER BY option_name" ! "), E',\n ') AS ftoptions " ! "FROM pg_catalog.pg_foreign_table ft " ! "JOIN pg_catalog.pg_foreign_server fs " ! "ON (fs.oid = ft.ftserver) " ! "WHERE ft.ftrelid = '%u'", ! tbinfo->dobj.catId.oid); ! res = ExecuteSqlQueryForSingleRow(fout, query->data); ! i_srvname = PQfnumber(res, "srvname"); ! i_ftoptions = PQfnumber(res, "ftoptions"); ! srvname = pg_strdup(PQgetvalue(res, 0, i_srvname)); ! ftoptions = pg_strdup(PQgetvalue(res, 0, i_ftoptions)); ! PQclear(res); ! } ! else ! { ! reltypename = "TABLE"; ! srvname = NULL; ! ftoptions = NULL; } numParents = tbinfo->numParents; parents = tbinfo->parents; --- 12520,12579 ---- appendPQExpBuffer(q, "CREATE VIEW %s", fmtId(tbinfo->dobj.name)); if (tbinfo->reloptions && strlen(tbinfo->reloptions) > 0) appendPQExpBuffer(q, " WITH (%s)", tbinfo->reloptions); ! result = createViewAsClause(fout, tbinfo); ! appendPQExpBuffer(q, " AS\n %s;\n", result->data); ! destroyPQExpBuffer(result); appendPQExpBuffer(labelq, "VIEW %s", fmtId(tbinfo->dobj.name)); } else { ! switch (tbinfo->relkind) { ! case (RELKIND_FOREIGN_TABLE): ! { ! PQExpBuffer query = createPQExpBuffer(); ! PGresult *res; ! int i_srvname; ! int i_ftoptions; ! ! reltypename = "FOREIGN TABLE"; ! ! /* retrieve name of foreign server and generic options */ ! appendPQExpBuffer(query, ! "SELECT fs.srvname, " ! "pg_catalog.array_to_string(ARRAY(" ! "SELECT pg_catalog.quote_ident(option_name) || " ! "' ' || pg_catalog.quote_literal(option_value) " ! "FROM pg_catalog.pg_options_to_table(ftoptions) " ! "ORDER BY option_name" ! "), E',\n ') AS ftoptions " ! "FROM pg_catalog.pg_foreign_table ft " ! "JOIN pg_catalog.pg_foreign_server fs " ! "ON (fs.oid = ft.ftserver) " ! "WHERE ft.ftrelid = '%u'", ! tbinfo->dobj.catId.oid); ! res = ExecuteSqlQueryForSingleRow(fout, query->data); ! i_srvname = PQfnumber(res, "srvname"); ! i_ftoptions = PQfnumber(res, "ftoptions"); ! srvname = pg_strdup(PQgetvalue(res, 0, i_srvname)); ! ftoptions = pg_strdup(PQgetvalue(res, 0, i_ftoptions)); ! PQclear(res); ! destroyPQExpBuffer(query); ! break; ! } ! case (RELKIND_MATVIEW): ! reltypename = "MATERIALIZED VIEW"; ! srvname = NULL; ! ftoptions = NULL; ! break; ! default: ! reltypename = "TABLE"; ! srvname = NULL; ! ftoptions = NULL; } + numParents = tbinfo->numParents; parents = tbinfo->parents; *************** *** 12550,12558 **** dumpTableSchema(Archive *fout, TableInfo *tbinfo) actual_atts++; /* Attribute name */ ! appendPQExpBuffer(q, "%s ", fmtId(tbinfo->attnames[j])); if (tbinfo->attisdropped[j]) { /* --- 12645,12657 ---- actual_atts++; /* Attribute name */ ! appendPQExpBuffer(q, "%s", fmtId(tbinfo->attnames[j])); + /* Materialized views just have column names, not types. */ + if (tbinfo->relkind == RELKIND_MATVIEW) + continue; + if (tbinfo->attisdropped[j]) { /* *************** *** 12560,12566 **** dumpTableSchema(Archive *fout, TableInfo *tbinfo) * so we will not have gotten a valid type name; insert * INTEGER as a stopgap. We'll clean things up later. */ ! appendPQExpBuffer(q, "INTEGER /* dummy */"); /* Skip all the rest, too */ continue; } --- 12659,12665 ---- * so we will not have gotten a valid type name; insert * INTEGER as a stopgap. We'll clean things up later. */ ! appendPQExpBuffer(q, " INTEGER /* dummy */"); /* Skip all the rest, too */ continue; } *************** *** 12568,12584 **** dumpTableSchema(Archive *fout, TableInfo *tbinfo) /* Attribute type */ if (tbinfo->reloftype && !binary_upgrade) { ! appendPQExpBuffer(q, "WITH OPTIONS"); } else if (fout->remoteVersion >= 70100) { ! appendPQExpBuffer(q, "%s", tbinfo->atttypnames[j]); } else { /* If no format_type, fake it */ ! appendPQExpBuffer(q, "%s", myFormatType(tbinfo->atttypnames[j], tbinfo->atttypmod[j])); } --- 12667,12683 ---- /* Attribute type */ if (tbinfo->reloftype && !binary_upgrade) { ! appendPQExpBuffer(q, " WITH OPTIONS"); } else if (fout->remoteVersion >= 70100) { ! appendPQExpBuffer(q, " %s", tbinfo->atttypnames[j]); } else { /* If no format_type, fake it */ ! appendPQExpBuffer(q, " %s", myFormatType(tbinfo->atttypnames[j], tbinfo->atttypmod[j])); } *************** *** 12685,12691 **** dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (ftoptions && ftoptions[0]) appendPQExpBuffer(q, "\nOPTIONS (\n %s\n)", ftoptions); ! appendPQExpBuffer(q, ";\n"); /* * To create binary-compatible heap files, we have to ensure the same --- 12784,12803 ---- if (ftoptions && ftoptions[0]) appendPQExpBuffer(q, "\nOPTIONS (\n %s\n)", ftoptions); ! /* ! * For materialized views, create the AS clause just like a view. ! */ ! if (tbinfo->relkind == RELKIND_MATVIEW) ! { ! PQExpBuffer result; ! ! result = createViewAsClause(fout, tbinfo); ! appendPQExpBuffer(q, " AS\n %s\n WITH NO DATA;\n", ! result->data); ! destroyPQExpBuffer(result); ! } ! else ! appendPQExpBuffer(q, ";\n"); /* * To create binary-compatible heap files, we have to ensure the same *************** *** 12941,12947 **** dumpTableSchema(Archive *fout, TableInfo *tbinfo) dumpTableConstraintComment(fout, constr); } - destroyPQExpBuffer(query); destroyPQExpBuffer(q); destroyPQExpBuffer(delq); destroyPQExpBuffer(labelq); --- 13053,13058 ---- *************** *** 13110,13115 **** dumpIndex(Archive *fout, IndxInfo *indxinfo) --- 13221,13302 ---- } /* + * dumpMatViewIndex + * write out to fout a user-defined index + */ + static void + dumpMatViewIndex(Archive *fout, IndxInfo *indxinfo) + { + TableInfo *tbinfo = indxinfo->indextable; + PQExpBuffer q; + PQExpBuffer delq; + PQExpBuffer labelq; + + if (dataOnly) + return; + + q = createPQExpBuffer(); + delq = createPQExpBuffer(); + labelq = createPQExpBuffer(); + + appendPQExpBuffer(labelq, "INDEX %s", + fmtId(indxinfo->dobj.name)); + + /* + * If there's an associated constraint, don't dump the index per se, but + * do dump any comment for it. (This is safe because dependency ordering + * will have ensured the constraint is emitted first.) + */ + if (indxinfo->indexconstraint == 0) + { + if (binary_upgrade) + binary_upgrade_set_pg_class_oids(fout, q, + indxinfo->dobj.catId.oid, true); + + /* Plain secondary index */ + appendPQExpBuffer(q, "%s;\n", indxinfo->indexdef); + + /* If the index is clustered, we need to record that. */ + if (indxinfo->indisclustered) + { + appendPQExpBuffer(q, "\nALTER TABLE %s CLUSTER", + fmtId(tbinfo->dobj.name)); + appendPQExpBuffer(q, " ON %s;\n", + fmtId(indxinfo->dobj.name)); + } + + /* + * DROP must be fully qualified in case same name appears in + * pg_catalog + */ + appendPQExpBuffer(delq, "DROP INDEX %s.", + fmtId(tbinfo->dobj.namespace->dobj.name)); + appendPQExpBuffer(delq, "%s;\n", + fmtId(indxinfo->dobj.name)); + + ArchiveEntry(fout, indxinfo->dobj.catId, indxinfo->dobj.dumpId, + indxinfo->dobj.name, + tbinfo->dobj.namespace->dobj.name, + indxinfo->tablespace, + tbinfo->rolname, false, + "INDEX", SECTION_POST_DATA, + q->data, delq->data, NULL, + NULL, 0, + NULL, NULL); + } + + /* Dump Index Comments */ + dumpComment(fout, labelq->data, + tbinfo->dobj.namespace->dobj.name, + tbinfo->rolname, + indxinfo->dobj.catId, 0, indxinfo->dobj.dumpId); + + destroyPQExpBuffer(q); + destroyPQExpBuffer(delq); + destroyPQExpBuffer(labelq); + } + + /* * dumpConstraint * write out to fout a user-defined constraint */ *************** *** 14435,14440 **** addBoundaryDependencies(DumpableObject **dobjs, int numObjs, --- 14622,14629 ---- addObjectDependency(postDataBound, dobj->dumpId); break; case DO_INDEX: + case DO_REFRESH_MATVIEW: + case DO_MATVIEW_INDEX: case DO_TRIGGER: case DO_EVENT_TRIGGER: case DO_DEFAULT_ACL: *** a/src/bin/pg_dump/pg_dump.h --- b/src/bin/pg_dump/pg_dump.h *************** *** 121,127 **** typedef enum DO_BLOB_DATA, DO_PRE_DATA_BOUNDARY, DO_POST_DATA_BOUNDARY, ! DO_EVENT_TRIGGER } DumpableObjectType; typedef struct _dumpableObject --- 121,129 ---- DO_BLOB_DATA, DO_PRE_DATA_BOUNDARY, DO_POST_DATA_BOUNDARY, ! DO_EVENT_TRIGGER, ! DO_REFRESH_MATVIEW, ! DO_MATVIEW_INDEX } DumpableObjectType; typedef struct _dumpableObject *************** *** 253,258 **** typedef struct _tableInfo --- 255,261 ---- bool hasrules; /* does it have any rules? */ bool hastriggers; /* does it have any triggers? */ bool hasoids; /* does it have OIDs? */ + bool relisvalid; /* is valid for use in queries */ uint32 frozenxid; /* for restore frozen xid */ Oid toast_oid; /* for restore toast frozen xid */ uint32 toast_frozenxid; /* for restore toast frozen xid */ *** a/src/bin/pg_dump/pg_dump_sort.c --- b/src/bin/pg_dump/pg_dump_sort.c *************** *** 69,75 **** static const int oldObjectTypePriority[] = 12, /* DO_BLOB_DATA */ 10, /* DO_PRE_DATA_BOUNDARY */ 13, /* DO_POST_DATA_BOUNDARY */ ! 20 /* DO_EVENT_TRIGGER */ }; /* --- 69,77 ---- 12, /* DO_BLOB_DATA */ 10, /* DO_PRE_DATA_BOUNDARY */ 13, /* DO_POST_DATA_BOUNDARY */ ! 20, /* DO_EVENT_TRIGGER */ ! 15, /* DO_REFRESH_MATVIEW */ ! 15 /* DO_MATVIEW_INDEX */ }; /* *************** *** 97,106 **** static const int newObjectTypePriority[] = 18, /* DO_TABLE */ 20, /* DO_ATTRDEF */ 27, /* DO_INDEX */ ! 28, /* DO_RULE */ ! 29, /* DO_TRIGGER */ 26, /* DO_CONSTRAINT */ ! 30, /* DO_FK_CONSTRAINT */ 2, /* DO_PROCLANG */ 10, /* DO_CAST */ 23, /* DO_TABLE_DATA */ --- 99,108 ---- 18, /* DO_TABLE */ 20, /* DO_ATTRDEF */ 27, /* DO_INDEX */ ! 29, /* DO_RULE */ ! 30, /* DO_TRIGGER */ 26, /* DO_CONSTRAINT */ ! 31, /* DO_FK_CONSTRAINT */ 2, /* DO_PROCLANG */ 10, /* DO_CAST */ 23, /* DO_TABLE_DATA */ *************** *** 111,122 **** static const int newObjectTypePriority[] = 15, /* DO_TSCONFIG */ 16, /* DO_FDW */ 17, /* DO_FOREIGN_SERVER */ ! 31, /* DO_DEFAULT_ACL */ 21, /* DO_BLOB */ 24, /* DO_BLOB_DATA */ 22, /* DO_PRE_DATA_BOUNDARY */ 25, /* DO_POST_DATA_BOUNDARY */ ! 32 /* DO_EVENT_TRIGGER */ }; static DumpId preDataBoundId; --- 113,126 ---- 15, /* DO_TSCONFIG */ 16, /* DO_FDW */ 17, /* DO_FOREIGN_SERVER */ ! 32, /* DO_DEFAULT_ACL */ 21, /* DO_BLOB */ 24, /* DO_BLOB_DATA */ 22, /* DO_PRE_DATA_BOUNDARY */ 25, /* DO_POST_DATA_BOUNDARY */ ! 33, /* DO_EVENT_TRIGGER */ ! 28, /* DO_REFRESH_MATVIEW */ ! 28 /* DO_MATVIEW_INDEX */ }; static DumpId preDataBoundId; *************** *** 1153,1158 **** describeDumpableObject(DumpableObject *obj, char *buf, int bufsize) --- 1157,1172 ---- "INDEX %s (ID %d OID %u)", obj->name, obj->dumpId, obj->catId.oid); return; + case DO_REFRESH_MATVIEW: + snprintf(buf, bufsize, + "REFRESH MATERIALIZED VIEW %s (ID %d OID %u)", + obj->name, obj->dumpId, obj->catId.oid); + return; + case DO_MATVIEW_INDEX: + snprintf(buf, bufsize, + "MATERIALIZED VIEW INDEX %s (ID %d OID %u)", + obj->name, obj->dumpId, obj->catId.oid); + return; case DO_RULE: snprintf(buf, bufsize, "RULE %s (ID %d OID %u)", *** a/src/bin/psql/command.c --- b/src/bin/psql/command.c *************** *** 355,361 **** exec_command(const char *cmd, success = describeTableDetails(pattern, show_verbose, show_system); else /* standard listing of interesting things */ ! success = listTables("tvsE", NULL, show_verbose, show_system); break; case 'a': success = describeAggregates(pattern, show_verbose, show_system); --- 355,361 ---- success = describeTableDetails(pattern, show_verbose, show_system); else /* standard listing of interesting things */ ! success = listTables("tvmsE", NULL, show_verbose, show_system); break; case 'a': success = describeAggregates(pattern, show_verbose, show_system); *************** *** 422,427 **** exec_command(const char *cmd, --- 422,428 ---- break; case 't': case 'v': + case 'm': case 'i': case 's': case 'E': *** a/src/bin/psql/describe.c --- b/src/bin/psql/describe.c *************** *** 717,727 **** permissionsList(const char *pattern) printfPQExpBuffer(&buf, "SELECT n.nspname as \"%s\",\n" " c.relname as \"%s\",\n" ! " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'S' THEN '%s' WHEN 'f' THEN '%s' END as \"%s\",\n" " ", gettext_noop("Schema"), gettext_noop("Name"), ! gettext_noop("table"), gettext_noop("view"), gettext_noop("sequence"), gettext_noop("foreign table"), gettext_noop("Type")); --- 717,736 ---- printfPQExpBuffer(&buf, "SELECT n.nspname as \"%s\",\n" " c.relname as \"%s\",\n" ! " CASE c.relkind" ! " WHEN 'r' THEN '%s'" ! " WHEN 'v' THEN '%s'" ! " WHEN 'm' THEN '%s'" ! " WHEN 'S' THEN '%s'" ! " WHEN 'f' THEN '%s'" ! " END as \"%s\",\n" " ", gettext_noop("Schema"), gettext_noop("Name"), ! gettext_noop("table"), ! gettext_noop("view"), ! gettext_noop("materialized view"), ! gettext_noop("sequence"), gettext_noop("foreign table"), gettext_noop("Type")); *************** *** 738,744 **** permissionsList(const char *pattern) appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_class c\n" " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" ! "WHERE c.relkind IN ('r', 'v', 'S', 'f')\n"); /* * Unless a schema pattern is specified, we suppress system and temp --- 747,753 ---- appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_class c\n" " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" ! "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')\n"); /* * Unless a schema pattern is specified, we suppress system and temp *************** *** 1315,1320 **** describeOneTableDetails(const char *schemaname, --- 1324,1330 ---- * types, and foreign tables (c.f. CommentObject() in comment.c). */ if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' || + tableinfo.relkind == 'm' || tableinfo.relkind == 'f' || tableinfo.relkind == 'c') appendPQExpBuffer(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)"); } *************** *** 1343,1348 **** describeOneTableDetails(const char *schemaname, --- 1353,1366 ---- printfPQExpBuffer(&title, _("View \"%s.%s\""), schemaname, relationname); break; + case 'm': + if (tableinfo.relpersistence == 'u') + printfPQExpBuffer(&title, _("Unlogged materialized view \"%s.%s\""), + schemaname, relationname); + else + printfPQExpBuffer(&title, _("Materialized view \"%s.%s\""), + schemaname, relationname); + break; case 'S': printfPQExpBuffer(&title, _("Sequence \"%s.%s\""), schemaname, relationname); *************** *** 1385,1390 **** describeOneTableDetails(const char *schemaname, --- 1403,1409 ---- cols = 2; if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' || + tableinfo.relkind == 'm' || tableinfo.relkind == 'f' || tableinfo.relkind == 'c') { show_modifiers = true; *************** *** 1404,1413 **** describeOneTableDetails(const char *schemaname, if (verbose) { headers[cols++] = gettext_noop("Storage"); ! if (tableinfo.relkind == 'r' || tableinfo.relkind == 'f') headers[cols++] = gettext_noop("Stats target"); /* Column comments, if the relkind supports this feature. */ if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' || tableinfo.relkind == 'c' || tableinfo.relkind == 'f') headers[cols++] = gettext_noop("Description"); } --- 1423,1434 ---- if (verbose) { headers[cols++] = gettext_noop("Storage"); ! if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' || ! tableinfo.relkind == 'f') headers[cols++] = gettext_noop("Stats target"); /* Column comments, if the relkind supports this feature. */ if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' || + tableinfo.relkind == 'm' || tableinfo.relkind == 'c' || tableinfo.relkind == 'f') headers[cols++] = gettext_noop("Description"); } *************** *** 1418,1425 **** describeOneTableDetails(const char *schemaname, for (i = 0; i < cols; i++) printTableAddHeader(&cont, headers[i], true, 'l'); ! /* Check if table is a view */ ! if (tableinfo.relkind == 'v' && verbose) { PGresult *result; --- 1439,1446 ---- for (i = 0; i < cols; i++) printTableAddHeader(&cont, headers[i], true, 'l'); ! /* Check if table is a view or materialized view */ ! if ((tableinfo.relkind == 'v' || tableinfo.relkind == 'm') && verbose) { PGresult *result; *************** *** 1507,1513 **** describeOneTableDetails(const char *schemaname, false, false); /* Statistics target, if the relkind supports this feature */ ! if (tableinfo.relkind == 'r' || tableinfo.relkind == 'f') { printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1), false, false); --- 1528,1535 ---- false, false); /* Statistics target, if the relkind supports this feature */ ! if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' || ! tableinfo.relkind == 'f') { printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1), false, false); *************** *** 1515,1520 **** describeOneTableDetails(const char *schemaname, --- 1537,1543 ---- /* Column comments, if the relkind supports this feature. */ if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' || + tableinfo.relkind == 'm' || tableinfo.relkind == 'c' || tableinfo.relkind == 'f') printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 2), false, false); *************** *** 1611,1654 **** describeOneTableDetails(const char *schemaname, PQclear(result); } - else if (view_def) - { - PGresult *result = NULL; - - /* Footer information about a view */ - printTableAddFooter(&cont, _("View definition:")); - printTableAddFooter(&cont, view_def); - - /* print rules */ - if (tableinfo.hasrules) - { - printfPQExpBuffer(&buf, - "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n" - "FROM pg_catalog.pg_rewrite r\n" - "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1;", - oid); - result = PSQLexec(buf.data, false); - if (!result) - goto error_return; - - if (PQntuples(result) > 0) - { - printTableAddFooter(&cont, _("Rules:")); - for (i = 0; i < PQntuples(result); i++) - { - const char *ruledef; - - /* Everything after "CREATE RULE" is echoed verbatim */ - ruledef = PQgetvalue(result, i, 1); - ruledef += 12; - - printfPQExpBuffer(&buf, " %s", ruledef); - printTableAddFooter(&cont, buf.data); - } - } - PQclear(result); - } - } else if (tableinfo.relkind == 'S') { /* Footer information about a sequence */ --- 1634,1639 ---- *************** *** 1687,1693 **** describeOneTableDetails(const char *schemaname, */ PQclear(result); } ! else if (tableinfo.relkind == 'r' || tableinfo.relkind == 'f') { /* Footer information about a table */ PGresult *result = NULL; --- 1672,1679 ---- */ PQclear(result); } ! else if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' || ! tableinfo.relkind == 'f') { /* Footer information about a table */ PGresult *result = NULL; *************** *** 1888,1894 **** describeOneTableDetails(const char *schemaname, } /* print rules */ ! if (tableinfo.hasrules) { if (pset.sversion >= 80300) { --- 1874,1880 ---- } /* print rules */ ! if (tableinfo.hasrules && tableinfo.relkind != 'm') { if (pset.sversion >= 80300) { *************** *** 1983,1988 **** describeOneTableDetails(const char *schemaname, --- 1969,2013 ---- } } + if (view_def) + { + PGresult *result = NULL; + + /* Footer information about a view */ + printTableAddFooter(&cont, _("View definition:")); + printTableAddFooter(&cont, view_def); + + /* print rules */ + if (tableinfo.hasrules) + { + printfPQExpBuffer(&buf, + "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n" + "FROM pg_catalog.pg_rewrite r\n" + "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1;", + oid); + result = PSQLexec(buf.data, false); + if (!result) + goto error_return; + + if (PQntuples(result) > 0) + { + printTableAddFooter(&cont, _("Rules:")); + for (i = 0; i < PQntuples(result); i++) + { + const char *ruledef; + + /* Everything after "CREATE RULE" is echoed verbatim */ + ruledef = PQgetvalue(result, i, 1); + ruledef += 12; + + printfPQExpBuffer(&buf, " %s", ruledef); + printTableAddFooter(&cont, buf.data); + } + } + PQclear(result); + } + } + /* * Print triggers next, if any (but only user-defined triggers). This * could apply to either a table or a view. *************** *** 2106,2112 **** describeOneTableDetails(const char *schemaname, /* * Finish printing the footer information about a table. */ ! if (tableinfo.relkind == 'r' || tableinfo.relkind == 'f') { PGresult *result; int tuples; --- 2131,2138 ---- /* * Finish printing the footer information about a table. */ ! if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' || ! tableinfo.relkind == 'f') { PGresult *result; int tuples; *************** *** 2303,2309 **** add_tablespace_footer(printTableContent *const cont, char relkind, Oid tablespace, const bool newline) { /* relkinds for which we support tablespaces */ ! if (relkind == 'r' || relkind == 'i') { /* * We ignore the database default tablespace so that users not using --- 2329,2335 ---- Oid tablespace, const bool newline) { /* relkinds for which we support tablespaces */ ! if (relkind == 'r' || relkind == 'm' || relkind == 'i') { /* * We ignore the database default tablespace so that users not using *************** *** 2585,2590 **** listDbRoleSettings(const char *pattern, const char *pattern2) --- 2611,2617 ---- * t - tables * i - indexes * v - views + * m - materialized views * s - sequences * E - foreign table (Note: different from 'f', the relkind value) * (any order of the above is fine) *************** *** 2596,2601 **** listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys --- 2623,2629 ---- bool showTables = strchr(tabtypes, 't') != NULL; bool showIndexes = strchr(tabtypes, 'i') != NULL; bool showViews = strchr(tabtypes, 'v') != NULL; + bool showMatViews = strchr(tabtypes, 'm') != NULL; bool showSeq = strchr(tabtypes, 's') != NULL; bool showForeign = strchr(tabtypes, 'E') != NULL; *************** *** 2604,2611 **** listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys printQueryOpt myopt = pset.popt; static const bool translate_columns[] = {false, false, true, false, false, false, false}; ! if (!(showTables || showIndexes || showViews || showSeq || showForeign)) ! showTables = showViews = showSeq = showForeign = true; initPQExpBuffer(&buf); --- 2632,2639 ---- printQueryOpt myopt = pset.popt; static const bool translate_columns[] = {false, false, true, false, false, false, false}; ! if (!(showTables || showIndexes || showViews || showMatViews || showSeq || showForeign)) ! showTables = showViews = showMatViews = showSeq = showForeign = true; initPQExpBuffer(&buf); *************** *** 2616,2627 **** listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys printfPQExpBuffer(&buf, "SELECT n.nspname as \"%s\",\n" " c.relname as \"%s\",\n" ! " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's' THEN '%s' WHEN 'f' THEN '%s' END as \"%s\",\n" " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"", gettext_noop("Schema"), gettext_noop("Name"), gettext_noop("table"), gettext_noop("view"), gettext_noop("index"), gettext_noop("sequence"), gettext_noop("special"), --- 2644,2664 ---- printfPQExpBuffer(&buf, "SELECT n.nspname as \"%s\",\n" " c.relname as \"%s\",\n" ! " CASE c.relkind" ! " WHEN 'r' THEN '%s'" ! " WHEN 'v' THEN '%s'" ! " WHEN 'm' THEN '%s'" ! " WHEN 'i' THEN '%s'" ! " WHEN 'S' THEN '%s'" ! " WHEN 's' THEN '%s'" ! " WHEN 'f' THEN '%s'" ! " END as \"%s\",\n" " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"", gettext_noop("Schema"), gettext_noop("Name"), gettext_noop("table"), gettext_noop("view"), + gettext_noop("materialized view"), gettext_noop("index"), gettext_noop("sequence"), gettext_noop("special"), *************** *** 2667,2672 **** listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys --- 2704,2711 ---- appendPQExpBuffer(&buf, "'r',"); if (showViews) appendPQExpBuffer(&buf, "'v',"); + if (showMatViews) + appendPQExpBuffer(&buf, "'m',"); if (showIndexes) appendPQExpBuffer(&buf, "'i',"); if (showSeq) *** a/src/bin/psql/help.c --- b/src/bin/psql/help.c *************** *** 220,225 **** slashUsage(unsigned short int pager) --- 220,226 ---- fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n")); fprintf(output, _(" \\dl list large objects, same as \\lo_list\n")); fprintf(output, _(" \\dL[S+] [PATTERN] list procedural languages\n")); + fprintf(output, _(" \\dm[S+] [PATTERN] list materialized views\n")); fprintf(output, _(" \\dn[S+] [PATTERN] list schemas\n")); fprintf(output, _(" \\do[S] [PATTERN] list operators\n")); fprintf(output, _(" \\dO[S+] [PATTERN] list collations\n")); *** a/src/bin/psql/tab-complete.c --- b/src/bin/psql/tab-complete.c *************** *** 435,445 **** static const SchemaQuery Query_for_list_of_relations = { NULL }; ! static const SchemaQuery Query_for_list_of_tsvf = { /* catname */ "pg_catalog.pg_class c", /* selcondition */ ! "c.relkind IN ('r', 'S', 'v', 'f')", /* viscondition */ "pg_catalog.pg_table_is_visible(c.oid)", /* namespace */ --- 435,445 ---- NULL }; ! static const SchemaQuery Query_for_list_of_tsvmf = { /* catname */ "pg_catalog.pg_class c", /* selcondition */ ! "c.relkind IN ('r', 'S', 'v', 'm', 'f')", /* viscondition */ "pg_catalog.pg_table_is_visible(c.oid)", /* namespace */ *************** *** 450,460 **** static const SchemaQuery Query_for_list_of_tsvf = { NULL }; ! static const SchemaQuery Query_for_list_of_tf = { /* catname */ "pg_catalog.pg_class c", /* selcondition */ ! "c.relkind IN ('r', 'f')", /* viscondition */ "pg_catalog.pg_table_is_visible(c.oid)", /* namespace */ --- 450,475 ---- NULL }; ! static const SchemaQuery Query_for_list_of_tmf = { /* catname */ "pg_catalog.pg_class c", /* selcondition */ ! "c.relkind IN ('r', 'm', 'f')", ! /* viscondition */ ! "pg_catalog.pg_table_is_visible(c.oid)", ! /* namespace */ ! "c.relnamespace", ! /* result */ ! "pg_catalog.quote_ident(c.relname)", ! /* qualresult */ ! NULL ! }; ! ! static const SchemaQuery Query_for_list_of_tm = { ! /* catname */ ! "pg_catalog.pg_class c", ! /* selcondition */ ! "c.relkind IN ('r', 'm')", /* viscondition */ "pg_catalog.pg_table_is_visible(c.oid)", /* namespace */ *************** *** 480,485 **** static const SchemaQuery Query_for_list_of_views = { --- 495,515 ---- NULL }; + static const SchemaQuery Query_for_list_of_matviews = { + /* catname */ + "pg_catalog.pg_class c", + /* selcondition */ + "c.relkind IN ('m')", + /* viscondition */ + "pg_catalog.pg_table_is_visible(c.oid)", + /* namespace */ + "c.relnamespace", + /* result */ + "pg_catalog.quote_ident(c.relname)", + /* qualresult */ + NULL + }; + /* * Queries to get lists of names of various kinds of things, possibly *************** *** 743,748 **** static const pgsql_thing_t words_after_create[] = { --- 773,779 ---- {"GROUP", Query_for_list_of_roles}, {"LANGUAGE", Query_for_list_of_languages}, {"INDEX", NULL, &Query_for_list_of_indexes}, + {"MATERIALIZED VIEW", NULL, NULL}, {"OPERATOR", NULL, NULL}, /* Querying for this is probably not such a * good idea. */ {"OWNED", NULL, NULL, THING_NO_CREATE}, /* for DROP OWNED BY ... */ *************** *** 844,850 **** psql_completion(char *text, int start, int end) "COMMENT", "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE", "DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE", "EXPLAIN", "FETCH", "GRANT", "INSERT", "LISTEN", "LOAD", "LOCK", "MOVE", "NOTIFY", "PREPARE", ! "REASSIGN", "REINDEX", "RELEASE", "RESET", "REVOKE", "ROLLBACK", "SAVEPOINT", "SECURITY LABEL", "SELECT", "SET", "SHOW", "START", "TABLE", "TRUNCATE", "UNLISTEN", "UPDATE", "VACUUM", "VALUES", "WITH", NULL --- 875,881 ---- "COMMENT", "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE", "DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE", "EXPLAIN", "FETCH", "GRANT", "INSERT", "LISTEN", "LOAD", "LOCK", "MOVE", "NOTIFY", "PREPARE", ! "REASSIGN", "REFRESH", "REINDEX", "RELEASE", "RESET", "REVOKE", "ROLLBACK", "SAVEPOINT", "SECURITY LABEL", "SELECT", "SET", "SHOW", "START", "TABLE", "TRUNCATE", "UNLISTEN", "UPDATE", "VACUUM", "VALUES", "WITH", NULL *************** *** 924,930 **** psql_completion(char *text, int start, int end) static const char *const list_ALTER[] = {"AGGREGATE", "COLLATION", "CONVERSION", "DATABASE", "DEFAULT PRIVILEGES", "DOMAIN", "EXTENSION", "FOREIGN DATA WRAPPER", "FOREIGN TABLE", "FUNCTION", ! "GROUP", "INDEX", "LANGUAGE", "LARGE OBJECT", "OPERATOR", "ROLE", "SCHEMA", "SERVER", "SEQUENCE", "TABLE", "TABLESPACE", "TEXT SEARCH", "TRIGGER", "TYPE", "USER", "USER MAPPING FOR", "VIEW", NULL}; --- 955,961 ---- static const char *const list_ALTER[] = {"AGGREGATE", "COLLATION", "CONVERSION", "DATABASE", "DEFAULT PRIVILEGES", "DOMAIN", "EXTENSION", "FOREIGN DATA WRAPPER", "FOREIGN TABLE", "FUNCTION", ! "GROUP", "INDEX", "LANGUAGE", "LARGE OBJECT", "MATERIALIZED VIEW", "OPERATOR", "ROLE", "SCHEMA", "SERVER", "SEQUENCE", "TABLE", "TABLESPACE", "TEXT SEARCH", "TRIGGER", "TYPE", "USER", "USER MAPPING FOR", "VIEW", NULL}; *************** *** 1093,1098 **** psql_completion(char *text, int start, int end) --- 1124,1137 ---- COMPLETE_WITH_LIST(list_ALTERLARGEOBJECT); } + /* ALTER MATERIALIZED VIEW */ + else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 && + pg_strcasecmp(prev2_wd, "MATERIALIZED") == 0 && + pg_strcasecmp(prev_wd, "VIEW") == 0) + { + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL); + } + /* ALTER USER,ROLE */ else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 && !(pg_strcasecmp(prev2_wd, "USER") == 0 && pg_strcasecmp(prev_wd, "MAPPING") == 0) && *************** *** 1259,1264 **** psql_completion(char *text, int start, int end) --- 1298,1313 ---- COMPLETE_WITH_LIST(list_ALTERVIEW); } + /* ALTER MATERIALIZED VIEW */ + else if (pg_strcasecmp(prev4_wd, "ALTER") == 0 && + pg_strcasecmp(prev3_wd, "MATERIALIZED") == 0 && + pg_strcasecmp(prev2_wd, "VIEW") == 0) + { + static const char *const list_ALTERMATVIEW[] = + {"ALTER COLUMN", "OWNER TO", "RENAME TO", "SET SCHEMA", NULL}; + + COMPLETE_WITH_LIST(list_ALTERMATVIEW); + } /* ALTER TRIGGER , add ON */ else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 && pg_strcasecmp(prev2_wd, "TRIGGER") == 0) *************** *** 1717,1730 **** psql_completion(char *text, int start, int end) */ else if (pg_strcasecmp(prev_wd, "CLUSTER") == 0 && pg_strcasecmp(prev2_wd, "WITHOUT") != 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, "UNION SELECT 'VERBOSE'"); /* * If the previous words are CLUSTER VERBOSE produce list of tables */ else if (pg_strcasecmp(prev_wd, "VERBOSE") == 0 && pg_strcasecmp(prev2_wd, "CLUSTER") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL); /* If we have CLUSTER , then add "USING" */ else if (pg_strcasecmp(prev2_wd, "CLUSTER") == 0 && --- 1766,1779 ---- */ else if (pg_strcasecmp(prev_wd, "CLUSTER") == 0 && pg_strcasecmp(prev2_wd, "WITHOUT") != 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, "UNION SELECT 'VERBOSE'"); /* * If the previous words are CLUSTER VERBOSE produce list of tables */ else if (pg_strcasecmp(prev_wd, "VERBOSE") == 0 && pg_strcasecmp(prev2_wd, "CLUSTER") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, NULL); /* If we have CLUSTER , then add "USING" */ else if (pg_strcasecmp(prev2_wd, "CLUSTER") == 0 && *************** *** 1771,1777 **** psql_completion(char *text, int start, int end) {"CAST", "COLLATION", "CONVERSION", "DATABASE", "EXTENSION", "FOREIGN DATA WRAPPER", "FOREIGN TABLE", "SERVER", "INDEX", "LANGUAGE", "RULE", "SCHEMA", "SEQUENCE", ! "TABLE", "TYPE", "VIEW", "COLUMN", "AGGREGATE", "FUNCTION", "OPERATOR", "TRIGGER", "CONSTRAINT", "DOMAIN", "LARGE OBJECT", "TABLESPACE", "TEXT SEARCH", "ROLE", NULL}; --- 1820,1826 ---- {"CAST", "COLLATION", "CONVERSION", "DATABASE", "EXTENSION", "FOREIGN DATA WRAPPER", "FOREIGN TABLE", "SERVER", "INDEX", "LANGUAGE", "RULE", "SCHEMA", "SEQUENCE", ! "TABLE", "TYPE", "VIEW", "MATERIALIZED VIEW", "COLUMN", "AGGREGATE", "FUNCTION", "OPERATOR", "TRIGGER", "CONSTRAINT", "DOMAIN", "LARGE OBJECT", "TABLESPACE", "TEXT SEARCH", "ROLE", NULL}; *************** *** 1945,1951 **** psql_completion(char *text, int start, int end) pg_strcasecmp(prev2_wd, "INDEX") == 0 || pg_strcasecmp(prev2_wd, "CONCURRENTLY") == 0) && pg_strcasecmp(prev_wd, "ON") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL); /* If we have CREATE|UNIQUE INDEX CONCURRENTLY, then add "ON" */ else if ((pg_strcasecmp(prev3_wd, "INDEX") == 0 || pg_strcasecmp(prev2_wd, "INDEX") == 0) && --- 1994,2000 ---- pg_strcasecmp(prev2_wd, "INDEX") == 0 || pg_strcasecmp(prev2_wd, "CONCURRENTLY") == 0) && pg_strcasecmp(prev_wd, "ON") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, NULL); /* If we have CREATE|UNIQUE INDEX CONCURRENTLY, then add "ON" */ else if ((pg_strcasecmp(prev3_wd, "INDEX") == 0 || pg_strcasecmp(prev2_wd, "INDEX") == 0) && *************** *** 2051,2057 **** psql_completion(char *text, int start, int end) else if (pg_strcasecmp(prev2_wd, "CREATE") == 0 && pg_strcasecmp(prev_wd, "UNLOGGED") == 0) { ! COMPLETE_WITH_CONST("TABLE"); } /* CREATE TABLESPACE */ --- 2100,2109 ---- else if (pg_strcasecmp(prev2_wd, "CREATE") == 0 && pg_strcasecmp(prev_wd, "UNLOGGED") == 0) { ! static const char *const list_UNLOGGED[] = ! {"TABLE", "MATERIALIZED VIEW", NULL}; ! ! COMPLETE_WITH_LIST(list_UNLOGGED); } /* CREATE TABLESPACE */ *************** *** 2220,2225 **** psql_completion(char *text, int start, int end) --- 2272,2290 ---- pg_strcasecmp(prev_wd, "AS") == 0) COMPLETE_WITH_CONST("SELECT"); + /* CREATE MATERIALIZED VIEW */ + /* Complete CREATE MATERIALIZED VIEW with AS */ + else if (pg_strcasecmp(prev4_wd, "CREATE") == 0 && + pg_strcasecmp(prev3_wd, "MATERIALIZED") == 0 && + pg_strcasecmp(prev2_wd, "VIEW") == 0) + COMPLETE_WITH_CONST("AS"); + /* Complete "CREATE MATERIALIZED VIEW AS with "SELECT" */ + else if (pg_strcasecmp(prev5_wd, "CREATE") == 0 && + pg_strcasecmp(prev4_wd, "MATERIALIZED") == 0 && + pg_strcasecmp(prev3_wd, "VIEW") == 0 && + pg_strcasecmp(prev_wd, "AS") == 0) + COMPLETE_WITH_CONST("SELECT"); + /* DECLARE */ else if (pg_strcasecmp(prev2_wd, "DECLARE") == 0) { *************** *** 2346,2351 **** psql_completion(char *text, int start, int end) --- 2411,2425 ---- COMPLETE_WITH_LIST(drop_CREATE_FOREIGN); } + + /* DROP MATERIALIZED VIEW */ + else if (pg_strcasecmp(prev3_wd, "DROP") == 0 && + pg_strcasecmp(prev2_wd, "MATERIALIZED") == 0 && + pg_strcasecmp(prev_wd, "VIEW") == 0) + { + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL); + } + else if (pg_strcasecmp(prev4_wd, "DROP") == 0 && (pg_strcasecmp(prev3_wd, "AGGREGATE") == 0 || pg_strcasecmp(prev3_wd, "FUNCTION") == 0) && *************** *** 2521,2527 **** psql_completion(char *text, int start, int end) else if ((pg_strcasecmp(prev3_wd, "GRANT") == 0 || pg_strcasecmp(prev3_wd, "REVOKE") == 0) && pg_strcasecmp(prev_wd, "ON") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvf, " UNION SELECT 'DATABASE'" " UNION SELECT 'DOMAIN'" " UNION SELECT 'FOREIGN DATA WRAPPER'" --- 2595,2601 ---- else if ((pg_strcasecmp(prev3_wd, "GRANT") == 0 || pg_strcasecmp(prev3_wd, "REVOKE") == 0) && pg_strcasecmp(prev_wd, "ON") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvmf, " UNION SELECT 'DATABASE'" " UNION SELECT 'DOMAIN'" " UNION SELECT 'FOREIGN DATA WRAPPER'" *************** *** 2740,2745 **** psql_completion(char *text, int start, int end) --- 2814,2827 ---- pg_strcasecmp(prev5_wd, "REASSIGN") == 0) COMPLETE_WITH_QUERY(Query_for_list_of_roles); + /* REFRESH MATERIALIZED VIEW */ + else if (pg_strcasecmp(prev_wd, "REFRESH") == 0) + COMPLETE_WITH_CONST("MATERIALIZED VIEW"); + else if (pg_strcasecmp(prev3_wd, "REFRESH") == 0 && + pg_strcasecmp(prev2_wd, "MATERIALIZED") == 0 && + pg_strcasecmp(prev_wd, "VIEW") == 0) + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL); + /* REINDEX */ else if (pg_strcasecmp(prev_wd, "REINDEX") == 0) { *************** *** 2751,2757 **** psql_completion(char *text, int start, int end) else if (pg_strcasecmp(prev2_wd, "REINDEX") == 0) { if (pg_strcasecmp(prev_wd, "TABLE") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL); else if (pg_strcasecmp(prev_wd, "INDEX") == 0) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL); else if (pg_strcasecmp(prev_wd, "SYSTEM") == 0 || --- 2833,2839 ---- else if (pg_strcasecmp(prev2_wd, "REINDEX") == 0) { if (pg_strcasecmp(prev_wd, "TABLE") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, NULL); else if (pg_strcasecmp(prev_wd, "INDEX") == 0) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL); else if (pg_strcasecmp(prev_wd, "SYSTEM") == 0 || *************** *** 2783,2791 **** psql_completion(char *text, int start, int end) pg_strcasecmp(prev_wd, "ON") == 0)) { static const char *const list_SECURITY_LABEL[] = ! {"LANGUAGE", "SCHEMA", "SEQUENCE", "TABLE", "TYPE", "VIEW", "COLUMN", ! "AGGREGATE", "FUNCTION", "DOMAIN", "LARGE OBJECT", ! NULL}; COMPLETE_WITH_LIST(list_SECURITY_LABEL); } --- 2865,2873 ---- pg_strcasecmp(prev_wd, "ON") == 0)) { static const char *const list_SECURITY_LABEL[] = ! {"LANGUAGE", "SCHEMA", "SEQUENCE", "TABLE", "TYPE", "VIEW", ! "MATERIALIZED VIEW", "COLUMN", "AGGREGATE", "FUNCTION", "DOMAIN", ! "LARGE OBJECT", NULL}; COMPLETE_WITH_LIST(list_SECURITY_LABEL); } *************** *** 2971,2977 **** psql_completion(char *text, int start, int end) /* TRUNCATE */ else if (pg_strcasecmp(prev_wd, "TRUNCATE") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL); /* UNLISTEN */ else if (pg_strcasecmp(prev_wd, "UNLISTEN") == 0) --- 3053,3059 ---- /* TRUNCATE */ else if (pg_strcasecmp(prev_wd, "TRUNCATE") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, NULL); /* UNLISTEN */ else if (pg_strcasecmp(prev_wd, "UNLISTEN") == 0) *************** *** 3032,3038 **** psql_completion(char *text, int start, int end) * VACUUM [ FULL | FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ] */ else if (pg_strcasecmp(prev_wd, "VACUUM") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, " UNION SELECT 'FULL'" " UNION SELECT 'FREEZE'" " UNION SELECT 'ANALYZE'" --- 3114,3120 ---- * VACUUM [ FULL | FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ] */ else if (pg_strcasecmp(prev_wd, "VACUUM") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, " UNION SELECT 'FULL'" " UNION SELECT 'FREEZE'" " UNION SELECT 'ANALYZE'" *************** *** 3040,3073 **** psql_completion(char *text, int start, int end) else if (pg_strcasecmp(prev2_wd, "VACUUM") == 0 && (pg_strcasecmp(prev_wd, "FULL") == 0 || pg_strcasecmp(prev_wd, "FREEZE") == 0)) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, " UNION SELECT 'ANALYZE'" " UNION SELECT 'VERBOSE'"); else if (pg_strcasecmp(prev3_wd, "VACUUM") == 0 && pg_strcasecmp(prev_wd, "ANALYZE") == 0 && (pg_strcasecmp(prev2_wd, "FULL") == 0 || pg_strcasecmp(prev2_wd, "FREEZE") == 0)) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, " UNION SELECT 'VERBOSE'"); else if (pg_strcasecmp(prev3_wd, "VACUUM") == 0 && pg_strcasecmp(prev_wd, "VERBOSE") == 0 && (pg_strcasecmp(prev2_wd, "FULL") == 0 || pg_strcasecmp(prev2_wd, "FREEZE") == 0)) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, " UNION SELECT 'ANALYZE'"); else if (pg_strcasecmp(prev2_wd, "VACUUM") == 0 && pg_strcasecmp(prev_wd, "VERBOSE") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, " UNION SELECT 'ANALYZE'"); else if (pg_strcasecmp(prev2_wd, "VACUUM") == 0 && pg_strcasecmp(prev_wd, "ANALYZE") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, " UNION SELECT 'VERBOSE'"); else if ((pg_strcasecmp(prev_wd, "ANALYZE") == 0 && pg_strcasecmp(prev2_wd, "VERBOSE") == 0) || (pg_strcasecmp(prev_wd, "VERBOSE") == 0 && pg_strcasecmp(prev2_wd, "ANALYZE") == 0)) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL); /* WITH [RECURSIVE] */ --- 3122,3155 ---- else if (pg_strcasecmp(prev2_wd, "VACUUM") == 0 && (pg_strcasecmp(prev_wd, "FULL") == 0 || pg_strcasecmp(prev_wd, "FREEZE") == 0)) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, " UNION SELECT 'ANALYZE'" " UNION SELECT 'VERBOSE'"); else if (pg_strcasecmp(prev3_wd, "VACUUM") == 0 && pg_strcasecmp(prev_wd, "ANALYZE") == 0 && (pg_strcasecmp(prev2_wd, "FULL") == 0 || pg_strcasecmp(prev2_wd, "FREEZE") == 0)) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, " UNION SELECT 'VERBOSE'"); else if (pg_strcasecmp(prev3_wd, "VACUUM") == 0 && pg_strcasecmp(prev_wd, "VERBOSE") == 0 && (pg_strcasecmp(prev2_wd, "FULL") == 0 || pg_strcasecmp(prev2_wd, "FREEZE") == 0)) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, " UNION SELECT 'ANALYZE'"); else if (pg_strcasecmp(prev2_wd, "VACUUM") == 0 && pg_strcasecmp(prev_wd, "VERBOSE") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, " UNION SELECT 'ANALYZE'"); else if (pg_strcasecmp(prev2_wd, "VACUUM") == 0 && pg_strcasecmp(prev_wd, "ANALYZE") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, " UNION SELECT 'VERBOSE'"); else if ((pg_strcasecmp(prev_wd, "ANALYZE") == 0 && pg_strcasecmp(prev2_wd, "VERBOSE") == 0) || (pg_strcasecmp(prev_wd, "VERBOSE") == 0 && pg_strcasecmp(prev2_wd, "ANALYZE") == 0)) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, NULL); /* WITH [RECURSIVE] */ *************** *** 3082,3088 **** psql_completion(char *text, int start, int end) /* ANALYZE */ /* If the previous word is ANALYZE, produce list of tables */ else if (pg_strcasecmp(prev_wd, "ANALYZE") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tf, NULL); /* WHERE */ /* Simple case of the word before the where being the table name */ --- 3164,3170 ---- /* ANALYZE */ /* If the previous word is ANALYZE, produce list of tables */ else if (pg_strcasecmp(prev_wd, "ANALYZE") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tmf, NULL); /* WHERE */ /* Simple case of the word before the where being the table name */ *************** *** 3094,3104 **** psql_completion(char *text, int start, int end) else if (pg_strcasecmp(prev_wd, "FROM") == 0 && pg_strcasecmp(prev3_wd, "COPY") != 0 && pg_strcasecmp(prev3_wd, "\\copy") != 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvf, NULL); /* ... JOIN ... */ else if (pg_strcasecmp(prev_wd, "JOIN") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvf, NULL); /* Backslash commands */ /* TODO: \dc \dd \dl */ --- 3176,3186 ---- else if (pg_strcasecmp(prev_wd, "FROM") == 0 && pg_strcasecmp(prev3_wd, "COPY") != 0 && pg_strcasecmp(prev3_wd, "\\copy") != 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvmf, NULL); /* ... JOIN ... */ else if (pg_strcasecmp(prev_wd, "JOIN") == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvmf, NULL); /* Backslash commands */ /* TODO: \dc \dd \dl */ *************** *** 3138,3144 **** psql_completion(char *text, int start, int end) COMPLETE_WITH_QUERY(Query_for_list_of_schemas); else if (strncmp(prev_wd, "\\dp", strlen("\\dp")) == 0 || strncmp(prev_wd, "\\z", strlen("\\z")) == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvf, NULL); else if (strncmp(prev_wd, "\\ds", strlen("\\ds")) == 0) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences, NULL); else if (strncmp(prev_wd, "\\dt", strlen("\\dt")) == 0) --- 3220,3226 ---- COMPLETE_WITH_QUERY(Query_for_list_of_schemas); else if (strncmp(prev_wd, "\\dp", strlen("\\dp")) == 0 || strncmp(prev_wd, "\\z", strlen("\\z")) == 0) ! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvmf, NULL); else if (strncmp(prev_wd, "\\ds", strlen("\\ds")) == 0) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences, NULL); else if (strncmp(prev_wd, "\\dt", strlen("\\dt")) == 0) *************** *** 3150,3155 **** psql_completion(char *text, int start, int end) --- 3232,3239 ---- COMPLETE_WITH_QUERY(Query_for_list_of_roles); else if (strncmp(prev_wd, "\\dv", strlen("\\dv")) == 0) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL); + else if (strncmp(prev_wd, "\\dm", strlen("\\dm")) == 0) + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL); /* must be at end of \d list */ else if (strncmp(prev_wd, "\\d", strlen("\\d")) == 0) *** a/src/include/catalog/heap.h --- b/src/include/catalog/heap.h *************** *** 70,75 **** extern Oid heap_create_with_catalog(const char *relname, --- 70,76 ---- bool is_internal); extern void heap_create_init_fork(Relation rel); + extern bool heap_is_matview_init_fork(Relation rel); extern void heap_drop_with_catalog(Oid relid); *** a/src/include/catalog/pg_class.h --- b/src/include/catalog/pg_class.h *************** *** 66,71 **** CATALOG(pg_class,1259) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83) BKI_SCHEMA_MACRO --- 66,79 ---- bool relhasrules; /* has (or has had) any rules */ bool relhastriggers; /* has (or has had) any TRIGGERs */ bool relhassubclass; /* has (or has had) derived classes */ + + /* + * When relisvalid is set to false, a query which references the relation + * will throw an error saying the relation is not available. The initial + * intended use is to flag whether a materialized view has been populated. + * It may prove useful for other purposes. + */ + bool relisvalid; /* is valid for use in queries */ TransactionId relfrozenxid; /* all Xids < this are frozen in this rel */ #ifdef CATALOG_VARLEN /* variable-length fields start here */ *************** *** 91,97 **** typedef FormData_pg_class *Form_pg_class; * ---------------- */ ! #define Natts_pg_class 27 #define Anum_pg_class_relname 1 #define Anum_pg_class_relnamespace 2 #define Anum_pg_class_reltype 3 --- 99,105 ---- * ---------------- */ ! #define Natts_pg_class 28 #define Anum_pg_class_relname 1 #define Anum_pg_class_relnamespace 2 #define Anum_pg_class_reltype 3 *************** *** 116,124 **** typedef FormData_pg_class *Form_pg_class; #define Anum_pg_class_relhasrules 22 #define Anum_pg_class_relhastriggers 23 #define Anum_pg_class_relhassubclass 24 ! #define Anum_pg_class_relfrozenxid 25 ! #define Anum_pg_class_relacl 26 ! #define Anum_pg_class_reloptions 27 /* ---------------- * initial contents of pg_class --- 124,133 ---- #define Anum_pg_class_relhasrules 22 #define Anum_pg_class_relhastriggers 23 #define Anum_pg_class_relhassubclass 24 ! #define Anum_pg_class_relisvalid 25 ! #define Anum_pg_class_relfrozenxid 26 ! #define Anum_pg_class_relacl 27 ! #define Anum_pg_class_reloptions 28 /* ---------------- * initial contents of pg_class *************** *** 130,142 **** typedef FormData_pg_class *Form_pg_class; */ /* Note: "3" in the relfrozenxid column stands for FirstNormalTransactionId */ ! DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 0 0 0 0 0 0 0 f f p r 30 0 t f f f f 3 _null_ _null_ )); DESCR(""); ! DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 0 f f p r 21 0 f f f f f 3 _null_ _null_ )); DESCR(""); ! DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 0 0 0 0 0 0 0 f f p r 27 0 t f f f f 3 _null_ _null_ )); DESCR(""); ! DATA(insert OID = 1259 ( pg_class PGNSP 83 0 PGUID 0 0 0 0 0 0 0 0 f f p r 27 0 t f f f f 3 _null_ _null_ )); DESCR(""); --- 139,151 ---- */ /* Note: "3" in the relfrozenxid column stands for FirstNormalTransactionId */ ! DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 0 0 0 0 0 0 0 f f p r 30 0 t f f f f t 3 _null_ _null_ )); DESCR(""); ! DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 0 f f p r 21 0 f f f f f t 3 _null_ _null_ )); DESCR(""); ! DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 0 0 0 0 0 0 0 f f p r 27 0 t f f f f t 3 _null_ _null_ )); DESCR(""); ! DATA(insert OID = 1259 ( pg_class PGNSP 83 0 PGUID 0 0 0 0 0 0 0 0 f f p r 28 0 t f f f f t 3 _null_ _null_ )); DESCR(""); *************** *** 147,152 **** DESCR(""); --- 156,162 ---- #define RELKIND_VIEW 'v' /* view */ #define RELKIND_COMPOSITE_TYPE 'c' /* composite type */ #define RELKIND_FOREIGN_TABLE 'f' /* foreign table */ + #define RELKIND_MATVIEW 'm' /* materialized view */ #define RELPERSISTENCE_PERMANENT 'p' /* regular table */ #define RELPERSISTENCE_UNLOGGED 'u' /* unlogged permanent table */ *** a/src/include/commands/createas.h --- b/src/include/commands/createas.h *************** *** 19,24 **** --- 19,28 ---- #include "tcop/dest.h" + extern Query *SetupForCreateTableAs(Query *query, IntoClause *into, + const char *queryString, + ParamListInfo params, DestReceiver *dest); + extern void ExecCreateTableAs(CreateTableAsStmt *stmt, const char *queryString, ParamListInfo params, char *completionTag); *** a/src/include/commands/explain.h --- b/src/include/commands/explain.h *************** *** 63,74 **** extern void ExplainInitState(ExplainState *es); extern TupleDesc ExplainResultDesc(ExplainStmt *stmt); extern void ExplainOneUtility(Node *utilityStmt, IntoClause *into, ! ExplainState *es, ! const char *queryString, ParamListInfo params); extern void ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ! ExplainState *es, ! const char *queryString, ParamListInfo params); extern void ExplainPrintPlan(ExplainState *es, QueryDesc *queryDesc); --- 63,74 ---- extern TupleDesc ExplainResultDesc(ExplainStmt *stmt); extern void ExplainOneUtility(Node *utilityStmt, IntoClause *into, ! ExplainState *es, const char *queryString, ! DestReceiver *dest, ParamListInfo params); extern void ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ! ExplainState *es, const char *queryString, ! DestReceiver *dest, ParamListInfo params); extern void ExplainPrintPlan(ExplainState *es, QueryDesc *queryDesc); *** /dev/null --- b/src/include/commands/matview.h *************** *** 0 **** --- 1,25 ---- + /*------------------------------------------------------------------------- + * + * matview.h + * prototypes for matview.c. + * + * + * Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * src/include/commands/matview.h + * + *------------------------------------------------------------------------- + */ + #ifndef MATVIEW_H + #define MATVIEW_H + + #include "nodes/params.h" + #include "tcop/dest.h" + + extern void ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString, + ParamListInfo params, char *completionTag); + + extern DestReceiver *CreateTransientRelDestReceiver(Oid oid); + + #endif /* MATVIEW_H */ *** a/src/include/commands/tablecmds.h --- b/src/include/commands/tablecmds.h *************** *** 50,55 **** extern void CheckTableNotInUse(Relation rel, const char *stmt); --- 50,56 ---- extern void ExecuteTruncate(TruncateStmt *stmt); extern void SetRelationHasSubclass(Oid relationId, bool relhassubclass); + extern void SetRelationIsValid(Oid relationId, bool relisvalid); extern Oid renameatt(RenameStmt *stmt); *************** *** 78,81 **** extern void AtEOSubXact_on_commit_actions(bool isCommit, --- 79,84 ---- extern void RangeVarCallbackOwnsTable(const RangeVar *relation, Oid relId, Oid oldRelId, void *arg); + extern bool isQueryUsingTempRelation(Query *query); + #endif /* TABLECMDS_H */ *** a/src/include/commands/view.h --- b/src/include/commands/view.h *************** *** 18,21 **** --- 18,23 ---- extern Oid DefineView(ViewStmt *stmt, const char *queryString); + extern void StoreViewQuery(Oid viewOid, Query *viewParse, bool replace); + #endif /* VIEW_H */ *** a/src/include/nodes/nodes.h --- b/src/include/nodes/nodes.h *************** *** 361,366 **** typedef enum NodeTag --- 361,367 ---- T_AlterExtensionContentsStmt, T_CreateEventTrigStmt, T_AlterEventTrigStmt, + T_RefreshMatViewStmt, /* * TAGS FOR PARSE TREE NODES (parsenodes.h) *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** *** 703,708 **** typedef struct RangeTblEntry --- 703,709 ---- */ Oid relid; /* OID of the relation */ char relkind; /* relation kind (see pg_class.relkind) */ + bool isResultRel; /* used in target of SELECT INTO or similar */ /* * Fields valid for a subquery RTE (else NULL): *************** *** 1125,1130 **** typedef enum ObjectType --- 1126,1132 ---- OBJECT_INDEX, OBJECT_LANGUAGE, OBJECT_LARGEOBJECT, + OBJECT_MATVIEW, OBJECT_OPCLASS, OBJECT_OPERATOR, OBJECT_OPFAMILY, *************** *** 2436,2441 **** typedef struct ExplainStmt --- 2438,2445 ---- * A query written as CREATE TABLE AS will produce this node type natively. * A query written as SELECT ... INTO will be transformed to this form during * parse analysis. + * A query written as CREATE MATERIALIZED view will produce this node type, + * during parse analysis, since it needs all the same data. * * The "query" field is handled similarly to EXPLAIN, though note that it * can be a SELECT or an EXECUTE, but not other DML statements. *************** *** 2446,2455 **** typedef struct CreateTableAsStmt --- 2450,2470 ---- NodeTag type; Node *query; /* the query (see comments above) */ IntoClause *into; /* destination table */ + ObjectType relkind; /* type of object */ bool is_select_into; /* it was written as SELECT INTO */ } CreateTableAsStmt; /* ---------------------- + * REFRESH MATERIALIZED VIEW Statement + * ---------------------- + */ + typedef struct RefreshMatViewStmt + { + NodeTag type; + RangeVar *relation; /* relation to insert into */ + } RefreshMatViewStmt; + + /* ---------------------- * Checkpoint Statement * ---------------------- */ *************** *** 2506,2512 **** typedef struct ConstraintsSetStmt typedef struct ReindexStmt { NodeTag type; ! ObjectType kind; /* OBJECT_INDEX, OBJECT_TABLE, OBJECT_DATABASE */ RangeVar *relation; /* Table or index to reindex */ const char *name; /* name of database to reindex */ bool do_system; /* include system tables in database case */ --- 2521,2527 ---- typedef struct ReindexStmt { NodeTag type; ! ObjectType kind; /* OBJECT_INDEX, OBJECT_TABLE, etc. */ RangeVar *relation; /* Table or index to reindex */ const char *name; /* name of database to reindex */ bool do_system; /* include system tables in database case */ *** a/src/include/nodes/primnodes.h --- b/src/include/nodes/primnodes.h *************** *** 80,86 **** typedef struct RangeVar } RangeVar; /* ! * IntoClause - target information for SELECT INTO and CREATE TABLE AS */ typedef struct IntoClause { --- 80,87 ---- } RangeVar; /* ! * IntoClause - target information for SELECT INTO, CREATE TABLE AS, and ! * CREATE MATERIALIZED VIEW */ typedef struct IntoClause { *************** *** 92,97 **** typedef struct IntoClause --- 93,99 ---- OnCommitAction onCommit; /* what do we do at COMMIT? */ char *tableSpaceName; /* table space to use, or NULL */ bool skipData; /* true for WITH NO DATA */ + char relkind; /* RELKIND_RELATION or RELKIND_MATVIEW */ } IntoClause; *** a/src/include/parser/kwlist.h --- b/src/include/parser/kwlist.h *************** *** 232,237 **** PG_KEYWORD("location", LOCATION, UNRESERVED_KEYWORD) --- 232,238 ---- PG_KEYWORD("lock", LOCK_P, UNRESERVED_KEYWORD) PG_KEYWORD("mapping", MAPPING, UNRESERVED_KEYWORD) PG_KEYWORD("match", MATCH, UNRESERVED_KEYWORD) + PG_KEYWORD("materialized", MATERIALIZED, UNRESERVED_KEYWORD) PG_KEYWORD("maxvalue", MAXVALUE, UNRESERVED_KEYWORD) PG_KEYWORD("minute", MINUTE_P, UNRESERVED_KEYWORD) PG_KEYWORD("minvalue", MINVALUE, UNRESERVED_KEYWORD) *************** *** 301,306 **** PG_KEYWORD("recheck", RECHECK, UNRESERVED_KEYWORD) --- 302,308 ---- PG_KEYWORD("recursive", RECURSIVE, UNRESERVED_KEYWORD) PG_KEYWORD("ref", REF, UNRESERVED_KEYWORD) PG_KEYWORD("references", REFERENCES, RESERVED_KEYWORD) + PG_KEYWORD("refresh", REFRESH, UNRESERVED_KEYWORD) PG_KEYWORD("reindex", REINDEX, UNRESERVED_KEYWORD) PG_KEYWORD("relative", RELATIVE_P, UNRESERVED_KEYWORD) PG_KEYWORD("release", RELEASE, UNRESERVED_KEYWORD) *** a/src/include/storage/lwlock.h --- b/src/include/storage/lwlock.h *************** *** 79,84 **** typedef enum LWLockId --- 79,85 ---- SerializablePredicateLockListLock, OldSerXidLock, SyncRepLock, + UnloggedMatViewInitLock, /* Individual lock IDs end here */ FirstBufMappingLock, FirstLockMgrLock = FirstBufMappingLock + NUM_BUFFER_PARTITIONS, *** a/src/include/tcop/dest.h --- b/src/include/tcop/dest.h *************** *** 93,99 **** typedef enum DestTuplestore, /* results sent to Tuplestore */ DestIntoRel, /* results sent to relation (SELECT INTO) */ DestCopyOut, /* results sent to COPY TO code */ ! DestSQLFunction /* results sent to SQL-language func mgr */ } CommandDest; /* ---------------- --- 93,100 ---- DestTuplestore, /* results sent to Tuplestore */ DestIntoRel, /* results sent to relation (SELECT INTO) */ DestCopyOut, /* results sent to COPY TO code */ ! DestSQLFunction, /* results sent to SQL-language func mgr */ ! DestTransientRel /* results sent to transient relation */ } CommandDest; /* ---------------- *** a/src/include/tcop/utility.h --- b/src/include/tcop/utility.h *************** *** 52,55 **** extern bool CommandIsReadOnly(Node *parsetree); --- 52,57 ---- extern void CheckRelationOwnership(RangeVar *rel, bool noCatalogs); + extern bool RelationIsFlaggedAsValid(Oid relid); + #endif /* UTILITY_H */ *** a/src/pl/plpgsql/src/pl_comp.c --- b/src/pl/plpgsql/src/pl_comp.c *************** *** 1760,1770 **** plpgsql_parse_cwordtype(List *idents) classStruct = (Form_pg_class) GETSTRUCT(classtup); /* ! * It must be a relation, sequence, view, composite type, or foreign table */ if (classStruct->relkind != RELKIND_RELATION && classStruct->relkind != RELKIND_SEQUENCE && classStruct->relkind != RELKIND_VIEW && classStruct->relkind != RELKIND_COMPOSITE_TYPE && classStruct->relkind != RELKIND_FOREIGN_TABLE) goto done; --- 1760,1772 ---- classStruct = (Form_pg_class) GETSTRUCT(classtup); /* ! * It must be a relation, sequence, view, materialized view, composite ! * type, or foreign table */ if (classStruct->relkind != RELKIND_RELATION && classStruct->relkind != RELKIND_SEQUENCE && classStruct->relkind != RELKIND_VIEW && + classStruct->relkind != RELKIND_MATVIEW && classStruct->relkind != RELKIND_COMPOSITE_TYPE && classStruct->relkind != RELKIND_FOREIGN_TABLE) goto done; *************** *** 1982,1991 **** build_row_from_class(Oid classOid) classStruct = RelationGetForm(rel); relname = RelationGetRelationName(rel); ! /* accept relation, sequence, view, composite type, or foreign table */ if (classStruct->relkind != RELKIND_RELATION && classStruct->relkind != RELKIND_SEQUENCE && classStruct->relkind != RELKIND_VIEW && classStruct->relkind != RELKIND_COMPOSITE_TYPE && classStruct->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, --- 1984,1997 ---- classStruct = RelationGetForm(rel); relname = RelationGetRelationName(rel); ! /* ! * Accept relation, sequence, view, materialized view, composite type, or ! * foreign table. ! */ if (classStruct->relkind != RELKIND_RELATION && classStruct->relkind != RELKIND_SEQUENCE && classStruct->relkind != RELKIND_VIEW && + classStruct->relkind != RELKIND_MATVIEW && classStruct->relkind != RELKIND_COMPOSITE_TYPE && classStruct->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, *** a/src/pl/tcl/pltcl.c --- b/src/pl/tcl/pltcl.c *************** *** 501,506 **** pltcl_init_load_unknown(Tcl_Interp *interp) --- 501,507 ---- return; /* must be table or view, else ignore */ if (!(pmrel->rd_rel->relkind == RELKIND_RELATION || + pmrel->rd_rel->relkind == RELKIND_MATVIEW || pmrel->rd_rel->relkind == RELKIND_VIEW)) { relation_close(pmrel, AccessShareLock); *** /dev/null --- b/src/test/regress/expected/matview.out *************** *** 0 **** --- 1,176 ---- + -- create a table to use as a basis for views and materialized views in various combinations + CREATE TABLE t (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL); + INSERT INTO t VALUES + (1, 'x', 2), + (2, 'x', 3), + (3, 'y', 5), + (4, 'y', 7), + (5, 'z', 11); + -- we want a view based on the table, too, since views present additional challenges + CREATE VIEW tv AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type; + SELECT * FROM tv; + type | totamt + ------+-------- + y | 12 + z | 11 + x | 5 + (3 rows) + + -- create a materialized view with no data, and confirm correct behavior + CREATE MATERIALIZED VIEW tm AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA; + SELECT * FROM tm; + ERROR: materialized view "tm" has not been populated + HINT: Use the REFRESH MATERIALIZED VIEW command. + REFRESH MATERIALIZED VIEW tm; + SELECT * FROM tm; + type | totamt + ------+-------- + y | 12 + z | 11 + x | 5 + (3 rows) + + -- create various views + CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv; + SELECT * FROM tvm; + type | totamt + ------+-------- + y | 12 + z | 11 + x | 5 + (3 rows) + + CREATE MATERIALIZED VIEW tmm AS SELECT sum(totamt) AS grandtot FROM tm; + CREATE MATERIALIZED VIEW tvmm AS SELECT sum(totamt) AS grandtot FROM tvm; + CREATE VIEW tvv AS SELECT sum(totamt) AS grandtot FROM tv; + CREATE MATERIALIZED VIEW tvvm AS SELECT * FROM tvv; + -- modify the underlying table data + INSERT INTO t VALUES (6, 'z', 13); + -- confirm pre- and post-refresh contents of fairly simple materialized views + SELECT * FROM tm ORDER BY type; + type | totamt + ------+-------- + x | 5 + y | 12 + z | 11 + (3 rows) + + SELECT * FROM tvm ORDER BY type; + type | totamt + ------+-------- + x | 5 + y | 12 + z | 11 + (3 rows) + + REFRESH MATERIALIZED VIEW tm; + REFRESH MATERIALIZED VIEW tvm; + SELECT * FROM tm ORDER BY type; + type | totamt + ------+-------- + x | 5 + y | 12 + z | 24 + (3 rows) + + SELECT * FROM tvm ORDER BY type; + type | totamt + ------+-------- + x | 5 + y | 12 + z | 24 + (3 rows) + + -- confirm pre- and post-refresh contents of nested materialized views + SELECT * FROM tmm; + grandtot + ---------- + 28 + (1 row) + + SELECT * FROM tvmm; + grandtot + ---------- + 28 + (1 row) + + SELECT * FROM tvvm; + grandtot + ---------- + 28 + (1 row) + + REFRESH MATERIALIZED VIEW tmm; + REFRESH MATERIALIZED VIEW tvmm; + REFRESH MATERIALIZED VIEW tvvm; + SELECT * FROM tmm; + grandtot + ---------- + 41 + (1 row) + + SELECT * FROM tvmm; + grandtot + ---------- + 41 + (1 row) + + SELECT * FROM tvvm; + grandtot + ---------- + 41 + (1 row) + + -- test diemv when the mv does not exist + DROP MATERIALIZED VIEW IF EXISTS tum; + NOTICE: materialized view "tum" does not exist, skipping + -- make sure that an unlogged materialized view works (in the absence of a crash) + CREATE UNLOGGED MATERIALIZED VIEW tum AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA; + SELECT * FROM tum; + ERROR: materialized view "tum" has not been populated + HINT: Use the REFRESH MATERIALIZED VIEW command. + REFRESH MATERIALIZED VIEW tum; + SELECT * FROM tum; + type | totamt + ------+-------- + y | 12 + z | 24 + x | 5 + (3 rows) + + TRUNCATE tum; + SELECT * FROM tum; + ERROR: materialized view "tum" has not been populated + HINT: Use the REFRESH MATERIALIZED VIEW command. + REFRESH MATERIALIZED VIEW tum; + SELECT * FROM tum; + type | totamt + ------+-------- + y | 12 + z | 24 + x | 5 + (3 rows) + + -- test diemv when the mv does exist + DROP MATERIALIZED VIEW IF EXISTS tum; + -- make sure that dependencies are reported properly when they block the drop + DROP TABLE t; + ERROR: cannot drop table t because other objects depend on it + DETAIL: view tv depends on table t + view tvv depends on view tv + materialized view tm depends on table t + materialized view tmm depends on materialized view tm + materialized view tvm depends on table t + materialized view tvmm depends on materialized view tvm + materialized view tvvm depends on table t + HINT: Use DROP ... CASCADE to drop the dependent objects too. + -- make sure dependencies are dropped and reported + DROP TABLE t CASCADE; + NOTICE: drop cascades to 7 other objects + DETAIL: drop cascades to view tv + drop cascades to view tvv + drop cascades to materialized view tm + drop cascades to materialized view tmm + drop cascades to materialized view tvm + drop cascades to materialized view tvmm + drop cascades to materialized view tvvm *** a/src/test/regress/parallel_schedule --- b/src/test/regress/parallel_schedule *************** *** 88,94 **** test: privileges security_label collate # ---------- # Another group of parallel tests # ---------- ! test: misc alter_generic # rules cannot run concurrently with any test that creates a view test: rules --- 88,94 ---- # ---------- # Another group of parallel tests # ---------- ! test: misc alter_generic matview # rules cannot run concurrently with any test that creates a view test: rules *** a/src/test/regress/serial_schedule --- b/src/test/regress/serial_schedule *************** *** 97,102 **** test: security_label --- 97,103 ---- test: collate test: misc test: alter_generic + test: matview test: rules test: event_trigger test: select_views *** /dev/null --- b/src/test/regress/sql/matview.sql *************** *** 0 **** --- 1,70 ---- + -- create a table to use as a basis for views and materialized views in various combinations + CREATE TABLE t (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL); + INSERT INTO t VALUES + (1, 'x', 2), + (2, 'x', 3), + (3, 'y', 5), + (4, 'y', 7), + (5, 'z', 11); + + -- we want a view based on the table, too, since views present additional challenges + CREATE VIEW tv AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type; + SELECT * FROM tv; + + -- create a materialized view with no data, and confirm correct behavior + CREATE MATERIALIZED VIEW tm AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA; + SELECT * FROM tm; + REFRESH MATERIALIZED VIEW tm; + SELECT * FROM tm; + + -- create various views + CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv; + SELECT * FROM tvm; + CREATE MATERIALIZED VIEW tmm AS SELECT sum(totamt) AS grandtot FROM tm; + CREATE MATERIALIZED VIEW tvmm AS SELECT sum(totamt) AS grandtot FROM tvm; + CREATE VIEW tvv AS SELECT sum(totamt) AS grandtot FROM tv; + CREATE MATERIALIZED VIEW tvvm AS SELECT * FROM tvv; + + -- modify the underlying table data + INSERT INTO t VALUES (6, 'z', 13); + + -- confirm pre- and post-refresh contents of fairly simple materialized views + SELECT * FROM tm ORDER BY type; + SELECT * FROM tvm ORDER BY type; + REFRESH MATERIALIZED VIEW tm; + REFRESH MATERIALIZED VIEW tvm; + SELECT * FROM tm ORDER BY type; + SELECT * FROM tvm ORDER BY type; + + -- confirm pre- and post-refresh contents of nested materialized views + SELECT * FROM tmm; + SELECT * FROM tvmm; + SELECT * FROM tvvm; + REFRESH MATERIALIZED VIEW tmm; + REFRESH MATERIALIZED VIEW tvmm; + REFRESH MATERIALIZED VIEW tvvm; + SELECT * FROM tmm; + SELECT * FROM tvmm; + SELECT * FROM tvvm; + + -- test diemv when the mv does not exist + DROP MATERIALIZED VIEW IF EXISTS tum; + + -- make sure that an unlogged materialized view works (in the absence of a crash) + CREATE UNLOGGED MATERIALIZED VIEW tum AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA; + SELECT * FROM tum; + REFRESH MATERIALIZED VIEW tum; + SELECT * FROM tum; + TRUNCATE tum; + SELECT * FROM tum; + REFRESH MATERIALIZED VIEW tum; + SELECT * FROM tum; + + -- test diemv when the mv does exist + DROP MATERIALIZED VIEW IF EXISTS tum; + + -- make sure that dependencies are reported properly when they block the drop + DROP TABLE t; + + -- make sure dependencies are dropped and reported + DROP TABLE t CASCADE;