relation public.xxx does not exist

[[email protected]]

My pgsql database has three schemas: backtests, futuresdata, and public.
There are two tables: backtests.backtests_table and futuresdata.futuresdata_table.
The search_path is set to "backtests, futuresdata".
I use the code below to update the values in futuresdata_table:

Code: Select all

			
string connectionString = "User Id=Brian;Host=localhost;Port=5432;Database=devart_test_20190530;"
	+ "Connection Timeout=30;Persist Security Info=False;Integrated Security=False;AllowDateTimeOffset=True;";
using( PgSqlConnection conn = new PgSqlConnection( connectionString ) )
{
	conn.Open();

	PgSqlCommand select = new PgSqlCommand();
	select.CommandText = "select * from futuresdata_table ";

	PgSqlDataTable table = new PgSqlDataTable( select, conn );
	table.FetchAll = true;
	table.Active   = true;

	var data = table.Select();

	DataRow row = data.SingleOrDefault(r => (int)r["f1"] == 1);
	if (row != null)
	{
		row["f2"] = 101;
	}

	int n = 0;
	try
	{
		n = table.Update();
	}
	catch( Exception e )
	{
		Console.WriteLine( "Exception : " + e );
	}
}

If the value of row["f2"] is actually changed, the Update() call always throws the exception with the message "relation public.futuresdata_table does not exist". The exception Error is:

- Error {Error: 42P01: relation "public.futuresdata_table" does not exist} Devart.Data.PostgreSql.PgSqlError
CallStack "" string
ColumnName "" string
ConstraintName "" string
DataTypeName "" string
DetailMessage "" string
ErrorCode "42P01" string
ErrorSql "UPDATE public.futuresdata_table SET f2 = $1 WHERE ((($2 = 1 AND f1 IS NULL) OR (f1 = $3)) AND (($4 = 1 AND f2 IS NULL) OR (f2 = $5)))" string
FileName "parse_relation.c" string
Hint "" string
InternalPosition "" string
InternalQuery "" string
LineNumber 1159 int
Message "relation \"public.futuresdata_table\" does not exist" string
Position 8 int
ProcedureName "parserOpenTable" string
SchemaName "" string
Severity Error Devart.Data.PostgreSql.Severity
TableName "" string
+ Non-Public members

If I change the search_path to "futuresdata, backtests", then no exception occurs.

Other than receiving the first item found in the path, I don't believe the order of schemas presented in search_path should affect query execution. This is especially true when there is no name collision, as is the case here. The second issue I see is the question of why dotConnect would arbitrarily use a "public" schema qualifier on a name in a query.

Is this a bug?

[Pinturiccio]

PgSqlConnection has the "Initial Schema" connection string parameter. For more information, please refer to https://www.devart.com/dotconnect/postg ... tring.html

If you do no specify a value for this parameter, the default value is used. And it is the public schema. Thus, all queries are run against the public schema. If you want all queries to run against other schema, you need specify it in the connection string. If you want run a query against other schema, you need to specify the schema name before the name of a table.

[[email protected]]

Got it - thanks.