Postgres: Foreign Key constraint violation after deleting an item

[tseidler]

Describe the Bug

I added a select field 'cakes' to the payload sample project pages collection, and ran into issues in the terminal after I deleted one of my pages.

Everything works fine in the front-end, but the server complains about foreign key violations ;-).

(My linked code uses the create-payload-app -t blank code)

Link to the code that reproduces this issue

https://github.com/tseidler/payload-select-postgres-issue

Reproduction Steps

Here's a video of me reproducing this using a fresh database: https://www.youtube.com/watch?v=GBK2ZlpCEv0

1. Add cakes to any collection. I used the media collection (code below)
2. Make sure the collection has autosave / drafts / versions enabled
3. Log in, open the collection.
4. Create a new media item. Publish.
5. Create a second media item. Publish
6. Everything is OK at this point.
7. Delete the first media item
8. Change the value of alt
9. See the errors in the terminal

The errors I got:

ERROR: There was an error while saving a version for the Media with ID 2.
    err: {
      "type": "DatabaseError",
      "message": "insert or update on table \"_media_v_version_cakes\" violates foreign key constraint \"_media_v_version_cakes_parent_fk\"",
      "stack":
          error: insert or update on table "_media_v_version_cakes" violates foreign key constraint "_media_v_version_cakes_parent_fk"
              at /home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/pg/lib/client.js:526:17
              at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
              at async eval (webpack-internal:///(rsc)/./node_modules/drizzle-orm/node-postgres/session.js:96:22)
              at async Object.insert (webpack-internal:///(rsc)/./node_modules/@payloadcms/drizzle/dist/postgres/insert.js:11:18)
              at async upsertRow (webpack-internal:///(rsc)/./node_modules/@payloadcms/drizzle/dist/upsertRow/index.js:310:17)
              at async Object.updateVersion (webpack-internal:///(rsc)/./node_modules/@payloadcms/drizzle/dist/updateVersion.js:32:20)
              at async saveVersion (webpack-internal:///(rsc)/./node_modules/payload/dist/versions/saveVersion.js:76:30)
              at async updateByIDOperation (webpack-internal:///(rsc)/./node_modules/payload/dist/collections/operations/updateByID.js:292:22)
              at async updateByIDHandler (webpack-internal:///(rsc)/./node_modules/payload/dist/collections/endpoints/updateByID.js:27:17)
              at async handleEndpoints (webpack-internal:///(rsc)/./node_modules/payload/dist/utilities/handleEndpoints.js:173:26)
              at async eval (webpack-internal:///(rsc)/./node_modules/@payloadcms/next/dist/routes/rest/index.js:26:20)
              at async AppRouteRouteModule.do (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/compiled/next-server/app-route.runtime.dev.js:10:32804)
              at async AppRouteRouteModule.handle (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/compiled/next-server/app-route.runtime.dev.js:10:39698)
              at async doRender (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/base-server.js:1452:42)
              at async responseGenerator (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/base-server.js:1813:28)
              at async DevServer.renderToResponseWithComponentsImpl (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/base-server.js:1823:28)
              at async DevServer.renderPageComponent (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/base-server.js:2250:24)
              at async DevServer.renderToResponseImpl (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/base-server.js:2288:32)
              at async DevServer.pipeImpl (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/base-server.js:959:25)
              at async NextNodeServer.handleCatchallRenderRequest (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/next-server.js:281:17)
              at async DevServer.handleRequestImpl (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/base-server.js:853:17)
              at async /home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/dev/next-dev-server.js:371:20
              at async Span.traceAsyncFn (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/trace/trace.js:153:20)
              at async DevServer.handleRequest (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/dev/next-dev-server.js:368:24)
              at async invokeRender (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/lib/router-server.js:230:21)
              at async handleRequest (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/lib/router-server.js:408:24)
              at async requestHandlerImpl (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/lib/router-server.js:432:13)
              at async Server.requestListener (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/lib/start-server.js:146:13)
      "length": 309,
      "name": "error",
      "severity": "ERROR",
      "code": "23503",
      "detail": "Key (parent_id)=(2) is not present in table \"_media_v\".",
      "schema": "public",
      "table": "_media_v_version_cakes",
      "constraint": "_media_v_version_cakes_parent_fk",
      "file": "ri_triggers.c",
      "line": "2599",
      "routine": "ri_ReportViolation"
    }
[22:35:26] ERROR: Cannot read properties of undefined (reading 'alt')
    err: {
      "type": "TypeError",
      "message": "Cannot read properties of undefined (reading 'alt')",
      "stack":
          TypeError: Cannot read properties of undefined (reading 'alt')
              at promise (webpack-internal:///(rsc)/./node_modules/payload/dist/fields/hooks/afterRead/promise.js:51:151)
              at eval (webpack-internal:///(rsc)/./node_modules/payload/dist/fields/hooks/afterRead/traverseFields.js:9:80)
              at Array.forEach (<anonymous>)
              at traverseFields (webpack-internal:///(rsc)/./node_modules/payload/dist/fields/hooks/afterRead/traverseFields.js:8:12)
              at afterRead (webpack-internal:///(rsc)/./node_modules/payload/dist/fields/hooks/afterRead/index.js:29:71)
              at updateByIDOperation (webpack-internal:///(rsc)/./node_modules/payload/dist/collections/operations/updateByID.js:308:100)
              at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
              at async updateByIDHandler (webpack-internal:///(rsc)/./node_modules/payload/dist/collections/endpoints/updateByID.js:27:17)
              at async handleEndpoints (webpack-internal:///(rsc)/./node_modules/payload/dist/utilities/handleEndpoints.js:173:26)
              at async eval (webpack-internal:///(rsc)/./node_modules/@payloadcms/next/dist/routes/rest/index.js:26:20)
              at async AppRouteRouteModule.do (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/compiled/next-server/app-route.runtime.dev.js:10:32804)
              at async AppRouteRouteModule.handle (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/compiled/next-server/app-route.runtime.dev.js:10:39698)
              at async doRender (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/base-server.js:1452:42)
              at async responseGenerator (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/base-server.js:1813:28)
              at async DevServer.renderToResponseWithComponentsImpl (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/base-server.js:1823:28)
              at async DevServer.renderPageComponent (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/base-server.js:2250:24)
              at async DevServer.renderToResponseImpl (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/base-server.js:2288:32)
              at async DevServer.pipeImpl (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/base-server.js:959:25)
              at async NextNodeServer.handleCatchallRenderRequest (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/next-server.js:281:17)
              at async DevServer.handleRequestImpl (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/base-server.js:853:17)
              at async /home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/dev/next-dev-server.js:371:20
              at async Span.traceAsyncFn (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/trace/trace.js:153:20)
              at async DevServer.handleRequest (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/dev/next-dev-server.js:368:24)
              at async invokeRender (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/lib/router-server.js:230:21)
              at async handleRequest (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/lib/router-server.js:408:24)
              at async requestHandlerImpl (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/lib/router-server.js:432:13)
              at async Server.requestListener (/home/tseidler/workspace/bettershifting/site-v2/cannot-eat-cake/node_modules/next/dist/server/lib/start-server.js:146:13)
    }
 PATCH /api/media/2?draft=true&autosave=true&locale=undefined 500 in 91ms

This is what those tables look like:
_v_media_v

_v_media_v_version_cakes

Now... if I disable the autosave/drafts option everything works OK - also when I manually save a draft. Perhaps a race condition?

My Media collection:

import type { CollectionConfig } from 'payload'

export const Media: CollectionConfig = {
  slug: 'media',
  access: {
    read: () => true,
  },
  fields: [
    {
      name: 'alt',
      type: 'text',
      required: true,
    },
    {
      name: 'cakes',
      type: 'select',
      hasMany: true,
      admin: {
        position: 'sidebar',
      },
      defaultValue: ['1'],
      options: [
        {
          label: '1 cake',
          value: '1',
        },
        {
          label: '2 cakes',
          value: '2',
        },
      ],
    },
  ],
  versions: {
    drafts: {
      autosave: true,  // <--- WITHOUT autosave, it works fine :)
    },
    maxPerDoc: 50,
  },
  upload: true,
}

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

db-postgres, Not sure

Environment Info

Binaries:
  Node: 22.12.0
  npm: 10.9.0
  Yarn: 1.22.22
  pnpm: N/A
Relevant Packages:
  payload: 3.16.0
  next: 15.1.0
  @payloadcms/db-postgres: 3.16.0
  @payloadcms/email-nodemailer: 3.16.0
  @payloadcms/graphql: 3.16.0
  @payloadcms/next/utilities: 3.16.0
  @payloadcms/payload-cloud: 3.16.0
  @payloadcms/richtext-lexical: 3.16.0
  @payloadcms/translations: 3.16.0
  @payloadcms/ui/shared: 3.16.0
  react: 19.0.0
  react-dom: 19.0.0
Operating System:
  Platform: linux
  Arch: x64
  Version: #1 SMP Tue Nov 5 00:21:55 UTC 2024
  Available memory (MB): 15665
  Available CPU cores: 8