liquibase with PostgreSQL relation issue

Question

I am using Liquibase to create my database for my spring-boot project. When I try and start up against PostgreSQL, I get errors that I cannot track down. I have the following entities:

@Entity
public class SubscriptionPackage {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long subscriptionPackageId;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "subscriptionPackage", fetch = FetchType.EAGER)
    private List<Menu> menus = new ArrayList<Menu>();

    @Enumerated(EnumType.STRING)
    @Column(name = "servicePackage", nullable = false)
    private ServicePackageType servicePackage;

    private String description;

and

@Entity
public class Menu {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long menuId;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "menu", fetch = FetchType.EAGER)
    private List<MenuItem> menuItems = new ArrayList<MenuItem>();

    @ManyToOne
    @JoinColumn(name = "subscriptionPackageId", nullable = false)
    private SubscriptionPackage subscriptionPackage;

    private String displayText;
    private int displayOrder;

Then in my db.changelog-master.xml I have:

<changeSet id="1" ..>
 <createTable tableName="SubscriptionPackage">
            <column name="subscriptionPackageId" type="bigint" autoIncrement="true">
                <constraints primaryKey="true"/>
            </column>
            <column name="servicePackage" type="varchar(300)"/>
            <column name="description" type="varchar(500)"/>
        </createTable>
..
      <createTable tableName="Menu">
            <column name="menuId" type="bigint" autoIncrement="true">
                <constraints primaryKey="true"/>
            </column>
            <column name="subscriptionPackageId" type="bigint"/>
            <column name="displayText" type="varchar(300)"/>
            <column name="displayOrder" type="int"/>
        </createTable>
</changeSet>

<changeSet id="2" ..>

        <addForeignKeyConstraint baseColumnNames="subscriptionPackageId"
                                 baseTableName="SubscriptionPackage"
                                 constraintName="fk_menu_subscriptionpackage"
                                 onDelete="CASCADE"
                                 onUpdate="RESTRICT"
                                 referencedColumnNames="menuId"
                                 referencedTableName="Menu"/>
</changeSet>

Here is the error that I get:

Caused by: org.postgresql.util.PSQLException: ERROR: relation "subscription_package" does not exist
  Position: 13
    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:388)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187)

I don't see why I get the "subscription_package" relation not found. I thought it was the foreign key, but added an explicit creation on my second changeset. The tables are being created in the database, so I think the mapping is correct, I just don't see what would cause this particular issue.

UPDATE I have confirmed that the database table is being created and the foreign key constraints are in place in Postgresql:

CREATE TABLE "SubscriptionPackage"
(
  "subscriptionPackageId" bigserial NOT NULL,
  "servicePackage" character varying(300),
  description character varying(500),
  CONSTRAINT pk_subscriptionpackage PRIMARY KEY ("subscriptionPackageId"),
  CONSTRAINT fk_menu_subscriptionpackage FOREIGN KEY ("subscriptionPackageId")
      REFERENCES "Menu" ("menuId") MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE CASCADE
)

And I have tracked down the exception to being thrown in my startup/seed when I try and save a list of SubscriptionPackage that do not have Menu's associated with them. Now, I wonder if my SubscriptionPackage have a list of Menu and my Menu has a reference back to SubscriptionPackage is that my respositories are getting confused.

Anyway, I am going to close this off and aska different question since I am pretty sure this is no longer a liquibase issue.

CLOSED

`SubscriptionPackage` and `"SubscriptionPackage"` are two different names. See the manual http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS The easiest way is to *never* quote your object names. — , Sep 10 '14 at 06:08

score 0 · Answer 1 · edited May 23 '17 at 11:57

0

I think you're missing to add the default schema name to your spring hibernate configuration.

There's a similar case here

edited May 23 '17 at 11:57

Community

1
1

answered Sep 05 '14 at 19:28

Sezin Karli

2,517
19
24

Thank you for the quick response. I have the following in my application.properties: **spring.datasource.url=jdbc:postgresql://localhost:5432/myschema** And the fact that the tables and sequences are created indicates it is found. – sonoerin Sep 05 '14 at 19:52
Shouldnt you explicitly tell that you'll use subscription_package table? I see no direct ref to it in the config. – Sezin Karli Sep 05 '14 at 21:18
I think the @Entity will tell Hibernate that it should use a table by the name of "SubscriptionPackage" since that is the class name. I tried adding the **@Table(name = "SubscriptionPackage")** to force that, but I still get the same error. – sonoerin Sep 07 '14 at 03:50
Maybe you can create the db by hand and try if it works thay way. By doing this we can understand that the problem comes from the autocreation part – Sezin Karli Sep 07 '14 at 04:31

liquibase with PostgreSQL relation issue

1 Answers1