Seeing what is defined¶

One call lists every vault table and what it allows.

SELECT * FROM pgvault_tables.view_vault_tables();

 schema_name | table_name  |     permissions      | retention_days
-------------+-------------+----------------------+----------------
 archive     | statements  | insert               |           2555
 public      | audit_log   | insert               |
 public      | ledger      | insert,update,delete |
 reference   | currencies  | insertonce           |

Anyone can call it. It is a convenience, not a secret — the same information is already visible in PostgreSQL's own catalogue to anybody who cares to look. The function simply returns it in a sensible shape instead of making you join system tables and unpick an options array.

Reading the output¶

permissions is always shown in the same order regardless of how you typed it, so two tables with the same permissions always look the same. If you wrote 'drop,insert', it comes back as insert,drop.

retention_days is blank (null) for a table that never set retention. That distinction matters: blank means "no retention period", not "zero days".

Useful queries¶

Everything in one schema:

SELECT * FROM pgvault_tables.view_vault_tables()
 WHERE schema_name = 'archive';

Everything with a retention period:

SELECT * FROM pgvault_tables.view_vault_tables()
 WHERE retention_days IS NOT NULL
 ORDER BY retention_days DESC;

Anything that can be dropped — worth reviewing occasionally:

SELECT * FROM pgvault_tables.view_vault_tables()
 WHERE permissions LIKE '%drop%';

Anything where retention is not actually protecting the rows, because delete is also granted:

SELECT * FROM pgvault_tables.view_vault_tables()
 WHERE retention_days IS NOT NULL
   AND permissions LIKE '%delete%';

That last one is worth running now and then. It finds tables that look protected but are not — see Retention.

Checking one table¶

To see the deadline on individual rows, query the table itself:

SELECT id, _$purge_ts, _$purge_ts < now() AS can_be_purged
  FROM archive.statements
 ORDER BY _$purge_ts
 LIMIT 10;