Hi Ivanov:

In message <ae63c765fa4f2115a325430a87176b7f@riseup.net>,
Ivanov writes:
>Ivanov added the comment:
>I changed the line. Also, I found that I only get this exception when I
>stay logged into the psql console as user roundup_dev. So it might be a
>problem with the postgresql instance.

When you are logged in as roundup_dev, are you connected to the
database you are trying to drop? When Roundup does db commands
(create/drop) it connects to the database "postgres" specifically so it
won't connect to the db it's trying to DROP.

>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
>line 96, in db_command
>    raise RuntimeError('10 attempts to create database failed when
>running: %s' % command)
>RuntimeError: 10 attempts to create database failed when running: DROP
>DATABASE "roundup_dev"

I suspect you are connected to roundup_dev in your psql instance. Try
using \c postgres to connect to another db then try the drop.

>Otherwise I get another exception:
>
>$ roundup-admin initialise
>Enter tracker home: core
>Admin Password: 
>       Confirm: 
>WARNING: The database is already initialised!
>If you re-initialise it, you will lose all the data!
>Erase it? Y/N: Y
>2023-12-19 03:18:14,738 INFO DROP DATABASE "roundup_dev"
>2023-12-19 03:18:15,787 INFO CREATE DATABASE "roundup_dev" WITH
>ENCODING='UNICODE' TEMPLATE=template0
>Traceback (most recent call last):
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
>line 106, in pg_command
>    cursor.execute(command)
>psycopg2.errors.InsufficientPrivilege: permission denied to create
>database

Ok good. I tested by the CREATEDB role from the roundup user in my
test instance and got the same error. This is expected.

>During handling of the above exception, another exception occurred:

Yeah this is the ugly part I mentioned in the second I think message
on this issue.

>> Also can you connect to the db with psql and run \du so I can see what
>postgres=# \du
>                                      List of roles
>    Role name    |                         Attributes                   
>     | Member of 
>-----------------+------------------------------------------------------------+-----------
> postgres        | Superuser, Create role, Create DB, Replication,
>Bypass RLS | {}
> roundup_dev     |                                                      
>     | {}

Good. That's what I was expecting and matching.

       ====== start aside

Discussing automation is probably worth bringing back onto the list. I
think there are a couple of people who run multiple trackers and one
uses ansible for deployment. However one of them, Tom, retired last
week, so he might not be around anymore.

>>>Maybe some kind of non-destructive initialization might be possible,
>>>without recreating the entire database?
>> 
>> [...] It checks to see if the db exists. If the db exists it tries
>> to load the roundup schema. If that fails it initializes the
>> db. But it doesn't look like you are getting that far.

>Hmm... I'm also concerned about automation. If I have a pipeline, I
>would like to initialize the database only if it is not already
>initialized. If some data is already in the database, it is advisable to
>skip the initialization step.

The init command opens the hyperdb as the admin user. This triggers
the loading of the roundup internal schema. If the schema is missing
from the database, it creates the tables and run the migrations.

If you attempt to do an init and the schema exists it will ask you if
you want to remove the db.

If you want to automate this, I would do it at the psql level and
create the db and dbuser if and only if they don't exist. Otherwise
just let roundup do its thing.

>Of course, this would be easier to do with
>a native Roundup command rather than using psql checks. I think it would
>be great to have a special flag for the 'initialise' command for such
>cases.

You should only ever run initialize once. In the docker container
under a guided deployment, the guiding script creates a sentinal file
indicating that it has been initialized and not to repeat the step.

If you want you can use:

  if roundup-admin -i tracker_home display user1 > /dev/null 2>&1; then
     # roundup is initialized
  else
    # roundup needs to be initialized

user1 is guaranteed to be the admin user if the tracker is
initialized.  If you fail to get user1 displayed, roundup-admin should
exit non-zero. If user1 fails to display, even if you have data in the
tracker's db it's not something roundup can use.

Changes to the roundup schema are automatically deployed. When the
tracker is opened, the stored schema is obtained and compared to the
schema generated by running schema.py. If they don't match, it mutates
the database so the schema matches the one in schema.py. Then it
stores a copy of the schema.py schema back in the schema table of the
db.

    ===== end aside

>>>By the way, why a database and not a schema?
>> 
>> Because thats how it was originally designed and nobody has tried to
>> integrate https://issues.roundup-tracker.org/issue2550852 and finish
>> up tests/docs etc. I have a vague recollection of testing the patch a
>> few years ago and it worked.
>
>I will try to test with the current branch.

Cool. I think I tried it a few years back. Probably in the 1.x
series. So it will need a little work for utf8 strings.
Also it counts on the database in the config.ini file being defined as:

  database = roundup_db.my_schema

Somehow we are going to need to get the schema defined in
pg_service.conf out of libpg. I have no clue how to do that. Figuring
out what db is used is a major hack.

>> How are you creating/directing roundup to use an alternate schema?  Is
>>[...]

>$ cat .pg_service.conf
>[roundup_dev]
>[...]
>options=-c search_path=roundup_dev,public

Gotcha. As I mentioned above I have no idea how to get the schema from
here.

>But if I remember correctly, postgresql by default tries the schema
>named by the username first.

That doesn't seem to be the case for me with postgresql 15 at
least. All the tables (\dt) are in the public schema.

>>>If I only use the 'public' schema for my database, the migration command
>>>works well.
>[...]
>There are no tables at all.

I think the DDL is done in a transaction. So crashing in
fix_version_3_tables migration rolls back all the tables including the
schema table.

>> Can you change the reference to public.__words to just __words around
>> line 335 in back_postgresql.py. Then see if the migrate works. I don't
>> see any other explicit schema references in that file.
>
>It works. Thank you very much.

I've fixed that in the core code.

 changeset:   7715:95f0002e85c4

>roundup_dev=> \dt roundup_dev.*
>                   List of relations
>   Schema    |       Name        | Type  |    Owner    
>-------------+-------------------+-------+-------------
> roundup_dev | __fts             | table | roundup_dev
> roundup_dev | __textids         | table | roundup_dev
> [removed a good looking list of tables. all in the roundup_dev schema]
> roundup_dev | user_queries      | table | roundup_dev
>(31 rows)
>
>roundup_dev=> \dt public.*
>Did not find any relation named "public.*".

Yup that makes sense since you are explicitly setting the
schema/search_path in your connection.

Have a great day.