UUID generation problem

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
adrian.klaver@aklaver.com

"James B. Byrne" <byrnejb@harte-lyne.ca> 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

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: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

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

"James B. Byrne" <byrnejb@harte-lyne.ca> 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: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

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
adrian.klaver@aklaver.com

"James B. Byrne" <byrnejb@harte-lyne.ca> 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

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

"James B. Byrne" <byrnejb@harte-lyne.ca> 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: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

On 10/2/20 7:17 PM, James B. Byrne wrote:

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

"James B. Byrne" <byrnejb@harte-lyne.ca> 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
adrian.klaver@aklaver.com

Hi James,

On 03. Oct, 2020, at 04:17, James B. Byrne <byrnejb@harte-lyne.ca> wrote:

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

"James B. Byrne" <byrnejb@harte-lyne.ca> 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

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: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

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
adrian.klaver@aklaver.com

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: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

"James B. Byrne" <byrnejb@harte-lyne.ca> 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

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
adrian.klaver@aklaver.com

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: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

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
adrian.klaver@aklaver.com

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: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

"James B. Byrne" <byrnejb@harte-lyne.ca> 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

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
adrian.klaver@aklaver.com