Error when running new_database targeting postgresql

[dhoogest]

sqlite3 builds are working fine, however the new_database subcommand fails at present with the following error (during addition of the pg constraints):

[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING - Traceback (most recent call last):
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -   File "/usr/local/bin/taxit", line 8, in <module>
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -     sys.exit(main())
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -              ^^^^^^
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -   File "/usr/local/lib/python3.11/site-packages/taxtastic/scripts/taxit.py", line 51, in main
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -     return action(arguments)
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -            ^^^^^^^^^^^^^^^^^
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -   File "/usr/local/lib/python3.11/site-packages/taxtastic/subcommands/new_database.py", line 108, in action
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -     taxtastic.ncbi.execute_template(engine, 'add_pg_constraints.sql')
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -   File "/usr/local/lib/python3.11/site-packages/taxtastic/ncbi.py", line 191, in execute_template
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -     conn.exec_driver_sql(cmd)
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -   File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1776, in exec_driver_sql
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -     ret = self._execute_context(
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -           ^^^^^^^^^^^^^^^^^^^^^^
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -   File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1843, in _execute_context
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -     return self._exec_single_context(
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -            ^^^^^^^^^^^^^^^^^^^^^^^^^^
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -   File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -     self._handle_dbapi_exception(
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -   File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2352, in _handle_dbapi_exception
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
[2025-05-21, 12:28:42 PDT] {ssh.py:531} WARNING -   File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
[2025-05-21, 12:28:43 PDT] {ssh.py:531} WARNING -     self.dialect.do_execute(
[2025-05-21, 12:28:43 PDT] {ssh.py:531} WARNING -   File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 942, in do_execute
[2025-05-21, 12:28:43 PDT] {ssh.py:531} WARNING -     cursor.execute(statement, parameters)
[2025-05-21, 12:28:43 PDT] {ssh.py:531} WARNING - sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "nodes" violates foreign key constraint "nodes_rank_fkey"
[2025-05-21, 12:28:43 PDT] {ssh.py:531} WARNING - DETAIL:  Key (rank)=(realm) is not present in table "ranks".
[2025-05-21, 12:28:43 PDT] {ssh.py:531} WARNING - 
[2025-05-21, 12:28:43 PDT] {ssh.py:531} WARNING - [SQL: ALTER TABLE ONLY public.nodes ADD CONSTRAINT nodes_rank_fkey FOREIGN KEY (rank) REFERENCES public.ranks(rank);]
[2025-05-21, 12:28:43 PDT] {ssh.py:531} WARNING - (Background on this error at: https://sqlalche.me/e/20/gkpj)

Inspection of current taxdmp reveals 7 nodes with the 'realm' rank, which is not present in the ranks table.

[crosenth]

https://github.com/fhcrc/taxtastic/pull/177/files

[crosenth]

https://github.com/fhcrc/taxtastic/releases/tag/v0.12.0