UUID generation problem

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
53 messages Options
123
Reply | Threaded
Open this post in threaded view
|

UUID generation problem

byrnejb
I have a FreeBSD-12.1p10 system running postgreSQL-11.8. This is a test
platform for Idempiere.  When creating a new client in Idempiere I get this
error message:

** org.postgresql.util.PSQLException: ERROR: function uuid_generate_v4() does
not exist Hint: No function matches the given name and argument types. You
might need to add explicit type casts. Where: PL/pgSQL function generate_uuid()
line 3 at RETURN

This usually means that the uuid-ossp extension is not active in the database.
However, I previously installed this and psql shows that it is present:

sudo -u postgres psql --dbname=idempiere
psql (11.8)
Type "help" for help.

idempiere=# \dx
                            List of installed extensions
   Name    | Version |   Schema   |                   Description
-----------+---------+------------+-------------------------------------------------
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
 uuid-ossp | 1.1     | public     | generate universally unique identifiers
(UUIDs)

The owner of the idempiere database is idempiere_dbadmin and this is the user
that is attempting to generate the UUID.

idempiere=# \l
                                      List of databases
        Name         |       Owner       | Encoding | Collate | Ctype |  
Access privileges
---------------------+-------------------+----------+---------+-------+-----------------------
 idempiere           | idempiere_dbadmin | UTF8     | C       | C     |

What is the problem?


--
***          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:[hidden email]
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



Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

Adrian Klaver-4
On 10/2/20 3:08 PM, James B. Byrne wrote:

> I have a FreeBSD-12.1p10 system running postgreSQL-11.8. This is a test
> platform for Idempiere.  When creating a new client in Idempiere I get this
> error message:
>
> ** org.postgresql.util.PSQLException: ERROR: function uuid_generate_v4() does
> not exist Hint: No function matches the given name and argument types. You
> might need to add explicit type casts. Where: PL/pgSQL function generate_uuid()
> line 3 at RETURN
>
> This usually means that the uuid-ossp extension is not active in the database.
> However, I previously installed this and psql shows that it is present:
>
> sudo -u postgres psql --dbname=idempiere
> psql (11.8)
> Type "help" for help.
>
> idempiere=# \dx
>                              List of installed extensions
>     Name    | Version |   Schema   |                   Description
> -----------+---------+------------+-------------------------------------------------
>   plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
>   uuid-ossp | 1.1     | public     | generate universally unique identifiers
> (UUIDs)
>
> The owner of the idempiere database is idempiere_dbadmin and this is the user
> that is attempting to generate the UUID.
>
> idempiere=# \l
>                                        List of databases
>          Name         |       Owner       | Encoding | Collate | Ctype |
> Access privileges
> ---------------------+-------------------+----------+---------+-------+-----------------------
>   idempiere           | idempiere_dbadmin | UTF8     | C       | C     |
>
> What is the problem?

Per comment over at SO, have you run uuid_generate_v4()/generate_uuid().
in psql to see if it is there and works?

>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

Tom Lane-2
In reply to this post by byrnejb
"James B. Byrne" <[hidden email]> writes:
> I have a FreeBSD-12.1p10 system running postgreSQL-11.8. This is a test
> platform for Idempiere.  When creating a new client in Idempiere I get this
> error message:

> ** org.postgresql.util.PSQLException: ERROR: function uuid_generate_v4() does
> not exist Hint: No function matches the given name and argument types. You
> might need to add explicit type casts. Where: PL/pgSQL function generate_uuid()
> line 3 at RETURN

> This usually means that the uuid-ossp extension is not active in the database.
> However, I previously installed this and psql shows that it is present:

The two likely possibilities are that Idempiere isn't connecting to the
same database as you are doing manually, or that it is using a search_path
setting that doesn't include the "public" schema.

I think you could also get this if the "public" schema is not readable
by Idempiere's userid, but that doesn't seem terribly likely.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

byrnejb
In reply to this post by Adrian Klaver-4


On Fri, October 2, 2020 18:14, Adrian Klaver wrote:

>
> Per comment over at SO, have you run uuid_generate_v4()/generate_uuid().
> in psql to see if it is there and works?
>

[root@accounting-2 ~ (master)]# sudo -u postgres psql --dbname=idempiere
--username=idempiere_dbadmin
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.

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 uuid_generate();
ERROR:  function uuid_generate() does not exist
LINE 1: select uuid_generate();
               ^
HINT:  No function matches the given name and argument types. You might need to
add explicit type casts.
idempiere=#


--
***          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:[hidden email]
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



Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

byrnejb
In reply to this post by Tom Lane-2


On Fri, October 2, 2020 18:46, Tom Lane wrote:

> "James B. Byrne" <[hidden email]> writes:
>> I have a FreeBSD-12.1p10 system running postgreSQL-11.8. This is a test
>> platform for Idempiere.  When creating a new client in Idempiere I get this
>> error message:
>
>> ** org.postgresql.util.PSQLException: ERROR: function uuid_generate_v4() does
>> not exist Hint: No function matches the given name and argument types. You
>> might need to add explicit type casts. Where: PL/pgSQL function
>> generate_uuid()
>> line 3 at RETURN
>
>> This usually means that the uuid-ossp extension is not active in the
>> database.
>> However, I previously installed this and psql shows that it is present:
>
> The two likely possibilities are that Idempiere isn't connecting to the
> same database as you are doing manually, or that it is using a search_path
> setting that doesn't include the "public" schema.
>
> I think you could also get this if the "public" schema is not readable
> by Idempiere's userid, but that doesn't seem terribly likely.
>
> regards, tom lane
>

idempiere=# \dn
    List of schemas
   Name    |   Owner
-----------+-----------
 adempiere | adempiere
 public    | postgres


--
***          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:[hidden email]
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



Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

Adrian Klaver-4
In reply to this post by byrnejb
On 10/2/20 5:08 PM, James B. Byrne wrote:

>
>
> On Fri, October 2, 2020 18:14, Adrian Klaver wrote:
>
>>
>> Per comment over at SO, have you run uuid_generate_v4()/generate_uuid().
>> in psql to see if it is there and works?
>>
>
> [root@accounting-2 ~ (master)]# sudo -u postgres psql --dbname=idempiere
> --username=idempiere_dbadmin
> Password for user idempiere_dbadmin:
> psql (11.8)
> Type "help" for help.
>
> 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 uuid_generate();
> ERROR:  function uuid_generate() does not exist
> LINE 1: select uuid_generate();
>                 ^
> HINT:  No function matches the given name and argument types. You might need to
> add explicit type casts.
> idempiere=#
>
>

So per Tom's post:

What does:

show search_path;

return?

Also what does:

select public.uuid_generate_v4();

do?



--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

Tom Lane-2
In reply to this post by byrnejb
"James B. Byrne" <[hidden email]> writes:
> On Fri, October 2, 2020 18:46, Tom Lane wrote:
>> The two likely possibilities are that Idempiere isn't connecting to the
>> same database as you are doing manually, or that it is using a search_path
>> setting that doesn't include the "public" schema.
>> I think you could also get this if the "public" schema is not readable
>> by Idempiere's userid, but that doesn't seem terribly likely.

> idempiere=# \dn
>     List of schemas
>    Name    |   Owner
> -----------+-----------
>  adempiere | adempiere
>  public    | postgres

Uh ... that rules out exactly none of those three possibilities.
"\dn" says what schemas exist, but it tells you nothing about
either search_path or privileges.

You could try "select current_schemas(true)" to narrow things
down a little bit, as that would show the active search path
in your session.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

byrnejb


On Fri, October 2, 2020 21:13, Tom Lane wrote:

> "James B. Byrne" <[hidden email]> writes:
>> On Fri, October 2, 2020 18:46, Tom Lane wrote:
>>> The two likely possibilities are that Idempiere isn't connecting to the
>>> same database as you are doing manually, or that it is using a search_path
>>> setting that doesn't include the "public" schema.
>>> I think you could also get this if the "public" schema is not readable
>>> by Idempiere's userid, but that doesn't seem terribly likely.
>
>> idempiere=# \dn
>>     List of schemas
>>    Name    |   Owner
>> -----------+-----------
>>  adempiere | adempiere
>>  public    | postgres
>
> Uh ... that rules out exactly none of those three possibilities.
> "\dn" says what schemas exist, but it tells you nothing about
> either search_path or privileges.
>
> You could try "select current_schemas(true)" to narrow things
> down a little bit, as that would show the active search path
> in your session.
>
> regards, tom lane
>

Sorry about that.  Still finding my way.  Anyway, I believe that I may have
found the problem.  When I created the test database I was following the
software installation guide. The user the guide refers to is 'adempiere' and
not 'idempiere_dbadmin'.  At some point I came at the task from a different pov
and created another user to be the database owner.  Cannot say why but it is
evident that is what I did.

I will resolve the conflict either by granting 'idempiere_dbadmin' the
necessary privileges or by changing the connection to use the 'adempiere' user
instead.

Thanks for the pointers.

Regards,

P.S.  If it turns out to be something else then I will return with more details.

--
***          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:[hidden email]
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



Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

Adrian Klaver-4
On 10/2/20 7:17 PM, James B. Byrne wrote:

>
>
> On Fri, October 2, 2020 21:13, Tom Lane wrote:
>> "James B. Byrne" <[hidden email]> writes:
>>> On Fri, October 2, 2020 18:46, Tom Lane wrote:
>>>> The two likely possibilities are that Idempiere isn't connecting to the
>>>> same database as you are doing manually, or that it is using a search_path
>>>> setting that doesn't include the "public" schema.
>>>> I think you could also get this if the "public" schema is not readable
>>>> by Idempiere's userid, but that doesn't seem terribly likely.
>>
>>> idempiere=# \dn
>>>      List of schemas
>>>     Name    |   Owner
>>> -----------+-----------
>>>   adempiere | adempiere
>>>   public    | postgres
>>
>> Uh ... that rules out exactly none of those three possibilities.
>> "\dn" says what schemas exist, but it tells you nothing about
>> either search_path or privileges.
>>
>> You could try "select current_schemas(true)" to narrow things
>> down a little bit, as that would show the active search path
>> in your session.
>>
>> regards, tom lane
>>
>
> Sorry about that.  Still finding my way.  Anyway, I believe that I may have
> found the problem.  When I created the test database I was following the
> software installation guide. The user the guide refers to is 'adempiere' and
> not 'idempiere_dbadmin'.  At some point I came at the task from a different pov
> and created another user to be the database owner.  Cannot say why but it is
> evident that is what I did.
>
> I will resolve the conflict either by granting 'idempiere_dbadmin' the
> necessary privileges or by changing the connection to use the 'adempiere' user
> instead.

I'm not sure that is going to help. You are not, AFAICT, getting any
permission denied messages.

What does:

\df+ uuid_generate_v4

show under Access privileges?

What does:

select current_schemas(true);

show?


>
> Thanks for the pointers.
>
> Regards,
>
> P.S.  If it turns out to be something else then I will return with more details.
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

Paul Förster
In reply to this post by byrnejb
Hi James,

> On 03. Oct, 2020, at 04:17, James B. Byrne <[hidden email]> wrote:
>
> On Fri, October 2, 2020 21:13, Tom Lane wrote:
>> "James B. Byrne" <[hidden email]> writes:
>>> On Fri, October 2, 2020 18:46, Tom Lane wrote:
>>
>>> idempiere=# \dn
>>>    List of schemas
>>>   Name    |   Owner
>>> -----------+-----------
>>> adempiere | adempiere
>>> public    | postgres

> I will resolve the conflict either by granting 'idempiere_dbadmin' the
> necessary privileges or by changing the connection to use the 'adempiere' user
> instead.

you can also rename roles/users:

    alter role adempiere_dbadmin rename to idempiere_dbadmin;

https://www.postgresql.org/docs/13/sql-alterrole.html

or schema:

    alter schema adempiere rename to idempiere;

https://www.postgresql.org/docs/13/sql-alterschema.html

Cheers,
Paul

Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

byrnejb
In reply to this post by Adrian Klaver-4


On Sat, October 3, 2020 00:28, Adrian Klaver wrote:

>
> I'm not sure that is going to help. You are not, AFAICT, getting any
> permission denied messages.
>
> What does:
>
> \df+ uuid_generate_v4
>
> show under Access privileges?
>
> What does:
>
> select current_schemas(true);
>
> show?
>
>

idempiere=# \df+ uuid_generate_v4
                                                                                    List
of
functions
 Schema |       Name       | Result data type | Argument data types | Type |
Volatility | Parallel |  Owner   | Security | Access privileges | Language |  
Source code    | Description
--------+------------------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+------------------+-------------
 public | uuid_generate_v4 | uuid             |                     | func |
volatile   | safe     | postgres | invoker  |                   | c        |
uuid_generate_v4 |
(1 row)



idempiere=# select current_schemas(true);
   current_schemas
---------------------
 {pg_catalog,public}
(1 row


--
***          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:[hidden email]
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



Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

Adrian Klaver-4
On 10/5/20 6:18 AM, James B. Byrne wrote:

>
>
> On Sat, October 3, 2020 00:28, Adrian Klaver wrote:
>>
>> I'm not sure that is going to help. You are not, AFAICT, getting any
>> permission denied messages.
>>
>> What does:
>>
>> \df+ uuid_generate_v4
>>
>> show under Access privileges?
>>
>> What does:
>>
>> select current_schemas(true);
>>
>> show?
>>
>>
>
> idempiere=# \df+ uuid_generate_v4
>                                                                                      List
> of
> functions
>   Schema |       Name       | Result data type | Argument data types | Type |
> Volatility | Parallel |  Owner   | Security | Access privileges | Language |
> Source code    | Description
> --------+------------------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+------------------+-------------
>   public | uuid_generate_v4 | uuid             |                     | func |
> volatile   | safe     | postgres | invoker  |                   | c        |
> uuid_generate_v4 |
> (1 row)
>
>
>
> idempiere=# select current_schemas(true);
>     current_schemas
> ---------------------
>   {pg_catalog,public}
> (1 row
>
>

So as same user:

select uuid_generate_v4();

select public.uuid_generate_v4();

\dn+ public

--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

byrnejb


On Mon, October 5, 2020 10:18, Adrian Klaver wrote:
> So as same user:
>
> select uuid_generate_v4();
>
> select public.uuid_generate_v4();
>
> \dn+ public
>

[root@accounting-2 ~ (master)]# sudo -u postgres psql --dbname=idempiere
--username=idempiere_dbadmin
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.

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
--------------------------------------
 87f70b3b-4dc5-4775-b8f7-b7a351e7b97b
(1 row)

idempiere=# \dn+ public
                              List of schemas
  Name  |  Owner   |      Access privileges       |      Description
--------+----------+------------------------------+------------------------
 public | postgres | postgres=UC/postgres        +| standard public schema
        |          | =UC/postgres                +|
        |

--
***          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:[hidden email]
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



Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

Tom Lane-2
"James B. Byrne" <[hidden email]> writes:

> 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
> --------------------------------------
>  87f70b3b-4dc5-4775-b8f7-b7a351e7b97b
> (1 row)

That seems to show quite definitively that public is not in your
search_path, which contradicts the current_schemas() result you
gave earlier.  I continue to suspect that you are somehow
confusing yourself by testing in different databases and/or
with different user accounts.

Possibly connected to this is that the "#" mark in your psql
prompt implies that you are running as superuser.  I sure
hope you are not letting your application do that.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

Adrian Klaver-4
In reply to this post by byrnejb
On 10/5/20 7:22 AM, James B. Byrne wrote:

>
>
> On Mon, October 5, 2020 10:18, Adrian Klaver wrote:
>> So as same user:
>>
>> select uuid_generate_v4();
>>
>> select public.uuid_generate_v4();
>>
>> \dn+ public
>>
>
> [root@accounting-2 ~ (master)]# sudo -u postgres psql --dbname=idempiere
> --username=idempiere_dbadmin
> Password for user idempiere_dbadmin:
> psql (11.8)
> Type "help" for help.
>
> 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
> --------------------------------------
>   87f70b3b-4dc5-4775-b8f7-b7a351e7b97b
> (1 row)
>
> idempiere=# \dn+ public
>                                List of schemas
>    Name  |  Owner   |      Access privileges       |      Description
> --------+----------+------------------------------+------------------------
>   public | postgres | postgres=UC/postgres        +| standard public schema
>          |          | =UC/postgres                +|
>          |
>

Per Tom's post this does not make sense.

What if you connect doing?:

psql --dbname=idempiere --username=idempiere_dbadmin

And specify the port(-p)

Cut out the sudo.


Then do:

select current_schemas(true);

select uuid_generate_v4();

select public.uuid_generate_v4();

--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

byrnejb
In reply to this post by Tom Lane-2


On Mon, October 5, 2020 10:53, Tom Lane wrote:

> That seems to show quite definitively that public is not in your
> search_path, which contradicts the current_schemas() result you
> gave earlier.  I continue to suspect that you are somehow
> confusing yourself by testing in different databases and/or
> with different user accounts.

Possibly.  I do not make any definitive claims at this point.  However, the
application properties of the DB connection are:

Connection=xyzCConnection[name\=accounting.harte-lyne.ca
{localhost-idempiere-idempiere_dbadmin},
AppsHost\=accounting.harte-lyne.ca,
WebPort\=8080,
SSLPort\=8443,
type\=PostgreSQL,
DBhost\=localhost,
DBport\=5432,
DBname\=idempiere,
BQ\=false,
FW\=false,
FWhost\=,
FWport\=0,
UID\=idempiere_dbadmin,
PWD\=xxxxxxxxxxxxxxxx]

This indicates that the DB name is idempiere and that the DB connection user
name is idempiere_dbadmin.

psql shows this:

[root@accounting-2 ~ (master)]# psql --dbname=idempiere
--username=idempiere_dbadmin
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.

idempiere=# \l
                                      List of databases
        Name         |       Owner       | Encoding | Collate | Ctype |  
Access privileges
---------------------+-------------------+----------+---------+-------+-----------------------
 idempiere           | idempiere_dbadmin | UTF8     | C       | C     |
 lsmb_access_test_db | postgres          | UTF8     | C       | C     |
 postgres            | postgres          | UTF8     | C       | C     |
 template0           | postgres          | UTF8     | C       | C     |
=c/postgres          +
                     |                   |          |         |       |
postgres=CTc/postgres
 template1           | postgres          | UTF8     | C       | C     |
=c/postgres          +
                     |                   |          |         |       |
postgres=CTc/postgres
 testcompany         | lsmb_dbadmin      | UTF8     | C       | C     |
(6 rows)

The username idempiere_dbadmin does not exist in /etc/passwd.

idempiere=# \dx
                            List of installed extensions
   Name    | Version |   Schema   |                   Description
-----------+---------+------------+-------------------------------------------------
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
 uuid-ossp | 1.1     | public     | generate universally unique identifiers
(UUIDs)
(2 rows)

idempiere=# \dx+
      Objects in extension "plpgsql"
            Object description
-------------------------------------------
 function plpgsql_call_handler()
 function plpgsql_inline_handler(internal)
 function plpgsql_validator(oid)
 language plpgsql
(4 rows)

      Objects in extension "uuid-ossp"
             Object description
---------------------------------------------
 function public.uuid_generate_v1()
 function public.uuid_generate_v1mc()
 function public.uuid_generate_v3(uuid,text)
 function public.uuid_generate_v4()
 function public.uuid_generate_v5(uuid,text)
 function public.uuid_nil()
 function public.uuid_ns_dns()
 function public.uuid_ns_oid()
 function public.uuid_ns_url()
 function public.uuid_ns_x500()
(10 rows)

The idempiere database was created before I added the uuid-ossp extension. That
possibly bears on this matter.


--
***          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:[hidden email]
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



Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

Adrian Klaver-4
On 10/5/20 8:43 AM, James B. Byrne wrote:

>
>
> On Mon, October 5, 2020 10:53, Tom Lane wrote:
>
>> That seems to show quite definitively that public is not in your
>> search_path, which contradicts the current_schemas() result you
>> gave earlier.  I continue to suspect that you are somehow
>> confusing yourself by testing in different databases and/or
>> with different user accounts.
>
> Possibly.  I do not make any definitive claims at this point.  However, the
> application properties of the DB connection are:
>
> Connection=xyzCConnection[name\=accounting.harte-lyne.ca
> {localhost-idempiere-idempiere_dbadmin},
> AppsHost\=accounting.harte-lyne.ca,
> WebPort\=8080,
> SSLPort\=8443,
> type\=PostgreSQL,
> DBhost\=localhost,
> DBport\=5432,
> DBname\=idempiere,
> BQ\=false,
> FW\=false,
> FWhost\=,
> FWport\=0,
> UID\=idempiere_dbadmin,
> PWD\=xxxxxxxxxxxxxxxx]
>
> This indicates that the DB name is idempiere and that the DB connection user
> name is idempiere_dbadmin.
>
> psql shows this:
>
> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
> --username=idempiere_dbadmin
> Password for user idempiere_dbadmin:
> psql (11.8)
> Type "help" for help.
>
> idempiere=# \l
>                                        List of databases
>          Name         |       Owner       | Encoding | Collate | Ctype |
> Access privileges
> ---------------------+-------------------+----------+---------+-------+-----------------------
>   idempiere           | idempiere_dbadmin | UTF8     | C       | C     |
>   lsmb_access_test_db | postgres          | UTF8     | C       | C     |
>   postgres            | postgres          | UTF8     | C       | C     |
>   template0           | postgres          | UTF8     | C       | C     |
> =c/postgres          +
>                       |                   |          |         |       |
> postgres=CTc/postgres
>   template1           | postgres          | UTF8     | C       | C     |
> =c/postgres          +
>                       |                   |          |         |       |
> postgres=CTc/postgres
>   testcompany         | lsmb_dbadmin      | UTF8     | C       | C     |
> (6 rows)
>
> The username idempiere_dbadmin does not exist in /etc/passwd.

It would not unless it was an OS/system user also. It is just a Postgres
database user name(role).

>
> idempiere=# \dx
>                              List of installed extensions
>     Name    | Version |   Schema   |                   Description
> -----------+---------+------------+-------------------------------------------------
>   plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
>   uuid-ossp | 1.1     | public     | generate universally unique identifiers
> (UUIDs)

Your previous posts show uuid-ossp and its functions installed
somewhere. The issue is that the functions are not being found unless
they are schema qualified and that has to do with search_path.

>
> The idempiere database was created before I added the uuid-ossp extension. That
> possibly bears on this matter.

That is the natural order of events. The database has to exist before
you can add an extension to it. Unless you are saying that you did not
build the extension until after the database was created.



--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

byrnejb
In reply to this post by Adrian Klaver-4


On Mon, October 5, 2020 11:15, Adrian Klaver wrote:

>
> Per Tom's post this does not make sense.
>
> What if you connect doing?:
>
> psql --dbname=idempiere --username=idempiere_dbadmin
>
> And specify the port(-p)
>
> Cut out the sudo.
>
>
> Then do:
>
> select current_schemas(true);
>
> select uuid_generate_v4();
>
> select public.uuid_generate_v4();
>

[root@accounting-2 ~ (master)]# psql --dbname=idempiere
--username=idempiere_dbadmin
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
--------------------------------------
 066e3298-3c91-4079-98ee-2b279bfc4025
(1 row)

--
***          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:[hidden email]
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



Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

Tom Lane-2
In reply to this post by byrnejb
"James B. Byrne" <[hidden email]> writes:
> On Mon, October 5, 2020 10:53, Tom Lane wrote:
>> I continue to suspect that you are somehow
>> confusing yourself by testing in different databases and/or
>> with different user accounts.

> Possibly.  I do not make any definitive claims at this point.  However, the
> application properties of the DB connection are:

> Connection=xyzCConnection[name\=accounting.harte-lyne.ca
> {localhost-idempiere-idempiere_dbadmin},
> AppsHost\=accounting.harte-lyne.ca,
> WebPort\=8080,
> SSLPort\=8443,
> type\=PostgreSQL,
> DBhost\=localhost,
> DBport\=5432,
> DBname\=idempiere,
> BQ\=false,
> FW\=false,
> FWhost\=,
> FWport\=0,
> UID\=idempiere_dbadmin,
> PWD\=xxxxxxxxxxxxxxxx]

Hm, is "UID" really how they spell "database user name"?  Doesn't seem
terribly consistent with the other field names you show here.

> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
> --username=idempiere_dbadmin

psql, by default, is going to try to connect to a Unix socket.
I wonder if you've got two postmasters on that machine and the
other one is what's answering TCP port 5432.  You might try
explicitly saying "--host=localhost" here.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: UUID generation problem

Adrian Klaver-4
In reply to this post by byrnejb
On 10/5/20 8:57 AM, James B. Byrne wrote:

>
>
> On Mon, October 5, 2020 11:15, Adrian Klaver wrote:
>>
>> Per Tom's post this does not make sense.
>>
>> What if you connect doing?:
>>
>> psql --dbname=idempiere --username=idempiere_dbadmin
>>
>> And specify the port(-p)
>>
>> Cut out the sudo.
>>
>>
>> Then do:
>>
>> select current_schemas(true);
>>
>> select uuid_generate_v4();
>>
>> select public.uuid_generate_v4();
>>
>
> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
> --username=idempiere_dbadmin
> Password for user idempiere_dbadmin:
> psql (11.8)
> Type "help" for help.
>
> idempiere=# select current_schemas(true);
>      current_schemas
> ------------------------
>   {adempiere,pg_catalog}

Well there is your problem. That is a different search_path and it does
not include the 'public' schema. This would indicate you are connecting
to a different instance of Postgres then in your previous example. I'm
going to bet you are connecting to different ports. I use the following
in my .psqlrc(local psql conf file):

\set PROMPT1 '%/%R%# '

to get the database name and port. I would try that at least
temporarily(at psql prompt) to sort out what/where you are connecting to.



> (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
> --------------------------------------
>   066e3298-3c91-4079-98ee-2b279bfc4025
> (1 row)
>


--
Adrian Klaver
[hidden email]


123