# DDL Reference
Complete reference for TVIEW creation and management with FraiseQL patterns.
**Version**: 0.1.0-beta.1 • **Last Updated**: December 11, 2025
## Overview
pg_tviews provides transactional materialized views through DDL and SQL functions. TVIEWs follow FraiseQL's trinity identifier pattern and CQRS architecture.
## Creating TVIEWs
### DDL Method: CREATE TABLE tv_* AS SELECT
**Syntax**:
```sql
CREATE TABLE tv_<entity> AS
SELECT
<pk_column> as pk_<entity>, -- Required: lineage root
<uuid_column> as id, -- Optional: GraphQL ID
<other_columns>, -- Optional: cascade FKs, filtering FKs
<jsonb_data> as data -- Required: JSONB read model
FROM tb_<entity> t
[LEFT JOIN tb_<related> r ON ...]
[WHERE ...]
[GROUP BY ...];
```
**Note**: The ProcessUtility hook automatically intercepts `CREATE TABLE tv_* AS SELECT` statements and converts them to TVIEW creation. This provides DDL-like syntax for TVIEW creation.
### Function Method: pg_tviews_create()
**Syntax**:
```sql
SELECT pg_tviews_create('tv_<entity>', '
SELECT
<pk_column> as pk_<entity>, -- Required: lineage root
<uuid_column> as id, -- Optional: GraphQL ID
<other_columns>, -- Optional: cascade FKs, filtering FKs
<jsonb_data> as data -- Required: JSONB read model
FROM tb_<entity> t
[LEFT JOIN tb_<related> r ON ...]
[WHERE ...]
[GROUP BY ...]
');
```
**Note**: This is the programmatic approach that can be used in scripts and applications.
### FraiseQL Naming Conventions
Following FraiseQL patterns:
- **TVIEW name**: `tv_<entity>` (e.g., `tv_post`, `tv_user`)
- **Source tables**: `tb_<entity>` (e.g., `tb_post`, `tb_user`)
- **Backing view**: `tviews.<schema>__tv_<entity>` (automatically created in
pg_tviews' own schema, named after the TVIEW's table and fitted to 63 bytes;
`tviews.registry.view` reports it). The application's own `v_<entity>` view is
left alone: a TVIEW can materialize it (`pg_tviews_create('tv_order', 'SELECT * FROM
v_order')`) or read views that read it. Its privileges follow the TVIEW's table:
whoever can `SELECT` from `tv_<entity>` can `SELECT` from it (see
[Privileges](#privileges))
- **Embedding another TVIEW**: read its table `tv_<entity>` (`JOIN tv_user u ON
u.pk_user = p.fk_user`). Any other read of another TVIEW's table, directly or
through views, is traced like a read of a base table: a view aggregating
`tv_line` by `order_id`, joined on `order_id = o.id`, or a correlated subquery on
a column other than its key. A refresh of the inner TVIEW then refreshes the rows
of this one it reaches, in the same flush; a read nothing links to the key goes
through the `uncascaded_policy`
- **Entity name**: Derived from TVIEW name by removing `tv_` prefix
### Required Columns
#### Primary Key Column (`pk_<entity>`)
Every TVIEW must have exactly one primary key column named `pk_<entity>`:
```sql
-- Correct: Follows trinity pattern
SELECT p.pk_post as pk_post, ... FROM tb_post p
-- Incorrect: Wrong name
SELECT tb_post.id as pk_post, ... FROM tb_post -- ERROR: not lineage root
-- Incorrect: Wrong type
SELECT tb_post.id::bigint as pk_post, ... FROM tb_post -- ERROR: not original PK
```
**Requirements**:
- Must be named `pk_<entity>` where `<entity>` matches TVIEW name
- Must be the actual primary key from source table (no casting)
- Used for lineage tracking and cascade propagation
#### JSONB Data Column (`data`)
Every TVIEW must have exactly one JSONB column named `data`:
```sql
-- Correct: JSONB read model
jsonb_build_object(
'id', p.id,
'title', p.title,
'author', jsonb_build_object('id', u.id, 'name', u.name)
) as data
-- Incorrect: Wrong type
jsonb_build_object(...)::text as data -- ERROR: not JSONB
-- Incorrect: Wrong name
jsonb_build_object(...) as json_data -- ERROR: not named 'data'
```
**Best Practices**:
- Include all GraphQL-required fields
- Use nested objects for relationships
- Include UUIDs for GraphQL filtering
- Add computed fields as needed
### Optional Columns
#### Trinity Identifiers
Following FraiseQL's trinity pattern:
```sql
SELECT
p.pk_post as pk_post, -- Required: lineage root
p.id as id, -- Optional: GraphQL ID (UUID)
p.identifier as identifier, -- Optional: SEO slug (text)
p.fk_user as fk_user, -- Optional: cascade FK (integer)
u.id as user_id, -- Optional: filtering FK (UUID)
jsonb_build_object(...) as data
FROM tb_post p
JOIN tb_user u ON p.fk_user = u.pk_user;
```
#### Cascade Foreign Keys
Include all foreign keys used for cascade propagation:
```sql
-- Include FKs for automatic cascade updates
SELECT
p.pk_post,
p.fk_user, -- Enables user → post cascades
p.fk_category, -- Enables category → post cascades
jsonb_build_object(...) as data
FROM tb_post p;
```
#### Filtering Foreign Keys
Include UUID FKs for efficient GraphQL filtering:
```sql
-- Include UUID FKs for WHERE clauses
SELECT
p.pk_post,
u.id as user_id, -- Filter posts by user UUID
c.id as category_id, -- Filter posts by category UUID
jsonb_build_object(...) as data
FROM tb_post p
JOIN tb_user u ON p.fk_user = u.pk_user
JOIN tb_category c ON p.fk_category = c.pk_category;
```
### Complete Examples
#### Simple TVIEW
```sql
CREATE TABLE tv_user AS
SELECT
u.pk_user as pk_user,
u.id,
u.identifier,
u.name,
jsonb_build_object(
'id', u.id,
'identifier', u.identifier,
'name', u.name,
'email', u.email,
'created_at', u.created_at
) as data
FROM tb_user u;
```
#### Complex TVIEW with Relationships
```sql
CREATE TABLE tv_post AS
SELECT
p.pk_post as pk_post,
p.id,
p.identifier,
p.fk_user,
u.id as user_id,
jsonb_build_object(
'id', p.id,
'identifier', p.identifier,
'title', p.title,
'content', p.content,
'created_at', p.created_at,
'author', jsonb_build_object(
'id', u.id,
'identifier', u.identifier,
'name', u.name
),
'comments', COALESCE(
jsonb_agg(
jsonb_build_object(
'id', c.id,
'text', c.text,
'author', jsonb_build_object('id', cu.id, 'name', cu.name)
)
) FILTER (WHERE c.id IS NOT NULL),
'[]'::jsonb
)
) as data
FROM tb_post p
JOIN tb_user u ON p.fk_user = u.pk_user
LEFT JOIN tb_comment c ON c.fk_post = p.pk_post
LEFT JOIN tb_user cu ON c.fk_user = cu.pk_user
GROUP BY p.pk_post, p.id, p.identifier, p.title, p.content,
p.created_at, p.fk_user, u.id, u.identifier, u.name;
```
### What Happens During TVIEW Creation
1. **SQL Analysis**: Parses SELECT statement to identify dependencies
2. **Schema Inference**: Determines column types and relationships
3. **Backing View Creation**: Creates `tviews.<schema>__tv_<entity>` with your SELECT,
owned by you
4. **Materialized Table Creation**: Creates `tv_<entity>` table
5. **Trigger Installation**: Sets up triggers on all source tables
6. **Initial Population**: Fills TVIEW with current data
7. **Metadata Registration**: Records TVIEW in system catalogs
**Column types.** Every column of `tv_<entity>` has the type of the backing view's
column, typmod included: an enum, a domain, a composite, an array of them, a type in
another schema, `varchar(5)`, `numeric(6,2)`, `bit(4)`. Only the convention columns
have fixed types: `pk_<entity>` and `fk_*` are `BIGINT`, `id` is `UUID`, `data` is
`JSONB`. A TVIEW created before 0.1.0-beta.21 stored enums, domains and composites as
`text` and dropped typmods; it keeps those types until
`pg_tviews_create_or_replace()` is run with its definition, which converts each such
column in place and returns `altered`.
Because the backing view's columns depend on their types, `DROP TYPE … CASCADE` of a
type the view returns drops the view, and pg_tviews then drops the whole TVIEW (its
table, triggers and registration), as when a base table is dropped with `CASCADE`.
### Supported SQL Features
#### ✅ Supported
- **JOINs**: INNER, LEFT, RIGHT, FULL OUTER
- **Aggregations**: GROUP BY, HAVING, jsonb_agg(), array_agg()
- **Expressions**: CASE, COALESCE, NULLIF, FILTER
- **Subqueries** in the SELECT list or WHERE (`(SELECT …)`, `ARRAY(SELECT …)`,
`EXISTS`, `IN (SELECT …)`), `LATERAL`, and plain views (with `GROUP BY` too):
writes to the tables they read cascade when a condition links them to the TVIEW
key (`l.fk_order = o.pk_order`, `l.pos > o.min_pos`). An uncorrelated subquery
links nothing: see [Tables no cascade reaches](#tables-no-cascade-reaches)
- **Outer joins**: a table on the preserved side of a `LEFT`/`RIGHT JOIN` is linked
through the nullable side when the key is on that side or the path goes on from it
by an equality (a view
`tb_line l LEFT JOIN tb_order o ON l.fk_order = o.pk_order` exposing `o.id AS
order_id`, read by the TVIEW with `v.order_id = t.id`): a row with no match yields
NULLs there and matches no key
- **`GROUP BY` / `DISTINCT ON` views**: a view column passes through when it is a
grouping or `DISTINCT ON` key, or equal to one through a join condition
(`DISTINCT ON (l.fk_order) o.pk_order` with `l.fk_order = o.pk_order`): its value
is the key's on every row that can match
- **Window functions partitioned by a linked column**, in a view or subquery: a
row's window values come from the rows of its partition, so a column in every
window's `PARTITION BY` passes through like a `DISTINCT ON` key, and the other
columns don't. "The first row per key", `ROW_NUMBER() OVER (PARTITION BY
o.fk_customer ORDER BY …) … WHERE rn = 1` joined on `fk_customer`, refreshes the
partitions a write leaves and enters, as `DISTINCT ON (o.fk_customer)` does; so do
`RANK`, `DENSE_RANK`, `FIRST_VALUE` and other window functions, whatever filters
their output. A table joined to **another** column of that first row (the product
of each customer's first order, `LEFT JOIN tb_product p ON p.pk_product =
f.fk_product`) is mapped in two hops: a write to it reaches every row of the
subquery's table carrying it (a superset of the first rows), then their partition
or `DISTINCT ON` key. The subquery's own table maps only through that key: a write
that changes which row is first changes the partition it is in, while the other
column can belong to a row that was not written. So a table linked to the TVIEW
key *only* through such a column of its own first-row level stays `all_keys`
- **View columns the TVIEW doesn't read**: a view, subquery or CTE is followed only
for the columns read from it (in the select list, `WHERE`, joins, or through a
whole-row reference). The tables behind the other columns get no trigger, so
writes to them cost nothing. A column used for sorting, grouping or `DISTINCT`,
or returning a set, always counts
- **Functions**: jsonb_build_object(), jsonb_array_elements(), etc.
- **Operators**: Standard PostgreSQL operators
- **UNION / UNION ALL**: incremental refresh cascades to every branch's base
table. Each branch derives `pk_<entity>` from its own table: a column, or an
immutable expression of that table's row when two entities have their own key
spaces (`p.pk_product` in one branch, `-l.pk_order_line` or
`l.pk_order_line + 1000000000` in the other). The UNION may be the definition
itself or a view it reads (`SELECT v.pk_attachment, … FROM v_attachment v`): a
write to a branch's table refreshes that branch's keys, and a table joined to
the union's output refreshes the keys of every branch. Branch keys must be
disjoint: overlapping ones fail the create (duplicate key) and, later, the
write that makes two rows share a key (`pg_tviews.union_duplicate_policy`,
`error` by default). A key computed from two tables (`COALESCE(p.pk_product,
-l.pk_order_line)` over two outer joins) is no branch table's: put it in the
branches instead. A key taken from one branch's table through an inner join
holds only that branch's rows, as the definition says. A branch keyed by an
expression is refreshed by filtering on it: an index on the expression
(`CREATE INDEX ON tb_order_line ((-pk_order_line))`) keeps that cheap
- **INTERSECT / EXCEPT**: maintained branch by branch like UNION. A refresh
recomputes the view's row for each changed key, and both operators compare whole
rows, key included, so a row enters or leaves the TVIEW as the set operation says
- **CTEs (`WITH`)**: cascade paths resolve through a CTE whose body reads one or
several joined base tables, reads earlier CTEs (a chain of any length) or
subqueries in its `FROM`, or is a set operation. A CTE the view defines but never
uses is accepted; the tables it reads get no trigger
- **Computed columns**: a view, subquery or CTE column computed by an immutable
expression of base columns (`upper(n.name) AS code`) links like the expression
itself: a join on it (`x.code = s.code`) maps writes through `x.code =
upper(n.name)`. A column computed by a volatile or stable expression (`now()`,
`random()`) links nothing
- **Arrays of keys**: `a.pk_node = ANY (<array>)`, a join on `unnest(<array>)` in a
subquery's or CTE's select list, and `LATERAL unnest(<array>)` are the same
condition: the array's element equals the key. A write to the joined table maps
through `<array> @> ARRAY[<key>]`, which a GIN index on the array expression
serves; the create-time notice names it (below). A cast of the element,
`unnest(string_to_array(n.path, '.'))::bigint` or `u.x::bigint` for `LATERAL
unnest(…) AS u(x)`, is an element of the cast array,
`(string_to_array(n.path, '.'))::bigint[]`, and the index goes on that
- **Window functions, `LIMIT`/`OFFSET`, `GROUPING SETS`**: accepted, but a write to a
table read under one of them can change rows other than its own (a window without
`PARTITION BY`, or partitioned by a column nothing links to the key; any window in
the backing view's own SELECT), so the table is
`all_keys` and the TVIEW's `uncascaded_policy` decides (see [Tables no cascade
reaches](#tables-no-cascade-reaches)). The same holds for a set-returning function
in the backing view's own select list. In a subquery's select list a set-returning
function only multiplies rows: the other columns pass through, and an `unnest`
output is an array element (above)
- **Materialized views**: a materialized view the definition reads (directly or
through a view) is `all_keys`: `REFRESH MATERIALIZED VIEW` replaces its rows and
no trigger sees them. Under the default `error` policy the TVIEW is refused;
under `full_refresh`, `REFRESH MATERIALIZED VIEW` (plain or `CONCURRENTLY`)
refreshes the TVIEW in full, in the same transaction; under `warn` the TVIEW
keeps the rows it had. `REFRESH … WITH NO DATA` makes the matview unreadable and
refreshes nothing. No trigger is installed on a matview
- **Recursive CTEs (`WITH RECURSIVE`)**, in the definition or in a view it reads: a
row of a recursive CTE comes from rows of the step before, so the tables read
inside it are `all_keys` (`read in a recursive CTE (public.v_category_path)`) and
the tables read outside it keep their mapping. A small lookup tree read by a large
entity view is the usual case: create the TVIEW with `uncascaded_policy =
'full_refresh'`, and the entity's own writes stay incremental
- **DISTINCT ON**: deduplicated read models, keyed on their `DISTINCT ON` key
([ADR 0169](../adr/0169-tview-row-identity.md)): its value names the TVIEW's
rows, it is the table's primary key, and `tviews.registry.identity` reports it.
- The key is a column, projected (`DISTINCT ON (o.id) o.pk_order, o.id …`; it may
be aliased, `DISTINCT ON (c.id_contract) c.id_contract AS pk_contract`) or equal
through a join condition to a projected column (`DISTINCT ON (l.fk_order)
o.pk_order` with `l.fk_order = o.pk_order`). Its type can be anything (`bigint`,
`uuid`, `text`, `numeric`, `date`, a quoted mixed-case column…).
- Tables read through joins are followed like any TVIEW's, whatever the key.
- Writes are followed from the old and the new row: a row that changes its key
leaves its old group and joins the new one, and a statement writing several
groups refreshes each of them. The refresh filters on the key with its type, so
PostgreSQL reaches the base table's index through the `DISTINCT ON`.
- `pk_<entity>` is still required: parents embed the TVIEW through
`fk_<entity> = pk_<entity>`, and they follow the winning row when it changes.
- Refused at create: a composite key (`DISTINCT ON (a, b)`: a TVIEW row is one
entity with one key; model "one row per (a, b)" as an entity of its own), and a
key that is an expression, or a column not projected that no projected column
equals. The message names the key.
- **Generated columns**: `STORED` columns are ordinary columns. A **virtual**
generated column (PostgreSQL 18's default) has no value in the rows a trigger
sees, so pg_tviews follows its inputs instead: a TVIEW reading `code = upper(name)`
refreshes when `name` changes. A key or join on a virtual column is mapped by
computing the column from its expression over the changed rows, and the direct
and fan-out patches never copy a virtual column or one of its inputs.
#### ❌ Not Supported
- **Self-Joins**: May cause dependency cycles
### How a write finds the TVIEW rows to refresh
When a TVIEW is created, pg_tviews reads PostgreSQL's query tree of its backing view
(views, CTEs, subqueries and `UNION` branches included) and records, per base table,
how a changed row maps to TVIEW keys (`tviews.registry.cascade_kinds`): the key is a
column of the row (`local`), a generated query over the changed rows finds it
(`mapped`, for chains of joins and non-equality conditions), a TVIEW it embeds
refreshes it (`propagated`), or nothing selective links them (`all_keys`). A `mapped`
query that would scan a large table sequentially is reported at create time with
the index that avoids it:
```
NOTICE: writes to public.tb_sku map to tv_order keys with a sequential scan of tb_line
(about 20000 rows); an index on tb_line (fk_sku) would make them cheaper
```
A lookup by an expression (an array of keys, a computed column) names the index to
create:
```
NOTICE: writes to public.tb_node map to tv_node keys with a sequential scan of tb_node
(about 3000 rows); CREATE INDEX ON public.tb_node USING gin
(((pg_catalog.string_to_array(path, '.'::pg_catalog.text))::bigint[])) would
make them cheaper
```
`tviews.pg_tviews_mapping_query('tv_order', 'tb_sku')` returns the query.
The triggers follow the kind of each table:
| `local` | row trigger | the key is read off each changed row (and its old image) |
| `mapped`, `all_keys` | three statement triggers with transition tables (`INSERT`, `UPDATE`, `DELETE`) | one mapping query over the statement's changed rows; under `full_refresh` an `all_keys` write refreshes the whole TVIEW |
| `propagated` | none | refreshing the embedded TVIEW refreshes this one |
Another TVIEW's table read as `mapped` or `all_keys` gets the three statement
triggers alone: they fire on the refreshes of that TVIEW, inside the flush, which
then refreshes the rows of this one they map to.
Every base table but a `propagated` one also gets an `AFTER TRUNCATE` trigger, which
refreshes the whole TVIEW. An `UPDATE` that changes none of the columns the TVIEW
reads from a `mapped` table maps nothing (rows are matched to their old image by
primary key).
**Partitioned tables.** A partitioned table of any kind but `propagated` gets the
row trigger, which PostgreSQL copies onto every partition, and maps each changed row
from it: the transition tables of a statement trigger on the partitioned table would
miss the rows of a statement that names a partition. Every partition, leaf or middle
level, also gets the flush and `AFTER TRUNCATE` triggers, because a statement
trigger fires only on the table the statement names. So a write or a `TRUNCATE` that
targets a partition directly refreshes the TVIEW like one through the partitioned
table, and a `TRUNCATE` of the partitioned table refreshes it once.
Partitions created (`CREATE TABLE … PARTITION OF`, also from a function such as a
partition manager's) or attached after the TVIEW get the same triggers, and a
detached one loses them. `ATTACH` and `DETACH` move rows in or out with no row
trigger firing, so each refreshes the TVIEWs over that table in full.
`pg_tviews_health_check()` reports a partition whose triggers are missing, and
`pg_tviews_reregister_all()` puts them back.
### Tables no cascade reaches
A table classified `all_keys` has no condition linking its rows to the TVIEW key,
so a write to it could change any row. It is reported when the TVIEW is created and
listed in `tviews.registry.uncascaded_tables`. Common shapes:
- an uncorrelated subquery (`(SELECT count(*) FROM tb_flag)` in every row);
- a window function, `LIMIT`/`OFFSET`, a set-returning function in the select list,
or `GROUPING SETS`, in the backing view's own SELECT (`count(*) OVER ()` changes
every row when one is inserted; `ORDER BY … LIMIT 10` changes which rows are in):
the reason reads `read under a window function in the top-level SELECT`;
- a subquery or view whose rows are not passed through to the key: under a window
function that is not partitioned by a linked column, `LIMIT`/`OFFSET` or
`GROUPING SETS`, or joined on a column computed by a volatile or stable
expression;
- a materialized view (`a materialized view: REFRESH MATERIALIZED VIEW replaces its
rows without firing triggers`);
- the rows of a UNION branch whose key is not a column or expression of one of
its tables (`the TVIEW key is not a column of a base table in every UNION
branch`);
- a table read inside a recursive CTE (`read in a recursive CTE (…)`);
- a table read inside a function the definition calls, declared in
`function_reads` (`read inside public.label_suffix()`, see [Functions that read
tables](#functions-that-read-tables)).
A table read in several places is `all_keys` when one of them can't be traced, but
its other reads keep refreshing the rows they reach: a TVIEW's own table, read
where its key comes from, refreshes the row of each entity it writes. The reason
then ends with `the rows its other reads reach are still refreshed`, and the policy
decides only about the rest.
What happens is fixed per TVIEW by its `uncascaded_policy`, declared with the TVIEW
and stored with it:
| `error` (default) | `ERROR`: nothing is created; the HINT says what to declare | — |
| `full_refresh` | `NOTICE` | the whole TVIEW is brought up to date at flush, once per transaction or statement; unchanged rows are not rewritten |
| `warn` | `WARNING: writes to public.tb_flag will not refresh public.tv_report (read in a subquery, with no condition linking it to the TVIEW key)` | nothing: the rows stay stale until a mapped table changes |
A TVIEW with such a table and no declared policy is refused:
```
ERROR: writes to public.tb_flag would not refresh public.tv_report (read in a subquery, with no
condition linking it to the TVIEW key): declare what such a write does with the TVIEW's
uncascaded_policy
HINT: To refresh public.tv_report in full on such writes: pg_tviews_create_or_replace(
'public.tv_report', <definition>, options => '{"uncascaded_policy": "full_refresh"}');
before CREATE TABLE … AS or pg_tviews_create(): SET pg_tviews.uncascaded_policy =
'full_refresh'. "warn" accepts stale rows instead. Or join the tables on a column
pg_tviews can trace.
```
Declare it with the TVIEW:
```sql
SELECT pg_tviews_create_or_replace('tv_report', $$ … $$,
options => '{"uncascaded_policy": "full_refresh"}');
```
`CREATE TABLE … AS` and `pg_tviews_create()` take no options: they read the setting
`pg_tviews.uncascaded_policy` (default `error`) instead.
```sql
SET pg_tviews.uncascaded_policy = 'full_refresh';
CREATE TABLE tv_report AS SELECT …;
RESET pg_tviews.uncascaded_policy; -- the TVIEW keeps full_refresh
```
Changing the option of an existing TVIEW with `pg_tviews_create_or_replace()` is an
`altered` change: the policy is stored and the TVIEW re-registered, with no rebuild.
`full_refresh` recomputes every row of the TVIEW: on a 100 000-row TVIEW that is about
a second per flush that wrote to such a table. Use it for small TVIEWs, or rewrite the
definition so that the table is joined on a column pg_tviews can trace.
#### A policy per table
A TVIEW that reads a locale table or a small reference list nothing links to the key
can give that table its own policy, and keep refusing every other untraced read:
```sql
SELECT pg_tviews_create_or_replace('public.tv_x', $q$ … $q$, '{
"uncascaded_policy": "error",
"uncascaded_tables": {"public.tb_locale": "full_refresh", "catalog.tb_currency": "full_refresh"}
}');
```
A write to a named table follows its policy; any other table no cascade reaches
follows `uncascaded_policy`, so a later edit of a view that adds an untraced read is
still refused. A named table the definition does not read, or whose writes it traces
(`local`, `mapped`, `propagated`), is refused, so the list cannot rot; so is one that
does not exist. `tviews.registry.uncascaded_table_policies` reports the map. Leaving
`uncascaded_tables` out of a later `pg_tviews_create_or_replace()` keeps the map;
passing another one is an `altered` change.
### Functions that read tables
A function the definition calls (directly, or in a view, subquery or CTE it reads)
that is not `IMMUTABLE` and lives outside `pg_catalog` may read tables pg_tviews
cannot see: a `STABLE` lookup of a setting, a translation. Writes to those tables
would leave the TVIEW stale, so under the `error` and `full_refresh` policies such a
call is refused unless it is declared, and under `warn` it is reported:
```
ERROR: public.tv_contract calls public.label_suffix(), not immutable: the tables it reads are
invisible to pg_tviews, and writes to them would not refresh public.tv_contract: declare
them in function_reads
```
Declare the tables each function reads, naming the function with its argument types
(`[]` for a function that reads none, such as one reading a setting):
```sql
SELECT pg_tviews_create_or_replace('public.tv_contract', $q$
SELECT pk_contract, id, name || label_suffix() AS label FROM tb_contract $q$, '{
"function_reads": {"public.label_suffix()": ["public.tb_setting"], "public.app_tag()": []},
"uncascaded_tables": {"public.tb_setting": "full_refresh"}
}');
```
The declared tables become tables the TVIEW reads that no cascade reaches
(`all_keys`, `read inside public.label_suffix()`): they get the statement triggers,
appear in `base_tables`, `uncascaded_tables` and `cascade_kinds`, and their policy
decides what a write does. Above, `tb_setting` refreshes the TVIEW in full and any
other untraced read is still refused. A table the view also reads directly keeps
mapping the reads of it that can be traced. A declared function the definition does
not call, or that does not exist, is refused; `tviews.registry.function_reads`
reports the declarations.
Refreshes run as the TVIEW's owner with `search_path = pg_catalog, pg_temp`: a
function a definition calls must qualify the tables it reads (`public.tb_setting`) or
`SET search_path` itself. pg_tviews sees calls, not function bodies: the time a
function reads (`now()` inside it) is not detected.
### Time-dependent TVIEWs
A definition that reads the current time (`CURRENT_DATE`, `CURRENT_TIME`,
`CURRENT_TIMESTAMP`, `LOCALTIME`, `LOCALTIMESTAMP`, `now()`, `clock_timestamp()`,
`statement_timestamp()`, `transaction_timestamp()`, `timeofday()`, one-argument
`age()`), directly or in a view, subquery or CTE it reads, has rows that change
with no write: `end_date >= CURRENT_DATE AS is_current` flips at midnight. Under the
`error` and `full_refresh` policies it is refused unless it declares who brings it
up to date; under `warn` it is created with a WARNING.
```sql
SELECT pg_tviews_create_or_replace('public.tv_contract', $q$
SELECT pk_contract, id, name, end_date >= CURRENT_DATE AS is_current FROM tb_contract $q$,
'{"time_refresh": "external"}');
-- CREATE TABLE … AS, pg_tviews_create():
SET pg_tviews.time_refresh = 'external';
```
`tviews.registry.time_dependent` reports such TVIEWs (also one created under `warn`),
and `time_refresh` the declaration. Writes refresh it as usual; at the boundary,
something outside calls
```sql
SELECT * FROM tviews.pg_tviews_refresh_time_dependent(); -- every one you own
SELECT * FROM tviews.pg_tviews_refresh_time_dependent('public.tv_contract');
```
which refreshes it in full, as a write to a `full_refresh` table does (the TVIEWs
reading it follow), and returns the TVIEWs refreshed. With pg_cron, just after
midnight:
```sql
SELECT cron.schedule('tviews-day', '1 0 * * *',
'SELECT tviews.pg_tviews_refresh_time_dependent()');
```
`time_refresh` on a definition that reads no time is refused; the setting is ignored
for one. A literal evaluated at run time (`'now'::timestamptz`) is not detected: pass
the date as data, or write `now()`.
### Rendering
A value's text depends on session settings: `to_jsonb(timestamptz)` on `TimeZone`,
`::text` of a date or time on `DateStyle` too, of an interval on `IntervalStyle`, of a
float on `extra_float_digits`, of a `bytea` on `bytea_output`. Every computation of a
TVIEW's rows (creation, refreshes on writes, `pg_tviews_refresh()`, the time refresh,
`create_or_replace`) runs under fixed values, whoever writes and from whatever session:
| `TimeZone` | `UTC` |
| `DateStyle` | `ISO, YMD` |
| `IntervalStyle` | `postgres` |
| `extra_float_digits` | `1` |
| `bytea_output` | `hex` |
so a TVIEW's rows don't depend on who wrote last. The session's own settings are left
as they were. The definition itself is parsed under the caller's settings, as `CREATE
VIEW` parses it; a text-to-date conversion inside it runs at refresh time and reads
`ISO, YMD`.
`CURRENT_DATE` and `now()::date` in a refresh are the **UTC** day. For a local day,
write it in the definition, `(now() AT TIME ZONE 'Europe/Paris')::date`, and call
`pg_tviews_refresh_time_dependent()` just after that zone's midnight.
To compare a TVIEW with its definition, run the definition under the same settings:
```sql
BEGIN;
SET LOCAL TimeZone = 'UTC'; SET LOCAL DateStyle = 'ISO, YMD'; SET LOCAL IntervalStyle = 'postgres';
SET LOCAL extra_float_digits = 1; SET LOCAL bytea_output = 'hex';
SELECT count(*) FROM (TABLE tv_event EXCEPT SELECT … ) d;
COMMIT;
```
### Limitations
- **Dependency Depth**: Performance degrades with >5 cascade levels
- **Circular Dependencies**: Automatically detected and rejected
- **Column Name Conflicts**: Must resolve ambiguous column names
## Privileges
A TVIEW's table is an ordinary table: grant on it as on any other. Its backing view,
in `tviews`, follows it:
- whoever can `SELECT` from `tv_<entity>` (a role, or `PUBLIC`) can `SELECT` from its
backing view, so a role granted `SELECT ON ALL TABLES IN SCHEMA app`, or reading
`app` through default privileges, reads `tviews.app__tv_<entity>` too;
- the view's grants are made its table's when the TVIEW is created or rebuilt, and
after every `GRANT` or `REVOKE` on tables, including `ON ALL TABLES IN SCHEMA`;
- `ALTER TABLE tv_<entity> OWNER TO` (and `REASSIGN OWNED`) gives the view the new
owner, who reads the base tables through it;
- only `SELECT` is copied, without grant option; `INSERT`, `UPDATE` and the others
granted on the table are not. A grant made on the backing view alone is taken back
by the next of these.
`USAGE` on `tviews` is granted to `PUBLIC` by the extension.
## DROP TABLE tv_*
### Syntax
```sql
DROP TABLE [IF EXISTS] tv_<entity> [CASCADE];
```
### Examples
```sql
-- Drop a TVIEW
DROP TABLE tv_post;
-- Safe drop (no error if doesn't exist)
DROP TABLE IF EXISTS tv_missing;
-- Drop with CASCADE (drops dependent objects)
DROP TABLE tv_post CASCADE;
```
### What Happens During DROP TABLE tv_*
1. **Trigger Removal**: Uninstalls all triggers for this TVIEW
2. **Backing View Drop**: Removes its backing view in `tviews`
3. **Materialized Table Drop**: Removes `tv_<entity>` table
4. **Metadata Cleanup**: Removes entry from system catalogs
5. **Dependency Check**: Fails if other TVIEWs depend on this one
### Cascade Behavior
**CASCADE behavior**: PostgreSQL's standard CASCADE option is supported.
**Drop dependent TVIEWs first** (without CASCADE):
```sql
-- Find dependent TVIEWs (manual inspection for now)
-- Look for TVIEWs that reference this entity in their SELECT
-- Drop in reverse dependency order
DROP TABLE tv_post_comments; -- Depends on tv_post
DROP TABLE tv_post; -- Can now be dropped
```
**Or use CASCADE** (drops all dependents automatically):
```sql
DROP TABLE tv_post CASCADE; -- Drops tv_post and all dependent TVIEWs
```
**Dropped with something else.** A TVIEW whose table goes as a dependent of another
object, its schema (`DROP SCHEMA app CASCADE`), a base table or view its definition
reads (`DROP TABLE tb_post CASCADE`), or its owner's objects (`DROP OWNED BY`), is
deregistered with it: its triggers are removed and its backing view in `tviews` is
dropped along with what depends on it, so the TVIEW can be created again under the
same name.
**`DROP EXTENSION pg_tviews CASCADE`** drops the triggers and every backing view; the
`tv_*` tables stay as plain tables with their rows. Recreating a TVIEW under the same
name needs the table out of the way first (drop it, or rename it and copy what you
need). A backing view left by a drop in a session that never loaded the library (no
`shared_preload_libraries`) is dropped by the next `pg_tviews_create()` of that
TVIEW, with a NOTICE, when no TVIEW is registered with it and nothing depends on it.
## ALTER TVIEW
Change a TVIEW's definition or storage with `pg_tviews_create_or_replace()`, which makes
the smallest change (`altered`, `replaced` in place, or `rebuilt`):
```sql
SELECT tviews.pg_tviews_create_or_replace('tv_post', $$ SELECT ... -- new definition $$);
```
## Triggers
Creating a TVIEW installs, on each table its definition reads, a row-level trigger
(`tviews.pg_tview_trigger_handler`) that queues the affected keys, and a
statement-level trigger (`tviews.pg_tview_flush_trigger`) that refreshes them once per
statement. Nothing needs installing by hand. `tviews.pg_tviews_health_check()`
reports missing or orphaned triggers; `SELECT * FROM tviews.pg_tviews_reregister_all()`
re-installs any that are missing.
## Troubleshooting
### TVIEW Creation Errors
**"TVIEW name must follow tv_* convention"**
```sql
-- Fix: Use correct naming
SELECT pg_tviews_create('tv_post', '...'); -- ✅ Correct
SELECT pg_tviews_create('post_view', '...'); -- ❌ Wrong
```
**"Missing required column: pk_post"**
```sql
-- Fix: Include primary key column in SELECT
SELECT p.pk_post as pk_post, ... -- ✅ Correct
SELECT p.id as pk_post, ... -- ❌ Wrong column
```
**"Missing required column: data"**
```sql
-- Fix: Include JSONB data column
jsonb_build_object(...) as data -- ✅ Correct
jsonb_build_object(...) as json -- ❌ Wrong name
```
**"Dependency cycle detected"**
```sql
-- Fix: Restructure to avoid circular dependencies
-- TVIEW A references TVIEW B which references TVIEW A
```
### DROP TABLE tv_* Errors
**"Cannot drop tv_post: other TVIEWs depend on it"**
```sql
-- Fix: Drop dependent TVIEWs first
DROP TABLE tv_post_comments; -- Remove dependency
DROP TABLE tv_post; -- Now works
-- Or use CASCADE
DROP TABLE tv_post CASCADE; -- Drops all dependents
```
### Performance Issues
**Slow initial creation**:
- Complex SELECT with many JOINs
- Large tables (consider WHERE clauses for initial subset)
**Slow refreshes**:
- Deep cascade chains (>3 levels)
- Large JSONB objects (consider jsonb_delta extension)
## Best Practices
### Schema Design
1. **Follow Trinity Pattern**: Use id/pk_/fk_ consistently
2. **Include All FKs**: Both integer (cascade) and UUID (filtering)
3. **Use Meaningful Identifiers**: SEO-friendly slugs where appropriate
4. **Plan Cascade Depth**: Keep dependency chains shallow (<3 levels)
### TVIEW Design
1. **One Entity Per TVIEW**: Focus each TVIEW on a single primary entity
2. **Include GraphQL Fields**: All fields needed for API responses
3. **Use Efficient JOINs**: Prefer INNER JOINs where possible
4. **Test with Real Data**: Verify performance with production-scale data
### Maintenance
1. **Monitor Dependencies**: Track which TVIEWs depend on others
2. **Plan Drop Order**: Know dependency chains for maintenance
3. **Test Changes**: Use staging environment for DDL changes
4. **Backup First**: Always backup before major DDL operations
## See Also
- [FraiseQL Integration Guide](../getting-started/fraiseql-integration.md)
- [API Reference](api.md)
- [Troubleshooting Guide](../operations/troubleshooting.md)