Hibernate + PostgreSQL : relation does not exist - SQL Error: 0, SQLState: 42P01

Question

I am having some problems trying to work with PostgreSQL and Hibernate, more specifically, the issue mentioned in the title. I've been searching the net for a few hours now but none of the found solutions worked for me.

I am using Eclipse Java EE IDE for Web Developers. Build id: 20090920-1017 with HibernateTools, Hibernate 3, PostgreSQL 8.4.3 on Ubuntu 9.10.

Here are the relevant files:

Message.class

package hello;

        public class Message {
         private Long id;
         private String text;

         public Message() {
         }

         public Long getId() {
          return id;
         }

         public void setId(Long id) {
          this.id = id;
         }

         public String getText() {
          return text;
         }

         public void setText(String text) {
          this.text = text;
         }
        }

Message.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
 "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
 "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="hello">
  <class 
   name="Message"
   table="public.messages">
   <id  name="id" column="id">
    <generator class="assigned"/>
   </id>
   <property name="text" column="messagetext"/>
   </class>
</hibernate-mapping>

hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
  "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
  "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
        <property name="hibernate.connection.password">bar</property>
        <property name="hibernate.connection.url">jdbc:postgresql:postgres/tommy</property>
        <property name="hibernate.connection.username">foo</property>
        <property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
        <property name="show_sql">true</property>
        <property name="log4j.logger.org.hibernate.type">DEBUG</property>
        <mapping resource="hello/Message.hbm.xml"/> 
    </session-factory>
</hibernate-configuration>

Main

package hello;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class App {

 public static void main(String[] args) {
  SessionFactory sessionFactory = new Configuration().configure()
    .buildSessionFactory();

  Message message = new Message();
  message.setText("Hello Cruel World");
  message.setId(2L);

  Session session = null;
  Transaction transaction = null;
  try {
   session = sessionFactory.openSession();
   transaction = session.beginTransaction();
   session.save(message);
  } catch (Exception e) {
   System.out.println("Exception attemtping to Add message: "
     + e.getMessage());

  } finally {
   if (session != null && session.isOpen()) {
    if (transaction != null)
     transaction.commit();
    session.flush();
    session.close();
   }

  }
 }
}

Table structure:

foo=# \d messages
 Table "public.messages"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 id          | integer | 
 messagetext | text    | 

Eclipse console output when I run it

Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Environment <clinit>
INFO: Hibernate 3.5.1-Final
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Environment <clinit>
INFO: hibernate.properties not found
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Environment buildBytecodeProvider
INFO: Bytecode provider name : javassist
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Environment <clinit>
INFO: using JDK 1.4 java.sql.Timestamp handling
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Configuration configure
INFO: configuring from resource: /hibernate.cfg.xml
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Configuration getConfigurationInputStream
INFO: Configuration resource: /hibernate.cfg.xml
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Configuration addResource
INFO: Reading mappings from resource : hello/Message.hbm.xml
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.HbmBinder bindRootPersistentClassCommonValues
INFO: Mapping class: hello.Message -> public.messages
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.Configuration doConfigure
INFO: Configured SessionFactory: null
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Using Hibernate built-in connection pool (not for production use!)
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Hibernate connection pool size: 20
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: autocommit mode: false
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: using driver: org.postgresql.Driver at URL: jdbc:postgresql:postgres/tommy
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: connection properties: {user=foo, password=****}
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: RDBMS: PostgreSQL, version: 8.4.3
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC driver: PostgreSQL Native Driver, version: PostgreSQL 8.4 JDBC4 (build 701)
Apr 28, 2010 11:13:54 PM org.hibernate.dialect.Dialect <init>
INFO: Using dialect: org.hibernate.dialect.PostgreSQLDialect
Apr 28, 2010 11:13:54 PM org.hibernate.engine.jdbc.JdbcSupportLoader useContextualLobCreation
INFO: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException
Apr 28, 2010 11:13:54 PM org.hibernate.transaction.TransactionFactoryFactory buildTransactionFactory
INFO: Using default transaction strategy (direct JDBC transactions)
Apr 28, 2010 11:13:54 PM org.hibernate.transaction.TransactionManagerLookupFactory getTransactionManagerLookup
INFO: No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic flush during beforeCompletion(): disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic session close at end of transaction: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC batch size: 15
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC batch updates for versioned data: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Scrollable result sets: enabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC3 getGeneratedKeys(): enabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Connection release mode: auto
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default batch fetch size: 1
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Generate SQL with comments: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Order SQL updates by primary key: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Order SQL inserts for batching: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory createQueryTranslatorFactory
INFO: Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
Apr 28, 2010 11:13:54 PM org.hibernate.hql.ast.ASTQueryTranslatorFactory <init>
INFO: Using ASTQueryTranslatorFactory
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query language substitutions: {}
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JPA-QL strict compliance: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Second-level cache: enabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query cache: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory createRegionFactory
INFO: Cache region factory : org.hibernate.cache.impl.NoCachingRegionFactory
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Optimize cache for minimal puts: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Structured second-level cache entries: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Echoing all SQL to stdout
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Statistics: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Deleted entity synthetic identifier rollback: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default entity-mode: pojo
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Named query checking : enabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Check Nullability in Core (should be disabled when Bean Validation is on): enabled
Apr 28, 2010 11:13:54 PM org.hibernate.impl.SessionFactoryImpl <init>
INFO: building session factory
Apr 28, 2010 11:13:55 PM org.hibernate.impl.SessionFactoryObjectFactory addInstance
INFO: Not binding factory to JNDI, no JNDI name configured
Hibernate: insert into public.messages (messagetext, id) values (?, ?)
Apr 28, 2010 11:13:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: 42P01
Apr 28, 2010 11:13:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Batch entry 0 insert into public.messages (messagetext, id) values ('Hello Cruel World', '2') was aborted.  Call getNextException to see the cause.
Apr 28, 2010 11:13:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: 42P01
Apr 28, 2010 11:13:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ERROR: relation "public.messages" does not exist
  Position: 13
Apr 28, 2010 11:13:55 PM org.hibernate.event.def.AbstractFlushingEventListener performExecutions
SEVERE: Could not synchronize database state with session
org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
 at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
 at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:179)
 at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
 at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
 at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1206)
 at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:375)
 at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
 at hello.App.main(App.java:31)
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into public.messages (messagetext, id) values ('Hello Cruel World', '2') was aborted.  Call getNextException to see the cause.
 at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2569)
 at org.postgresql.core.v3.QueryExecutorImpl$1.handleError(QueryExecutorImpl.java:459)
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1796)
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2708)
 at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
 at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
 ... 8 more
Exception in thread "main" org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
 at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
 at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:179)
 at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
 at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
 at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1206)
 at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:375)
 at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
 at hello.App.main(App.java:31)
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into public.messages (messagetext, id) values ('Hello Cruel World', '2') was aborted.  Call getNextException to see the cause.
 at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2569)
 at org.postgresql.core.v3.QueryExecutorImpl$1.handleError(QueryExecutorImpl.java:459)
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1796)
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2708)
 at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
 at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
 ... 8 more

PostgreSQL log file

2010-04-28 23:13:55 EEST LOG:  execute S_1: BEGIN
2010-04-28 23:13:55 EEST ERROR:  relation "public.messages" does not exist at character 13
2010-04-28 23:13:55 EEST STATEMENT:  insert into public.messages (messagetext, id) values ($1, $2)
2010-04-28 23:13:55 EEST LOG:  unexpected EOF on client connection

If I copy/paste the query into the postgre command line and put the values in and ; after it, it works.

Everything is lowercase, so I don't think that it's that issue.

If I switch to MySQL, the same code same project (I only change driver,URL, authentication), it works.

In Eclipse Datasource Explorer, I can ping the DB and it succeeds. Weird thing is that I can't see the tables from there either. It expands the public schema but it doesn't expand the tables. Could it be some permission issue?

Thanks!

Answer 1

Your JDBC URL is "jdbc:postgresql:postgres/tommy" which is unusual. The documentation suggests "jdbc://hostname/databasename". Modern installations come with a "postgres" database that almost definitely isn't what you want to connect to; I don't know how strict the JDBC driver's URL parsing is.

What are you expecting your database name and hostname to be? e.g. what are your parameters to psql to connect to the database that way?

Tip: in postgresql.conf, some settings you may consider:

log_connections = on
log_disconnections = on
log_line_prefix = '%t %c %q%u@%h:%d '

If the error is what I think (you're connecting to the wrong database), this would log things like the database name along with the error in your postgresql.log file.

Answer 2

I've just solved the same / similar problem by specifying the schema.

@Entity
@Table(name = "mytable", schema="myschema")
public class MyTable implements Serializable {
    ...

Answer 3

Another answer may be to check schema permissions - which may be useful for others who happen to come across the subject heading of this question. I had the same exact symptoms regarding the error message which was;

<SQL Error: 0, SQLState: 42P01>
<ERROR: relation "tablename" does not exist>

The solution was to enable permissions on that schema for the user I was using to log in with.

Also: Noting that a good diagnostic tool here is to log into the postgres command line console with the same user your program is using and attempt to access the table and schema in the same way.