"James B. Byrne" <byrnejb@harte-lyne.ca> writes:
>> It might be worth poking into the pg_db_role_setting catalog,
>> which is the most likely source of a different search_path for
>> different connections.
> It seems so:
> idempiere=# SELECT * FROM pg_db_role_setting;
> setdatabase | setrole | setconfig
> -------------+---------+---------------------------------------
> 0 | 21328 | {"search_path=adempiere, pg_catalog"}
> (1 row)
Ah hah! That explains a good deal: if you connect as role 21328
(whichever that is; do "select 21328::regrole" to confirm), then
your search_path will get changed at the moment of connection.
If you initially connect as some other role, the search_path
stays at default, even if you later SET ROLE to that role.
Probably if you work through what you did, this is enough to
explain all the discrepancies.
To fix, use ALTER ROLE SET or ALTER ROLE RESET to change or drop
this setting. If you keep it, I'd advise "adempiere, public",
allowing the pg_catalog reference to be implicitly first.
Putting user schemas in front of pg_catalog is generally
hazardous from a security standpoint.
BTW, you might also be well advised to include "IN DATABASE",
to restrict the effects to databases in which
the adempiere schema actually exists.
regards, tom lane
