postgres: a shortened blocks field list leaves orphaned _rels rows behind

[JagpalSinghKooner]

Payload version: 3.82.1 (@payloadcms/db-postgres 3.82.1)
Adapter: @payloadcms/db-postgres (Postgres, via postgresAdapter)
Reproduced: 8 August 2026, against a fresh seed on a dedicated test database

Summary

When a document's blocks-type field is saved with fewer rows than it held
before, and one of the dropped rows carried a relationship field, the
relationship's _rels row is not deleted. It survives with a path naming a
block index the document no longer has a block at, pointing at whatever
document it used to relate to.

Reproduction

Collection: any collection with a top-level blocks field named sections
whose blocks can carry a relationship field, for example a faq block
with a hasMany relationship field named faqs.

1. Create a document with two blocks in sections, each with a faqs
   relationship picking one document:

   await payload.create({
     collection: 'pages',
     data: {
       // ...
       sections: [
         { blockType: 'faq', /* ... */ faqs: [faqOneId] },
         { blockType: 'faq', /* ... */ faqs: [faqTwoId] },
       ],
     },
   })

   This writes two rows to pages_blocks_faq (_order 1 and 2, _path
'sections') and two rows to pages_rels (path 'sections.0.faqs' and
   'sections.1.faqs').

2. Shorten the list to the first block only, through the database adapter
   directly (payload.db.updateOne), which is the same write path a
   payload.update call reaches once field hooks and validation have run:

   await payload.db.updateOne({
     collection: 'pages',
     id,
     data: { sections: [firstBlockOnly] },
   })

3. Query pages_rels for the document's id.

Expected: one row, path = 'sections.0.faqs'.

Actual: two rows. sections.1.faqs survives, still pointing at the
second block's picked document, even though block index 1 no longer exists
in pages_blocks_faq.

Path grammar this rests on

• A relationship inside a block stores its _rels row with path like
  sections.<zero-based index>.faqs.
• The block's own row lives in <collection>_blocks_<slug>, with
  _path = 'sections' and a one-based _order. Path index N is live
  exactly when a block row holds _order = N + 1.
• A versioned collection's versions twin prefixes the path with version.
  (version.sections.<index>.faqs, _path = 'version.sections'), and its
  _rels.parent_id references the version row, not the published document.
• Only a top-level blocks field's own table carries _path; a nested array
  table inside a block does not, which is the distinguishing signal between
  the two.

What a fix would look like

The write path that reinserts a document's block rows reinserts the _rels
rows the surviving blocks reference, but does not delete _rels rows whose
path names an index no surviving block occupies. A fix would delete every
_rels row under the field's path prefix before reinserting the rows the
current save still references, the same way the block rows themselves are
deleted and reinserted, so a shortened list leaves nothing pointing at a
position that no longer exists.

Downstream workaround

We currently work around this with a collection afterChange hook that
deletes a document's orphaned _rels rows on the request's own transaction
after every save, plus a one-off cleanup migration for rows the leak had
already produced. The regression is pinned by an integration test that
reproduces the leak through payload.db.updateOne, written to fail the day
this is fixed upstream, at which point the workaround retires.