We have a Codeigniter (PHP) application hosted on the Heroku instance. We are using the Heroku Postgres database to connect and do CRUD operations. It is also connected to the Salesforce instance through Heroku Connect, through which the data flows back and forth.
We already have two Salesforce sandbox instances connected to the Heroku applications that were set up more than a year ago. When we wanted to set up another sandbox instance this week, we got the following error while doing insert and update operations:
pg_query(): Query failed: function hstore(salesforce.account) does not exist LINE 1: trigger_row.old = hstore(OLD.) - excluded_cols ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: trigger_row.old = hstore(OLD.) - excluded_cols CONTEXT: PL/pgSQL function salesforce.hc_account_logger() line 31 at assignment
None of the files or line numbers mentioned in the error relate to our code.
We did some research on our side and found out that there have been a few structural changes to the Heroku schemas as of August 1st, 2022. From the error message, we understood that the hstore extension was either missing or not properly configured. So we did add the extension to the heroku_ext schema, as explained in some articles. But that didn't work for us.
So we reached out to Heroku support for further assistance. They responded by saying that the hstore extension seems to be installed in the right schema and that search_path is also set correctly.
However, they could not find any issues with the configuration.
But there are already two Heroku apps that were hosted more than a year ago that are working normally. There are no differences in the code versions or database connections.
We're clueless about the error that is being displayed here. Since we are close to production deployment and due to new issues, we have been held up for a week now.
Can someone please help us understand the issue?
We finally fixed it.
The issue was in Codeigniter 3 while connecting to the Postgres database, the code is manually overriding the search_path at line number 166 under the CI_DB_postgre_driver class (Filepath: /system/database/drivers/postgre/postgre_driver.php).
I'm using Codeigniter 3.1.0. The line number may vary for the other versions.
We commented the below line and tried to perform the insert/update operation, and that worked for us.
empty($this->schema) OR $this->simple_query('SET search_path TO '.$this->schema.',public');
Since Heroku has made it mandatory to install the extensions in the heroku_ext schema by default, the schema is missing from CodeIgniter's above code.
I hope that helps others in the community.
Thank you!