#payload local api with postgres adapter, build time page generation

14 messages · Page 1 of 1 (latest)

stark glen
#

Hi. There's an issue I can't resolve: I've got a route /product/[slug] and I want to generate pages at build time. But nextjs creates new instance of local api and therefore a new database connection for each dynamic param inside generateStaticParams(), and postgres throws an error of too many connections.
Here's some of the code:

`export const dynamic = 'force-static'
export const dynamicParams = true

export default async function ProductPage({ params }: params) {
if (!params.slug) {
return notFound()
}

const payload = await getPayloadClient()

const getProductData = (slug ) => unstable_cache(
async (slug) => {
//will create new local instance everytime and bypass getClient cache, why?
// const payload = await getPayloadClient()
return await payload.find({
collection: 'products',
where: {
slug: {
equals: slug,
},
},
})},

['product_data'],
{
  tags: [`products_${slug}`],
},

)(slug )`

const productData = await getProductData(params.slug)

outer ridgeBOT
stark glen
#

and here's an error:
`error: sorry, too many clients already
at /home/john/Desktop/.../node_modules/pg-pool/index.js:45:11
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
at async DrizzleORMPgClient.query (/home/john/Desktop/.../node_modules/drizzle-kit/payload.js:34498:21)
at async fromDatabase2 (/home/john/Desktop/.../node_modules/drizzle-kit/payload.js:2097:25)
at async pgPushIntrospect (/home/john/Desktop/.../node_modules/drizzle-kit/payload.js:36021:15)
at async pushSchema (/home/john/Desktop/.../node_modules/drizzle-kit/payload.js:36626:28)
at async Object.connect (/home/john/Desktop/.../node_modules/@payloadcms/db-postgres/dist/connect.js:89:67)
at async BasePayload.init (/home/john/Desktop/.../node_modules/payload/dist/payload.js:222:13)
at async getPayload (/home/john/Desktop/.../node_modules/payload/dist/payload.js:266:26)
at async initHTTP (/home/john/Desktop/.../node_modules/payload/dist/initHTTP.js:39:21)

error: deadlock detected
at /home/john/Desktop/.../node_modules/pg/lib/client.js:526:17
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
at async /home/john/Desktop/.../node_modules/drizzle-orm/node-postgres/session.cjs:81:22
at async find (/home/john/Desktop/.../node_modules/@payloadcms/db-postgres/dist/find/findMany.js:145:21)
at async Object.findOne (/home/john/Desktop/.../node_modules/@payloadcms/db-postgres/dist/findOne.js:19:22)
at async findByID (/home/john/Desktop/.../node_modules/payload/dist/collections/operations/findByID.js:69:22) {
length: 323,
severity: 'ERROR',
code: '40P01',
detail: 'Process 222 waits for AccessShareLock on relation 22674 of database 16384; blocked by process 287.\n' +`

#

'Process 287 waits for AccessExclusiveLock on relation 22682 of database 16384; blocked by process 222.',
hint: 'See server log for query details.',
position: '1543',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'deadlock.c',
line: '1130',
routine: 'DeadLockReport'
}
Generating static pages (11/13) [ ===]

In dev mode it works just fine, but when I build my project (this is a monorepo example with custom server from github btw) it crashes because too many connections of database are open.
How do I close them? Is there any way to destroy payload instance?

stark glen
#

so using postgres adapter it seems there's no visible way to close active db connection, which in turn creates an issue when using local api with generateStaticParams function, because it will create new payload instance for each param and postgress will fail because too many connections are open
In payload src there seems to be a method called destroy in database adapter, but it's failing test for them and code is commented. I've uncommented line:
await this.pool.end() and built adapter from source code but it won't close connection too and just hang promise awaiting operation.

#

`const STATIC_GEN_PRODUCT_LIMIT = 1000

export async function generateStaticParams() {
const payload = await getGlobalPayload()

const products = await payload.find({
collection: 'products',
limit: STATIC_GEN_PRODUCT_LIMIT,
})

await payload.db.destroy(payload)

const topProducts = products.docs.map(product => product.slug)

return topProducts.map(slug => ({
slug: slug
}))

}
`

this code will fail after 10-20 generated pages
so it seems the only solution is to use rest api

#

payload local api with postgres adapter, build time page generation

stark glen
#

well, in case someone's facing the same issue, here's a workaround until there's a working database connection closing method:

`export async function generateStaticParams() {

**if (!global.payload_client) {
global.payload_client = await payload.init({
local: true,
secret: process.env.PAYLOAD_SECRET,
})
} **

const _payload = global.payload_client

const products = await _payload.find({
collection: 'products',
limit: STATIC_GEN_PRODUCT_LIMIT,
})

// await payload.db.destroy(payload)
const topProducts = products.docs.map(product => product.slug)

return topProducts.map(slug => ({
slug: slug
}))

}`

do not get payload client from getPayloadClient, because it's being run in a different process (I guess) without global object sharing from where getPayloadClient caches newly created payload instance. that's why every time you call getPayloadClient it creates a new client and go over max connections limit.
This method needs to be done on every route basis.
Now I can use unstable_cache with local api + custom server (monorepo) and set tags to later invalidate cache when collection or global changes and calls afterchange hook.

#

yep, still fails when building after deleting .next folder

stark glen
#

Well, turns out the issue when building and generating static pages wasn't with payload and its getPayloadClient caching: somehow and I don't know why it was with root layout in my nextjs app that broke page building.
I had to manually export const dynamic = 'force-dynamic' and my app has been built just fine, leaving this parameter unset(defaulting to auto) or force-static will cause issues. Even though it must be set to force-dynamic, nextjs builds static pages and they are exported as static with ability to revalidate cache from payload collection or globals hooks.
Although I think the issue could be that I use unstable_cache to query some data and share it among all the layout children, but I don't know exactly why.
The issue with closing active connection with database in postgres adapter still stands.

stark glen
#

P.S build does not fail, but static pages are not generated when expecting .next plus setting root layout causes / page to be dynamically rendered

stark glen
#

I've tried to use mongodb adapter and my cache works fine. I can now generate pages at build time and revalidate cache with payload local api and unstable_cache.
I believe the issue with postgres adapter: creating too many connections without closing them, I've tried to build postgres adapter from source code but getting database destroy method to work was above my understangind of pg. I think the issues is that drizzle doesn't have an option to close database connection, but when you call pool.end() it fails because you did not released a client from the pool.
Here's more to read: https://github.com/brianc/node-postgres/issues/2648

GitHub
pg version 8.7.1 hangs on await db.end() but before version 8 doesn...

The node-postgres module frequently hangs when the PostgreSQL server restarts abruptly. The node-postgres package was working fine prior to pg version 8.7.1 as required by Node.js version 16. The t...

#

`try {
this.pool = await new Pool(this.poolOptions)
await connectWithReconnect({ adapter: this, payload })

const logger = this.logger || false
//here you pass pool to drizzle, which doesn't have an option to close DB connection
this.drizzle = drizzle(this.pool, { logger, schema: this.schema })
if (process.env.PAYLOAD_DROP_DATABASE === 'true') {
  this.payload.logger.info(`---- DROPPING TABLES SCHEMA(${this.schemaName || 'public'}) ----`)
  await this.drizzle.execute(
    sql.raw(`
    drop schema if exists ${this.schemaName || 'public'} cascade;
    create schema ${this.schemaName || 'public'};
  `),
  )
  this.payload.logger.info('---- DROPPED TABLES ----')
}

} catch (err) {
payload.logger.error(Error: cannot connect to Postgres. Details: ${err.message}, err)
process.exit(1)
}`

///////////

export const destroy: Destroy = async function destroy(this: PostgresAdapter) {
// TODO: this hangs test suite for some reason
//this fails for me
await this.pool.end()
}