Liquibase: ERROR: CREATE DATABASE cannot run inside a transaction block

user_12249903 0
Viewed 1165

I am trying to create a db on PostgreSQL using liquibase. This is my dbchangelog file

<?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-3.8.xsd">
  <changeSet  author="testuser"  id="3">
    <sql  dbms="postgresql"
            endDelimiter=";">
            CREATE DATABASE testdb;
    </sql>
  </changeSet>
</databaseChangeLog

When i try to run liquibase update from my project directory i am getting

Unexpected error running Liquibase: ERROR: CREATE DATABASE cannot run inside a transaction block [Failed SQL: (0) CREATE DATABASE testdb]

I saw setting AUTOCOMMIT = ON in PostgreSQL will solve this issue.But this method only works for PostgreSQL 9.4 and below . But i am using AWS RDS PostgreSQL 9.6.8

liquibase-sql liquibase postgresql
2 Answers

Short: add runInTransaction="false" to your changeSet.

Explanation:

Liquibase opens a transaction block in order to guarantee that a changeSets are executed fully or not at all.

PostgreSQL doc says: CREATE DATABASE cannot be executed inside a transaction block. ( see PostgreSQL Doc on Create Database!)

So there is clearly a conflicting behavior, which can only be avoided, by not using a transaction block.

Be advised that deactivation the transaction behavior with multiple commands, can lead to the problem that a changeSet is only partly executed.

user_37037950

Liquibase is not intended for database creation. You should have your db schema prepared and liquibase will populate it with your tables/indexes/...

user_2662540
Related