Converting existing tables¶

An ordinary table can become a vault table in place, keeping its OID — and so keeping its views, foreign keys, indexes, constraints, triggers, grants and comments. Nothing that depends on it needs changing, and the application keeps using the same name.

There are two ways to do it: the convert_table procedure, or by hand. Use the procedure unless the table is one it excludes.

This is one-way

Once conversion completes, the declaration is immutable, and if the table does not grant drop you cannot remove it. Rehearse on a copy of the database first, and use p_test => true before every real run.

The procedure¶

CALL pgvault_tables.convert_table(
    p_schema                 => 'app',
    p_table                  => 'orders',        -- NULL for every table
    p_insert                 => true,
    p_delete                 => true,
    p_retention              => 2555,
    p_min_date               => '2020-01-01+00',
    p_retention_start_column => 'created_at');

p_test defaults to true, so the call above reports and changes nothing. Conversion is irreversible and the declaration is immutable once it completes, so the safe outcome is the one you get by forgetting a parameter. Pass p_test => false when you have read the report and mean it.

Superuser only — writing a table's declaration into pg_class requires it.

Scope¶

Parameter	Meaning
`p_schema`	Required. Cannot be NULL and is never a pattern
`p_table`	NULL for every qualifying table in the schema, or a `LIKE` pattern

Reserved schemas can never hold a vault table. pg_catalog, information_schema, pg_toast and anything else beginning with pg_ are refused — by the extension itself, not merely by this procedure, so the rule holds however the table is created. Temporary schemas are the deliberate exception: a temporary vault table is a legitimate thing to want.

Without that rule a vault table could be created inside pg_catalog, and since a table granting no drop cannot be removed, the result was a permanent, unremovable object in a system schema.

Only ordinary tables qualify. Tables already using the vault access method are excluded, so the procedure is safe to re-run, and so are views, materialised views, foreign tables, temporary tables, partitions and partitioned parents — partitioning and the vault access method are refused together in any case.

A named table that does not qualify raises an error. A pattern matching nothing succeeds with a notice, since an empty match is an ordinary state of affairs. Only % marks a value as a pattern for that decision: LIKE also treats _ as a wildcard, but nearly every real table name contains one, so treating that as a deliberate pattern would turn a typo into a quiet notice.

Permissions¶

One boolean per permission, all false by default: p_insert, p_insertonce, p_delete, p_truncate, p_drop, p_trigger, p_rls, p_policy, p_log, p_tablespace, p_addcolumn, p_coldefault.

p_update is the exception, 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:

-- one column
CALL pgvault_tables.convert_table(
       p_schema => 'app', p_table => 'orders',
       p_insert => true, p_update => 'status', p_test => false);

-- several columns
CALL pgvault_tables.convert_table(
       p_schema => 'app', p_table => 'orders',
       p_insert => true, p_update => 'status,notes,reconciled', p_test => false);

-- spacing is free, and a name that needs quoting keeps them
CALL pgvault_tables.convert_table(
       p_schema => 'app', p_table => 'orders',
       p_insert => true, p_update => 'status, notes, "Reconciled By"', p_test => false);

-- and with retention, which is the usual shape for a ledger
CALL pgvault_tables.convert_table(
       p_schema                 => 'app',
       p_table                  => 'ledger',
       p_insert                 => true,
       p_update                 => 'status,notes',
       p_retention              => 2555,
       p_min_date               => '2020-01-01+00',
       p_retention_start_column => 'created_at',
       p_test                   => false);

Names are folded the way SQL folds them — Status means status, and "Status" means exactly that — and are resolved against each table as it is converted, then stored as that table's own column names. A name that matches no column raises, rather than being written through: writing the catalogue directly applies none of CREATE TABLE's validation, and a list that resolved to nothing would leave a table on which no column could be updated and no way to correct it.

That resolution is per table, so converting a whole schema with a column list requires every matched table to have those columns.

Every rule CREATE TABLE would apply is applied here too, because writing the catalogue directly applies none of them by itself:

either p_insert or p_insertonce, and not both
p_truncate cannot be combined with p_retention
p_retention is at least 1, or NULL for none — there is no value meaning "no retention"

All parameter validation happens before any table is touched.

p_insertonce on a table that already has rows

The one permitted insert is spent the moment the table holds anything, so no further rows can ever be added. The procedure warns loudly and proceeds, since sealing an existing table may be exactly the intent.

Retention¶

Three parameters, required together and meaningless apart:

Parameter	Purpose	Which way it errs
`p_retention_start_column`	The row's real start date. A column name, or any expression	None; this is the value you actually want
`p_min_date`	A floor under every derived deadline, so no row arrives already eligible for purge	Over-retains. A row dated 2019-03-15, with a floor of 2020-01-01 and 2555 days, gets a deadline of 2026-12-29 rather than the 2026-03-13 its own date would give — nine months longer
`p_retention`	Whole days	—

The deadline is:

GREATEST(<p_retention_start_column>, p_min_date) + p_retention * interval '1 day'

There is no separate parameter for a null start date, because the expression can handle it:

p_retention_start_column => 'COALESCE(created_at, ''2022-07-01''::timestamptz)'

Without one, GREATEST ignores the NULL and the row silently takes p_min_date. Nothing breaks, but a data problem is hidden — which is what p_test is for.

A pattern plus retention needs one table shape

The start expression is applied to every matched table, so it must be valid for all of them. Converting a whole schema whose tables use different date column names will fail on the ones that do not match — either convert them in groups, or use p_stop_on_error => false and repeat with a different expression.

`p_test`¶

Defaults to true. Changes nothing. Reports, per table, the row count, and where retention is in play, the number of rows whose start expression is NULL — that is, how many will inherit the floor — and the fully resolved deadline expression that would run:

NOTICE:  TEST: 2 table(s) in schema cvt; permissions to apply: insert,delete
NOTICE:  TEST: retention 2555 day(s), start expression created_at, floor 2020-01-01
NOTICE:    cvt.audit: 2 row(s); permissions insert,update(notes); 1 will inherit the floor
           from a null start date; deadline = GREATEST(created_at, '2020-01-01'::timestamptz)
           + 2555 * interval '1 day'
NOTICE:  TEST complete: 2 table(s) examined, 0 failed validation

Each per-table line reports the declaration that table would actually receive, column list included. That matters where a column list is in play, because the list is resolved against each table separately — so test mode is where a schema-wide call with a column list one table does not have will tell you:

ERROR:  column list "customer" does not match the columns of app.customers

The same validation runs on the real pass — p_test only decides whether to stop after it. There is no way to convert without being checked.

Pass p_test => false to actually convert.

`p_stop_on_error`¶

true by default. On failure the run stops, and tables already converted stay converted — the procedure commits after each one, so a failure part way through a long run does not undo hours of rewriting.

false reports the failure as a warning and moves to the next table:

WARNING:  skipped cvt.odd: column "created_at" does not exist
NOTICE:  converted cvt.orders: permissions insert,delete, retention 2555 day(s), 3 row(s), 1 dated from the floor
NOTICE:  convert_table complete: 1 converted, 1 failed; permissions insert,delete

Doing it by hand¶

Use this for tables the procedure excludes, or when you want to see every step. Wrap it in a transaction so a mistake can be rolled back, and take a copy of the table first — after the fourth step the declaration cannot be changed.

BEGIN;

-- keep a copy while you work
CREATE TABLE legacy_backup AS SELECT * FROM legacy;

-- 1. add the deadline column, only possible while it is still an ordinary table
ALTER TABLE legacy ADD COLUMN "_$purge_ts" timestamptz;

-- 2. derive the deadlines
UPDATE legacy SET "_$purge_ts" =
  GREATEST(COALESCE(created_at, '2022-07-01+00'::timestamptz),
           '2020-01-01+00'::timestamptz) + 2555 * interval '1 day';

-- 3. write the declaration, permitted because it is not yet a vault table
UPDATE pg_class SET reloptions = '{"permissions=insert","retention=2555"}'
 WHERE oid = 'legacy'::regclass;

-- CHECK IT before going further. After the next statement it cannot be fixed.
SELECT reloptions FROM pg_class WHERE oid = 'legacy'::regclass;

-- 4. switch the access method: rewrites, carrying both across
ALTER TABLE legacy SET ACCESS METHOD vault;

-- confirm, then commit
SELECT * FROM pgvault_tables.view_vault_tables() WHERE table_name = 'legacy';

COMMIT;

Step 3 does not pass through the option validator, so a typo is not caught. A declaration of permissions=insert,insertonce parses to nothing, producing a table that grants no operation at all and cannot be written to, altered or dropped — only a database drop removes it. That is why the check between steps 3 and 4 matters, and why the procedure reads the declaration back through the parser before switching.

Drop the backup table once you are satisfied.

Why the order is forced¶

The column must come first. ALTER TABLE on a vault table cannot add a column unless the table grants addcolumn, and the deadline column name is reserved even then. A table converted without it can never have retention.

Populating must precede the switch. Step 4 rewrites the table through the insert path. The extension treats SET ACCESS METHOD as a rewrite and carries existing deadlines across unchanged — but only values already present. A row still NULL receives an ordinary computed deadline instead, which is the safe direction: a forgotten UPDATE gives full-period retention rather than rows that can never be deleted.

The declaration must precede the switch. Switching first fails, and should:

ERROR:  insert is not permitted on vault table "pg_temp_93365"
DETAIL:  The table's permissions are "".

The rewrite's transient relation inherits the vault access method with an empty permission set, so fail-closed refuses to copy the rows and the table is left untouched.

Deadlines are derived once¶

After conversion the table behaves like any other vault table: a new row gets clock_timestamp() + retention, whatever its own dates say.

INSERT INTO legacy (id, created_at) VALUES (9, '2019-01-01+00');
-- deadline is 2555 days from now, not from 2019

That is deliberate. If the deadline were permanently derived from a data column, anyone able to insert could choose their own expiry by backdating it. The derivation belongs to the conversion, and only to the conversion.

Cost¶

The fourth step rewrites the table, so it needs room for a second copy of the data while it runs and holds an ACCESS EXCLUSIVE lock for the duration. Size the window accordingly, and prefer converting large tables individually rather than a whole schema in one call.