ConnectionString SearchPath with 2 schemas

Hello,

I am building postgresql database with 3 schema.
schema1 and schema2 are identical with all tables.
schema3 is for postgres extensions.

I have two connections like this:

var connString1 = $“Server={server};Port={port};Search Path=schema1,schema3;Database={database};UserId={username};Password={password}”;

var connString2 = $“Server={server};Port={port};Search Path=schema2,schema3;Database={database};UserId={username};Password={password}”;

Now, tables are only creating in schema1 with this commands:

db1.CreateTable();
db2.CreateTable();

Everything works if using unly one schema in Search Path like this:

var connString1 = $“Server={server};Port={port};Search Path=schema1;Database={database};UserId={username};Password={password}”;

var connString2 = $“Server={server};Port={port};Search Path=schema2;Database={database};UserId={username};Password={password}”;

I captured sql queries and found that if 1 schema is defined in SearchPath then sql for checking if table exists is this:

SELECT COUNT(*) FROM pg_class JOIN pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace WHERE relname = ‘test_table’ AND nspname = ‘schema2’

if 2 schemas are defined in SearchPath then sql is this:

SELECT COUNT(*) FROM pg_class JOIN pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace WHERE relname = ‘test_table’

This is my testing app if it helps:

private static void Main(string args)
{
const string server = “server”;
const string port = “5432”;
const string database = “TestDb”;
const string username = “postgres”;
const string password = “postgres”;

OrmLiteConfig.ExecFilter = new CaptureLastSqlFilter();

//first connection

const string searchPath1 = “schema1,schema3”;
var connString1 = $“Server={server};Port={port};Search Path={searchPath1};Database={database};UserId={username};Password={password};”;

using (var db = new OrmLiteConnectionFactory(connString1, PostgreSqlDialect.Provider).Open())
{
db.CreateTable();
}

//second connection

const string searchPath2 = “schema2,schema3”;
var connString2 = $“Server={server};Port={port};Search Path={searchPath2};Database={database};UserId={username};Password={password};”;

using (var db = new OrmLiteConnectionFactory(connString2, PostgreSqlDialect.Provider).Open())
{
db.CreateTable();
}
}

I have tried executing commands directly in postgres (pgAdmin) and postgres is creating tables as expected if two schemas are defined in search path (first schema is used):

SET search_path TO schema1,schema3;
CREATE TABLE “test_table” ( “column1” INTEGER PRIMARY KEY );

SET search_path TO schema2,schema3;
CREATE TABLE “test_table” ( “column1” INTEGER PRIMARY KEY );

I’m not sure is this is related to NpgSql driver or to OrmLite, but maybe someone could help.

Thanks,
Dejan

OrmLite has no knowledge related to PostgreSQL’s Search Path anywhere, if it’s not working on the connection string, try executing the statement like you’re doing in pgAdmin, e.g:

db.ExecuteSql("SET search_path TO schema1,schema3");
db.CreateTable<TestTable>();

Ok, I will try that.

I have captured SQL queries with OrmLiteConfig.ExecFilter that has SearchPath schema added in Where statement:

SELECT COUNT(*) FROM pg_class JOIN pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace WHERE relname = ‘test_table’ AND nspname = ‘schema2’

Because of that I thought that is related to OrmLite.

Thank you, it is working but Search Path only has to have one schema defined.