relation public.xxx does not exist
Posted: Fri 31 May 2019 02:38
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:
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:
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?
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 I change the search_path to "futuresdata, backtests", then no exception occurs.- 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
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?