Why do you set the (session) search_path?

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.

Welcome to the forums @TimonZ !

This sounds like something that should be triaged in a GitHub issue. Liquibase shouldn’t make any persistent changes to the database configuration without reverting them. I would file an issue on the GitHub repo to get a developer to take a look.

Thanks!

1 Like

Thank you for your reply!

I created a new issue: Set of search_path for connection (PostgreSQL) should not be persistent · Issue #7092 · liquibase/liquibase · GitHub

1 Like