Delphi/FireDAC using default schema instead of search_path on insert/update commands in Postgres

Question

I've been using FireDAC for a couple of years now and somehow this problem never came up.

We are using Postgres database with a couple of schemas defined. Each user has a Postgres Search_path defined and till now we mostly used schema1.

Example:

ALTER ROLE user1 SET search_path = schema1, schema2, schema3;

Now, in Delphi all queries and tables were defined without a schema (we all thought search_path is doing it's job) and while we were only using schema1 everything was working fine.

But whenever I use a table from either schema2 or schema3 only Open operation works as intended. Post fails with error 'relation "schema1.tableX" does not exist' even though tableX is of a different schema.

Relation does not exist What am I missing here? Thank you all in advance ...

I'm using Delphi Tokyo and Postgres 10.1 and datasets were connected to DevExpress grids. Also this behavior is verifiable on two seperate application using different schemas ... well, that part is probably understandable since they are both being developed and updated by me.

Possible steps to repeat this:

Postgres:
        ALTER ROLE user1 SET search_path = schema1, schema2; -- Default schema is schema1

    schema1.table1
    schema1.table2

    schema2.table3
    schema2.table4

Delphi:
table3.Tablename := 'table3';

table3 is connected to a TcxGrid via a Datasource.

table3.Open;

Then use either GUI or code to edit/insert a record.

Post operation should fail with error 'relation "schema1.table3" does not exist'.

Sample project - just enter connection credentials and enter non-default schema tablename

DDL:
CREATE DATABASE "AsystSQLProd"
  WITH OWNER = "AsystSQL"
       ENCODING = 'UTF8'
       TABLESPACE = ts_asyst_data
       LC_COLLATE = 'C'
       LC_CTYPE = 'C'
       CONNECTION LIMIT = -1;

GRANT ALL ON DATABASE "AsystSQLProd" TO "AsystSQL";
GRANT CONNECT, TEMPORARY ON DATABASE "AsystSQLProd" TO "AsystSupportRole";
GRANT CONNECT, TEMPORARY ON DATABASE "AsystSQLProd" TO "AsystLicensingRole";
GRANT CONNECT, TEMPORARY ON DATABASE "AsystSQLProd" TO "AsystFitRole";
GRANT CONNECT, TEMPORARY ON DATABASE "AsystSQLProd" TO "AsystMatRole";
REVOKE ALL ON DATABASE "AsystSQLProd" FROM public;


CREATE SCHEMA fit AUTHORIZATION "AsystSQL";

GRANT ALL ON SCHEMA fit TO "AsystSQL";
GRANT ALL ON SCHEMA fit TO "AsystFitRole";
GRANT USAGE ON SCHEMA fit TO "AsystLicensingRole";
GRANT USAGE ON SCHEMA fit TO "AsystSupportRole";

CREATE SCHEMA mat AUTHORIZATION "AsystSQL";

GRANT ALL ON SCHEMA mat TO "AsystSQL";
GRANT ALL ON SCHEMA mat TO "AsystMatRole";


CREATE USER andrejg WITH
  LOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  VALID UNTIL 'infinity'

GRANT 
  "AsystFitRole", 
  "AsystMatRole", 
  "AsystSupportRole" TO andrejg;

ALTER USER andrejg SET search_path TO mat, sup, fit;

Answer 1

Place public before the rest of the schemes:

ALTER DATABASE DBName SET search_path TO public, schema1, schema2