Re: UUID generation problem

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

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(dot)klaver(at)aklaver(dot)com