Re: UUID generation problem

On Mon, October 5, 2020 12:51, Tom Lane wrote:
> "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.

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"}
(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.

--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail

James B. Byrne mailto:ByrneJB(at)Harte-Lyne(dot)ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3