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