Enhanced PostgreSQL Addon Submitted

Development
62

You’re right that the timeout is the core issue, but I need to ask you to bear with me on the configuration approach.

Gramps has made changes to both their database and connection manager layers recently. I haven’t been actively working in the Gramps codebase for a while, and your hybrid environment variable approach bypasses the normal code paths. This makes it very difficult to diagnose where the timeout is actually occurring.

When you use PGHOST, PGPORT, etc., you’re mixing two configuration systems - the environment variables (which connection.py picks up directly) and the ConfigManager system (which Gramps now expects). This means I can’t be certain whether the failure is in my code or in how the two systems are interacting.

That said, I did just fix a connection leak bug that matches your symptoms exactly - connections not being cleaned up when initialization fails. This is now in PR 781.

To properly test this fix, I need you to remove the environment variables (PGHOST, PGPORT, PGUSER, POSTGRESQL_ENHANCED_MODE) and use Gramps Preferences instead. Go to Edit → Preferences → Database and set host: localhost, port: 5434, user: your_username.

Then download the fixed files from my repo - postgresqlenhanced.py and connection.py from https://raw.githubusercontent.com/glamberson/gramps-postgresql-enhanced/update-gramps-606-compatibility/ and replace them in C:\Users\Ulrich Demlehner\AppData\Roaming\gramps\gramps60\plugins\PostgreSQLEnhanced\

After that, try creating a tree with PostgreSQL Enhanced (Monolithic).

If it still times out with proper configuration, then I need the debug log from C:\Users\Ulrich Demlehner.gramps\postgresql_enhanced_debug.log

With a clean configuration, the debug log will clearly show which initialization step is hanging, and I can fix it. I know the environment variables got you further initially, but to properly diagnose and fix this issue, I need to see it working through the supported code paths. Please bear with me on this.

Thanks,

Greg

63

Ok, see your point. I deleted all PGxxx environment variables now. The username cannot be defined in the preferences, so I haven’t. The 606 files are now in my plugin folder.

(1) When trying to open the tree I created yesterday, I get

image
image559×261 22.1 KB

The error report is:

User Information:

Error Details:

88942: ERROR: dbloader.py: line 106: invalid literal for int() with base 10: ‘5434              # PostgreSQL port’
Traceback (most recent call last):
File “C:\Program Files\GrampsAIO64-6.0.6\gramps\gui\dbloader.py”, line 197, in read_file
db.load(
File “C:\Users\Ulrich Demlehner\AppData\Roaming\gramps\gramps60\plugins\PostgreSQLEnhanced\postgresqlenhanced.py”, line 617, in load
self._initialize(directory, actual_username, actual_password)
File “C:\Users\Ulrich Demlehner\AppData\Roaming\gramps\gramps60\plugins\PostgreSQLEnhanced\postgresqlenhanced.py”, line 360, in _initialize
config_mgr.set(‘database.port’, int(old_config.get(‘port’, ‘5432’)))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ValueError: invalid literal for int() with base 10: ‘5434              # PostgreSQL port’

System Information:

Gramps version: AIO64-6.0.6–1
Python version: 3.12.12
BSDDB version: not found
sqlite version: 3.51.0
orjson version: 3.11.4
LANG: de_DE.UTF-8
OS: Windows

GTK version    : 3.24.51
gobject version: 3.54.3
cairo version  : (1, 28, 0)

(2) When I create a new tree (no problem) and try to open it, I get the same error message.

64

Oops! That’s a bug in the config file parser. When connection_info.txt has inline comments like “port = 5434 # PostgreSQL port”, the code was trying to convert the whole string (including the comment) to an integer.

I just pushed a fix (30 seconds ago). Please download the updated postgresqlenhanced.py:

https://raw.githubusercontent.com/glamberson/gramps-postgresql-enhanced/update-gramps-606-compatibility/postgresqlenhanced.py

Replace it in C:\Users\Ulrich Demlehner\AppData\Roaming\gramps\gramps60\plugins\PostgreSQLEnhanced\

This should fix the parsing error. The fix strips inline comments before converting port and pool_size to integers.

Once you’ve updated the file, try opening your tree again. If you still get the timeout, I’ll need the debug log to see what’s hanging during initialization.

65

Hmm, I don’t have a connection_info.txt since I was assuming that it should automatically be created when creating a new tree. Am I supposed to add one manually?

66

I meant settings.ini . It should be created automatically when you create a tree using Gramps Preferences. If it’s not there, that’s the timeout issue.

67

So a settings.ini should automatically appear in the tree folder whenever I create a new tree? No, it didn’t.

I added one manually:

[database]
host=localhost
port=5434
user=postgres
shared-database=gramps_monolithic
pool-size=5

Now the invalid literal error message is gone and I’m back at the failed connection message:

image
image558×258 20.4 KB

So I guess this shows that my access to the database was by means of the PGxxx environment variables, not the settings in the Gramps preferences.

Just for testing this conclusion, I added the PGxxx environment variables. Result:

image
image555×277 22 KB

So it appears that either my conclusion was wrong or that your last updates blocked the usage of the environment variables.

68

Hi Ulrich,

I meant settings.ini, not connection_info.txt. The new system uses settings.ini (which is what Gramps prefers but i just never fixed it) which should be created automatically when you create a tree.

The fact that you don’t see any config file in your tree directory confirms what I suspected - the timeout is happening before the code gets to the step where it saves settings.ini. The initialization is failing somewhere earlier.

Please don’t use environment variables as a workaround. That bypasses the normal configuration system and makes it impossible for me to diagnose where the actual problem is. I need to see this working through the supported code path.

Can you do this:

  1. Remove all PostgreSQL environment variables (PGHOST, PGPORT, PGUSER, POSTGRESQL_ENHANCED_MODE)
  2. Make sure you have the latest postgresqlenhanced.py (the one that fixes the inline comment parsing bug)
  3. Delete any connection_info.txt files in the plugin directory to avoid confusion
  4. Set your preferences in Edit → Preferences → Database (host: localhost, port: 5434, user: your_username)
  5. Try creating a new tree with PostgreSQL Enhanced (Monolithic)
  6. When it times out, check the debug log at C:\Users\Ulrich Demlehner.gramps\postgresql_enhanced_debug.log

Send me the last 50-100 lines of that debug log. That will show exactly where the initialization is hanging and what step is timing out.

Greg

69

Did you notice that I have now a settings.ini in the folder which I created myself? So I’m not sure if your explanation is really valid.

They are already gone. I just wanted to test my hypothesis.

I did all this but I never come to the timeout. I’m stuck at this error message:

image
image556×301 28 KB

Ah, I just noticed that the settings.ini in the tree folder is now really created. It contains:

;; Gramps key file
;; Automatically created at 2025/12/21 16:34:18

[database]
host=‘192.168.178.20’
;;pool-size=5
port=5434
shared-database=‘gramps_monolithic’
user=‘postgres’

That looks good for me, we’re making progress.

70

Ok, I’ve been hunting down that pg_hba.conf file. I have one in the data folder of my PostgreSQL 18 installation and it says:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# “local” is for Unix domain socket connections only

local   all             all                                     scram-sha-256

# IPv4 local connections:

host    all             all             127.0.0.1/32            scram-sha-256

# IPv6 local connections:

host    all             all             ::1/128                 scram-sha-256

# Allow replication connections from localhost, by a user with the
# replication privilege.

local   replication     all                                     scram-sha-256
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

Since I’m on Win10, the only relevant entry is the 2nd non comment line and this allows access from my local machine where the server is running. So the error message makes not any sense at all for me. Or does Gramps for some reason really check if my machine has the IP address 127.0.0.1 which of course would make no sense?

Oh, indeed! After adding my IP address as 192.168.178.20:32, I now get:

image
image556×257 21.3 KB

And this makes sense since I never provided a password. How could I? The only way I can think of is the settings.ini with a line password=’password’. But no, this will not work, I just tried it.

71

Ulrich,

LOL ok I don’t speak German but I understand now. This is good news as it’s a simple postgres issue.

Your pg_hba.conf allows connections from 127.0.0.1 (localhost), but your settings.ini is configured to connect to 192.168.178.20 (your network IP address).

When you connect to 192.168.178.20, PostgreSQL sees that connection as coming FROM 192.168.178.20, not from 127.0.0.1. That is why the pg_hba.conf entry does not match.

You have two options to fix this:

Option 1 (Recommended): Change your settings.ini to use localhost instead of the IP address.

Edit the settings.ini file in your tree directory and change:
host=192.168.178.20

to:
host=localhost

This will make the connection go through 127.0.0.1 which is already allowed in your pg_hba.conf.

Option 2: Add your network IP to pg_hba.conf.

Add this line to pg_hba.conf:
host all all 192.168.178.20/32 scram-sha-256

Then reload PostgreSQL configuration with: SELECT pg_reload_conf();

I recommend Option 1 since you are running Gramps and PostgreSQL on the same machine. There is no need to use the network IP address. Using localhost will be faster and does not require changing your PostgreSQL server configuration.

Greg

72

LOL, I did that and this takes me one step further to the missing password. What about that? I guess Gramps should fire up a dialog window that requests the password, but this does not happen.

PS: I also changed the settings.ini to localhost.

73

Welcome to the bleeding edge. Sorry about the mess.

When I redesigned the configuration system to use settings.ini instead of connection_info.txt, I intentionally made it more secure by not storing passwords in the configuration file. However I forgot to implement the requires_login method that tells Gramps to prompt for the password.

I just pushed a fix. Download the updated postgresqlenhanced.py:

https://raw.githubusercontent.com/glamberson/gramps-postgresql-enhanced/update-gramps-606-compatibility/postgresqlenhanced.py

Replace it in C:\Users\Ulrich Demlehner\AppData\Roaming\gramps\gramps60\plugins\PostgreSQLEnhanced\

Now when you create or open a tree, Gramps should show a login dialog asking for username and password before connecting to the database.

After updating the file, try creating the tree again. You should see a password prompt, and after entering your PostgreSQL password, it should connect successfully.

Let me know how it goes.

Greg

74

Try to either set the port number in ““ or if they are in ““, remove them…

I had that INT problem earlier, when running Gramps in WSL using the Gramps DB config on the Windows Host…

75

I guess we made it – it’s Christmas time! I created a new tree. The tree folder has a settings.ini that looks good. When I try to open the tree, Gramps fires up the user id/password dialog (which is a bit inconsistent, since the user id is already defined in the preferences) and after I enter the authentification information, I’m greeted by the Gramps UI without any timeout.

I started the import of my 3.5M XML lines tree but this does not look great. The progress bar hasn’t moved away from 0 after a few minutes which is not a good sign.

pgAdmin says:

image
image1255×183 20.5 KB

76

Hi Ulrich,

The username issue is a limitation of Gramps’s GrampsLoginDialog class. So I’ll leave that alone.

REgarding your import, that kind of thing is something I’ve run into before, but not for a long time. This is what happens when you dig into code after not looking at it for a while.

Can you check the debug log while the import is stuck?

Look at C:\Users\Ulrich Demlehner.gramps\postgresql_enhanced_debug.log

What are the last few lines? This will show where the plugin got to before it stopped.

Also check the main Gramps log:

C:\Users\Ulrich Demlehner.gramps\gramps60\grampslog.txt

Look for any errors or warnings during the import.

Greg

77

Sorry, but I do not have any of those two files anywhere in my user tree.

The progress bar is now slowly crawling and currently at approx 25 %. If I extrapolate this the import will need some 2 - 3 hours compared with approx 10 min in Gramps 5.1.6 on BSDDB. pgAdmin shows:

image
image716×215 12.5 KB

image
image480×207 9.68 KB

I don’t know what “Tuples out” are, but I understand the transaction graph as saying that the import is done in transactions which will not speed up things. So I guess this could be a problem.

Additionally I see a very long list of locks. I’d estimate some 100 and the names point to indexes and keys. For example: idx_tree_69481cf3_source_gramps_id 69481cf3 being the id of the tree I’m importing into. So I guess the import is significantly slowed down by doing it in transactions. I have no idea if this can explain the low performance of the import, but it looks a bit suspicious.

78

Ulrich,

Oh wow. I know what’s happening. We talked aobut this months ago but I don’t remember whether this was done when I worked on this extension last. There’s a flag in Gramps : transaction.batch=True . I ignore this right now. This is the problem. It’s doing a commit after efery entry.

That’s the problem.

I’ll implement this now. You can either let it run or give me a bit to work on it.

OK actually it wasn’t that bad. I did have some other issues, but I had a mockup of this laying around. It’s almost ready.

Greg

79

Ulrich,

Download the fixed connection.py:

https://raw.githubusercontent.com/glamberson/gramps-postgresql-enhanced/update-gramps-606-compatibility/connection.py

Replace it in C:\Users\Ulrich Demlehner\AppData\Roaming\gramps\gramps60\plugins\PostgreSQLEnhanced\

Then try a fresh import. It should now take 10-15 minutes instead of hours. The import you are running now will still be slow since it is using the old code, but with this fix new imports should match BSDDB performance.

Greg

80

I’ll finish the running import just to see where it will end up. The progress bar is now around 75 %, so it should finish not later than 8 pm. After that I’ll run a new one with the new code. Stay tuned!

1 Like
81

That doesn’t look too good …

The import was finished at around 1925 or so. I usually start in the citations view since it is one of the views where the performance problems of the Gramps 5.1.6/BSDDB aren’t really relevant. I immediately noticed that sorting this view based on one of the standard columns displayed (e g the date of the citation) is a lot slower compared with Gramps 5.1.6/BSDDB.

Changing to the person view isn’t possible at all. I guess this has to do with Gramps trying to do some standard sorting of the view (as far as I tried to analyze that, Gramps is sorting the first column displayed in standard ascending order). Since this means in my case the sorting of some 80k names, it appears to be quite time consuming so that the view cannot be displayed even after waiting for some minutes.

Trying to switch to any other view (with the exception of citations and relationships) immediately triggers an error. I guess the reason is that the change would start a new sorting process that collides with the still running one. Citations are already sorted, so they can be displayed immediately, and relationships need no sorting.

I’m currently running the import with the new connection.py but it does not appear to be faster.