Re: UUID generation problem

On 10/5/20 9:31 AM, James B. Byrne wrote:
>
>
> 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"'

FYI, you don't need to change to a system user to use that username in
the psql connection. As long as you specify -U some_name you can connect
from the terminal of any system user and have the connection be from the
-U some_name.

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

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'.

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

It would depend on whether the database is live or not. If it is still
in dev stage I would say start over with install, using the new found
knowledge. Otherwise it will depend on the answers to the questions
downstream. Will answer there.

>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com