A multi-value SQL primary key make the db pull crash

[slaivyn]

Describe the Bug

I am using the postgreSQL provider in a database that already contains data. And the application crashes with the following message:

⨯ [Error: Failed query: SELECT conname AS primary_key
            FROM   pg_constraint join pg_class on (pg_class.oid = conrelid)
            WHERE  contype = 'p' 
            AND    connamespace = $1::regnamespace  
            AND    pg_class.relname = $2;
params: ] {
  query: 'SELECT conname AS primary_key\n' +
    '            FROM   pg_constraint join pg_class on (pg_class.oid = conrelid)\n' +
    "            WHERE  contype = 'p' \n" +
    '            AND    connamespace = $1::regnamespace  \n' +
    '            AND    pg_class.relname = $2;',
  params: [],
  payloadInitError: true,
  digest: '3175034571',
  [cause]: [error: there is no parameter $1] {
    length: 95,
    severity: 'ERROR',
    code: '42P02',
    detail: undefined,
    hint: undefined,
    position: '175',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'parse_expr.c',
    line: '904',
    routine: 'transformParamRef'
  }
}
[⢿] Pulling schema from database...
[⢿] Pulling schema from database...
[⢿] Pulling schema from database...

After hours of investigation I have found out that the root cause is the existence of some multi-value primary keys in the SQL database.

Here is a simple SQL code you could use to reproduce the problem:

ALTER TABLE ONLY public."AtomCollectionMember"
    ADD CONSTRAINT "AtomCollectionMember_pkey" PRIMARY KEY ("userId", "collectionId");

I have not found how to create a reproduction repo as the problematic SQL code has nothing to do with Payload code.

Link to the code that reproduces this issue

not reproductible as a codebase

Reproduction Steps

• start Payload app, connected to postgresql database to init
• stop the app
• create a multi-value primary key using this template and replacing the texts between "<" and ">" :

ALTER TABLE ONLY public."<table name>"
    ADD CONSTRAINT "keyname" PRIMARY KEY ("<key_id_1>", "<key_id_2>");

• start again Payload app
• you should see the crashing message multiple times

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

db-postgres

Environment Info

Payload v3.43.0
Node.js v20.18.3
Next.js v15.3.3

[github-actions[bot]]

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@latest -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

[sameer-ahmd]

any update on this?

[technofreak]

I am trying to add Payload into my existing Next.JS app but when I try to hit '/admin' I end up with this error.

[0] [Error: Failed query: SELECT conname AS primary_key
[0]             FROM   pg_constraint join pg_class on (pg_class.oid = conrelid)
[0]             WHERE  contype = 'p' 
[0]             AND    connamespace = $1::regnamespace  
[0]             AND    pg_class.relname = $2;
[0] params: ] {
[0]   query: 'SELECT conname AS primary_key\n' +
[0]     '            FROM   pg_constraint join pg_class on (pg_class.oid = conrelid)\n' +
[0]     "            WHERE  contype = 'p' \n" +
[0]     '            AND    connamespace = $1::regnamespace  \n' +
[0]     '            AND    pg_class.relname = $2;',
[0]   params: [],
[0]   [cause]: [error: there is no parameter $1] {
[0]     length: 95,
[0]     severity: 'ERROR',
[0]     code: '42P02',
[0]     detail: undefined,
[0]     hint: undefined,
[0]     position: '175',
[0]     internalPosition: undefined,
[0]     internalQuery: undefined,
[0]     where: undefined,
[0]     schema: undefined,
[0]     table: undefined,
[0]     column: undefined,
[0]     dataType: undefined,
[0]     constraint: undefined,
[0]     file: 'parse_expr.c',
[0]     line: '900',
[0]     routine: 'transformParamRef'
[0]   }
[0] }

I would appreciate some guidance on how to fix this.

[topzdev]

Im also having this issue too!

[r1tsuu]

Hey, I reproduced this issue, but I don't think we can do something here since it's on Drizzle side and this is where it happens - https://github.com/drizzle-team/drizzle-orm/blob/37d059f95ebe4ca7da6e60415de0164c729a8454/drizzle-kit/src/serializer/pgSerializer.ts#L1406-L1419. I'll report that to Drizzle

[r1tsuu]

In the meantime, I think if you don't use schema push with push: false and instead rely on migrations you should be fine.

[antho1404]

I had a similar issue due to the multi-value primary key as described in the issue.

I'm using prisma and this can be reproduced with a many to many relations like

model Author {
  id String @id @default(cuid())
  posts Post[]
}

model Post {
  id String @id @default(cuid())
  authors Author[]
}

This creates a _AuthorToPost table with the id of both to do the join and uses the combined ids as id (similar to this primaryKey([author_id, post_id])).

2 ways to fix this

Break your relations

One way to fix that is to create your own joined table or more generally break every single composed primary keys but that is quite a big change in some existing applications.

Isolate payload

This works on postgres but probably not on other db.

Another way, much simpler is limit the access to payload to its own schema in the database so you can have your application on schema public for example and everything related to payload to the payload schema.

This can be done really easily in your payload.config.ts file by adding the schema option to your db adapter:

export default buildConfig({
  ...
  db: postgresAdapter({
    schemaName: "payload",  <--- this is the schema that should be different from the schema you use already
    pool: {
      connectionString: `${process.env.DATABASE_URL}`,
    },
  }),
});

With this your application db and the payload db are separated and when introspecting the database payload doesn't crash anymore with these types of primary keys.

[Fox333-Lab]

hi, i am new to payload and am facing the same issue

[Fox333-Lab]

In the meantime, I think if you don't use schema push with push: false and instead rely on migrations you should be fine.

hi @r1tsuu please guide step by step how to do this.

Thanks in advance

[meliksahmc]

this problem is solved?