Converting existing tables¶
You do not have to start again to get a vault table. An ordinary table can become one in place, keeping everything that points at it.
Why convert rather than recreate¶
Converting preserves the table's identity — its OID — so everything built on top of it survives untouched: views, foreign keys, indexes, constraints, triggers, grants, comments and sequences. Recreating the table means rebuilding all of that by hand and repointing anything that referenced it.
The extension provides a procedure for it:
It is superuser-only, and by default it changes nothing — see below.
Always look before you convert¶
p_test defaults to true, so the call above reports what it would do and stops. That default is deliberate: conversion cannot be undone, and a forgotten parameter should produce a report rather than an irreversible change.
CALL pgvault_tables.convert_table(
p_schema => 'app',
p_insert => true,
p_update => 'status,notes');
NOTICE: TEST: 3 table(s) in schema app; permissions to apply: insert,update
NOTICE: app.customers: 1 row(s); permissions insert,update(status,notes)
NOTICE: app.order_items: 1 row(s); permissions insert,update(status,notes)
NOTICE: app.orders: 1 row(s); permissions insert,update(status,notes)
NOTICE: TEST complete: 3 table(s) examined, 0 failed validation
Each line shows the declaration that table would actually receive. Read them before going further, then add p_test => false to do it.
One table¶
Name it with p_table:
CALL pgvault_tables.convert_table(
p_schema => 'app',
p_table => 'customers',
p_insert => true,
p_update => 'status,notes',
p_coldefault => true,
p_test => false);
NOTICE: converted app.customers: permissions insert,update(status,notes),coldefault, 1 row(s)
NOTICE: convert_table complete: 1 converted, 0 failed; permissions insert,update,coldefault
Several tables, with a wildcard¶
p_table is matched with LIKE, so % selects a group:
CALL pgvault_tables.convert_table(
p_schema => 'app',
p_table => 'order%', -- orders, order_items, order_history …
p_insert => true,
p_update => 'status,notes',
p_test => false);
NOTICE: converted app.order_items: permissions insert,update(status,notes), 1 row(s)
NOTICE: converted app.orders: permissions insert,update(status,notes), 1 row(s)
NOTICE: convert_table complete: 2 converted, 0 failed; permissions insert,update
Only % marks a value as a pattern. LIKE also treats _ as a single-character wildcard, but almost every real table name contains an underscore, so treating that as a deliberate pattern would turn a mistyped table name into a quiet notice instead of the error it should be. Write order% when you mean a group, and the exact name when you mean one table.
Every table in a schema¶
Leave p_table out entirely:
CALL pgvault_tables.convert_table(
p_schema => 'app',
p_insert => true,
p_update => 'status,notes',
p_test => false);
Tables already using the vault access method are skipped, so this is safe to run again — useful when new tables arrive in a schema you have already converted:
Views, materialised views, foreign tables, temporary tables, partitions and partitioned parents are all skipped too. Only ordinary tables qualify.
Updating only some columns¶
p_update is the one permission that is not a simple true/false, because it can be narrowed to named columns. It takes three forms:
p_update => |
Meaning |
|---|---|
left out, or 'false' |
update is not granted at all |
'true' |
update is granted for every column |
'status,notes' |
update is granted for those columns only |
Multiple columns are one comma-separated string — the same text you would write inside update(...) in a CREATE TABLE. Spacing is free, and a name that needs quoting keeps its quotes:
p_update => 'status'
p_update => 'status,notes,reconciled'
p_update => 'status, notes, "Reconciled By"'
Names fold the way SQL folds them, so Status means the column status, and "Status" means exactly that.
The list is resolved against each table as it is converted, and stored as that table's own column names. A name that matches no column stops the conversion rather than being written through — which matters, because a list resolving to nothing would leave a table on which no column could be updated and no way to correct it.
That resolution is per table, which has a consequence worth planning for:
ERROR: column list "customer" does not match the columns of app.customers
DETAIL: Every name must be an existing, non-generated column of the table being converted.
Converting a whole schema with a column list therefore requires every table in it to have those columns. Where they differ, convert in groups — one call per set of tables that share the columns you want mutable.
Adding retention while you convert¶
Retention needs a deadline for every existing row, so three parameters go together:
| Parameter | Meaning |
|---|---|
p_retention |
The period, in days |
p_retention_start_column |
A column or expression to measure from — typically the row's own date |
p_min_date |
A floor, used where that expression is NULL or very old |
CALL pgvault_tables.convert_table(
p_schema => 'app',
p_insert => true,
p_update => 'notes',
p_retention => 2555,
p_min_date => '2020-01-01+00',
p_retention_start_column => 'occurred_at',
p_test => false);
Test mode shows the exact expression before anything happens, and how many rows will fall back to the floor:
NOTICE: app.events: 2 row(s); permissions insert,update(notes); 1 will inherit the floor
from a null start date; deadline = GREATEST(occurred_at, '2020-01-01'::timestamptz)
+ 2555 * interval '1 day'
id | occurred_at | deadline
----+-------------+------------
1 | 2024-03-01 | 2031-02-28
2 | | 2026-12-30
Row 1 gets seven years from its own date; row 2 had no date, so it is measured from the floor instead. A NULL deadline would mean "never expires", so the floor exists to make sure no row ends up with one.
What to watch out for¶
It cannot be undone. There is no unconvert_table. Once the access method is switched the declaration is in force, and if it does not grant drop the table cannot even be removed. Run in test mode first, every time.
The declaration is permanent. Nothing about it can be changed afterwards — not the permissions, not the column list. Getting it wrong means creating a replacement table and copying the data, which is only possible if the original granted drop.
Retention can never be added later. A table converted without p_retention has no deadline column, and one cannot be added to a vault table. If retention is even possible for a table, decide at conversion.
It commits table by table. A failure part way through leaves the tables already done converted, rather than rolling back hours of work. That is usually what you want on a long run, but it means a failed schema-wide call can leave you half converted — read the notices to see where it stopped. p_stop_on_error => false carries on past a table that fails instead.
A named table that does not qualify is an error; a pattern that matches nothing is not. Naming a table you cannot convert tells you so; a pattern matching nothing succeeds quietly, because an empty match is an ordinary state of affairs.
insertonce on a table that already has rows consumes its one permitted insert immediately, so no further rows could ever be added. The procedure warns rather than refusing, because it is occasionally what you want.
Reserved schemas are refused. pg_catalog, information_schema, pg_toast and anything beginning with pg_ cannot hold a vault table, by the extension itself rather than merely by this procedure.
Afterwards¶
The table appears in the registry with the declaration it was given:
schema_name | table_name | permissions | update_columns | retention_days
-------------+-------------+--------------------------+----------------+----------------
app | customers | insert,update,coldefault | status,notes |
app | order_items | insert,update | status,notes |
app | orders | insert,update | status,notes |
Enforcement is live immediately, with no further setup:
ERROR: column "amount" is not updatable on vault table "orders"
DETAIL: The table permits updating only: status,notes.
And everything that pointed at the table still does — the views still resolve, the foreign keys still fire, the indexes are still the same indexes. That is what converting in place buys you.
For the mechanics underneath — the four steps the procedure performs, why their order is forced, and how deadlines are derived — see Converting Existing Tables in the DBA Guide.