Issue while trying to use PostGis in testcontainer initialization.

[deabreu]

Hi everyone. Please I need some assistance here. Recently I started a task in which I had to integrate PostGIS to the currently implemented test suite which uses testcontainers to validate DB transactions.

I started it by extending the PostgreSQLContainer to use the same container image as the PostgisSQLContainer class in testcontainers-java.

The code compiled, but when I ran the tests I started to get stack trace error messages like this one:

org.postgresql.util.PSQLException: ERROR: CREATE DATABASE cannot be executed within a pipeline at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2532) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2267) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:312) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:153) at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:142) at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)

I couldn't get a straight answer from Stackoverflow's search results regarding this error except that this is a safeguard. But, what I didn't get is why this was working before.

The apparently problematic script is the following:
` val dbCreateSQL =

      s"""|DO
          |'
          |BEGIN
          | IF NOT EXISTS (
          |         SELECT FROM pg_catalog.pg_roles
          |         WHERE  rolname = ''$userName'') THEN
          |
          |           CREATE ROLE $userName WITH SUPERUSER LOGIN PASSWORD ''$password'';
          | END IF;
          |
          |END
          |'
          |;
          |CREATE DATABASE $dbName WITH OWNER = $userName;
          |""".stripMargin
    withCloseable(sharedDBConnection.prepareStatement(dbCreateSQL)) { ps =>
      val _ = ps.execute()
    }`

Essentially, all I did was change this statement DockerImageName.parse("postgres:13.3") for this statement DockerImageName.parse("postgis/postgis").asCompatibleSubstituteFor("postgres").

I appreciate any insight you can provide me.