UUID generation problem
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. Byrnemailto:byrn...@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
Re: UUID generation problem
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. Byrnemailto:byrn...@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
Re: UUID generation problem
On Fri, October 2, 2020 18:46, Tom Lane wrote: > "James B. Byrne" 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. Byrnemailto:byrn...@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
Re: UUID generation problem
On Fri, October 2, 2020 21:13, Tom Lane wrote: > "James B. Byrne" 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. Byrnemailto:byrn...@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
Re: UUID generation problem
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. Byrnemailto:byrn...@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
Re: UUID generation problem
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. Byrnemailto:byrn...@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
Re: UUID generation problem
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\=]
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. Byrnemailto:byrn...@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
Re: UUID generation problem
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. Byrnemailto:byrn...@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
Re: UUID generation problem
On Mon, October 5, 2020 11:52, Adrian Klaver wrote: > 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. > That is the meaning that I meant to convey. The a rough outline of the sequent of events respecting this was: Install postgreqsl Initialise database su - postgres -c 'createuser -S -d -r -l -P adempiere' Run install script which creates the application database. The install script(s) required a number of iterations and some modifications to get working on FreeBSD. su idempiere -c" psql -d idempiere -U adempiere -c 'CREATE EXTENSION "uuid-ossp"' At some point I decided to switch the name of the user owning the application database from adempiere to idempiere-dbadmin. Probably this was done to align the user names with our internal conventions. A decision which I strongly suspect is at the root of this problem. Following this decision I went through the entire modified install scripts again, this time using the new username(s). However, I did discover this: 2000 2020-09-11 16:15:17: psql -d idempiere -U adempiere -c 'CREATE EXTENSION "uuid-ossp"' 2001 2020-09-11 16:17:08: su idempiere -c" psql -d idempiere -U adempiere -c 'CREATE EXTENSION "uuid-ossp" I can only conjecture that I got confused at this point in the process and used the admpiere username artifact from the initial install attempts instead of the idempiere-admin username subsequently employed. I realise that I am providing this information in a rather disjointed manner. However the number of modifications and restarts I had to perform to get the software to install rather confuses my memory and the history logs do not add much in the way of clarity. What I need to know now is how to correct this error. -- *** 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. Byrnemailto:byrn...@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
Re: UUID generation problem
On Mon, October 5, 2020 12:06, Tom Lane wrote:
> "James B. Byrne" 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\=]
>
> Hm, is "UID" really how they spell "database user name"? Doesn't seem
> terribly consistent with the other field names you show here.
That is extracted directly from the properties file created by the install
process, excepting that the password has been edited.
>
>> [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. . .
I see only one master process
[root@accounting-2 ~ (master)]# ps -auwx | grep postgres
postgres 19108 0.0 0.2 177972 30540 - IsJ 11:580:00.01 postgres:
idempiere_dbadmin idempiere 127.0.88.1(52228) (postgres)
postgres 19109 0.0 0.2 178608 36876 - IsJ 11:580:00.03 postgres:
idempiere_dbadmin idempiere 127.0.88.1(52229) (postgres)
postgres 19110 0.0 0.3 182712 51688 - IsJ 11:580:00.09 postgres:
idempiere_dbadmin idempiere 127.0.88.1(52230) (postgres)
postgres 20028 0.0 0.2 178676 37624 - IsJ 12:180:00.03 postgres:
idempiere_dbadmin idempiere 127.0.88.1(22758) (postgres)
postgres 20029 0.0 0.2 178728 36784 - IsJ 12:180:00.03 postgres:
idempiere_dbadmin idempiere 127.0.88.1(22759) (postgres)
postgres 68949 0.0 0.2 175504 34656 - IsJ Fri180:00.85 postgres:
checkpointer(postgres)
postgres 68950 0.0 0.1 175468 23420 - SsJ Fri180:00.82 postgres:
background writer(postgres)
postgres 68951 0.0 0.1 175468 23416 - SsJ Fri180:01.48 postgres:
walwriter(postgres)
postgres 68952 0.0 0.1 175800 23800 - SsJ Fri180:04.95 postgres:
autovacuum launcher(postgres)
postgres 68953 0.0 0.1 32012 14076 - SsJ Fri180:18.87 postgres:
stats collector(postgres)
postgres 68954 0.0 0.1 175644 23644 - IsJ Fri180:00.08 postgres:
logical replication launcher(postgres)
root 20522 0.0 0.0 113482216 2 S+J 12:320:00.00 grep
--color=auto postgres
postgres 68947 0.0 0.1 175468 23408 2 SJ Fri180:19.13
/usr/local/bin/postgres -D /var/db/postgres/data11
[root@accounting-2 ~ (master)]# service postgresql status
pg_ctl: server is running (PID: 68947)
/usr/local/bin/postgres "-D" "/var/db/postgres/data11"
>. . . You might try
> explicitly saying "--host=localhost" here.
>
> regards, tom lane
>
[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)
--
*** 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. Byrnemailto:byrn...@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
Re: UUID generation problem
On Mon, October 5, 2020 12:08, Adrian Klaver wrote:
> 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.
>
[root@accounting-2 ~ (master)]# cat .psqlrc
\set PROMPT1 '%/%R%# '
[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=# \set
. . .
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
. . .
VERSION_NAME = '11.8'
VERSION_NUM = '110008'
idempiere=#
idempiere=#
idempiere=#
The setting does not appear to alter the actual prompt.
--
*** 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. Byrnemailto:byrn...@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
Re: UUID generation problem
On Mon, October 5, 2020 12:12, Adrian Klaver wrote: > > Also I would do at system command line: > > ps ax | grep post > > to see how many instances of Postgres you have running. > [root@accounting-2 ~ (master)]# ps ax | grep post 20028 - IsJ 0:00.04 postgres: idempiere_dbadmin idempiere 127.0.88.1(22758) (postgres) 20029 - IsJ 0:00.06 postgres: idempiere_dbadmin idempiere 127.0.88.1(22759) (postgres) 20754 - IsJ 0:00.00 postgres: idempiere_dbadmin idempiere 127.0.88.1(35753) (postgres) 20755 - IsJ 0:00.03 postgres: idempiere_dbadmin idempiere 127.0.88.1(35754) (postgres) 20756 - IsJ 0:00.03 postgres: idempiere_dbadmin idempiere 127.0.88.1(35755) (postgres) 68949 - IsJ 0:00.85 postgres: checkpointer(postgres) 68950 - SsJ 0:00.82 postgres: background writer(postgres) 68951 - SsJ 0:01.49 postgres: walwriter(postgres) 68952 - SsJ 0:04.96 postgres: autovacuum launcher(postgres) 68953 - SsJ 0:18.94 postgres: stats collector(postgres) 68954 - IsJ 0:00.08 postgres: logical replication launcher(postgres) 21085 2 S+J 0:00.00 grep --color=auto post 68947 2 SJ0:19.21 /usr/local/bin/postgres -D /var/db/postgres/data11 -- *** 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. Byrnemailto:byrn...@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
Re: UUID generation problem
On Mon, October 5, 2020 12:19, Paul Förster wrote:
>
> just out of curiosity, what does the search_path contain? It needs not
> necessarily reflect the contents of current_schemas, see the following
> example:
>
> postgres=# select current_schemas(true);
>current_schemas
> --
> {pg_catalog,postgres,public}
> (1 row)
>
> postgres=# select current_schemas(false);
> current_schemas
> ---
> {postgres,public}
> (1 row)
>
> postgres=# show search_path;
>search_path
> -
> "$user", public
> (1 row)
>
> Cheers,
> Paul
[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 current_schemas(false);
current_schemas
{adempiere,pg_catalog}
(1 row)
idempiere=# show search_path;
search_path
---
adempiere, pg_catalog
(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. Byrnemailto:byrn...@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
Re: UUID generation problem
On Mon, October 5, 2020 12:51, Tom Lane wrote:
> "James B. Byrne" 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. Byrnemailto:byrn...@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
Re: UUID generation problem
On Mon, October 5, 2020 13:07, Paul Förster wrote: > > I guess that's why you don't see the uuid_generate_v4() function. I suggest > you > either fully qualify it, i.e. public.uuid_generate_v4() or add public to your > search path. As this is an application package it is not within my purview to alter the code. To do so would rik a return of the problem with every update. Adding public to the search path is fine by me. However, I still need to find out how this situation arose. Is it something I did or something that the installer does by default? The project notes 'suggest' 'adempiere' as the username for access. However they do not use wording to imply that one must use it. However, if there is something in the installer that uses 'adempiere' regardless of the properties settings then I need to discover this. -- *** 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. Byrnemailto:byrn...@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
Re: UUID generation problem
On Mon, October 5, 2020 13:24, Adrian Klaver wrote: > > To confirm what role this is assigned to do: > > select rolname from pg_authid where oid = 21328; > idempiere=# select rolname from pg_authid where oid = 21328; rolname --- idempiere_dbadmin (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. Byrnemailto:byrn...@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
Re: UUID generation problem
On Mon, October 5, 2020 13:31, Adrian Klaver wrote: > > That is because I sent the wrong prompt:( It should have been: > > \set PROMPT1 '%/(%>)%R%# ' > idempiere=# \set PROMPT1 '%/(%>)%R%# ' idempiere(5432)=# That works. -- *** 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. Byrnemailto:byrn...@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
Re: UUID generation problem
On Mon, October 5, 2020 13:34, Paul Förster wrote:
>
> well, actually, you can just set the search_path for the role the application
> logs in with:
>
> alter role set search_path = ', pg_catalog, public';
>
> The next time logs in, it should see the freshly set search_path.
>
> When we create an app schema and role set in our databases, we always do this
> to make sure that the application role always finds its schema. We never had
> any problems with this.
>
> https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
> https://www.postgresql.org/docs/current/sql-alterrole.html
>
> Hope this helps,
> Paul
idempiere(5432)=# select schema_name
idempiere-# from information_schema.schemata;
schema_name
public
information_schema
pg_catalog
pg_toast_temp_1
pg_temp_1
pg_toast
adempiere
(7 rows)
Do I infer from this that the application installer adds a schema called
adempiere; regardless of what username I choose for the database owner? Or did
I cause this selection of schema name through some explicit action of my own?
I also infer that this can be corrected in the manner suggested by issuing:
alter role idempiere-dbadmin set search_path = 'adempiere, pg_catalog, public';
Is this correct?
When I created the user adempiere I used this:
su - postgres -c 'createuser -S -d -r -l -P adempiere'
Now, man createuser says this:
. . .
-S
--no-superuser
The new user will not be a superuser. This is the default.
. . .
But, psql says this (somewhat compressed):
idempiere(5432)=# \du
List of roles
Role name | Attributes| Member of
adempiere | Superuser | {}
. . .
So, as I specified -S (--no-superuser when creating this user how comes it that
the adempiere username does, in fact, have the Superuser privilege? This is
not something that I am conscious of having granted. For that matter, I first
would have to research the exact command syntax to carry it out.
Something in the installer must be doing something to the adempiere role, but I
cannot find where.
--
*** 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. Byrnemailto:byrn...@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
Re: UUID generation problem
On Mon, October 5, 2020 13:46, Adrian Klaver wrote: > The thing is, from upstream: > > 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) > > This indicates that you created the extension as user 'postgres'. > Why would I not use the postgres user to create an extension? -- *** 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. Byrnemailto:byrn...@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
Re: UUID generation problem
On Mon, October 5, 2020 15:20, Adrian Klaver wrote: > > The uuid-extension needs to be installed by a super user: > . . . > > So postgres is probably as good as any. > > I was just pointing out that what you posted earlier: > > 2000 2020-09-11 16:15:17: psql -d idempiere -U adempiere -c 'CREATE > EXTENSION > "uuid-ossp"' > > 2001 2020-09-11 16:17:08: su idempiere -c" psql -d idempiere -U > adempiere -c > 'CREATE EXTENSION "uuid-ossp" > > > does not match what you showed for the actual installed code: > > > 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 | > > NOTE: Owner = postgres. > > > Ah, I copied the wrong line from the history display. I am so confused by this. I tried to do this: [root@accounting-2 ~ (master)]# psql -E --dbname=idempiere --username=idempiere_dbadmin --host=localhost Password for user idempiere_dbadmin: psql (11.8) Type "help" for help. idempiere(5432)=# alter role "idempiere-dbadmin" set search_path = 'adempiere, pg_catalog, public'; ERROR: role "idempiere-dbadmin" does not exist idempiere(5432)=# If the role idempiere-dbadmin does not exist then how did I connect to the idempiere database? -- *** 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. Byrnemailto:byrn...@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
Re: UUID generation problem
On Mon, October 5, 2020 15:35, David G. Johnston wrote:
> On Monday, October 5, 2020, James B. Byrne wrote:
>
>>
>>
>> I am so confused by this. I tried to do this:
>>
>> [root@accounting-2 ~ (master)]# psql -E --dbname=idempiere
>> --username=idempiere_dbadmin --host=localhost
>> Password for user idempiere_dbadmin:
>> psql (11.8)
>> Type "help" for help.
>>
>> idempiere(5432)=# alter role "idempiere-dbadmin" set search_path =
>> 'adempiere,
>> pg_catalog, public';
>> ERROR: role "idempiere-dbadmin" does not exist
>> idempiere(5432)=#
>>
>> If the role idempiere-dbadmin does not exist then how did I connect to the
>> idempiere database?
>>
>
> Underscore versus hyphen.
>
> David J.
>
Sigh.
--
*** 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. Byrnemailto:byrn...@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
Re: UUID generation problem
[root@accounting-2 ~ (master)]# psql -E --dbname=idempiere
--username=idempiere_dbadmin --host=localhost
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.
idempiere(5432)=# select current_schemas(true);
current_schemas
-
{pg_catalog}
(1 row)
idempiere(5432)=# alter role "idempiere_dbadmin" set search_path = 'adempiere,
public';
ALTER ROLE
idempiere(5432)=# select current_schemas(true);
current_schemas
-
{pg_catalog}
(1 row)
This does not look like the ALTER statement had any effect. Am I missing a
step?
--
*** 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. Byrnemailto:byrn...@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
[SOLVED] Re: UUID generation problem
Thank you all for the help. This is what ultimate resolved the issue for me:
[root@accounting-2 ~ (master)]# psql -E --dbname=idempiere --username=postgres
--host=localhost
Password for user postgres:
psql (11.8)
Type "help" for help.
idempiere(5432)=# select current_schemas(true);
current_schemas
-
{pg_catalog,public}
(1 row)
idempiere(5432)=# ALTER ROLE idempiere_dbadmin SET search_path TO
adempiere,public;
ALTER ROLE
idempiere(5432)=# \q
[root@accounting-2 ~ (master)]# psql -E --dbname=idempiere
--username=idempiere_dbadmin --host=localhost
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.
idempiere(5432)=# select current_schemas(true);
current_schemas
---
{pg_catalog,adempiere,public}
--
*** 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. Byrnemailto:byrn...@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
character datatype explaination sought
I am testing Idempiere and have run across this in an example: character(1) DEFAULT 'Y'::bpchar NOT NULL, How does this differ from character(1) DEFAULT 'Y' NOT NULL, -- *** 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. Byrnemailto:byrn...@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
interval data type
What is the difference between interval(3)[] and simply interval(3)? Where in the documentation is the [] syntax discussed? This question arises only because I am trying to map an Informix database column defined as lead_time INTERVAL DAY(3) TO DAY to an equivalent PostgreSQL datatype. I got this to work with: ADD COLUMN lead_time interval day; and also with: ADD COLUMN lead_time interval(3); but I do not understand what these mean frankly. Does the form 'interval(3) imply a field value of SECOND? As a side note: The use of of the word 'fields' in the manual discussion regarding interval is, to me, misleading in that only one 'field' value is permitted. "The interval type has an additional option, which is to restrict the set of stored fields by writing one of these phrases: . . . Note that if both fields and p are specified, the fields must include SECOND, since the precision applies only to the seconds." The manual says 'one of these phrases'; but then uses the word "fields" together with the word "includes" and thereby implies that multiple selections are permitted. Given the brevity of the exposition this initially confused me. Are there other types of 'fields' that may be used with interval that are not given? I could not find a definition of 'sectored fields' in the manual. What is its meaning? Also I do not understand under what circumstance one would use the interval type in place of a simple integer. If someone would be kind enough to explain these things to me I would be appreciative. Regards, -- *** 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. Byrnemailto:byrn...@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
Need another set of eyes on this
I am recreating a test database with data provided for a series of future exercises. Presently I am trying to understand why a simple insert statement is not working. The user invoking this insert is 'postgres' and has superuser privileges. The error is this: postgres : STATEMENT: INSERT INTO public.stock( stock_num, manu_code, description, unit_price, unit, unit_descr) VALUES (3, "SHM", "aluminum Bat", 180.00, "case", "12/case"); postgres : ERROR: 42703: column "SHM" does not exist at character 105 postgres : LOCATION: errorMissingColumn, parse_relation.c:3194 postgres : STATEMENT: INSERT INTO public.stock( stock_num, manu_code, description, unit_price, unit, unit_descr) VALUES (3, "SHM", "aluminum bat", 180.00, "case", "12/case"); The create script for this table is this: -- Table: public.stock -- DROP TABLE public.stock; CREATE TABLE public.stock ( stock_num smallint NOT NULL, manu_code character(3) COLLATE pg_catalog."default", description character(20) COLLATE pg_catalog."default", unit_price money, unit character(4) COLLATE pg_catalog."default", unit_descr character(15) COLLATE pg_catalog."default" ) WITH ( OIDS = FALSE ) TABLESPACE pg_default; ALTER TABLE public.stock OWNER to hll_4gl_testing; COMMENT ON TABLE public.stock IS 'The stock table is a catalog of the items sold by the distributor.'; COMMENT ON COLUMN public.stock.stock_num IS 'Stock number that identifies type of item'; COMMENT ON COLUMN public.stock.manu_code IS 'Manufacturer code (foreign key to manufact table)'; COMMENT ON COLUMN public.stock.description IS 'Description of item'; COMMENT ON COLUMN public.stock.unit_price IS 'Price per item'; COMMENT ON COLUMN public.stock.unit IS 'Unit by which item is ordered: Each Pair Case Box'; COMMENT ON COLUMN public.stock.unit_descr IS 'Description of unit'; -- Index: stock_manu_code_idx -- DROP INDEX public.stock_manu_code_idx; CREATE INDEX stock_manu_code_idx ON public.stock USING btree (manu_code COLLATE pg_catalog."default" ASC NULLS LAST) TABLESPACE pg_default; -- Index: stock_stock_num_idx -- DROP INDEX public.stock_stock_num_idx; CREATE INDEX stock_stock_num_idx ON public.stock USING btree (stock_num ASC NULLS LAST) TABLESPACE pg_default; COMMENT ON INDEX public.stock_stock_num_idx IS 'Index for stock numbers. Numbers may be duplicated by different manufacturers.'; -- Index: stock_stock_num_manu_code_idx -- DROP INDEX public.stock_stock_num_manu_code_idx; CREATE UNIQUE INDEX stock_stock_num_manu_code_idx ON public.stock USING btree (stock_num ASC NULLS There will be a foreign key constraint on manu_code to manufact.manu_code but that is not yet implemented. The reference to column "SHM" not existing has me confused. I am probably missing something obvious but I cannot see what the problem is. Any help would be most welcome. Thanks, -- *** 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. Byrnemailto:byrn...@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
Re: Need another set of eyes on this
On Mon, January 25, 2021 14:19, aNullValue (Drew Stemen) wrote: > > You're using double-quotes rather than single-quotes for string values. > > "SHM" = column > 'SHM' = string value > On Mon, January 25, 2021 14:19, Peter Coppens wrote: > > Try single quotes > Thank you both. -- *** 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. Byrnemailto:byrn...@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
Problem with trigger function
I am trying to implement a trigger in a PostgreSQL-9.6.17 database:
CREATE OR REPLACE FUNCTION customer_num_informix()
RETURNS trigger AS $$
BEGIN
-- An Aubit/Informix 4GL INSERT statement passes the value 0 to Informix
-- DBMS for columns that have the SERIAL data type. Informix will then
-- use the incremented serial number in place of 0. PostgreSQL instead
-- will simply take the value 0 and replace the incremented serial number.
-- This trigger function emulates the Informix DBMS behaviour.
--
-- The NEW variable contains the data for the row to be INSERTed or
-- UPDATEd. It, and its complement OLD used for UPDATE and DELETE,
-- are automatically created and populated by PostgreSQL whenever
-- a data-change function is called.
--
IF NEW.customer_num = 0 THEN
SELECT nextval('customer_customer_num_seq') INTO NEW.customer_customer_num;
ELSE
IF NEW.customer_customer_num > 0 THEN
PERFORM setval('customer_customer_num_seq',
NEW.customer_customer_num);
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION public.customer_num_informix() OWNER TO hll_4gl_testing;
CREATE TRIGGER customer_num_serial
BEFORE INSERT ON customer
FOR EACH ROW EXECUTE customer_num_informix();
The problem is that I am getting a syntax error on the CREATE TRIGGER statement:
ERROR: syntax error at or near "customer_num_informix"
LINE 3: FOR EACH ROW EXECUTE customer_num_informix();
I do not see what the error is. What is wrong with the syntax I used?
--
*** 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. Byrnemailto:byrn...@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
