"Missing table" on materialized view

Question

I have the following entity:

@Entity
@Table(name = "full_address")
public class FullAddress {

  @Id
  @Column(name = "guid")
  private String id;

  @Column(name = "address")
  private String address;

  //getters and setters omitted
}

Also I create materialized view as follows:

CREATE TABLE address_table (
  -- address fields,
  aoid VARCHAR(36),
  CONSTRAINT address_pk PRIMARY KEY (aoid)
);
CREATE MATERIALIZED VIEW full_address AS
  SELECT buildFullAddressById(addrobj.aoid) AS address, addrobj.aoid AS guid FROM address_table AS addrobj;

-- buildFullAddressById is an sql function which is not important here

When I launch Tomcat application, i always get

org.hibernate.HibernateException: Missing table: full_address

Why is this happening? How to fix it?

UPD: looks like a bug in hibernate: https://hibernate.atlassian.net/browse/HHH-9602

Did you check your grant permission? – Karthik R Sep 17 '15 at 08:00 — Karthik R, Sep 17 '15 at 08:00

score 5 · Answer 1 · answered Feb 05 '18 at 11:49

I have had the same bug for several past days. As this answer said, it is possible to disable hibernate.hbm2ddl.auto property in your persistence.xml, but it is not a good idea if your project is rapidly developing.

TL;DR: set property hibernate.hbm2dll.extra_physical_table_types to MATERIALIZED VIEW.

Or add -Dhibernate.hbm2dll.extra_physical_table_types="MATERIALIZED VIEW" to VM options. But it is better to such options to configuration file.

Right now, we are using PostgreSQL 9.6 and Hibernate 5.2.12.Final. Somewhy, all materialized views validations were failing with the following exception:

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing table [our_project_schema.mv_one_of_views]

All entities that successfully passed validation were either simple tables or views.

It seems like it is a default behaviour for generic databases. In sources here on lines 79-81 they add only these types:

final List<String> tableTypesList = new ArrayList<>();
tableTypesList.add( "TABLE" );
tableTypesList.add( "VIEW" );

Lines 85-87 tell us that there is a possibility to extend these hardcoded values with custom ones:

if ( extraPhysicalTableTypes != null ) {
    Collections.addAll( tableTypesList, extraPhysicalTableTypes );
}

On line 56 it is declared private String[] extraPhysicalTableTypes;, and on lines 71-77 there are some more values added into this array:

if ( !"".equals( extraPhysycalTableTypesConfig.trim() ) ) {
    this.extraPhysicalTableTypes = StringHelper.splitTrimmingTokens(
        ",;",
        extraPhysycalTableTypesConfig,
        false
    );
}

They come from lines 66-70, encoded as String under key EXTRA_PHYSICAL_TABLE_TYPES with empty default value:

final String extraPhysycalTableTypesConfig = configService.getSetting(
    AvailableSettings.EXTRA_PHYSICAL_TABLE_TYPES,
    StandardConverters.STRING,
    ""
);

And here on line 1545 is the declaration of that key:

/**
 * Identifies a comma-separate list of values to specify extra table types,
 * other than the default "TABLE" value, to recognize as defining a physical table
 * by schema update, creation and validation.
 *
 * @since 5.0
 */
String EXTRA_PHYSICAL_TABLE_TYPES = "hibernate.hbm2dll.extra_physical_table_types";

So, adding this property will add another entry to tableTypesList that is used for filtering of many other entities in database, such as sequences, indices, temporary tables and others, that may have name similar to yours materialized view.

This is how my persistence.xml looks like, if you are interested:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
             version="2.1">
    <persistence-unit name="project-pu">
        <jta-data-source>java:jboss/datasources/project-pu</jta-data-source>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.spatial.dialect.postgis.PostgisPG95Dialect"/>
            <property name="hibernate.hbm2ddl.auto" value="validate"/>
            <property name="hibernate.hbm2dll.extra_physical_table_types" value="MATERIALIZED VIEW"/>
            <property name="hibernate.show_sql" value="false"/>
            <property name="hibernate.format_sql" value="false"/>
            <property name="hibernate.use_sql_comments" value="false"/>
            <property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/mgt"/>
            <property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
        </properties>
    </persistence-unit>
</persistence>

P.S. I know it is a very old post, but I fought with this problem for a few days. I failed finding an answer, so I decided to put it somewhere in the Internet. And this somewhere became here. :)

Thanks for taking the time to document this. Much appreciated. :) — cagcowboy, Jun 18 '21 at 20:03

score 3 · Accepted Answer · answered Sep 17 '15 at 09:18

3

It's definitely a bug of Hibernate and was described here: https://hibernate.atlassian.net/browse/HHH-9602

So, I recommend removing hibernate.hbm2ddl.auto property as a workaround.

answered Sep 17 '15 at 09:18

Everv0id

1,862
3
25
47

score 3 · Answer 3 · answered May 17 '18 at 10:57

I found out the most simple solution (assuming you still want to use Hibernate to validate the schema) is to add a subselect to the table definition.

Using hbm.xml

<hibernate-mapping>
    <class name="com.initech.MvObject" table="MV_OBJ">
        <!-- This is necessary when using older Hibernate versions because of 
            Hibernate bugs. See
            https://hibernate.atlassian.net/browse/HHH-1329
            https://hibernate.atlassian.net/browse/HHH-9602
         -->
        <subselect>select * from MV_OBJ</subselect>

Using annotations

import org.hibernate.annotations.Subselect;
import javax.persistence.Entity;

@Entity
@Subselect("select * from MV_OBJ")
public class MvObj{

}

"Missing table" on materialized view

3 Answers3

Linked