PGXN

New functions and types for working with routines and partitioned relations.

# PostgreSQL Version Support

PostgreSQL 10 and 11 are no longer supported as of this release. The 0.2.3→0.3.0 update script adds values to existing enum types (ALTER TYPE ... ADD VALUE), which cannot run inside an extension update script on PG11 or earlier (a restriction lifted in PG12). Because a version that cannot be updated to is not truly supported, PG10 and PG11 support is dropped entirely.

cat_tools 0.3.0 supports PostgreSQL 12 through 18+.

# Changes

• Added routine/function/procedure introspection support: new enum types routine_type, routine_argument_mode, routine_volatility, and routine_parallel_safety, plus the composite type routine_argument.

• Added routine functions for parsing and retrieving argument information (routine__parse_arg_types, routine__parse_arg_names, routine__arg_types, routine__arg_names, and their _text variants), along with the mapping functions routine__type, routine__argument_mode, routine__volatility, and routine__parallel_safety.

• function__arg_types() and function__arg_types_text() are now deprecated in favor of routine__parse_arg_types() and routine__parse_arg_types_text().

• Fixed a gap in the update path from 0.2.0/0.2.1: the five enum types that predate 0.2.2 (constraint_type, procedure_type, relation_type, relation_relkind, object_type) never received their cat_tools__usage USAGE grant when reached via that path — only a fresh 0.2.2+ install had it, since ALTER DEFAULT PRIVILEGES only covers objects created after it runs, and these types predate it in the legacy update scripts. The 0.2.3→0.3.0 update script now grants it retroactively.

• Corrected the relation__kind() / relation__relkind() mapping for relkind values c, f, and m (composite type, foreign table, and materialized view respectively), which were previously mapped incorrectly.

• _cat_tools helper functions now REVOKE EXECUTE ... FROM PUBLIC so they are no longer callable by roles without cat_tools__usage.

Fix the backwards pg_class.relkind mapping (c/f/m) in relation__kind() and relation__relkind(). Also fix binary pg_upgrade for databases updated from 0.2.0/0.2.1, whose catalog views kept columns removed in PostgreSQL 12+ (relhasoids/relhaspkey): to pg_upgrade such a cluster to PG12+, first update the extension to 0.2.3, which rebuilds those views. The rebuild drops and recreates public views (pg_class_v, column, pg_class()), so drop any of your own dependent objects first. See the README "Updating the extension" section and https://github.com/Postgres-Extensions/cat_tools/pull/42.

Compatibility release: fixes broken installs on PostgreSQL 11 and 12+, and provides an update path from 0.2.0 and 0.2.1.

# PostgreSQL Version Support

cat_tools 0.2.1 (and earlier) only installs correctly on PostgreSQL 9.2 – 10. It fails on newer versions due to catalog schema changes:

• PG 11: pg_attribute gained the attmissingval column (pseudo-type anyarray), which cannot be used as a view column. The 0.2.1 install script’s SELECT a.* from pg_attribute picks this column up and fails.

• PG 12: System catalog oid columns became visible as regular columns. Views using SELECT c.* alongside an explicit c.oid alias produce duplicate column names.

cat_tools 0.2.2 installs and updates correctly on PostgreSQL 9.2 through 18+ (all currently supported versions).

# Update Path

You must update cat_tools to 0.2.2 before upgrading PostgreSQL to version 11 or later. Update via ALTER EXTENSION cat_tools UPDATE.

# Update Warning: Objects Depending on cat_tools.column

cat_tools.column in 0.2.0 and 0.2.1 had extra columns: an unqualified SELECT * across a LEFT JOIN pg_constraint accidentally pulled in all pg_constraint columns (conname, contype, etc.). The 0.2.2 update fixes the column list by dropping and recreating cat_tools.column (and its underlying _cat_tools.column).

If you have created any views, functions, or other objects that depend on cat_tools.column, you must drop them before updating and recreate them afterward. The update will fail with an error if any such dependent objects exist — this is intentional, to avoid silently breaking user-defined objects. After dropping your dependent objects, run ALTER EXTENSION cat_tools UPDATE again.

# pg_upgrade Compatibility (Re-release)

pg_upgrade physically copies data files and re-applies schema definitions on the new cluster. Any view that references a catalog column removed in the new PostgreSQL version will cause the upgrade to fail. The initial 0.2.2 release omitted oid and attmissingval from the catalog views, but missed several columns that were later removed from PostgreSQL:

• relhasoids was removed from pg_catalog.pg_class in PG12.

• relhaspkey was removed from pg_catalog.pg_class in PG17.

• attcacheoff was removed from pg_catalog.pg_attribute in PG17.

Without this fix, running pg_upgrade across any of these version boundaries with cat_tools installed would fail.

You must have the re-release of 0.2.2 installed before running pg_upgrade to PostgreSQL 12 or later.

# Changes

• sql/cat_tools--0.1.4--0.1.5.sql was empty; added -- empty update placeholder so PostgreSQL accepts it as a valid (no-op) update script.

• cat_tools.column now exposes attmissingval as text[] (cast from anyarray on PG 11+, or NULL::text[] on older versions). Any SELECT * on cat_tools.column will now include this column.

• Views rebuilt using omit_column() to enumerate columns explicitly, avoiding both the anyarray and duplicate-oid problems.

• A direct 0.2.0 → 0.2.2 update path is provided (cat_tools--0.2.0--0.2.2.sql), which also applies all 0.2.1 function additions in a single step.

• GRANT SELECT ON cat_tools.pg_extension_v TO cat_tools__usage is now applied on the update path from 0.2.0 (it was absent in 0.2.0 and only added via the 0.2.1 update).

• (Re-release) _cat_tools.pg_class_v now explicitly omits relhasoids (removed in PG12) and relhaspkey (removed in PG17) to prevent pg_upgrade failures.

• (Re-release) _cat_tools.pg_attribute_v now explicitly omits attcacheoff (removed in PG17) to prevent pg_upgrade failures.

Fix significant problems with a previous update script.

Add pg_extension_v and related functions.

# Improvements to trigger__parse() Add output fields for the table a trigger is on, as well as the function the trigger is using. Based on work done by Luís Lizardo (https://github.com/lizardoluis).

Add function__arg_types().

Add object type functions (object__catalog(), object__reg_type(), relation__kind(), relation_relkind()).

Add regprocedure().

Add support for Postgres 9.1 and 9.2

=## BUGFIX: Fix trigger__get_oid()

=## BUGFIX: Drop pg_temp objects created by extension

It was not possible to actually install 0.1.2 and 0.1.1 because they left temporary objects behind. During session cleanup, the cascade drop of those objects would cascede to the extension itself.

=## BUGFIX: Install update script

=## Add cat_tools__usage role

Previously, granting permissions to use these tools was difficult. Now all you need to do is grant cat_tools__usage to roles that you would like to have access to these tools. Keep in mind they are not meant for use by people, only by code.