active_record-progresql

ActiveRecord adapter extension for ProgreSQL — teaches ActiveRecord to express and round-trip the fork's schema features so schema.rb stays canonical.

ProgreSQL is a PostgreSQL fork that adds GLOBAL spanning indexes (true cross-partition / cross-inheritance-child PRIMARY KEY / UNIQUE) and cross-child foreign keys (a FK to an inheritance/partition root that resolves into any typed child). Those features need DDL that vanilla ActiveRecord can neither emit nor dump. active_record-progresql closes that gap.

Composes with active_record-mti (which owns the inherits: DSL). active_record-mti provides the inheritance hierarchy; active_record-progresql provides the spanning/GLOBAL primitives over it (and over declarative partitioning, independently).

What it adds

create_table :data, id: :uuid, global: true                    # … PRIMARY KEY (id) GLOBAL
add_index :entity, :id, unique: true, global: true             # CREATE UNIQUE INDEX … GLOBAL
t.index :id, unique: true, global: true

• global: on indexes / primary keys → appends the GLOBAL keyword. Omitted → a normal LOCAL index (unchanged).
  • On a primary key, global: true rides alongside id: (not primary_key:, which names the PK column in ActiveRecord) and emits a table-level PRIMARY KEY (id) GLOBAL — the fork only accepts GLOBAL on a table constraint or CREATE INDEX, never on an inline column constraint.
  • The fork enforces that GLOBAL indexes live on a partitioned (or inheritance-root) table; it rejects them on a plain table.
• Schema-dumper round-trip → db:schema:dump re-emits global: true, so schema.rb doesn't silently lose the fork features (the reason you'd otherwise be forced onto schema_format = :sql). This is the load-bearing feature.
• Cross-child FKs need no new DSL — a standard add_foreign_key to a root resolves into children automatically because the referenced index is GLOBAL. The only primitive is global:.

Spanning-index placement (validated rule)

Auto-derivable from the inheritance tree + FK graph (or declarable explicitly):

Table role	Index
inheritance / partition root	PRIMARY KEY (id) GLOBAL
FK-target sub-root with children	UNIQUE (id) GLOBAL
FK-target leaf	LOCAL UNIQUE (id) — cheaper, ON CONFLICT-able; auto-satisfied by the root GLOBAL PK
leaf that later gains time-bucket children	upgrade its UNIQUE (id) LOCAL → GLOBAL

Golden fixture

The validated target output is db/structure.sql in the claudepilot monorepo — the real fact-web schema (23 tables / 101 FKs / 3 spanning indexes), already loaded clean against ProgreSQL. The gem's DSL → SQL output should reproduce it, and a dump of a database built from it should round-trip back to the same schema.rb.

Detecting ProgreSQL

version() reports a plain PostgreSQL <n> string on the fork, so the adapter detects the fork by probing for fork-only catalog objects:

ActiveRecord::Base.connection.progresql?        # => true on the fork, false on vanilla PostgreSQL
ActiveRecord::Base.connection.progresql_version # => fork feature-set version String, or nil

Detection prefers the blessed progresql_version() function and falls back to the pg_index_partition bootstrap catalog, so it works on fork builds predating the function.

Status

✅ Core feature set complete (verified against a real ProgreSQL build):

1. ✅ global: on add_index / index DDL emit (CREATE [UNIQUE] INDEX … GLOBAL).
2. ✅ global primary key on create_table (table-level PRIMARY KEY (id) GLOBAL).
3. ✅ schema-dumper round-trip — schema.rb stays canonical (dump → load → dump is stable). Includes fork-aware index/PK introspection, since stock AR indexes()/primary_keys() choke on a GLOBAL index's internal partseq column.
4. ⬜ (optional) auto-placement from the inheritance + FK graph.

Plus: ✅ fork detection (progresql? / progresql_version); ✅ cross-child foreign keys verified to resolve through a plain add_foreign_key to a GLOBAL-referenced root (no new DSL).

Introspection depends on ProgreSQL's pg_index_is_global() and pg_index_global_columns() functions (i.e. a build that reports progresql_version()).

Development

bundle install
rake spec        # or: bundle exec rspec

Specs connect to PostgreSQL via PGHOST / PGPORT / PGDATABASE (default localhost:5432, database active_record_progresql_test).

• DDL-generation specs assert the emitted SQL string and run against any PostgreSQL — vanilla is fine, since GLOBAL is only executed, not emitted, against the fork. Tagged :db; skipped if no connection.

• Fork integration specs (tagged :progresql) execute real GLOBAL DDL and need a live ProgreSQL build (>= the progresql-c1 root-first-GLOBAL fix). They auto-skip unless connection.progresql? is true, so point the suite at a fork cluster to run them:

  PGPORT=5444 bundle exec rspec   # a fork cluster on :5444

active_record-mti is a dev dependency for the inheritance-hierarchy fixtures.

License

MIT — see LICENSE.txt.