# 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**: `v_<entity>` (automatically created)
- **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 `v_<entity>` with your SELECT
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
- **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; branches must key on disjoint `pk_<entity>` values (otherwise
`pg_tviews.union_duplicate_policy` governs the duplicate)
- **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. The columns the CTE joins on must
pass base columns through unchanged (a computed join column cannot be traced back
to a base row). A CTE the view defines but never uses is accepted; the tables it
reads get no trigger
- **Window functions, `LIMIT`/`OFFSET`, set-returning functions, `GROUPING SETS`**:
accepted, but a write to a table read under one of them can change rows other
than its own, so the table is `all_keys` and the TVIEW's `uncascaded_policy`
decides (see [Tables no cascade reaches](#tables-no-cascade-reaches))
- **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
- **Recursive Queries**: `WITH RECURSIVE` (rejected at create time)
- **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
```
`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 |
Every 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 the same
shapes, or a join on a computed column.
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 always
refreshes its rows): 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 `pg_tviews.uncascaded_policy` at create time:
| `warn` (default) | `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 |
| `error` | `ERROR` with the same text; nothing is created | — |
| `full_refresh` | `NOTICE` | the whole TVIEW is brought up to date at flush, once per transaction or statement; unchanged rows are not rewritten |
`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.
```sql
SET pg_tviews.uncascaded_policy = 'full_refresh';
SELECT pg_tviews_create('tv_order', $$ … $$);
RESET pg_tviews.uncascaded_policy; -- the TVIEW keeps full_refresh
```
### Limitations
- **Dependency Depth**: Performance degrades with >5 cascade levels
- **Circular Dependencies**: Automatically detected and rejected
- **Column Name Conflicts**: Must resolve ambiguous column names
## 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 `v_<entity>` view
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
```
## 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)