What a view page tells you

The symptom

The finance dashboard reads billing.monthly_revenue, and your ticket is to rename a column in billing.invoices. Nobody knows what else reads the view, and the materialized copy the monthly report uses shows September's numbers in the third week of October.

A view is a saved query

A view is a query with a name. It holds no rows. SELECT * FROM billing.monthly_revenue runs the query inside it every time, against today's invoices and payments. A materialized view is the opposite: the result written to disk once, at a time you chose, which you can index like a table. It is data with a date, fast and exactly as old as its last refresh.

The sentence under the header is the summary: Reads 3 tables and returns 5 columns: revenue per month and plan, in USD only. Runs with the owner's permissions. 2 objects depend on it. The summary and the sections below are Pro. On Free the page stops after Definition, with the names of the Pro sections over a blurred silhouette (Object pages shows the gate). A materialized view created or last refreshed WITH NO DATA gets a verdict before anything else: it holds no snapshot, so refresh it before anything reads it.

Snapshot

Materialized views only, so not in the figure.

The headline is the snapshot's age, or Age unknown when Seqora has seen no refresh, or Empty: never refreshed. A timeline of refreshes follows, with Refreshes in 7d, Typical time, Slowest and Readers blocked.

One button opens the refresh statement in a console, where you run it and Seqora records the run. It reads Refresh now for REFRESH MATERIALIZED VIEW, which blocks readers until the new snapshot is written, or Refresh concurrently when the view can take CONCURRENTLY, which builds the new snapshot beside the old one and swaps the rows that changed, so readers keep reading meanwhile. That needs a unique index on the view with no WHERE clause and no expression, and one plain refresh first. The chip says Can refresh without blocking readers (unique index on month, plan) or Refreshing blocks readers: it has no unique index.

Is it still current? has one button, Compare with live data. It runs the view's query now and compares the result with the snapshot row by row, in a read-only transaction that stops after 12 seconds: 4 rows in the snapshot no longer match the query, and 1 new row is missing.

Postgres doesn't record when a materialized view was refreshed. The timeline combines the refreshes Seqora ran from its console, pg_cron's run log, and changes Seqora noticed between visits. With pg_stat_statements installed, one more line counts refreshes from every client: All clients: 961 refreshes since 40 days ago, mean 1.9 s, slowest 4.3 s. Without it, Typical time and Slowest come only from the timeline, and the footnote says pg_stat_statements isn't installed.

Lineage

Three columns. Reads from is a card per source table with the columns the query touches, each tagged with its job: a filter such as = 'USD' on currency, join, group. Returns says how each column is made: plan as is, gross_cents a sum, net_cents computed from two other outputs.

Used by is the part to read before you change anything. Here it lists billing.revenue_dashboard, a view that reads all 5 columns, and billing.export_revenue(), a function drawn dashed because Postgres doesn't record it and Seqora found it by searching the function's code. The note under them is the consequence: changing a source column's type is blocked while this view exists, and dropping it breaks both dependents.

DROP VIEW refuses without CASCADE, and CASCADE drops the dependents too. CREATE OR REPLACE VIEW can add columns at the end but can't rename, remove or retype one, so a change to month means recreating this view and every view above it, in order. The dashed function has no such protection: drop the view and export_revenue() fails the next time it runs.

Query shape

The query as a row of steps: invoices, join accounts, left join payments drawn dashed because it is outer, where currency = 'USD', group by month, plan. Under the steps, whether you can write through the view: Grouping makes it read-only: you can't insert or update through it.

Beside it, the security verdict. A view runs with its owner's permissions unless you say otherwise. Here invoices has row security, and everyone who can read the view sees every row reporting_owner can, so the policy is bypassed. The panel says so, Runs with the owner's permissions, and offers the fix:

ALTER VIEW billing.monthly_revenue SET (security_invoker = on);

security_invoker makes the view run as whoever reads it, with their grants and their row security. It needs PostgreSQL 15 or later. Access, All properties and DDL fold out below.

Caveats

• Dashed dependents come from searching function bodies in this database. Application code is never searched, so an empty Used by doesn't mean unused.
• Compare with live data is a real query against the source tables, and it gives up after 12 seconds. Volatile functions such as now() always differ.
• MySQL and MariaDB have no materialized views, and their views carry a DEFINER and SQL SECURITY instead of security_invoker. SQLite records no dependencies, so lineage there is read from the SQL text.