db-postgres: removing an array row leaves its hasMany relationship rows in _rels at the vacated index; a later row appended there inherits them

[katlianik]

Describe the Bug

On Postgres, when an array field whose rows contain a hasMany relationship field is saved with fewer rows than before (for example a middle row removed), the _rels rows belonging to the positions that no longer exist are not deleted. The array table itself is wiped by _parentID and reinserted, but _rels cleanup is driven only by the paths present in the new write (deleteExistingRowsByPath with rows: [...relationsToInsert, ...generalRelationshipDeletes], exact-path inArray). Paths such as groups.2.visible_to that no surviving row writes are never touched.

The orphan is invisible at read time until a new array row is appended at that index without an explicit value for the relationship field: the read transform looks up _rels by groups.<index>.visible_to, so the new row returns a relationship value it was never given.

Reproduced on both a global and a collection; the behaviour is identical.

This is the array-field counterpart of #17724 / #16647 (blocks). The open PR #17913 adds a path-prefix delete only under field.type === 'blocks', so array fields would remain affected.

Link to the code that reproduces this issue

Minimal config (Payload 3.88.0, @payloadcms/db-postgres 3.88.0, schema created by drizzle push on an empty database):

import { postgresAdapter } from '@payloadcms/db-postgres'
import { buildConfig } from 'payload'

export default buildConfig({
  secret: 'repro-secret',
  db: postgresAdapter({ pool: { connectionString: process.env.DATABASE_URI } }),
  collections: [
    { slug: 'users', auth: true, fields: [] },
    { slug: 'groups', fields: [{ name: 'name', type: 'text' }] },
    {
      slug: 'pages',
      fields: [
        {
          name: 'groups',
          type: 'array',
          fields: [
            { name: 'key', type: 'text' },
            { name: 'visible_to', type: 'relationship', relationTo: 'groups', hasMany: true },
          ],
        },
      ],
    },
  ],
  globals: [
    {
      slug: 'nav',
      fields: [
        {
          name: 'groups',
          type: 'array',
          fields: [
            { name: 'key', type: 'text' },
            { name: 'visible_to', type: 'relationship', relationTo: 'groups', hasMany: true },
          ],
        },
      ],
    },
  ],
})

Script:

import { getPayload } from 'payload'
import config from './payload.config.mjs'

const payload = await getPayload({ config })
const a = await payload.create({ collection: 'groups', data: { name: 'A' } }) // id 1
const b = await payload.create({ collection: 'groups', data: { name: 'B' } }) // id 2
const c = await payload.create({ collection: 'groups', data: { name: 'C' } }) // id 3

// 1. three rows
await payload.updateGlobal({ slug: 'nav', data: { groups: [
  { key: 'r1', visible_to: [a.id] },
  { key: 'r2', visible_to: [b.id] },
  { key: 'r3', visible_to: [c.id] },
] } })

// 2. remove the middle row
await payload.updateGlobal({ slug: 'nav', data: { groups: [
  { key: 'r1', visible_to: [a.id] },
  { key: 'r3', visible_to: [c.id] },
] } })

// 3. append a row without a visible_to value
await payload.updateGlobal({ slug: 'nav', data: { groups: [
  { key: 'r1', visible_to: [a.id] },
  { key: 'r3', visible_to: [c.id] },
  { key: 'r4' },
] } })

const nav = await payload.findGlobal({ slug: 'nav', depth: 0 })
console.log(nav.groups.map((r) => ({ key: r.key, visible_to: r.visible_to })))

Reproduction Steps

1. Run the script above against an empty Postgres database.

2. After step 1, select id, "order", parent_id, path, groups_id from nav_rels order by path shows:

   id | order | parent_id | path                | groups_id
   1  | 1     | 1         | groups.0.visible_to | 1
   2  | 1     | 1         | groups.1.visible_to | 2
   3  | 1     | 1         | groups.2.visible_to | 3
   

3. After step 2 (middle row removed), nav_groups correctly holds two rows (r1, r3), but nav_rels shows three:

   4  | 1     | 1         | groups.0.visible_to | 1
   5  | 1     | 1         | groups.1.visible_to | 3
   3  | 1     | 1         | groups.2.visible_to | 3   <-- untouched, no array row at index 2
   

   findGlobal at depth 0 still returns two rows and looks correct.

4. After step 3 (row r4 appended with no visible_to), nav_rels is unchanged (ids 6, 7 rewritten at index 0 and 1; id 3 still at groups.2.visible_to) and findGlobal({ slug: 'nav', depth: 0 }) returns:

   [
     { "key": "r1", "visible_to": [1] },
     { "key": "r3", "visible_to": [3] },
     { "key": "r4", "visible_to": [3] }
   ]

   r4 was never given a value.

5. Control: rewriting r4 with visible_to: [] deletes the orphan (an explicit empty array pushes the path into relationshipsToDelete), which confirms the exact-path cleanup is the cause.

6. The same three writes through payload.create / payload.update on the pages collection give the same pages_rels contents and the same visible_to: [3] on the appended row.

Expected Behavior

After an array field is saved, _rels holds only rows whose path index is occupied by a surviving array row; a row appended at a vacated index without a relationship value reads [].

Actual Behavior

_rels rows at indices no surviving row writes survive the save (here groups.2.visible_to), and a row later appended at that index adopts them.

Where in the code

packages/drizzle/src/upsertRow/index.ts, "INSERT RELATIONSHIPS" (main lines 352-378; @payloadcms/drizzle@3.88.0 dist upsertRow/index.js lines 289-313): on update, deleteExistingRowsByPath is called with rows: [...relationsToInsert, ...generalRelationshipDeletes], and deleteExistingRowsByPath.ts deletes with inArray(table.path, pathsToDelete) — an exact match on the paths of the incoming write. Array rows get their path from packages/drizzle/src/transform/write/array.ts line 122 (${path}${field.name}.${i}.), so a removed or shifted row leaves the highest previous index unnamed and therefore undeleted. The array table rows themselves are deleted by _parentID (deleteExistingArrayRows), which is why the array and _rels tables diverge. The read side (transform/read/traverseFields.ts, lookup relationships[${sanitizedPath}${field.name}]) then matches the stale row to whichever array row later occupies that index.

A fix along the lines of PR #17913 (delete _rels rows by path prefix ${path}${field.name}. before reinserting) would need to be applied when an array field is traversed as well, not only for blocks.

Verified unchanged on the v3.89.0 tag and on main (06f05f7, 2026-09-10).

Which area(s) are affected? (Select all that apply)

db-postgres

Environment Info

• Payload: 3.88.0
• @payloadcms/db-postgres: 3.88.0 (@payloadcms/drizzle 3.88.0)
• Node.js: v22.23.1
• PostgreSQL: 17.10
• Package manager: pnpm (packages resolved from an existing pnpm install; the reproduction uses a package.json pinning payload 3.88.0 and @payloadcms/db-postgres 3.88.0)
• OS: macOS (Darwin 27.0.0)