Setting 'search_path' via SQL with DBExpress not working
Posted: Thu 07 Apr 2022 10:34
Hello,
We are experiencing an issue with the latest version (v5.1.1) running with PostgreSQL 14, compiling under Delphi 11.0 Alexandria.
We have migrated the data in our database from the 'public' schema to our own schema called 'petrog'. Having done this, we have then dropped the tables in 'public', so the tables only exist in 'petrog'. When we connect to the database, we use SQL to set the search path:
SQLQueryFieldDef.SQL.Add('ALTER DATABASE petrog SET search_path TO petrog,public;');
SQLQueryFieldDef.ExecSQL;
Having done this, if we then immediately call
SQLQueryFieldDef.SQL.Add('SHOW search_path;');
SQLQueryFieldDef.Active := true;
s := SQLQueryFieldDef.fields[0].Value;
then s is returning as 'public', not 'petrog,public'. As a result, our software is then unable to write data to any table e.g.
EUpdateError details: relation "windowposn" does not exist
as it is looking in the 'public' schema where no tables exist, whereas it should be looking in the 'petrog' schema. Every table has an associated TSQLTable component, and the 'SchemaName' property for each of these is set to 'petrog'.
We have also tried
'ALTER ROLE petrog SET search_path = petrog,public;'
and
'ALTER ROLE postgres IN DATABASE petrog SET search_path = petrog,public;'
with the same result.
So our question is - why is the search_path failing to set correctly when we execute any of the above SQL statements in our software which uses the DBExpress drivers? If we execute the exact same SQL statements in PGAdmin, the search_path is then set correctly.
Can anyone help? It would be greatly appreciated!
Barrie Wells
We are experiencing an issue with the latest version (v5.1.1) running with PostgreSQL 14, compiling under Delphi 11.0 Alexandria.
We have migrated the data in our database from the 'public' schema to our own schema called 'petrog'. Having done this, we have then dropped the tables in 'public', so the tables only exist in 'petrog'. When we connect to the database, we use SQL to set the search path:
SQLQueryFieldDef.SQL.Add('ALTER DATABASE petrog SET search_path TO petrog,public;');
SQLQueryFieldDef.ExecSQL;
Having done this, if we then immediately call
SQLQueryFieldDef.SQL.Add('SHOW search_path;');
SQLQueryFieldDef.Active := true;
s := SQLQueryFieldDef.fields[0].Value;
then s is returning as 'public', not 'petrog,public'. As a result, our software is then unable to write data to any table e.g.
EUpdateError details: relation "windowposn" does not exist
as it is looking in the 'public' schema where no tables exist, whereas it should be looking in the 'petrog' schema. Every table has an associated TSQLTable component, and the 'SchemaName' property for each of these is set to 'petrog'.
We have also tried
'ALTER ROLE petrog SET search_path = petrog,public;'
and
'ALTER ROLE postgres IN DATABASE petrog SET search_path = petrog,public;'
with the same result.
So our question is - why is the search_path failing to set correctly when we execute any of the above SQL statements in our software which uses the DBExpress drivers? If we execute the exact same SQL statements in PGAdmin, the search_path is then set correctly.
Can anyone help? It would be greatly appreciated!
Barrie Wells