1. Deleting one partition disables the foreign key for every other partition

_drop_pg_child_tables (sqlalchemy_segment_store.py:1064-1080) drops both child tables with CASCADE. segment_store_dv_ln has a foreign key to segment_store_sg declared on the partitioned parents, so the CASCADE does not stop at the partition being deleted -- it drops the parent-level constraint outright.

Measured on PostgreSQL 16, with two partitions a and b, deleting b and then inserting a dv_ln row in a that points at a nonexistent segment:

baseline (nothing dropped):                  orphan accepted = False
after dropping partition b (current order):  orphan accepted = True
after dropping partition b (reversed order): orphan accepted = True

After any partition deletion the store no longer enforces the link for the partitions that remain, and ON DELETE CASCADE stops working with it -- so delete_segments, which relies on it ("CASCADE deletes derivatives via FK", line 682), silently leaves orphaned derivative links behind. Those rows are visible through the public API, because get_derivative_uuids_by_segment_uuids reads segment_store_dv_ln directly with no join to segment_store_sg: deleted segments keep returning their derivative UUIDs.

This is deterministic, not a race, and it happens on the first partition deletion.

Reversing the drop order does not help, as the measurements above show. Dropping without CASCADE fails with DependentObjectsStillExistError, which is presumably why CASCADE is there. Detaching each child before dropping it preserves both the constraint and the cascade:

DROP CASCADE, dv_ln then sg (today)      ok      FK still enforced=False  cascade works=False
DROP CASCADE, sg then dv_ln (reversed)   ok      FK still enforced=False  cascade works=False
DROP without CASCADE, dv_ln then sg      error   FK still enforced=True   cascade works=True
DETACH both, then DROP                   ok      FK still enforced=True   cascade works=True

2. Concurrent create and delete can deadlock

The two paths reach the parent tables in opposite order:

• _create_pg_child_tables (1044-1062): segment_store_sg, then segment_store_dv_ln
• _drop_pg_child_tables (1064-1080): segment_store_dv_ln, then segment_store_sg

Both statement kinds take ACCESS EXCLUSIVE on the parent, so the order matters. Nothing serializes the two paths beforehand: the create paths take LOCK TABLE segment_store_pt IN SHARE ROW EXCLUSIVE MODE (line 801), while delete_partition takes only SELECT ... FOR UPDATE on the partition row (936-940), which is a ROW SHARE table lock -- and ROW SHARE does not conflict with SHARE ROW EXCLUSIVE.

Creating one partition while deleting a different one, across seven sampled interleavings against PostgreSQL 16:

delete: FOR UPDATE only (today)         -> deadlocks in 4/7 interleavings
delete: LOCK TABLE + FOR UPDATE (fix)   -> deadlocks in 0/7 interleavings

The row lock has to stay: it is what makes delete_partition wait for in-flight writers, which hold FOR SHARE on the same row via _lock_partition_for_write (222-234). SHARE ROW EXCLUSIVE does not conflict with the ROW SHARE those writers take, so the table lock alone would let the drops start before writers finish. The fix is to take the table lock in addition to the row lock, not instead of it.

Dropping the constraint explicitly is possible but expensive. ALTER TABLE segment_store_dv_ln DROP CONSTRAINT ..., then the two DROP TABLEs, then re-adding the foreign key all succeed, but the re-add validates every existing row across all remaining partitions -- an O(N) scan in place of an O(1) partition drop, holding SHARE ROW EXCLUSIVE on both tables while it runs. The usual escape hatch is unavailable: PostgreSQL rejects ADD CONSTRAINT ... NOT VALID for a foreign key on a partitioned table (cannot add NOT VALID foreign key on partitioned table). Removing only the per-partition constraint entry is also refused (cannot drop inherited constraint), so it is the whole constraint or nothing.

Detaching is the constant-time alternative, and only the segment_store_sg child strictly needs it -- the link child has nothing depending on it. This change detaches both, so the rule reads uniformly and does not depend on which way the foreign key points.

Scope

The event-memory backend is opt-in: a missing backend deserializes as "declarative" (episodic_config.py:13-30), and the change that would make event the default is not merged. So this only affects a database where someone explicitly configured backend: event on PostgreSQL and has deleted a partition -- development and benchmark databases in practice.

Worth knowing for those: the constraint does not come back by itself, because metadata.create_all(checkfirst=True) skips tables that already exist. Recreating the schema is the simplest remedy; no migration is warranted at this stage.

Fix

Both are addressed in the linked PR: detach each child partition before dropping it, and have delete_partition acquire the same LOCK TABLE ... IN SHARE ROW EXCLUSIVE MODE the create paths take, before the existing row lock. A regression test covers the first (it fails against PostgreSQL before the change and passes after); the second is a lock-ordering change with no black-box assertion, since a deadlock test would be timing-dependent.

🤖 Written by Claude Code (Opus 5) on behalf of @edwinyyyu.