Ensuring Record Commit Before Related Actions in PostgreSQL

[ra7bi]

Describe the Bug

Hi Payload team ,

I'm facing an issue with PostgreSQL where I need to create a related record after the parent record is created, but it seems the parent record isn’t fully committed when the action is triggered. This results in a foreign key constraint violation.

insert or update on table "related_table" violates foreign key constraint
Key (parent_id)=(7) is not present in table "parent_table".

Hook

const afterChangeHook: CollectionConfig['hooks']['afterChange'][0] = async ({ doc, req, operation }) => {
  if (operation === 'create') {
    try {
      const schoolSettings = await req.payload.create({
        collection: 'school-settings',
        data: {
          school: doc.id,
          autoConfirmEnrollment: false,
          timezone: 'UTC',
        },
      });

      await req.payload.update({
        collection: 'schools',
        id: doc.id,
        data: { 'school-settings': schoolSettings.id },
      });
    } catch (error) {
      console.error('Error creating school settings:', error);
    }
  }
};

How can I ensure the parent record is fully committed before executing this related action? Any best practices or patterns for such scenarios?

Thanks!

Link to the code that reproduces this issue

pnpx create-payload-app@latest -t blank

Reproduction Steps

Create a schools record in the schools table.
Trigger an afterChange hook to create a related school-settings record with a foreign key referencing the schools record.
Observe that the hook attempts to create the school-settings record before the schools record is fully committed.
Encounter a foreign key constraint violation.

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

db-postgres

Environment Info

# Added by Payload
DATABASE_URI=postgres://postgres:[email protected]:5432/school
PAYLOAD_SECRET=a270760d65376de12e532b19

[github-actions]

Please add a reproduction in order for us to be able to investigate.

Depending on the quality of reproduction steps, this issue may be closed if no reproduction is provided.

Why was this issue marked with the invalid-reproduction label?

To be able to investigate, we need access to a reproduction to identify what triggered the issue. We prefer a link to a public GitHub repository created with create-payload-app@beta -t blank or a forked/branched version of this repository with tests added (more info in the reproduction-guide).

To make sure the issue is resolved as quickly as possible, please make sure that the reproduction is as minimal as possible. This means that you should remove unnecessary code, files, and dependencies that do not contribute to the issue. Ensure your reproduction does not depend on secrets, 3rd party registries, private dependencies, or any other data that cannot be made public. Avoid a reproduction including a whole monorepo (unless relevant to the issue). The easier it is to reproduce the issue, the quicker we can help.

Please test your reproduction against the latest version of Payload to make sure your issue has not already been fixed.

I added a link, why was it still marked?

Ensure the link is pointing to a codebase that is accessible (e.g. not a private repository). "example.com", "n/a", "will add later", etc. are not acceptable links -- we need to see a public codebase. See the above section for accepted links.

Useful Resources

• Reproduction Guide
• Contributing to Payload

[jameswyse]

I've also just ran into this issue, I need a way to run side effects after a document is created and fully commited to the database

[coredevel]

Can the first create be ran in it's own transaction, or disable transaction for that statement, per the docs: https://payloadcms.com/docs/database/transactions

[r1tsuu]

Hey @ra7bi, your issue is related to transactions. Yes, the created document is not yet commited to the database in afterChange hooks and it's intentional, as you may have further failures in your hooks logic and you want to rollback all the changes, including initial write of the document as well.

As from the docs:

The initial request made to Payload will begin a new transaction and attach it to the req.transactionID. If you have a hook that interacts with the database, you can opt in to using the same transaction by passing the req in the arguments. For example:

To fix your issue, you need to pass the req from the hook args to your operations like so:

const schoolSettings = await req.payload.create({
  collection: 'school-settings',
  data: {
    school: doc.id,
    autoConfirmEnrollment: false,
    timezone: 'UTC',
  },
  req
});

await req.payload.update({
  collection: 'schools',
  id: doc.id,
  data: { 'school-settings': schoolSettings.id },
  req
})

I'm converting this to a discussion as it's not an issue with Payload, let me know if you have any more questions.