How to set up "search_path"

[ghost]

Hello there,

I have used prisma since a month ago.
My environment is Heroku and I attach Hetoku Postgres and Heroku Connect Add-on.

Today, I executed update function to a table in salesforce schema.
I expected it succeeded because I got success in my local environment. But it failed.
The error log is the line below.

ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, 
cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("42883"), 
message: "function get_xmlbinary() does not exist", detail: None, 
hint: Some("No function matches the given name and argument types. You might need to add explicit type casts."), 
position: Some(Internal { position: 9, query: "SELECT (get_xmlbinary() = \'base64\')" }), 
where_: Some("PL/pgSQL function hc_my_table__c_status() line 3 at IF"), 
schema: None, table: None, column: None, datatype: None, constraint: None, 
file: Some("parse_func.c"), line: Some(620), routine: Some("ParseFuncOrColumn") }) }) })

It seems the reason error happen is using Heroku Connect.
How do I resolve "function get_xmlbinary() does not exist" errors in Heroku Connect?

I have doubts about it. I've already configured "public" and "salesforce" about "search_path" with DB client.
I executed $queryRaw in order to check about "search_path" after instantiate Prisma Client.
The result is the line below.

[ { search_path: 'salesforce' } ]

I would like to ask a question for there.
As Prisma, how do I set up the "search_path" for Prisma Client?

Additional Information

I found a workaround. But it's JUST workaround. I wish that I can set up search_path at the right method.

    this.salesforce.$queryRaw('SET search_path TO salesforce, public')
      .then((result) => {
        logger.info(result)
      })
      .catch((error) => {
        Promise.reject(error)
      })

[ryands17]

@takuma-katanosaka-flect 👋

One way to do this would be to set the search_path in the migrations .sql file itself so that it persists.

[ghost]

@ryands17
Hello👋
Thank you for replying. I admired your advice. I couldn't think of it.
But ... I have a question. It have no problem to edit .sql files generated by prisma ?
I feel it's not better approach because idempotent is lost.🤔

[ryands17]

No it's fine because that's what the migration .sql files are for. You get the basics added by Prisma and you can add your own. It will always be idempotent 🙂

[ghost]

@ryands17
I understand🧐
Migration .sql files is generated by schema.prisma file.
So I assumed I must not add new lines.
But migration .sql files have never been changed since it was generate. I forgot it.

[janpio]

Note: Your workaround only works if accidentally the same connection is used for the raw query and the following query. This might not always be the case, and if you get a connection where you did not run the query yet the Prisma query will again fail.

[ghost]

@janpio
Thank you for your cooperation.

If I define prisma client as singleton pattern, does it appears same problem?

'use strict'
const { PrismaClient: PrismaClientPublic } = require('../models/public/index.js')
const { PrismaClient: PrismaClientSalesforce } = require('../models/salesforce/index.js')
const { NODE_ENV,DATABASE_URL_WITH_PUBLIC, DATABASE_URL_WITH_SALESFORCE } = require('../environment.js')

/**
 * @class
 */
class PrismaClientSingleton {
  constructor () {
    const dbPublic = { db: { url: DATABASE_URL_WITH_PUBLIC } }
    const dbSalesforce = { db: { url: DATABASE_URL_WITH_SALESFORCE } }
    const opt = logOptions[LOG_LEVEL]

    this.public = new PrismaClientPublic({ datasources: dbPublic, log: opt })
    this.salesforce = new PrismaClientSalesforce({ datasources: dbSalesforce, log: opt })
  }

  public () {
    return this.public
  }

  salesforce () {
    return this.salesforce
  }

  async disconnect () {
    if (!prismaClient) return

    await this.public.$disconnect()
    await this.salesforce.$disconnect()
    prismaClient = null
  }
}

let prismaClient = null
if (NODE_ENV === 'production') {
  prismaClient = new PrismaClientSingleton()
} else {
  if (!prismaClient) {
    prismaClient = new PrismaClientSingleton()
  }
}

module.exports = {
  prismaClient
}

If connection problem cannot be solved, I think I use transaction API.

const { prismaClient: PrismaClient } = require('./PrismaClientSingleton.js')
const prisma = PrismaClient.salesforce

const [, update] = await prisma.$transaction([
    prisma.$queryRaw('SET search_path TO salesforce,public'),
    prisma.mytable.update({
      where: {
        id: id
      },
      data: {
        count: 0,
        lock: null
      }
    })
  ])

return update

[ryands17]

Yes, because Prisma creates a connection pool and so even a single Prisma instance can make multiple connections to the database: https://www.prisma.io/docs/concepts/components/prisma-client/working-with-prismaclient/connection-pool

[ghost]

@ryands17
Oh...that's too bad. Prisma seems to be incompatible Heroku Connect😭
As I stated earlier, should I use transaction API?

[janpio]

Yes, the transaction API guarantees that the queries are run in the same connection and that might solve your problem.

Can you create a bug report issue about the problem with Heroku Connect though? I do not fully understand why ouy need to set the search_path manually and a bug issue is the better place to figure that out with all the necessary information provided via the bug issue template. Thanks.

[ghost]

@janpio @ryands17
OK. I'll try to create a bug report about this.
Thank you for answer my questions🙇🏻‍♂️