My response to Ivanov's email on the mailing list.

[elided header requesting it be taken to the issue]

             ====================

In message <725a7be5f273dae68da4e47dd58ee0b8@riseup.net>,
ivanov17@riseup.net writes:
>I have tested this and found the following two problems.
>
>First, the 'roundup-admin initialise' command doesn't work properly in
>this case. I got the same results using first the PGSERVICE environment
>variable and then the rdbms_service configuration parameter. 

Good it's consistent.

>$ 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-18 03:15:58,166 INFO DROP DATABASE ""
>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.SyntaxError: zero-length delimited identifier at or near
>""""
>LINE 1: DROP DATABASE ""

Ick.

If I have psycopg > 2.8 I can use
psycopg2.extensions.ConnectionInfo(connection) to get the dbname. I think.

>But what's even stranger is that I can't initialize the database when I
>specify the database name, host, port, username and password in the
>configuration file.

That is wierd. Do you have all the settings plus the service
defined?

>$ 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-18 19:31:01,239 INFO DROP DATABASE "roundup_dev"
>Traceback (most recent call last):
>  File "/home/user/Projects/www/roundup_dev/env/bin/roundup-admin", line
>8, in <module>
>    sys.exit(run())
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/scripts/roundup_admin.py",
>line 50, in run
>    sys.exit(tool.main())
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
>line 2230, in main
>    ret = self.run_command(args)
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
>line 2079, in run_command
>    return self.do_initialise(self.tracker_home, args)
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
>line 1168, in do_initialise
>    tracker.nuke()
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/instance.py",
>line 213, in nuke
>    self.backend.db_nuke(self.config)
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
>line 64, in db_nuke
>    db_command(config, command)
>  File
>"/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')
>RuntimeError: 10 attempts to create database failed

Hmm, db_command should say what command it's having an issue with ans
what the error is.

Can you change:

-    raise RuntimeError('10 attempts to create database failed')

to

+    raise RuntimeError('10 attempts to create database failed when running: %s' % command)

around line 96 of roundup/backends/back_postgresql.py. That will get
some of the info hopefully.

Also can you connect to the db with psql and run \du so I can see what
permissions the user has. I assume none are listed since you don't
have CREATEDB.

>My postgresql user is the owner of the selected database, but does not
>have permission to create databases. 
>
>I think the problem is here, but I don't want to give them additional
>permissions just to initially populate the database.
>
>Maybe some kind of non-destructive initialization might be possible,
>without recreating the entire database?

It's supposed IIRC. 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.

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

>At the same time, when I directly create Roundup users, it works for me.

Is the sequence here:

  set all db params in config.ini (host, database ...)

  roundup-admin init

  get the error: RuntimeError: 10 attempts to create database failed

  roundup-admin create user ....

  works.

>Second, there is a problem with using a schema other than 'public'. I
>can't perform database migration when I create scheme named by my
>postgresql username and use it in search_path. I see that Roundup
>expected me to use the 'public' schema.

That is either fixed in issue2550852, or a 'to be fixed' if support
for schemas comes on board.

>$ roundup-admin migrate
>Enter tracker home: core
>2023-12-18 03:13:02,011 INFO open database via service 'roundup_dev'
>2023-12-18 03:13:02,033 INFO rollback
>2023-12-18 03:13:02,033 DEBUG SQL 'CREATE TABLE schema (schema TEXT)'
>None
>2023-12-18 03:13:02,044 DEBUG SQL 'CREATE TABLE dual (dummy integer)'
>None
>2023-12-18 03:13:02,046 DEBUG SQL 'insert into dual values (1)' None
>2023-12-18 03:13:02,047 DEBUG SQL 'CREATE TABLE otks (otk_key
>VARCHAR(255),\n            otk_value TEXT, otk_time float)' None
>2023-12-18 03:13:02,055 DEBUG SQL 'CREATE INDEX otks_key_idx ON
>otks(otk_key)' None
>2023-12-18 03:13:02,060 DEBUG SQL 'CREATE TABLE sessions (\n           
>session_key VARCHAR(255), session_time float,\n            session_value
>TEXT)' None
>2023-12-18 03:13:02,065 DEBUG SQL 'CREATE INDEX sessions_key_idx ON\n   
>        sessions(session_key)' None
>2023-12-18 03:13:02,069 DEBUG SQL 'CREATE SEQUENCE ___textids_ids' None
>2023-12-18 03:13:02,070 DEBUG SQL 'CREATE TABLE __textids (\n           
>_textid integer primary key, _class VARCHAR(255),\n            _itemid
>VARCHAR(255), _prop VARCHAR(255))' None
>2023-12-18 03:13:02,079 DEBUG SQL 'CREATE TABLE __words (_word
>VARCHAR(55),\n            _textid integer)' None
>2023-12-18 03:13:02,080 DEBUG SQL 'CREATE INDEX words_word_idx ON
>__words(_word)' None
>2023-12-18 03:13:02,087 DEBUG SQL 'CREATE INDEX words_by_id ON __words
>(_textid)' None
>2023-12-18 03:13:02,092 DEBUG SQL 'CREATE UNIQUE INDEX
>__textids_by_props ON __textids (_class, _itemid, _prop)' None
>2023-12-18 03:13:02,097 DEBUG SQL 'DELETE FROM otks' None
>2023-12-18 03:13:02,097 DEBUG SQL 'ALTER TABLE otks DROP otk_value' None
>2023-12-18 03:13:02,098 DEBUG SQL 'ALTER TABLE otks ADD otk_value TEXT'
>None
>2023-12-18 03:13:02,099 DEBUG SQL 'DELETE FROM sessions' None
>2023-12-18 03:13:02,099 DEBUG SQL 'ALTER TABLE sessions DROP
>session_value' None
>2023-12-18 03:13:02,099 DEBUG SQL 'ALTER TABLE sessions ADD
>session_value TEXT' None
>2023-12-18 03:13:02,100 DEBUG SQL 'CREATE INDEX words_both_idx ON
>public.__words\n            USING btree (_word, _textid)' None
>Traceback (most recent call last):
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
>line 217, in open_connection
>    self.load_dbschema()
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/rdbms_common.py",
>line 305, in load_dbschema
>    self.cursor.execute('select schema from schema')
>psycopg2.errors.UndefinedTable: relation "schema" does not exist
>LINE 1: select schema from schema
>                           ^

Now this is wierd because:

  2023-12-18 03:13:02,033 DEBUG SQL 'CREATE TABLE schema (schema TEXT)'

the schema table and column were created, but probably in your
alternate schema. ("schema" is a confusing term for Roundup to use
there....)

How are you creating/directing roundup to use an alternate schema?  Is
there a schema= in pg_service.conf? Is the path associated with the
logged in user?

My guess is that the database layout is being put into your alternate
(non-public) schema and 'select schema from schema' uses the public
schema by default and fails.

The schema support patch does an explicit set search path to include
the schema.

>line 227, in __init__
>    self.open_connection()
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
>line 227, in open_connection

This is the code that runs to initialize the db if it already exists.
fix_version_3_tables runs after initializing the tables.

>    self.fix_version_3_tables()
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
>line 335, in fix_version_3_tables
>    self.sql('''CREATE INDEX words_both_idx ON public.__words
>[...]
>psycopg2.errors.UndefinedTable: relation "public.__words" does not exist
>
>If I only use the 'public' schema for my database, the migration command
>works well.

I wonder why this has an explicit schema on it? Can you check to see
if your alternate schema (assuming it got committed to the db) has a
__words table in it?

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.

> Thank you for your help.

No, thank you for helping to make Roundup better.