On 10/5/20 9:59 AM, James B. Byrne wrote:
On Mon, October 5, 2020 12:51, Tom Lane wrote:
"James B. Byrne" <byrnejb@xxxxxxxxxxxxx> writes:
[root@accounting-2 ~ (master)]# psql --dbname=idempiere
--username=idempiere_dbadmin --host=localhost
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.
idempiere=# select current_schemas(true);
current_schemas
------------------------
{adempiere,pg_catalog}
(1 row)
idempiere=# select uuid_generate_v4();
ERROR: function uuid_generate_v4() does not exist
LINE 1: select uuid_generate_v4();
^
HINT: No function matches the given name and argument types. You might need
to
add explicit type casts.
idempiere=# select public.uuid_generate_v4();
uuid_generate_v4
--------------------------------------
5ba19b69-ec8e-4d8e-8968-7c84eccc4351
(1 row)
Well, at least here we have consistent results: "public" is not in
your search_path. (Presumably, "show search_path" would confirm
that.) The question is what did you do differently before that
led to the other current_schemas result? If the *only* difference
is whether you use --host=localhost or not, it's hard to conclude
anything but that you're connecting to two different databases.
I don't quite see how that could be, with only one postmaster on
the machine, but maybe it's time to wonder about rogue connection
poolers or the like.
specifying the connection host does not change the observed behaviours.
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"}
To confirm what role this is assigned to do:
select rolname from pg_authid where oid = 21328;
(1 row)
Another line of thought is maybe you have a ~/.psqlrc that's
altering the search_path setting.
Up until 5 minutes ago I did not have a ~/.psqlrc file. And there is no system
psqlrc file.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
