Setting default schema for Vertica Database

Question

I am building a web application using Play! with Vertica database as back-end. The JDBC connection string for Vertica contains the server and database name, but my tables are under a specific schema (say "dev_myschema"). Thus, I should refer to my table as "dev_myschema.mytable". There is an exact copy of all these tables in a production schema as well (say "prod_myschema") with real data.

I would like to set this schema name in the configuration file so that it is easy to switch between these two schema. For now, I have a getConnection method in a helper class, that does DB.getConnection() and sets the configured schema as the default schema for that connection object. However, the same does not help in other model classes where it is mentioned along with its Entity annotation (@Entity @Table(name=dev_myschema.mytable))

Is there a way by which I can specify the schema name in the configuration file and have it read by the connection method as well as the model annotations?

Thanks.

Subsidy

i'm trying to get jpa to work with vertica. would you post your persistence and datasource configuration? i just can't get it to work. – Intestate

@Intestate - I am using it with Play framework and configured it consider the Vertica database like a Postgres dialect. FWIW, here is the configuration parameter jpa.dialect=org.hibernate.dialect.PostgreSQLDialect – Subsidy

got it to work two weeks ago with an custom dialect that was based on postgres. hbm2ddl didn't work for my (old) model since it used datatypse that where not supported by vertica (smallint to int4 is default, vertica doesn't know int4 ...). still thanks for the answer. – Intestate

Answer 1

Eugene got it almost correct, but was missing an underscore. The correct Vertica SQL syntax to set the default schema is:

set search_path to dev_myschema

As Eugene suggested, if you are using low-level JDBC, as soon as you create your Connection object you can do:

conn.createStatement().executeUpdate("set search_path to " + schemaName);

Nymph

Answer 2

As far as I'm aware (and I just scanned the 4.1.7 documentation), there is no way as of yet to set a schema as the default.

Rogovy

Answer 3

according to the sql guide the default schema is the first one found in your search tree. maybe you could exploit that and make sure your copy is found first.

Intestate

Answer 4

They way I handle this issue is by executing a "set search path" command if I am using my development schema. So, as soon as your Vertica connection object is created, execute the following command:

    "set search path to dev_myschema"

In my application code, I just have my Vertica object check an environment/config variable, and if the "dev schema" setting is present, it executes that statement upon establishing the connection. My production config doesn't have that setting, so it will just use the default schema in that case and not incur the additional overhead of executing that statement every time.

Curlicue

Answer 5

In 7.0, admin can set it at user level by issuing below command:

alter user user_name search_path schema1,schema2;

Treasurehouse