Question 273856 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Spring test @Sql throws "Unterminated dollar quote started … Expected terminating $$"

I am testing my Spring Boot API with a test class (FoodE2eTest) and I have a separate SQL script file (setup-test-schema.sql) that I want to run before the tests start to set up schema, tables, and data.

I am using PostgreSQL.

The problem

When I run the file by itself then it works fine but when I run the test class then I get the following error:

Unterminated dollar quote started at position 3 in SQL
DO $$ BEGIN IF EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'public') THEN DROP SCHEMA public CASCADE.
Expected terminating $$

Configuration file

...
spring.datasource.url=jdbc:postgresql://localhost:5432/testdb
spring.datasource.username=testuser
spring.datasource.password=testpass
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
...

Test class

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@TestPropertySource(locations = "/application-test.properties")
@AutoConfigureMockMvc
@Sql(scripts = {"/migration/setup-test-schema.sql"}, executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
public class FoodE2eTest {
...
}

SQL file

-- drop everything
DO
$$
    BEGIN
        IF EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'public') THEN
            DROP SCHEMA public CASCADE;
        END IF;
    END
$$;

CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
GRANT ALL ON SCHEMA public TO testuser;

-- create tables
CREATE TABLE public.user(
...

The problem persisted

  1. With no changes when I specified the language.
DO
$$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'public') THEN
        DROP SCHEMA public CASCADE;
    END IF;
END;
$$ LANGUAGE PLPGSQL;
  1. When I moved the @Sql annotation to method level.
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Found a solution but I don't know precisely why it works. I guess the Spring framework test environment doesn't support double dollar signs.

    Fixed with this change

    -- drop everything
DO
'
BEGIN
    IF EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = ''public'') THEN
        DROP SCHEMA public CASCADE;
    END IF;
END;
' LANGUAGE PLPGSQL;

CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
GRANT ALL ON SCHEMA public TO testuser;


-- create tables
CREATE TABLE public.user
(

  2. 0

    Possible problems of the Spring framework with dollar-quoting aside, there is a much simpler way with the dedicated DROP SCHEMA IF EXISTS:

    DROP SCHEMA IF EXISTS public CASCADE;

    Available since Postgres 8.2 (= forever). No need for PL/pgSQL. Plus, this is faster and safer. Your original allows for a tiny race condition between the check and executing the DROP, and might raise an exception occasionally after all under concurrent write load.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search