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

Question

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

you shouldn't be creating your tables as a superuser to begin with — , Apr 11 '21 at 10:46

score 11 · Answer 1 · answered Apr 11 '21 at 10:40

11

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.

answered Apr 11 '21 at 10:40

Philip Benischke

111
1
4

flawless answer! – Gaurav Mar 30 '22 at 13:03
I've tried this; using native sql migration format. This is my changeset line: `--changeset njohnson:create-db-0 runInTransaction="false"`; followed by of course `CREATE DATABASE mydatabase;`. Doesn't work. What am I missing? – NateJ Jun 30 '22 at 20:38
@NateJ little bit late, but have you tried `--changeset njohnson:create-db-0 runInTransaction:false`? native sql as well as the yaml format use `:` instead of `=`. also i think the `"` are not required in both. – Philip Benischke Sep 29 '22 at 08:21

score 1 · Answer 2 · answered Aug 13 '20 at 15:39

1

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

answered Aug 13 '20 at 15:39

bilak

4,526
3
35
75

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

2 Answers2