Re: UUID generation problem - Mailing list pgsql-general

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@harte-lyne.ca> 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@aklaver.com