What benefit does liquibase "splitStatements" provide?

user_4820101 0
Viewed 131

liquibase version being used - org.liquibase:liquibase-core:3.8.2. (not pro version)

Liquibase documents (1 & 2) says below about splitStatements (defaults to true)

Set to false to not have Liquibase split statements on ;'s and GO's. Defaults to true if not set

and

Removes Liquibase split statements on ;'s and GO's when it is set to false. Default value is: true.

Another useful sof post i found - In Liquibase is it OK to have an empty line on splitstatements?

I understand - when splitStatements is true, liquibase splits the statements on ; and GO

  1. It not entirely clear what benefit splitStatements adds - i.e if SQL statement are split on ; (end delimiter) or not, what difference will it make - i.e if the statements are executed in a single query or multiple queries - won't the db handle the ";" based stuff anyway. This seems to be essential to understand. --could some one quote an example.
  2. My current project has splitStatements:false. what advantages are we getting by disabling splitStatements. -any example would be greatly appreciated.

------------------------question expanded after answer from @user13579
below is an extract from a liquibase changelog file. This is what brought me to this question. It has splitStatements:false and a ;s in script and it works. With splitStatements:false i would expect a error in this case and the answer I suppose also suggests an error in this case. The below is from production code so I am NOT sure how it works and the backend is POSTGREs. Can someone explain.

--liquibase formatted sql

--changeset adam:001-users-001 failOnError:true splitStatements:false logicalFilePath:001-users.sql

CREATE TABLE sys_users
(
  user_id SERIAL,
  first_name character varying(64) NOT NULL,
  last_name character varying(64) NOT NULL,
  email character varying(255) NOT NULL
)
WITH (
  OIDS=FALSE
);

CREATE TABLE user_role
(
  role_id SERIAL,
  role_name character varying(255) NOT NULL,
  description character varying(255) NOT NULL,
  created_on timestamp(6) with time zone NOT NULL,
  created_by character varying(64) NOT NULL,
)
WITH (
  OIDS=FALSE
);
liquibase-sql liquibase
1 Answers

Database engines usually do not split statements by themselves. You may have a different impression when using SQL editors like SQL Management Studio, MySQL Workbench. They split your script and send separate SQL statements to the database engine e.g. using JDBC. It happens automatically so you may think that the database does that.

This will fail with splitStatements = true:

<changeSet id="it fails" author="DBA presents">
  <sql splitStatements="true">
    create procedure some_proc (out title int)
    begin
      select count(1) into title from titles
      where emp_no = 123;
    end
  </sql>
</changeSet>

Liquibase will try to split statements when encounters a semicolon which will obviously be a mistake as the semicolon is a part of a bigger statement (create procedure). Change splitStatement to false to allow semicolons in Liquibase SQL tag.

On the other hand, it is useful to have splitStatements set to true in most cases. For example when you want to provide multiple statements in a single SQL tag. With splitStatement=false, it will fail:

<changeSet id="it works" author="DBA presents">
        <sql splitStatements="false">
            update titles
            set title = 'abc'
            where emp_no = 123;

            update titles
            set title = 'abc'
            where emp_no = 234;
        </sql>
    </changeSet>

Your PostgreSQL example

I do not have Postgres installed on my computer but the same script (after obvious syntax changes) fails with MySQL because of splitStatements=false. It is definitely one of many differences between database engines - apparently Postgres splits a script to statements by itself. Perhaps for Postgres splitStatement attribute does not make sense, but for other engines it does like for MySQL. Liquibase supports various engines so not all options are necessary for all of them.

To sum up

Generally, use the default (splitStatements=true), you will not have to create separate SQL tags for each statement. But sometimes you may want to explicitly tell Liquibase "this is a single statement, don't try to split it" by setting splitStatements=false. For example when creating stored procedures.

user_44495070
Related