Re: UUID generation problem - Mailing list pgsql-general

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