Use execute-sql instead of update to apply changes

General Discussion
1

We wanted to implement a 2-step process to apply migrations for our Postgres db.

  1. Use liquibase update-sql to generate a .sql file with all the changes. In this file we see the entries to databasechangelog table included.
  2. Use liquibase execute-sql to execute the scripts in the file.

This seems to work fine for table changes, but failed when testing with a function. Error message = “Unexpected error running Liquibase: Unterminated dollar quote started at position …”

We use the prescribed splitStatements:false in our ChangeLog file.
The same ChangeLog file works successfully if we run liquibase update.
Is this by design or a bug?

2

Without seeing the SQL that’s failing, it’s difficult to diagnose what you may be hitting. Is there any way to sanitize and share the function that’s failing? It would also be good to know your database platform, version and the version of Liquibase you are using.

3

This previous question might help you: Facing Unterminated dollar quote started at position 3 in SQL DO $$ for postgresql migration

4

Postgres 17 is the target db. Running latest Liquibase 4.31.0 community ed.

Thanks for the link–that didn’t come up on initial searches. I’ll test endDelimiter=“$$”

My changeset looks like this:

--liquibase formatted sql

--changeset homer:1 labels:WI-12345 runOnChange:true splitStatements:false stripComments:false
--comment: Create function

CREATE OR REPLACE FUNCTION test_func1()
RETURNS TABLE(user_id INTEGER, firstname TEXT, lastname TEXT) 
AS 
$$
BEGIN
    RETURN QUERY 
    SELECT u.user_id, u.firstname, u.lastname
    FROM users u;    
END;
$$
LANGUAGE plpgsql;
5

Let me know how the endDelimiter test goes!

6

This didn’t work either. Same error message from execute-sql:
ERROR: Exception Primary Reason: Unterminated dollar quote started at position 107 in SQL CREATE OR REPLACE FUNCTION test_func2()

liquibase update also runs successfully.

--liquibase formatted sql

--changeset homer:1 labels:WI-12345 runOnChange:true splitStatements:false stripComments:false endDelimiter="\$\$"
--comment: Create function

CREATE OR REPLACE FUNCTION test_func2()
RETURNS TABLE(user_id INTEGER, firstname TEXT, lastname TEXT) 
AS 
$$
BEGIN
    RETURN QUERY 
    SELECT u.user_id, u.firstname, u.lastname
    FROM users u;    
END;
$$
LANGUAGE plpgsql;
7

The “offset 107” seems incorrect, too. I pulled up both the changeset file and the .sql output file and that doesn’t seem to correlate to what the error message references. Maybe the execute-sql is stripping/skipping comments to calculate that offset?

8

You defined endDelimiter is $$, but you have two lines with $$, so that delimiter is not going to work. You need to choose a delimiter that is not in the code.

9

I see. Like the property says “endDelimiter” is just that, not the start and end delimiter.

Same error with this:

--liquibase formatted sql

--changeset homer:1 labels:WI-12345 runOnChange:true splitStatements:false stripComments:false endDelimiter=~
--comment: Create function

CREATE OR REPLACE FUNCTION test_func3()
RETURNS TABLE(user_id INTEGER, firstname TEXT, lastname TEXT) 
AS 
$$
BEGIN
    RETURN QUERY 
    SELECT u.user_id, u.firstname, u.lastname
    FROM users u;    
END;
~
LANGUAGE plpgsql;
10

I think the nuance here is I’m using update-sql to generate a .sql file. When I look in that file, it looks “normal”. However, when I run execute-sql on that file, I get the error. I do not get the error with a typical “liquibase update”.

I can copy and paste that generated .sql right into pgAdmin and it all runs fine. It seems like when execute-sql is reading in the .sql file, it isn’t recognizing the $$ as I expect.

And when the liquibase update works on the same exact file, perhaps the parsing isn’t consistent?

Either way, how many others are using Liquibase in this manner? We were trying to match our migration process to other databases where a script is generated and saved in one step, and applied at a later time (often immediately, but not always). For the second step it should be ok to just us the regular update command since that works for us.

I was also going to test using psql.exe to apply the .sql file but am running short on time.

11

Why do you want to use execute-sql to execute, since it does not update the databasechangelog table? Seems like this defeats the purpose of using Liquibase.

I’d recommend opening an issue on github if you think there is a defect.

12

The generated .sql file does have the changes to databsaechangelog table, though. It even includes the pseudo-lock table entries. Here is an example of the generated .sql file.

– Lock Database
UPDATE public.databasechangeloglock SET LOCKED = TRUE, LOCKEDBY = ‘REDACTED’, LOCKGRANTED = NOW() WHERE ID = 1 AND LOCKED = FALSE;

– *********************************************************************
– Update Database Script
– *********************************************************************
– Change Log: ./changelog/db.changelog-master.xml
– Ran at: 2/19/25, 9:23 AM
– Against: postgres@jdbc:postgresql://host_goes_here:5432/dbname_goes_here
– Liquibase version: 4.31.0
– *********************************************************************

– Changeset changelog/public/functions/test_func1.sql::1::homer
– Create function
CREATE OR REPLACE FUNCTION test_func1()
RETURNS TABLE(user_id INTEGER, firstname TEXT, lastname TEXT)
AS
$$
BEGIN
RETURN QUERY
SELECT u.user_id, u.firstname, u.lastname
FROM users u;
END;
$$
LANGUAGE plpgsql;

INSERT INTO public.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES (‘1’, ‘homer’, ‘changelog/public/functions/test_func1.sql’, NOW(), 12, ‘9:115916fc33e5221e3f8c6b18705c60cc’, ‘sql’, ‘Create function’, ‘EXECUTED’, NULL, ‘wi-12345’, ‘4.31.0’, ‘9975023846’);

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

13

ok, I read your posts closer. You are using update-sql to generate the script, then using execute-sql to execute. I missed that detail. I still recommend opening an issue. I have not used execute-sql.

14

While I don’t know the differences in script parsing between update and execute-sql, I do know that the intent of execute-sql is to run arbitrary, non-changeset based statements for exploration and trouble shooting. A more common pattern we see with update-sql is one you’ve tried: execution of the script with an external client compatible with your database.

I would not be surprised if there is some difference between in how those statements are handled and how changessets in SQL files are parsed. I agree with @daryldoak about filing a GitHub issue. If you are a Liquibase Pro customer you can reach out to your support contact.

1 Like