Support temporal/period constraints: PRIMARY KEY/UNIQUE ... WITHOUT OVERLAPS (PG17) and FOREIGN KEY ... PERIOD (PG18)

[pematt]

Feature description

pgModeler cannot model PostgreSQL's native temporal (period) constraints, so databases that use them cannot be round-tripped, diffed, or generated from a model.

Two related, standard-SQL features are missing:

1. Temporal PRIMARY KEY / UNIQUE — WITHOUT OVERLAPS (PostgreSQL 17+)
   ALTER TABLE subscription_statuses
   ADD CONSTRAINT subscription_statuses_pk
   PRIMARY KEY (status_id, valid_period WITHOUT OVERLAPS);
2. The trailing key column is a range/multirange (e.g. tstzrange); WITHOUT OVERLAPS enforces that rows sharing the scalar key parts may not have overlapping periods. (Requires btree_gist when scalar columns participate.)
3. Temporal FOREIGN KEY — PERIOD (PostgreSQL 18+)
   ALTER TABLE subscriptions
   ADD CONSTRAINT subscriptions_status_fk
   FOREIGN KEY (status_id, PERIOD valid_period)
   REFERENCES subscription_statuses (status_id, PERIOD valid_period);
4. The PERIOD column must be covered, for the entire referencing period, by the union of matching referenced rows.

What's needed in pgModeler:

• Constraint editor: a WITHOUT OVERLAPS flag on the last column of a PK/UNIQUE constraint, and a PERIOD flag on the period column of a FK (both source and referenced column lists).
• SQL generation: emit the WITHOUT OVERLAPS / PERIOD keywords. The current template conf/schemas/sql/constraint.sch has no token for either — it only supports the classic constraint attributes (pk-constr, fk-constr, uq-constr, del-action, upd-action, deferrable, …).
• Model/XML: a way to persist the flags (e.g. a without-overlaps="true" attribute on pk-constr/uq-constr and a per-column period="true" marker on fk-constr) so models round-trip.
• Reverse engineering & diff: import these constraints from a live DB and treat them as first-class in the diff tool, so they aren't reported as drift / scheduled for DROP when a modeled DB already uses them.

Sample image

Not a visual/diagram feature — it's DDL generation + the constraint editor. The minimal UI touchpoints:

  ┌─ Primary key / Unique constraint ──────────────┐
  │ Columns:  [status_id          ]                 │
  │           [valid_period        ]  ☑ WITHOUT OVERLAPS   ← new checkbox on last col
  └─────────────────────────────────────────────────┘

  ┌─ Foreign key ──────────────────────────────────┐
  │ Src columns:   status_id ,  valid_period ☑ PERIOD     ← new "PERIOD" flag
  │ Ref columns:   status_id ,  valid_period ☑ PERIOD
  │ ON DELETE [NO ACTION ▾]   ON UPDATE [NO ACTION ▾]│
  └─────────────────────────────────────────────────┘

Additional info

• Versions: WITHOUT OVERLAPS for PK/UNIQUE shipped in PostgreSQL 17; temporal FOREIGN KEY ... PERIOD (and UPDATE/DELETE ... FOR PORTION OF, which is DML and out of scope here) shipped in PostgreSQL 18. Tested working on PostgreSQL 18.3, including a temporal FK on a partitioned referencing table.
• Reproduction (current behaviour): create the constraints above in a database, then reverse-engineer it in pgModeler 2.0.0-beta — the temporal qualifiers are lost; and a model→DB diff of a database that uses them reports them as objects to drop, because the model can't represent them.
• Impact: projects on PG17/18 that adopt temporal integrity must currently emulate it with BEFORE INSERT/UPDATE/DELETE trigger functions (to keep the relationship inside the pgModeler model and out of the diff tool's "drift" set). Native template support would let these be modeled directly and retire the trigger workarounds.
• Docs: PostgreSQL CREATE TABLE / ALTER TABLE constraint syntax (WITHOUT OVERLAPS, PERIOD), and the SQL:2011 application-time period tables this implements.

[pematt]

-- ============================================================================
-- pgModeler test reference: temporal / application-time period constraints
--
--   * PRIMARY KEY / UNIQUE ... WITHOUT OVERLAPS          (PostgreSQL 17+)
--   * FOREIGN KEY ... PERIOD ... REFERENCES ... PERIOD   (PostgreSQL 18+)
--
-- Attached to the pgModeler feature request asking for native modelling of
-- these constraints (currently not representable: conf/schemas/sql/constraint.sch
-- has no WITHOUT OVERLAPS / PERIOD token, so they are lost on reverse-engineering
-- and reported as drift by the diff tool).
--
-- Self-contained: creates everything under schema `temporal_demo`.
-- Verified end-to-end on PostgreSQL 18.3.
--
-- Usage:
--   psql -f temporal-constraints-pgmodeler-demo.sql
-- Then reverse-engineer the `temporal_demo` schema in pgModeler to confirm the
-- temporal qualifiers round-trip. (The teardown at the bottom is commented out
-- so the objects persist for import; re-running the script is idempotent.)
-- ============================================================================

-- Prerequisite: btree_gist lets scalar columns (bigint/text) sit alongside the
-- range column in the GiST index that backs WITHOUT OVERLAPS.
CREATE EXTENSION IF NOT EXISTS btree_gist;

DROP SCHEMA IF EXISTS temporal_demo CASCADE;
CREATE SCHEMA temporal_demo;
SET search_path TO temporal_demo;

-- ----------------------------------------------------------------------------
-- 1. Temporal PRIMARY KEY (WITHOUT OVERLAPS)
--    No two rows with the same status_id may have overlapping valid_period.
-- ----------------------------------------------------------------------------
CREATE TABLE status_whitelist (
    status_id    bigint NOT NULL,
    valid_period tstzrange NOT NULL,
    label        text,
    CONSTRAINT status_whitelist_pk
        PRIMARY KEY (status_id, valid_period WITHOUT OVERLAPS)
);

-- ----------------------------------------------------------------------------
-- 2. Temporal UNIQUE (WITHOUT OVERLAPS) alongside a plain surrogate PK
--    Covers the UNIQUE variant, which pgModeler must also model.
-- ----------------------------------------------------------------------------
CREATE TABLE plan_catalogue (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code         text NOT NULL,
    valid_period tstzrange NOT NULL,
    CONSTRAINT plan_catalogue_code_uq
        UNIQUE (code, valid_period WITHOUT OVERLAPS)
);

-- ----------------------------------------------------------------------------
-- 3. Temporal FOREIGN KEY (PERIOD) — non-partitioned referencing table
--    The whole span of subscriptions.valid_period must be covered by matching
--    status_whitelist rows.
-- ----------------------------------------------------------------------------
CREATE TABLE subscriptions (
    id           uuid NOT NULL DEFAULT gen_random_uuid(),
    status_id    bigint NOT NULL,
    valid_period tstzrange NOT NULL,
    CONSTRAINT subscriptions_pk PRIMARY KEY (id, valid_period WITHOUT OVERLAPS),
    CONSTRAINT subscriptions_status_fk
        FOREIGN KEY (status_id, PERIOD valid_period)
        REFERENCES status_whitelist (status_id, PERIOD valid_period)
);

-- ----------------------------------------------------------------------------
-- 4. Temporal FOREIGN KEY (PERIOD) — PARTITIONED referencing table
--    Demonstrates the FK propagating to and enforcing on partitions.
-- ----------------------------------------------------------------------------
CREATE TABLE subscriptions_part (
    id                 uuid NOT NULL DEFAULT gen_random_uuid(),
    upper_valid_period timestamptz NOT NULL DEFAULT 'infinity',
    status_id          bigint NOT NULL,
    valid_period       tstzrange NOT NULL,
    CONSTRAINT subscriptions_part_pk PRIMARY KEY (id, upper_valid_period),
    CONSTRAINT subscriptions_part_status_fk
        FOREIGN KEY (status_id, PERIOD valid_period)
        REFERENCES status_whitelist (status_id, PERIOD valid_period)
) PARTITION BY RANGE (upper_valid_period);

CREATE TABLE subscriptions_part_active
    PARTITION OF subscriptions_part FOR VALUES FROM ('2000-01-01') TO (MAXVALUE);

-- ----------------------------------------------------------------------------
-- 5. Enforcement smoke test (each failing case is trapped so the script runs
--    to completion). Optional — the DDL above is what pgModeler must model.
-- ----------------------------------------------------------------------------
INSERT INTO status_whitelist VALUES (1, tstzrange('2026-01-01', NULL), 'active');

\echo '(a) covered insert -> expect SUCCESS'
INSERT INTO subscriptions_part (status_id, valid_period) VALUES (1, tstzrange('2026-06-01', NULL));

\echo '(b) status not in whitelist -> expect FK VIOLATION'
DO $$ BEGIN
  INSERT INTO subscriptions_part (status_id, valid_period) VALUES (42, tstzrange('2026-06-01', NULL));
  RAISE EXCEPTION 'should not reach here';
EXCEPTION WHEN foreign_key_violation THEN RAISE NOTICE 'OK: rejected non-whitelisted status'; END $$;

\echo '(c) period not fully covered -> expect FK VIOLATION'
DO $$ BEGIN
  INSERT INTO subscriptions_part (status_id, valid_period) VALUES (1, tstzrange('2025-01-01', NULL));
  RAISE EXCEPTION 'should not reach here';
EXCEPTION WHEN foreign_key_violation THEN RAISE NOTICE 'OK: rejected partial temporal coverage'; END $$;

\echo '(d) overlapping whitelist rows -> expect EXCLUSION (WITHOUT OVERLAPS) VIOLATION'
DO $$ BEGIN
  INSERT INTO status_whitelist VALUES (1, tstzrange('2026-06-01', NULL), 'dup');
  RAISE EXCEPTION 'should not reach here';
EXCEPTION WHEN exclusion_violation THEN RAISE NOTICE 'OK: rejected overlapping period for same key'; END $$;

\echo '--- temporal_demo schema ready for reverse-engineering in pgModeler ---'

-- Teardown (commented out so the objects persist for import testing):
-- DROP SCHEMA temporal_demo CASCADE;

[rkhaotix]

Thanks @pematt for such a detailed request.

I'm working on finishing the git plugin for pgModeler and when possible I'll take care of this.:)