Re: UUID generation problem

"James B. Byrne" <byrnejb(at)harte-lyne(dot)ca> writes:
> [root(at)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.

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.

Another line of thought is maybe you have a ~/.psqlrc that's
altering the search_path setting.

regards, tom lane