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

Description

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)";
}

Environment

postgres 8.3+, hibernate 3.x

Activity

Show:
Michaël van de Giessen
May 18, 2016, 8:43 AM

verified it is also an issue om 5.1.0.Final

the mentioned function

exists in PostgreSQL81Dialect (in hibernate 5.1) and the the sql function pg_table_is_visible is also available in Postgres 8.1 (http://www.postgresql.org/docs/8.1/static/functions-info.html)

Michaël van de Giessen
May 18, 2016, 9:08 AM

my case

  • multi tenancy per schema

  • extended AbstractDataSourceBasedMultiTenantConnectionProviderImpl to switch schema for each connection

  • running schemaUpdate having set the schema with MetadataBuilder#applyImplicitSchemaName

  • running schema-update on schema of the first tenant is ok

  • schema-update on schema's of following tenant's are missing the 'create sequence ...' statements

works correctly; current workaround is to extend the Dialect for this

i might make a pull request to fix this

Michaël van de Giessen
May 18, 2016, 10:42 AM

setting search_path on the connection is specifically set this multi-tenancy use case;

in a normal setting it is handled inside Connection.setSchema; it needs to be tested if it behaves correctly in a non-multi-tenancy setting

Assignee

Unassigned

Reporter

IanI

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major