Error using PostgreSQL as database backend with Gramps v5.2.2

Help
14

A lot of interesting and quite diverse ideas that were brought up by @StoltHD @ennoborg @Nick-Hall @SNoiraud – thanks! I would conclude that Gramps running with a PostgreSQL backend on Win10 appears to be not something we can find quite often :wink:

I’ve done some other tests and would like to share the results:

(1) After the add-on update this morning I now have both PostgreSQL and SharedPostgreSQL to choose from in the configuration, so following @Nick-Hall, I chose PostgreSQL. It appears that the AIO does only include SharedPostgreSQL and PostgreSQL has to be added by an add-on update.

(2) Then I created a new database in my PostgreSQL 16 instance running on localhost:5432 with pgAdmin 4. The database has the name HABGRI3D and its encoding is set to UTF8. The tablespace is pg_default (the sole option):

image
image712×562 30.8 KB

I don’t see any way to switch to “binary” as @ennoborg proposed.

(3) Then I created a new tree in Gramps with PostgreSQL as database backend. The tree has also the name HABGRI3D following the Linux implementation description. No problem with that.

(4) Then I tried to open this new tree in Gramps. The authorization popup asking for user id and password for the PostgreSQL database appears

image

and I intentionally entered a wrong user id and a wrong password. The error message with the low level corruption pops up

image

This time both the byte (0xfc instead of 0xbb) and position (97 instead of 79) are different from the first screenshot.

(5) After clicking on Schließen (Close) the usual Gramps error/traceback appears:

6407145: ERROR: dbloader.py: line 107: 'utf-8' codec can't decode byte 0xfc in position 97: invalid start byte
Traceback (most recent call last):
  File "C:\Program Files\GrampsAIO64-5.2.2\gramps\gui\dbloader.py", line 198, in read_file
    db.load(
  File "C:\Program Files\GrampsAIO64-5.2.2\gramps\gen\db\generic.py", line 647, in load
    self._initialize(directory, username, password)
  File "C:\Users\Ulrich Demlehner\AppData\Roaming\gramps\gramps52\plugins\PostgreSQL\postgresql.py", line 105, in _initialize
    self.dbapi = Connection(**dbkwargs)
                 ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Ulrich Demlehner\AppData\Roaming\gramps\gramps52\plugins\PostgreSQL\postgresql.py", line 118, in __init__
    self.__connection = psycopg2.connect(*args, **kwargs)
                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "AIO/psycopg2/__init__.py", line 122, in connect
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xfc in position 97: invalid start byte

I do not see any differences to the “old” error/traceback message above.

(6) Checking the database HABGRI3D in pgAdmin 4, I cannot identify any changes exactly as it should be with wrong credentials.

(7) Now I repeat (3) and (4) but this time with correct credentials. And voila, no error message appears. I import my Gramps XML file with the 96k individuals and 238k events and I see in pgAdmin that the tables I’d expect have been created. The import appears to be significantly slower compared with the import into the good ole BDDB, but I have no exact figures.

(8) I repeat (3) and (4) with a database and tree called HABGRI 3D (notice the blank!) and correct credentials. No error messages, everything is fine.

(9) Now the same story again (actually with HABGRI3D2 as name of database and tree) but with SharedPostgreSQL as database backend and correct credentials. And now the error is back.

Obviously SharedPostgreSQL is not a good idea whatever the reason may be.

But the really bad news is that working with PostgreSQL appears to be much slower compared with BDDB. A regex query in the Person view that used to run some 10 - 15 sec on BDDB, needs now 50 sec. Even the queries in the Citation and Places views that used to be finished in 1 - 2 sec, now need 7 - 8 sec. And a query in the Event view runs nearly 1 min. These observations fit into my playing around with SQLite as backend, since SQLite appears also to be much slower than BDDB. Ok, not everybody has a database with nearly 100k individuals, but it appears to be a bit counterintuitive that a modern database like PostgreSQL is left in the dust by an oldie like BDDB.

Thanks for your help!

Ulrich

2 Likes