Running update-sql with PostgreSQL sets the search path multiple times

[tati-qalified]

Search first

• I searched and no similar issues were found

Description

When using PostgreSQL and the CLI, running update-sql will create a script that sets the search path way too many times.
For example, it is set once before the first update to the databasechangeloglock table, and four times between that and the next relevant statement.

This is an issue with Liquibase version 4.25.0, but it was a problem before that as well.

Here's the changelog I used:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
                      http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
                      http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

  <changeSet id="1" author="Liquibase">
    <createTable tableName="test_table">
      <column name="test_id" type="int">
        <constraints primaryKey="true" />
      </column>
      <column name="test_column" type="varchar" />
    </createTable>
  </changeSet>

  <changeSet id="2" author="Liquibase">
    <createTable tableName="test_table_2">
      <column name="test_id" type="int">
        <constraints primaryKey="true" />
      </column>
      <column name="test_column" type="varchar" />
    </createTable>
  </changeSet>

  <changeSet id="3" author="Liquibase">
    <createTable tableName="test_table_3">
      <column name="test_id" type="int">
        <constraints primaryKey="true" />
      </column>
      <column name="test_column" type="varchar" />
    </createTable>
  </changeSet>

</databaseChangeLog>

And here's the generated sql script:

SET SEARCH_PATH TO public, "$user","public";

-- Lock Database
UPDATE public.databasechangeloglock SET LOCKED = TRUE, LOCKEDBY = 'GXCN4031 (192.168.0.111)', LOCKGRANTED = NOW() WHERE ID = 1 AND LOCKED = FALSE;

SET SEARCH_PATH TO public, "$user","public";

SET SEARCH_PATH TO public, "$user","public";

-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: changelog.xml
-- Ran at: 05/12/2023, 12:45
-- Against: lbadminuser@jdbc:postgresql://localhost:54322/lbadmindb
-- Liquibase version: 4.25.0
-- *********************************************************************

SET SEARCH_PATH TO public, "$user","public";

-- Changeset changelog.xml::1::Liquibase
SET SEARCH_PATH TO public, "$user","public";

CREATE TABLE public.test_table (test_id INTEGER NOT NULL, test_column VARCHAR, CONSTRAINT test_table_pkey PRIMARY KEY (test_id));

INSERT INTO public.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1', 'Liquibase', 'changelog.xml', NOW(), 1, '9:c31cbcc82ef990951e46cc173776542c', 'createTable tableName=test_table', '', 'EXECUTED', NULL, NULL, '4.25.0', '1791137079');

-- Changeset changelog.xml::2::Liquibase
SET SEARCH_PATH TO public, "$user","public";

CREATE TABLE public.test_table_2 (test_id INTEGER NOT NULL, test_column VARCHAR, CONSTRAINT test_table_2_pkey PRIMARY KEY (test_id));

INSERT INTO public.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('2', 'Liquibase', 'changelog.xml', NOW(), 2, '9:a5a63d62e7fcd7d78d1acaa6c255fd1a', 'createTable tableName=test_table_2', '', 'EXECUTED', NULL, NULL, '4.25.0', '1791137079');

-- Changeset changelog.xml::3::Liquibase
SET SEARCH_PATH TO public, "$user","public";

CREATE TABLE public.test_table_3 (test_id INTEGER NOT NULL, test_column VARCHAR, CONSTRAINT test_table_3_pkey PRIMARY KEY (test_id));

INSERT INTO public.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('3', 'Liquibase', 'changelog.xml', NOW(), 3, '9:b09d5fc1dd4fc3177bd8e6426ba6d962', 'createTable tableName=test_table_3', '', 'EXECUTED', NULL, NULL, '4.25.0', '1791137079');

-- Release Database Lock
SET SEARCH_PATH TO public, "$user","public";

UPDATE public.databasechangeloglock SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

SET SEARCH_PATH TO public, "$user","public";

Steps To Reproduce

1. Set up Liquibase with a PostgreSQL database connection
2. Create a changelog - add multiple changesets for a clearer example
3. Run update-sql

Expected/Desired Behavior

The search path should be set only once

Liquibase Version

4.25.0

Database Vendor & Version

PostgreSQL - all versions

Liquibase Integration

CLI

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

No response

Additional Context

It's not an issue with other databases like MSSQL or DB2

Are you willing to submit a PR?

• I'm willing to submit a PR (Thank you!)

[kevin-atx]

Bringing this issue to the sprint review to consider fixing it since it affects everyone.