PostgreSQL – Querying Materialized View Definition

information-schemamaterialized-viewpostgresqlpostgresql-9.4

I'm wondering how to query the definition of a materialized view in Postgres. For reference, what I hoped to do is very similar to what you can do for a regular view:

SELECT * FROM information_schema.views WHERE table_name = 'some_view';

which gives you the following columns:

table_catalog
table_schema
table_name
view_definition
check_option
is_updatable
is_insertable_into
is_trigger_updatable
is_trigger_deletable
is_trigger_insertable_into

Is this possible for materialized views?

From my research so far, it appears that materialized views are deliberately excluded from information_schema, because

The information_schema can only show objects that exist in the SQL standard.

(http://www.postgresql.org/message-id/3794.1412980686@sss.pgh.pa.us)

Since they appear to being entirely excluded from information_schema, I'm not sure how to go about this, but what I'd like to do is twofold:

Query whether a particular materialized view exists. (So far the only way I've found to do this is try creating a mat view with the same name and see if it blows up.)
And then query the definition of the materialized view (similar to the view_definition column on information_schema.views).

Best Answer

Looks like 9.3 and up you can do:

select * from pg_matviews;
select * from pg_matviews where matviewname = 'view_name';

More info found here: https://stackoverflow.com/questions/29297296/postgres-see-query-used-to-create-materialized-view

Related Solutions

Error updating a master table of a materialized view

Just a guess, but since you're doing a fast refresh, you have an MV Log, which is what I would presume MAS$3 is. Perhaps the column user_data_20 has been added recently, and is not in the MV Log? You might have to rebuild your MV log on one or both tables.

Snapshot too old from materialized view refresh job

Hints

Just a quick summary of possible ways to provide you with additional information.

Job History

You might get additional information if you query the job history:

SELECT  /*+ RULE */
    A.JOB JOB#, 
    SCHEMA_USER MVIEW_OWNER,
    substr(DECODE(SUBSTR(WHAT,INSTR(WHAT,'.',1,2)+2,INSTR(WHAT,'”',1,4)-4-INSTR(WHAT,'.',1,2)+2),NULL,SUBSTR(WHAT,1,80), 
    SUBSTR(WHAT,INSTR(WHAT,'.',1,2)+2,INSTR(WHAT,'”',1,4)-4-INSTR(WHAT,'.',1,2)+2)),0,80) JOB_DESCRIPTION,
    to_char(LAST_DATE, 'yyyy-mm-dd hh24:mi:ss') LAST_RUN_DATE, 
    to_char(NEXT_DATE, 'yyyy-mm-dd hh24:mi:ss') NEXT_SCHED_RUN_DATE, 
    DECODE(BROKEN,'Y','YES','N','NO',' ') IS_BROKEN,
    FAILURES,  
    RUNNING IS_RUNNING, 
    B.SID SID 
FROM DBA_JOBS A LEFT OUTER JOIN 
    (SELECT /*+ RULE */ JOB,'YES' RUNNING,SID FROM DBA_JOBS_RUNNING ) B 
ON A.JOB = B.JOB 
ORDER BY SCHEMA_USER, JOB_DESCRIPTION;

Reference: My refresh materialized view is not refreshing – Part 1

Depending on what results you get, you might be able to look for additional information.

Rollback vs. Undo

Is your database running with Rollback Undo Segments? You might want to consider switching to the Undo Segments instead (10g+).

To enable SMU, set the UNDO_MANAGEMENT parameter to TRUE

Why?

Earlier releases of Oracle Database used rollback segments to store undo. Oracle9i introduced automatic undo management, which simplifies undo space management by eliminating the complexities associated with rollback segment management. Oracle strongly recommends (Oracle 9i and on words) to use undo tablespace (automatic undo management) to manage undo rather than rollback segments.

Reference: What is the difference between Rollback and Undo Tablespace?, section Undo vs. Rollback

Advisor Framework

If you are running in Undo Segements mode then you can retrieve advisory information about the Undo configuration from the system with the following script (Advisor Framework):

set serveroutput on

 DECLARE
  v VARCHAR2(300); 
 BEGIN
   v := dbms_undo_adv.undo_advisor(SYSDATE-1/1440, SYSDATE, 1);
   dbms_output.put_line(v);
 END;
 /

Reference: Oracle DBMS_UNDO_ADV

If you find your database doesn't have sufficient Undo Segments, then you might have to resize your UNDO tablespace to accommodate for the large amount of modifications/moves your MV is performing.

Related Question