Cannot create View in PostgreSQL 9.0.7 with Liquibase 2.0.4

General Discussion
1

Hello.

I have problems creating a View in my PostgreSQL schema with Liquibase. If I create the View manually in my schema it works, but if try to use following change log it fails;

  2.        
  3.             SELECT wl.worklog_id,
  4.                     d.driver_id,
  5.                     d.personal_id,
  6.                     d.forename,
  7.                     d.surname,
  8.                     extract(year from wl.date) as year,
  9.                     extract(month from wl.date) as month,
  10.                     extract(day from wl.date) as day,
  11.                     wl.start_time,
  12.                     wl.end_time,
  13.                     wl.end_time - wl.start_time as work_time,
  14.                     t.tour_id as tour_id,
  15.                     t.name as tour_name
  16.                 FROM worklogs wl left outer join drivers d on wl.driver_id=d.driver_id
  17.                         left outer join tours t on wl.tour_id=t.tour_id
  18.                 ORDER BY d.surname,
  19.                         d.forename,
  20.                         wl.date
  21.        
  22.    

This Exception is thrown:

  1. Liquibase Home is not set.
    Liquibase Home: /home/crazydoc/programme/liquibase
    INFO 30.04.12 15:33:liquibase: Successfully acquired change log lock
    INFO 30.04.12 15:33:liquibase: Reading from schema_name.databasechangelog
    INFO 30.04.12 15:33:liquibase: Reading from schema_name.databasechangelog
    SEVERE 30.04.12 15:33:liquibase: Change Set src/main/liquibase/first-views.xml::view-worklogs_report_postgresql::NormanKnott failed.  Error: Error executing SQL CREATE VIEW schema_name.worklogs_report AS SELECT wl.worklog_id,
                                    d.driver_id,
                                    d.personal_id,
                                    d.forename,
                                    d.surname,
                                    extract(year from wl.date) as year,
                                    extract(month from wl.date) as month,
                                    extract(day from wl.date) as day,
                                    wl.start_time,
                                    wl.end_time,
                                    wl.end_time - wl.start_time as work_time,
                                    t.tour_id as tour_id,
                                    t.name as tour_name
                            FROM worklogs wl left outer join drivers d on wl.driver_id=d.driver_id
                                            left outer join tours t on wl.tour_id=t.tour_id
                            ORDER BY d.surname,
                                            d.forename,
                                            wl.date: ERROR: relation “worklogs” does not exist
      Position: 444
    liquibase.exception.DatabaseException: Error executing SQL CREATE VIEW schema_name.worklogs_report AS SELECT wl.worklog_id,
                                    d.driver_id,
                                    d.personal_id,
                                    d.forename,
                                    d.surname,
                                    extract(year from wl.date) as year,
                                    extract(month from wl.date) as month,
                                    extract(day from wl.date) as day,
                                    wl.start_time,
                                    wl.end_time,
                                    wl.end_time - wl.start_time as work_time,
                                    t.tour_id as tour_id,
                                    t.name as tour_name
                            FROM worklogs wl left outer join drivers d on wl.driver_id=d.driver_id
                                            left outer join tours t on wl.tour_id=t.tour_id
                            ORDER BY d.surname,
                                            d.forename,
                                            wl.date: ERROR: relation “worklogs” does not exist
      Position: 444
            at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:61)
            at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:108)
            at liquibase.database.AbstractDatabase.execute(AbstractDatabase.java:1091)
            at liquibase.database.AbstractDatabase.executeStatements(AbstractDatabase.java:1075)
            at liquibase.changelog.ChangeSet.execute(ChangeSet.java:317)
            at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:27)
            at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:58)
            at liquibase.Liquibase.update(Liquibase.java:114)
            at liquibase.integration.commandline.Main.doMigration(Main.java:821)
            at liquibase.integration.commandline.Main.main(Main.java:134)
    Caused by: org.postgresql.util.PSQLException: ERROR: relation “worklogs” does not exist
      Position: 444
            at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
            at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
            at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
            at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
            at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
            at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366)
            at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:96)
            at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:54)
            … 9 more
    INFO 30.04.12 15:33:liquibase: Successfully released change log lock
    Liquibase Update Failed: Error executing SQL CREATE VIEW makno_prod.worklogs_report AS SELECT wl.worklog_id,
  2. …

This is my commandline:
  1. liquibase --classpath=postgresql-9.0-801.jdbc4.jar --driver=org.postgresql.Driver --changeLogFile=src/main/liquibase/changelog-master.xml --url=jdbc:postgresql://localhost/dbname --username=admin --defaultSchemaName=schema_name update

2

It seems to me Liquibase runs the Select statement of the View against the public schema, not against the given defaultSchema.

3

I put a fix in to 2.0.5 that should resolve this. Will hopefully be out tomorrow.


Nathan

4

Hey, thanks for the quick reply and fix! I tried the new release, but nothing changed for me.

For others that run into the same problem!
A work around for this, is to define the desired schema as default, before you use Liquibase.
This is done by the following statement:

  1. alter role userName set search_path to ‘schemaName’;

You have to execute this satement with role management privileges;

Before you use the statement, save the old variable content by login as the user. And then use the following statement to display the current search_path:

  1. show search_path;


Crazy Doc