[hibernate-issues] [Hibernate-JIRA] Created: (HHH-5538) Postgres: fix dialect to work in databases with mutiple schema and search_path set

[ian pojman (JIRA)]

Postgres: fix dialect to work in databases with mutiple schema and search_path set
----------------------------------------------------------------------------------

                 Key: HHH-5538
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5538
             Project: Hibernate Core
          Issue Type: Improvement
          Components: core
    Affects Versions: 3.6.0.Beta4, 3.3.1
         Environment: postgres 8.3+, hibernate 3.x 
            Reporter: ian pojman


In postgres you can have a database with multiple schemas, and configure a particular account to use only a particular schema (by setting something called search_path).

When I use hbm2dll to auto-create my schema objects, sequences are not created if they exist in another schema somewhere in the database, because the dialect simply queries the pg_class catalog for any sequences by that name, even if they are not in the current search path.

Modifying  PostgreSQLDialect to use the following query seems to this issue for us. Suggest consideration for inclusion in the postgres dialect. Have not evaluated pg version compatibility.

using hibernate 3.3.1, but verified version on trunk has this same issue.


@Override
    public String getQuerySequencesString() {
//        	return "select relname from pg_class where relkind='S'";

        // only return sequences which are visible in the current search path
        return "SELECT relname FROM pg_class WHERE relkind='S' and pg_table_is_visible(oid)";
    }

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira