Hello,
I’m using PostgreSQL, Java and the Liquibase Java API (org.liquibase:liquibase-core:4.29.1). Currently we set the schema name via liquibase.database.Database#setDefaultSchemaName to prevent the definition from being required in all changesets. In the database log I can now see for example the following entries:
LOG: execute <unnamed>: BEGIN
LOG: execute <unnamed>: SHOW SEARCH_PATH
LOG: execute <unnamed>: SET SEARCH_PATH TO customer, "$user","public"
LOG: execute <unnamed>: UPDATE customer.databasechangeloglock SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1
LOG: execute PGBOUNCER_1: COMMIT
As we can see, that search_path is set to customer in this case. Now I wonder why this is done, as the following statement is fully qualified - the the schema is used here already (UPDATE customer.databasechangeloglock).
Why I’m asking this: We use PgBouncer with transaction pool mode and here the use of session based functionality/information is not allowed or supported. See also: PgBouncer features
The problem is: Then you set the search_path, the physical connection between PgBouncer and the database holds this information/configuration. And this connection is reused also for other services - so the services influence each other. And so the set/modification of the search_path by Liquibase is not a good idea in my opinion. Maybe using SET LOCAL SEARCH_PATH would be better here!?
Thank you in advance for any explanation.