Foreign Key constraint violation

[sriechersrc]

Link to reproduction

No response

Payload Version

3.0.0-beta.46

Node Version

v20.10.0

Next.js Version

15.0.0-rc.0

Describe the Bug

I have been using the postgres adapter for a while now and have been running into a lot of foreign key constraint errors. Sadly I can't really tell when those errors are accuring, but they are quit frequent.

The last one I caught was this one: error: insert or update on table "events_locales" violates foreign key constraint "events_locales_parent_id_fk" after adding a relationship field to my events collection.

 ⨯ node_modules/.pnpm/pg-pool@3.6.2_pg@8.11.3/node_modules/pg-pool/index.js (45:1) @ eval
 ⨯ error: insert or update on table "events_locales" violates foreign key constraint "events_locales_parent_id_fk"
    at eval (webpack-internal:///(rsc)/./node_modules/.pnpm/pg-pool@3.6.2_pg@8.11.3/node_modules/pg-pool/index.js:45:11)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async DrizzleORMPgClient.query (/Users/stevenriechersrc/Desktop/PROJECTS/my-project/node_modules/.pnpm/drizzle-kit@0.20.14-1f2c838/node_modules/drizzle-kit/payload.js:34498:21)
    at async apply (/Users/stevenriechersrc/Desktop/PROJECTS/my-project/node_modules/.pnpm/drizzle-kit@0.20.14-1f2c838/node_modules/drizzle-kit/payload.js:36648:9)
    at async pushDevSchema (webpack-internal:///(rsc)/./node_modules/.pnpm/@payloadcms+db-postgres@3.0.0-beta.46_payload@3.0.0-beta.46_react@19.0.0-rc-f994737d14-202405_fjwknzmbrzvje7mytctp7ne6gi/node_modules/@payloadcms/db-postgres/dist/utilities/pushDevSchema.js:47:5)
    at async Object.connect (webpack-internal:///(rsc)/./node_modules/.pnpm/@payloadcms+db-postgres@3.0.0-beta.46_payload@3.0.0-beta.46_react@19.0.0-rc-f994737d14-202405_fjwknzmbrzvje7mytctp7ne6gi/node_modules/@payloadcms/db-postgres/dist/connect.js:87:9)
    at async BasePayload.init (webpack-internal:///(rsc)/./node_modules/.pnpm/payload@3.0.0-beta.46_@swc+core@1.5.28_@swc+types@0.1.8_graphql@16.8.1_typescript@5.4.5/node_modules/payload/dist/index.js:253:13)
    at async getPayload (webpack-internal:///(rsc)/./node_modules/.pnpm/payload@3.0.0-beta.46_@swc+core@1.5.28_@swc+types@0.1.8_graphql@16.8.1_typescript@5.4.5/node_modules/payload/dist/index.js:333:26)
    at async Object.generateStaticParams (webpack-internal:///(rsc)/./src/app/(frontend)/[...slug]/page.tsx:167:21)
    at async buildParams (/Users/stevenriechersrc/Desktop/PROJECTS/my-project/node_modules/.pnpm/next@15.0.0-rc.0_react-dom@19.0.0-rc-f994737d14-20240522_react@19.0.0-rc-f994737d14-20240522/node_modules/next/dist/build/utils.js:1037:40) {
  type: 'DatabaseError',
  length: 524,
  severity: 'ERROR',
  code: '23503',
  detail: 'Key (_parent_id)=(1) is not present in table "events".',
  where: 'SQL statement "ALTER TABLE "events_locales" ADD CONSTRAINT "events_locales_parent_id_fk" FOREIGN KEY ("_parent_id") REFERENCES "events"("id") ON DELETE cascade ON UPDATE no action"\n' +
    'PL/pgSQL function inline_code_block line 2 at SQL statement',
  schema: 'public',
  table: 'events_locales',
  constraint: 'events_locales_parent_id_fk',
  file: 'ri_triggers.c',
  line: '2608',
  routine: 'ri_ReportViolation',
  page: '/en'
}
  43 |     // replace the stack trace that leads to `TCP.onStreamRead` with one that leads back to the
  44 |     // application that created the query
> 45 |     Error.captureStackTrace(err)
     | ^
  46 |     throw err
  47 |   })
  48 |   return { callback: cb, result: result }

Also I cannot fix these errors which leads to me deleting the whole database and reinitalizing it. This might not be a big problem in dev, but in production this would be pretty bad

Reproduction Steps

Can't really tell how to reproduce these errors :(

Adapters and Plugins

db-postgres

[marvinengelmann]

Any updates on this? I also run into this errors very frequently. Every time it occurs I have to run dev again, otherwise, localhost will load infinitely. The issue occurs much more frequently when I open both /admin and the frontend at the same time. However, everything still works as expected; it just slows down the development process.

[ss-forkcoding]

same issue here🤣

[sriechersrc]

For anybody looking for a quick fix: I switched over to mongo db and now the errors are gone

[pdcolandrea]

I've noticed similar issues when doing bulk insertions on my side. Occasionally, restarting the development server, as another commenter suggested, solves the problem. Other times, if I leave it for a few hours, it starts working again. Wiping the database does always fixes it - but obviously isn't a practical solution. Was originally on beta-46; moved up to beta-65 and the issue still occurs

EDIT: unfortunately breaking constantly in production; migrating to mongodb instead :(

[alsherif-khalaf]

same is here when using localize : true ,

[alsherif-khalaf]

Error Type: DatabaseError
Severity: ERROR
Code: 23503
Table: _pages_v_blocks_header1_buttons
Constraint: _pages_v_blocks_header1_buttons_parent_id_fk
Detail: Key (_parent_id)=(20) is not present in table "_pages_v_blocks_header1".
Where: SQL statement "ALTER TABLE "_pages_v_blocks_header1_buttons" ADD CONSTRAINT "_pages_v_blocks_header1_buttons_parent_id_fk" FOREIGN KEY ("_parent_id") REFERENCES "_pages_v_blocks_header1"("id") ON DELETE cascade ON UPDATE no action"

[mikegreiling]

Just adding my voice to the chorus here... This is a showstopping bug. I was keen to try out create-payload-app on the v3 beta for the first time as my team is evaluating Payload for our homepage redesign and this bug came up within the first minute as I was simply poking around with a freshly-seeded homepage.

This is quite bad. Once encountered, I cannot do anything. Visiting the admin page yields the error. Visiting the published home page shows the error. The only way to resolve this is to wipe the db manually. Really not good.

I realize this is a beta, so I'm tempering my criticism here, but this is a really bad first impression.

[mikegreiling]

^ this was on the latest 3.0.0-beta.71 by the way

[paulpopus]

Hey all, just wanna say thank you for your patience on this. We are going to get postgres stable for v3, and @DanRibbens has been rewriting parts of the postgres adapter ahead of releasing the sqlite one. That PR will be merged in very soon, and we will have a concerted effort to fix these postgres issues.

Rest assured that they're not being ignored 🙏

[DanRibbens]

I was able to recreate this.

Steps to recreate:

1. npx create-payload-app@beta and choose website template & postgres
2. I used supabase in my postgres connection
3. start dev
4. create some data in the admin UI to posts
5. a new relationship to posts collection

Query: ALTER TABLE "pages_blocks_content" DROP CONSTRAINT "pages_blocks_content_parent_id_fk";

Query: ALTER TABLE "pages_blocks_cta" DROP CONSTRAINT "pages_blocks_cta_parent_id_fk";

Query: ALTER TABLE "pages_blocks_archive" DROP CONSTRAINT "pages_blocks_archive_parent_id_fk";

Query: ALTER TABLE "_pages_v_version_hero_links" DROP CONSTRAINT "_pages_v_version_hero_links_parent_id_fk";

Query: ALTER TABLE "pages" DROP CONSTRAINT "pages_hero_media_id_media_id_fk";

Query: ALTER TABLE "pages" DROP CONSTRAINT "pages_hero_media_id_media_id_fk";

Query: ALTER TABLE "pages_blocks_form_block" DROP CONSTRAINT "pages_blocks_form_block_form_id_forms_id_fk";

 ⨯ node_modules\.pnpm\pg-pool@3.6.2_pg@8.11.3\node_modules\pg-pool\index.js (45:1) @ eval
 ⨯ error: constraint "pages_hero_media_id_media_id_fk" of relation "pages" does not exist
    at async eval (./src/app/(frontend)/[slug]/page.tsx:108:21)
    at async Page (./src/app/(frontend)/[slug]/page.tsx:49:12)
digest: "1311275458"
  43 |     // replace the stack trace that leads to `TCP.onStreamRead` with one that leads back to the
  44 |     // application that created the query
> 45 |     Error.captureStackTrace(err)
     | ^
  46 |     throw err
  47 |   })
  48 |   return { callback: cb, result: result }
Query: ALTER TABLE "pages_blocks_media_block" DROP CONSTRAINT "pages_blocks_media_block_media_id_media_id_fk";

Query: ALTER TABLE "pages_blocks_cta" DROP CONSTRAINT "pages_blocks_cta_parent_id_fk";

This issue was already identified and fixed while colloborating with drizzle. We just need to migrate to a new version of drizzle-kit. Unfortunately this update is messing with our CI pipeline as we also experienced a change in enum recreation that is causing prompts that interupt our tests.

I'm waiting for a fix for that from Drizzle also.

[DanRibbens]

Fixed in https://github.com/payloadcms/payload/pull/7453

[HarleySalas]

Fixed in #7453

Thank you!

I have been having this issue for quite some time and never submitted an issue, because I didn't have time to investigate creating a consistent reproduction. It's been a major pain for so long.

Your work is greatly appreciated!

[przyb]

@DanRibbens same happens with payload v2.25.0 with @payloadcms/db-postgres 0.8.5. Can we expect the same patch for v2?

[DanRibbens]

@przyb, I think things have settled out enough with the recent version bumps to drizzle-kit + drizzle-orm in beta. We can make another update to postgres for 2.x but it won't have everything that beta currently does since the branches have diverged too significantly with breaking changes.

Here is the PR I opened to evaluate what we can/cannot migrate back to 2.x: https://github.com/payloadcms/payload/pull/7460

[didiraja]

@DanRibbens Really not happy to be migrating because 2.x became buggy, invested a lot of time building the project in this version but response from last weeks to all problems is "update to beta version". Not exactly confident to solve bugs using a beta version.

[przyb]

@DanRibbens thanks for the effort, appreciate it. We have eventually decided to migrate to Mongo for now, as we needed to move on with the project to fulfill the client's deadlines. Waiting for stable 3.x, but, similarly to @didiraja, we don't feel comfortable shipping beta to prod.

[DanRibbens]

Alright @przyb and @didiraja,

I feel this. I'll work on it again.

[github-actions[bot]]

This issue has been automatically locked. Please open a new issue if this issue persists with any additional detail.