[solved] reverse engineering with postgresql 8.1

adam.nowotny · **Posted:** Mon Mar 06, 2006 6:28 pm

I had a problem with reverse engineering after update to PostgreSQL 8.1. The foreign keys were not exctracted at all.
Stacktrace reported this problem "ERROR: function information_schema._pg_keypositions() does not exist"

This function exists in 8.0 databases, but not in fresh-created 8.1 databases. The solution is to add this function:

Code:

CREATE OR REPLACE FUNCTION information_schema._pg_keypositions()
  RETURNS SETOF int4 AS
$BODY$select g.s
        from generate_series(1,current_setting('max_index_keys')::int,1)
        as g(s)$BODY$
  LANGUAGE 'sql' IMMUTABLE;
ALTER FUNCTION information_schema._pg_keypositions() OWNER TO postgres;

Just though it might be useful :]
P.S. Just starting with Hibernate, looks promissing !

david · **Posted:** Mon Mar 06, 2006 8:27 pm

Great. Did you send this onto the PostgreSQL devs?

adam.nowotny · **Posted:** Mon Mar 06, 2006 8:33 pm

No, it looked like hibernate tools bug to me. Thought this function is deprecated or sth like this.

david · **Posted:** Mon Mar 06, 2006 8:49 pm

The reverse engineering occurs through the JDBC driver's meta data facility. How the driver gets the information is not a hibernate issue. So the PostgreSQL JDBC driver is using this function. So the question then becomes - Did you use the correct driver for the database? There are new drivers for the 8.1 series.

adam.nowotny · **Posted:** Tue Mar 07, 2006 6:16 am

Hmmm....old JDBC driver...this might actally be the cause of the whole problem. Will check this. Sorry, i'm quite new to Java programming :]

david · **Posted:** Tue Mar 07, 2006 7:59 pm

No problem we are here to help.

Blackbrrd · **Joined:** Wed Sep 28, 2005 6:45 pm **Posts:** 56

I ran into exactly the same problem. (I could reverse-engineer from postgres 8.0, but couldn't from postgres 8.1)

Running the threadstarters sql fixed the problem though. It didn't seem to work with even the newest jdbc driver, but I am a bit confused to how to change the jdbc driver to use for reverse engineering in eclipse?

It would probably be helpful if you could locate the hibernate-console.xml inside the project somewhere, because if you press any wrong buttons in the wizards you have to start all over again?

(I also noticed that after running the sql you get a table in hibernate.reveng.xml file called pg_logdir_ls, which I didn't have otherwise. - I haven't a clue if this is relevant at all)

max · **Posted:** Mon Apr 10, 2006 4:15 pm

just double click the console configuration to reedit it.

or if you want to move the mouse too then right click and select edit

Blackbrrd · **Joined:** Wed Sep 28, 2005 6:45 pm **Posts:** 56

That helped to get me thinking about how Eclipse works, I was in the java perspective where you can't see the console configuration, so now I am atleast using the latest driver.