System Catalog

System Catalog

DB9 implements PostgreSQL-compatible system catalog views for introspecting database objects.

pg_catalog Views

View	Description
pg_tables	User tables
pg_views	User views
pg_class	Tables, indexes, sequences, views
pg_attribute	Table columns
pg_attrdef	Column default values
pg_namespace	Schemas
pg_type	Data types
pg_index	Index metadata
pg_indexes	Index definitions (indexdef)
pg_constraint	Constraints (PK, FK, CHECK, UNIQUE, NOT NULL)
pg_proc	Functions and procedures
pg_trigger	Triggers. tgtype carries PostgreSQL’s timing/level/event bitmask, tgnargs and tgargs the trigger arguments; information_schema.triggers (action_timing, action_orientation) gives the same facts by name, and pg_get_triggerdef() reconstructs the whole statement (see below). tgenabled is live state, written by ALTER TABLE ... DISABLE TRIGGER
pg_enum	Enum type values
pg_sequence	Sequence metadata
pg_sequences	One row per sequence, with PostgreSQL 17’s columns. last_value is NULL until the first nextval(), and with CACHE above 1 it can run ahead of the values handed out so far, as in PostgreSQL
pg_extension	Installed extensions
pg_collation	Collations — built-ins only; collations you create work but never appear
pg_roles / pg_user	User and role definitions
pg_authid	The same roles as pg_roles, with rolpassword always NULL
pg_auth_members	Role membership: built-in rows making pg_monitor a member of pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables, plus any GRANT <role> TO <role> you issue. REVOKE removes the row again
pg_db_role_setting	Per-role parameter defaults written by ALTER ROLE … [IN DATABASE …] SET (also shown in pg_roles.rolconfig); applied to new sessions for that role
pg_database	Databases
pg_description	Object descriptions/comments
pg_depend	Dependency tracking
pg_am	Access methods
pg_stat_user_tables	One row per user table. relid, schemaname and relname are populated; every counter is NULL
pg_opclass	Operator classes
pg_policy	Row-level security policies
pg_policies	Readable RLS policy view (qual, with_check)
pg_settings	Runtime parameters, including DB9’s db9.* tuning settings
pg_prepared_statements	The current session’s prepared statements — SQL PREPARE (from_sql = true) and named wire-protocol statements (from_sql = false); DEALLOCATE removes the row. Over the HTTP SQL API each request is its own session, so it lists only statements prepared earlier in the same request
pg_timezone_names	Time zone names, abbreviations and UTC offsets (name, abbrev, utc_offset, is_dst)
pg_rewrite	Rewrite rules — populated with the _RETURN rule backing each view
pg_aggregate	Aggregate functions. Exposes only aggfnoid; the transition-function columns are absent
pg_publication	Publications, written by CREATE PUBLICATION. A logical replication slot captures changes only for tables in a publication, apart from the rows a CREATE TABLE ... AS or SELECT ... INTO loads — see pg_replication_slots below
pg_publication_rel	Publication table membership, written by ALTER PUBLICATION ... ADD/DROP/SET TABLE. Membership at write time decides which changes a slot captures, apart from the rows a CREATE TABLE ... AS or SELECT ... INTO loads. A materialized view cannot be added (42809)
pg_publication_tables	The readable view over the same membership — pubname, schemaname, tablename, attnames, rowfilter. Populated whenever pg_publication_rel is
pg_language	Function languages — internal, sql and plpgsql. Joins to pg_proc.prolang
pg_tablespace	pg_default and pg_global. Present for tools that join to it; DB9 has no user tablespaces and SET TABLESPACE is rejected
pg_default_acl	Default privilege rules. One system row is present from database creation (a read grant to _db9_sys_readonly on public); ALTER DEFAULT PRIVILEGES adds your own, see Roles & Privileges
pg_stat_activity	One row per session on the server node your connection landed on — 17 columns with live values, including state, query, backend_start, query_start, wait_event and client_addr. It is not a database-wide list: concurrent sessions served by another node do not appear, so it cannot stand in for a connection count. See Observability
pg_stat_replication	Node-local, like pg_stat_activity: a client streaming from a replication slot appears only to sessions on the server node it is connected to, so the view is usually empty — read a slot’s state from pg_replication_slots.active instead
pg_stat_database	One row per database. For the database you are connected to, numbackends, xact_commit, xact_rollback and the tup_* counters carry values; the block, timing and session counters are NULL. The counters are kept per server node, like pg_stat_activity — each node has its own totals and its own stats_reset, so two connections can read different numbers
pg_stat_all_tables	The same columns and rows as pg_stat_user_tables — user tables only, every counter NULL
pg_stat_all_indexes / pg_statio_all_tables / pg_statio_all_indexes	One row per user index or table with the identifying columns populated; every counter is NULL
pg_stat_gssapi	One row per session on the same node
pg_stat_ssl	One row per session on the same node, with that connection’s real TLS state in ssl, version, cipher and bits — true, TLSv1.3, TLS_AES_256_GCM_SHA384 and 256 for a TLS session, false and NULLs for a plaintext one. A query sent over the HTTP SQL API sees its own session as ssl = false. See Authentication & Roles
pg_stats	Per-column statistics written by ANALYZE — null_frac, avg_width, n_distinct, most_common_vals and histogram_bounds. n_distinct is a count of distinct values, never PostgreSQL’s negative fraction, and correlation is NULL. A table that has never been analyzed has no rows here
pg_event_trigger	Event triggers, written by CREATE EVENT TRIGGER — see Advanced SQL — Event triggers
pg_inherits	One row per partition of a partitioned table. CREATE TABLE ... INHERITS is rejected (42601), so partitions are the only parent/child pairs
pg_partitioned_table	Empty until the database has a partitioned table; from then on any query against it fails with 0A000 (cannot export persisted RANGE partitions through pg_partitioned_table: …). Find partitioned tables with pg_class.relkind = 'p' instead. Partitioning itself covers PARTITION BY RANGE on a timestamp/timestamptz key only — see the compatibility matrix
pg_replication_slots	Logical replication slots, 19 columns including conflicting and the PostgreSQL 17 additions failover/synced. Create a slot with SELECT * FROM pg_create_logical_replication_slot('<slot>', 'wal2json') — the function resolves in the FROM clause only (as SELECT pg_create_logical_replication_slot(...) it reports 42883), and through SQL only wal2json is accepted (pgoutput and test_decoding give 22023; pgoutput slots are for the replication protocol). Read changes with SELECT data FROM pg_logical_slot_peek_changes('<slot>', NULL, NULL, 'format-version', '2') (or pg_logical_slot_get_changes, which consumes them): wal2json v2 JSON rows, only for tables that were in a publication when the change was written (plus the rows a CREATE TABLE ... AS or SELECT ... INTO loads into its new table), a few seconds after the write. format-version 2 must be passed explicitly (22023). Drop a slot with SELECT pg_drop_replication_slot('<slot>'). CREATE SUBSCRIPTION is not parsed, so DB9 cannot subscribe to another server. Details, including the replication protocol: Logical decoding (CDC). Creating a slot turns on Realtime capture for the database permanently — it stays on after the slot is dropped, and ADD PRIMARY KEY on a populated table, dropping a populated table’s primary key, and ALTER TYPE ... RENAME VALUE on a label that stored rows use then fail with 55000; see DDL

The following relations resolve but are always empty — they exist so that catalog-introspecting tools and ORMs do not error, not because the feature behind them is implemented:

Relation	Note
pg_cast	No user-defined casts
pg_shdescription	No shared-object comments
pg_conversion	No encoding conversions
pg_operator / pg_opfamily	Operators and operator families are not catalog-registered, the way many built-in functions are not
pg_amop / pg_amproc	The strategy operators and support functions behind an operator class. pg_opclass has its built-in rows, but nothing describes their members, and CREATE OPERATOR CLASS is not parsed
pg_range	CREATE TYPE ... AS RANGE is not parsed, and the built-in int4range is not registered here either — read pg_type where typtype = 'r' instead
pg_foreign_data_wrapper / pg_foreign_server / pg_foreign_table	CREATE FOREIGN DATA WRAPPER, CREATE SERVER and CREATE FOREIGN TABLE are all rejected at parse time (42601)
pg_transform	CREATE TRANSFORM is not parsed
pg_seclabel / pg_seclabels	SECURITY LABEL is not parsed — the same reason pg_shseclabel below stays empty
pg_parameter_acl	GRANT ... ON PARAMETER is not parsed (42601)
pg_prepared_xacts	PREPARE TRANSACTION is not parsed (42601)
pg_init_privs	Initial-privilege snapshots are not recorded; CREATE EXTENSION does not write here
pg_ts_config / pg_ts_dict / pg_ts_parser / pg_ts_template	Full-text search works through built-in configurations that are not catalogued — see Full-Text Search
pg_largeobject_metadata	Large objects are not implemented
pg_shseclabel	Security labels are not implemented
pg_subscription	An empty stub — CREATE SUBSCRIPTION is not parsed, so nothing can populate it
pg_locks	Lock introspection is not exposed
pg_publication_namespace	ALTER PUBLICATION ... ADD TABLES IN SCHEMA is rejected, so this is never populated
pg_statistic_ext	No extended statistics objects

Absent entirely — these fail with relation "..." does not exist (42P01) rather than returning zero rows: pg_largeobject, pg_subscription_rel, pg_statistic_ext_data, pg_rules (use pg_rewrite) and pg_matviews (use pg_class filtered on relkind = 'm'). Also absent, and often reached for: pg_statistic, pg_shdepend, pg_ts_config_map, pg_cursors, pg_available_extensions, pg_file_settings, pg_hba_file_rules, pg_config, pg_user_mapping and pg_user_mappings.

Most of the pg_stat* family is absent as well. The views listed above — pg_stat_activity, pg_stat_database, pg_stat_all_tables, pg_stat_user_tables, pg_stat_all_indexes, pg_stat_replication, pg_stat_gssapi, pg_stat_ssl, pg_statio_all_tables, pg_statio_all_indexes, pg_statistic_ext and pg_stats — are the ones that resolve. The _user_ and _sys_ index and I/O variants (pg_stat_user_indexes, pg_stat_sys_tables, pg_statio_user_tables, pg_statio_user_indexes, …), pg_stat_statements, pg_stat_bgwriter and pg_stat_io all raise 42P01. One of the missing relations carries real weight: drizzle-kit push reads pg_stat_user_indexes for each existing table, so every push after the first fails against DB9. See ORM schema and migration tooling.

Do not take either list on trust across builds. A pg_catalog relation resolves exactly when it has a pg_class row in that namespace, so this is the authoritative enumeration for the database you are connected to — anything missing from it raises 42P01:

SQL

SELECT c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'pg_catalog'
ORDER BY c.relname;

“Resolves” and “has rows” are separate questions, and the second one is per database: pg_extension and pg_publication fill up as you install extensions and create publications. Use SELECT count(*) against the names above rather than assuming, and compare against a name you know is fake (SELECT count(*) FROM pg_catalog.pg_bogus_zzz) so you can tell an empty relation from a missing one.

information_schema Views

View	Description
tables	Tables
columns	Table columns (includes is_generated, generation_expression)
schemata	Schemas
sequences	Sequences
routines	Functions and procedures
table_constraints	PRIMARY KEY, FOREIGN KEY, CHECK and UNIQUE constraints (never NOT NULL — see below)
key_column_usage	PRIMARY/FOREIGN KEY columns
referential_constraints	Foreign key constraints
check_constraints	CHECK constraints
constraint_column_usage	Column constraint usage
table_privileges	Table access privileges
triggers	Triggers, with the declared action_timing (BEFORE / AFTER) and action_orientation (ROW / STATEMENT)
views	Views, with view_definition (the stored query, with relation names schema-qualified and quoted), check_option and is_updatable

The table above is the complete information_schema surface — any other information_schema relation, such as attributes, fails with relation "…" does not exist (42P01).

Cron Catalog

View	Description
cron.job	Scheduled cron jobs
cron.job_run_details	Job execution history
cron.running_jobs	Currently executing jobs (superuser only) — see pg_cron

---

Query Recipes

Practical queries for introspecting your database. Replace 'public' and 'my_table' with your actual schema and table names.

List all tables in a schema

Returns all user-defined tables in a schema with their owner and whether each has indexes, rules, and triggers. The second query lists the same tables from information_schema, which is more portable.

SQL

SELECT
  tablename        AS table,
  tableowner       AS owner,
  hasindexes       AS indexed,
  hasrules         AS has_rules,
  hastriggers      AS has_triggers
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

SELECT
  table_name,
  table_type
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

▶ Run

List columns of a table

Returns all columns with their data type, nullability, and default value.

SQL

SELECT
  column_name,
  data_type,
  character_maximum_length,
  is_nullable,
  column_default,
  ordinal_position
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name   = 'my_table'
ORDER BY ordinal_position;

-- Using pg_attribute for lower-level detail (includes system columns):
SELECT
  a.attname                               AS column,
  pg_catalog.format_type(a.atttypid, a.atttypmod) AS type,
  a.attnotnull                            AS not_null,
  a.attnum                                AS position
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relname = 'my_table'
  AND a.attnum  > 0          -- exclude system columns
  AND NOT a.attisdropped
ORDER BY a.attnum;

Find all indexes on a table

Lists every index on a table, including the columns covered and whether it is unique or primary.

SQL

SELECT
  indexname   AS index,
  indexdef    AS definition
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename  = 'my_table'
ORDER BY indexname;

-- More detailed view via pg_index + pg_class:
SELECT
  i.relname                                   AS index,
  ix.indisunique                              AS unique,
  ix.indisprimary                             AS primary,
  array_agg(a.attname ORDER BY a.attnum)      AS columns
FROM pg_index     ix
JOIN pg_class     t  ON t.oid  = ix.indrelid
JOIN pg_class     i  ON i.oid  = ix.indexrelid
JOIN pg_attribute a  ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
JOIN pg_namespace n  ON n.oid  = t.relnamespace
WHERE n.nspname = 'public'
  AND t.relname = 'my_table'
GROUP BY i.relname, ix.indisunique, ix.indisprimary
ORDER BY i.relname;

Check table constraints

The information_schema query returns the PRIMARY KEY, UNIQUE, CHECK and FOREIGN KEY constraints on a table; the pg_constraint variant additionally returns NOT NULL.

SQL

SELECT
  tc.constraint_name,
  tc.constraint_type,
  kcu.column_name,
  cc.check_clause
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
  ON kcu.constraint_name = tc.constraint_name
  AND kcu.table_schema   = tc.table_schema
LEFT JOIN information_schema.check_constraints cc
  ON cc.constraint_name  = tc.constraint_name
  AND cc.constraint_schema = tc.constraint_schema
WHERE tc.table_schema = 'public'
  AND tc.table_name   = 'my_table'
ORDER BY tc.constraint_type, tc.constraint_name;

-- Concise version using pg_constraint:
SELECT
  conname                     AS constraint,
  contype                     AS type,  -- p=PK, u=UNIQUE, c=CHECK, f=FK, n=NOT NULL
  pg_get_constraintdef(oid)   AS definition
FROM pg_constraint
WHERE conrelid = 'public.my_table'::regclass
ORDER BY contype, conname;

NOT NULL appears only in pg_constraint

The two queries above do not return the same set. information_schema.table_constraints emits only PRIMARY KEY, FOREIGN KEY, CHECK and UNIQUE — DB9 never surfaces a NOT NULL row there, unlike PostgreSQL, which reports each one as a generated CHECK.

pg_constraint does expose them, as contype = 'n' with a <table>_<column>_not_null name, so the concise query returns one extra row per NOT NULL column.

Which PostgreSQL you are porting from matters here. PostgreSQL only records NOT NULL in pg_constraint as of version 18, while DB9 reports 17.0 in version(). A query calibrated against the version DB9 claims to be — or against PostgreSQL 16, which has no contype = 'n' rows at all — will therefore see rows it does not expect. A query written for PG18 ports over as-is, with one difference: PG18 preserves a user-supplied NOT NULL constraint name, and DB9 does not. CONSTRAINT my_nn NOT NULL is stored as <table>_<column>_not_null regardless. Explicit PRIMARY KEY, UNIQUE and CHECK names are preserved as written.

To list nullability, prefer information_schema.columns.is_nullable or pg_attribute.attnotnull over either constraint view.

See RLS policies

Lists all row-level security policies on a table, including which roles they apply to and their USING / WITH CHECK expressions.

SQL

SELECT
  policyname      AS policy,
  cmd             AS command,   -- ALL, SELECT, INSERT, UPDATE, DELETE
  roles           AS applies_to,
  qual            AS using_expr,
  with_check      AS check_expr
FROM pg_policies
WHERE schemaname = 'public'
  AND tablename  = 'my_table'
ORDER BY policyname;

-- Check if RLS is enabled on the table:
SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class
WHERE oid = 'public.my_table'::regclass;

Find foreign keys

Returns all foreign key relationships — both outbound (this table references another) and inbound (other tables reference this one).

SQL

-- Outbound: FK constraints on my_table
SELECT
  tc.constraint_name,
  kcu.column_name            AS fk_column,
  ccu.table_name             AS references_table,
  ccu.column_name            AS references_column,
  rc.update_rule,
  rc.delete_rule
FROM information_schema.table_constraints     tc
JOIN information_schema.key_column_usage      kcu ON kcu.constraint_name = tc.constraint_name
  AND kcu.table_schema = tc.table_schema
JOIN information_schema.referential_constraints rc ON rc.constraint_name = tc.constraint_name
  AND rc.constraint_schema = tc.constraint_schema
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = rc.unique_constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema    = 'public'
  AND tc.table_name      = 'my_table'
ORDER BY tc.constraint_name;

-- All FK constraints in the schema (both directions):
SELECT
  c.conrelid::regclass::text  AS from_table,
  c.confrelid::regclass::text AS to_table,
  c.conname                   AS constraint,
  pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
JOIN pg_class     t ON t.oid = c.conrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE c.contype = 'f'
  AND n.nspname = 'public'
ORDER BY from_table, c.conname;

Pitfall: cast regclass to text, and filter the schema by join

Things to watch when adapting reg* queries. They fail silently rather than erroring:

• DB9 difference — ::regclass alone renders the numeric OID, not the relation name. SELECT conrelid::regclass returns 10000000005, where PostgreSQL returns child_t. Add ::text to get the name.
• regclass::text is schema-qualified only when the relation is not visible in search_path. With the default "$user", public, a table in public renders bare as child_t while a table in another schema renders as other_schema.t. So the common idiom WHERE conrelid::regclass::text LIKE 'public.%' returns zero rows on a schema that does have foreign keys — and under a non-default search_path it is worse than useless: a public table shadowed by a same-named one earlier in the path renders qualified and does match, so the filter silently returns an arbitrary subset. Matching on the bare name instead is no better — it breaks as soon as a table lives outside search_path, or has a name that needs quoting (public."Mix Ed" renders as "Mix Ed", quotes included). This idiom is equally broken on PostgreSQL; it is a widespread bug, not a DB9 limitation. Join to pg_class and pg_namespace and filter on nspname instead, as shown above.
• regrole never renders a name, with or without ::text. 3373::oid::regrole::text returns 3373, and so does a role OID that does exist. regclass, regtype and regnamespace all resolve to names once you add ::text; regrole does not, so join to pg_roles on oid when you need the role name.

Casting in the other direction is unaffected — WHERE conrelid = 'public.my_table'::regclass works as written, including for tables outside search_path.

List all functions

Returns all user-defined functions in a schema with their return types.

SQL

SELECT
  routine_name         AS function,
  routine_type         AS type,
  data_type            AS return_type
FROM information_schema.routines
WHERE routine_schema = 'public'
ORDER BY routine_name;

-- More detail via pg_proc:
SELECT
  p.proname                        AS function,
  format_type(p.prorettype, NULL)  AS return_type,
  p.prokind                        AS kind        -- 'f' function, 'a' aggregate, 'p' procedure, 'w' window
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public'
ORDER BY p.proname;

Note

pg_proc exposes oid, proname, pronamespace, proowner, prorettype, prokind, prosecdef, proacl, pronargs, pronargdefaults, proargtypes, proargmodes, proargnames, proretset, prorows, provolatile, provariadic, proconfig, proisstrict, prolang, prosrc, probin, procost, proleakproof, protrftypes, proparallel, prosupport, proallargtypes, proargdefaults and prosqlbody.

Argument-level introspection works for user-defined functions — a function declared add_two(a int, b int) reports pronargs = 2, proargtypes = 23 23 and proargnames = {a,b}:

SQL

SELECT proname, pronargs, proargtypes, proargnames
FROM pg_proc WHERE proname = 'add_two';

This is reliable only for functions you created. For catalog built-ins the argument columns are not trustworthy, and each one fails differently:

• proargnames and proargmodes are NULL for every pg_catalog row except the table functions pg_options_to_table, pg_available_extensions, pg_show_all_settings, pg_event_trigger_ddl_commands and pg_event_trigger_dropped_objects — test them with IS NULL, since proargnames = '{}' matches nothing.
• provolatile is populated for some rows and NULL for others, and the split does not follow anything you can predict: abs, age and concat read NULL, and so do now and random, which PostgreSQL marks volatile. The only pg_catalog rows that report v are the size functions pg_relation_size, pg_table_size and pg_total_relation_size, so a WHERE provolatile = 'v' filter does not find the volatile functions.
• proargtypes is frequently NULL even when pronargs says there are arguments — age, array_length, concat and array_to_string are all like this, and they are common enough in pg_catalog that you have to assume it rather than spot-check it. Test it with proargtypes IS NULL: cardinality(proargtypes) = 0 does not match these rows (it matches only zero-argument functions), and the comparison proargtypes = '{}' fails with invalid input syntax for type oid: "{}" (22P02). Don’t rely on a fixed row count: the number depends on which extensions the database has, not just on the server version — see below.
• pronargs itself can be wrong. make_interval and the json_build_* / jsonb_build_* family report pronargs = 0 while accepting arguments, and variadic functions under-report: concat says 1 but concat('a','b','c','d','e') works. The extensions.http* family has pronargs and proargtypes both NULL, though each takes one to four arguments.

Where arity or argument types matter for a built-in, confirm them by calling the function.

pg_get_function_arguments(), pg_get_function_result(), pg_proc.prosrc and the pg_language catalog all work for functions you created:

SQL

SELECT p.proname, l.lanname,
       pg_get_function_arguments(p.oid) AS args,
       pg_get_function_result(p.oid)    AS result,
       p.prosrc
FROM pg_proc p
JOIN pg_language l ON l.oid = p.prolang
WHERE p.proname = 'add_two';
-- add_two | sql | a integer, b integer | integer | SELECT a + b;

For pg_catalog built-ins they are only partly filled in. pg_get_function_result() returns the result type for every row, but pg_get_function_arguments() returns NULL wherever proargtypes is NULL — age, concat and the other rows above — so it cannot tell you those signatures. prosrc IS NULL for every row but the five table functions named above, and prolang always reads internal. So these are tools for inspecting your own schema, not for discovering a built-in’s signature — for that, call the function, or use information_schema.routines for a portable summary.

pg_proc is not a complete list of callable functions

Many built-ins are implemented in the executor and are not registered in the catalog. MIN, MAX, ARRAY_AGG, GREATEST, SUBSTR, COALESCE and UNNEST are all absent from pg_proc yet work normally — while SUM and AVG, their closest relatives, are registered. Catalog absence is never evidence that a function is missing — call it instead.

Registration is not fixed for a given server build either — it is per database. CREATE EXTENSION fs9 adds its fs9_* functions to pg_proc, so two databases on the same build report different counts. Mind the namespace when you check: the scalar fs9_* functions land in pg_catalog and the table functions (fs9_events, fs9_storage_stats, fs9_cached_storage_stats) in extensions, so a bare proname LIKE 'fs9%' counts more of them than a query filtered to pg_catalog does. ABS, for one, is registered, with a row for each of smallint, integer, bigint, real, double precision and numeric. Neither absence nor presence is a stable property — call the function.

The fs9_* rows added to pg_catalog carry no argument metadata — their proargtypes is NULL — so installing the extension raises the count of pg_catalog rows you cannot introspect without raising the count you can. The table functions in extensions do carry argument types.

Read a trigger definition

pg_get_triggerdef(oid) reconstructs the whole CREATE TRIGGER statement, which is the most readable way to recover a trigger’s timing, events and arguments without decoding the tgtype bitmask:

SQL

SELECT t.tgname, pg_get_triggerdef(t.oid) AS definition
FROM pg_trigger t
JOIN pg_class c ON c.oid = t.tgrelid
WHERE c.relname = 'orders' AND NOT t.tgisinternal
ORDER BY t.tgname;

The definition column comes back fully qualified and ready to replay:

CREATE TRIGGER orders_audit AFTER INSERT ON public.orders FOR EACH ROW EXECUTE FUNCTION public.orders_audit_fn()

Keep the NOT tgisinternal filter. A foreign key installs RI_ConstraintTrigger_* rows on both the referencing table and the one it points at, and pg_get_triggerdef() returns NULL for those. Filter them out with NOT tgisinternal; a <> '' test on the definition compares against NULL and yields NULL, so use IS NOT NULL if you filter on the definition itself.

Like most scalar functions, pg_get_triggerdef() is callable only in the select list; in the FROM clause it reports function pg_get_triggerdef(oid) does not exist (42883). That is ordinary call-position behaviour, not a missing function — a name that really is absent returns 42883 from both positions.

Check table sizes

Returns disk usage for every table, largest first. DB9 reports sizes through the storage accounting virtual tables rather than the PostgreSQL size functions.

SQL

SELECT
  table_name,
  data_bytes,
  index_bytes,
  total_bytes,
  round(total_bytes::numeric / 1024 / 1024, 2) AS total_mb
FROM _DB9_SYS_TABLE_STORAGE_STATS
ORDER BY total_bytes DESC;

-- Database-level totals:
SELECT
  database_name,
  total_bytes,
  round(total_bytes::numeric / 1024 / 1024, 2) AS total_mb,
  scanned_at
FROM _DB9_SYS_STORAGE_STATS;

DB9 Difference: PostgreSQL size functions do not report sizes

pg_total_relation_size(), pg_relation_size(), pg_table_size() and pg_database_size() resolve but return NULL rather than a size — with no error, so a query built on them looks healthy and reports nothing. pg_indexes_size() and pg_size_bytes() do not exist at all (function <name>(...) does not exist, 42883).

pg_size_pretty(bigint) does work as a formatter in the select list — pg_size_pretty(1048576) returns 1024 kB — but it has no numeric overload, and wrapping one of the size functions above just formats a NULL. Take the byte counts from the storage virtual tables above instead.