How to update sequences after seeding with preexisting ids in the same transaction?

[gaunt isle]

Added almost 20 thousand entities from another db into payload. To keep relational integrity working, I'm keeping the old ids instead of letting payload assign new ids to the insertions, with payload.create({collection: 'stuff', data: {id: entry.oldId}})
When you provide an id during creation, the relevant sequence is not updated, but the thing is that there's no way to update the relevant sequences through the provided apis, so we have to run 'raw' sql, probably with payload.db.execute() or payload.db.drizzle.execute()...

So far, this is where I'm stuck:

const transactionID = await payload.db.beginTransaction()
  if (transactionID === null) {
    throw new Error('Could not get a transaction ID')
  }
  try {
// many more things before, one of them is the 'stuff' collection seeding
    const drizzleTransaction = (await payload.db.sessions![transactionID].db) as PgTransaction<
    NodePgQueryResultHKT,
    Record<string, unknown>,
    ExtractTablesWithRelations<Record<string, unknown>>
  >
await payload.db.execute({
    db: drizzleTransaction,
    drizzle: payload.db.drizzle,
    sql: sql`
    SELECT setval(
      pg_get_serial_sequence(${sql.raw(`'${collectionSource}'`)}, 'id'),
      (SELECT MAX(id) FROM ${sql.raw(collectionSource)})
    )
  `,
  })
// many more things after, with an error that should rollback stuff for testing purposes
    await payload.db.commitTransaction(transactionID)
  } catch (error) {
    await payload.db.rollbackTransaction(transactionID)
    throw error
  }

This updates the sequences but disregards the transaction: when things fail, the sequence is still updated

Anyone has any ideas?

[hardy rover]

New Community-Help Thread Created!

Help is on the way! To mark it as solved, use the /solve command. In the meantime, here are some existing threads that may help you:

Documentation:

• Transactions
• Migrations - Using Transactions - Using database directly with the transaction
• Migrations - Using Transactions
• What is Payload?
• Transactions - Direct Transaction Access

[swift jewel]

db: postgresAdapter({
    pool: {
      connectionString: env.DATABASE_URI,
    },
    idType: 'uuid',
   /*Enable this flag if you want to thread your own ID to create operation data, for example:

// doc created with id 1
    const doc = await payload.create({           collection: 'posts', data: {id: 1,        title: "my title"
    }}) */
    allowIDOnCreate: true,
  }),

[gaunt isle]

@swift jewel this is already set, and it allows the creation with custom ids, but the relevant sequence is not updated with the insertions. The question is how to update the respective _id_seq

The entities are already being created with the desired ids, but the id sequence is not updates when you thread your own ids on creation

[blazing crag]

This is how Postgres behaves. Inserts with preset ids do not affect the id sequence.

Ending your import with

SELECT setval('my_table_id_seq', (SELECT MAX(id) FROM my_table));

or similar will fix it.