Error using PostgreSQL as database backend with Gramps v5.2.2

[Nick-Hall]

It would be interesting to know what parts of Gramps suffer from performance issues that irritate you most as a user. Perhaps we should start a separate thread to discuss this further.

[UlrichDemlehner]

@Nick-Hall

Perhaps we should start a separate thread to discuss this further.

Agreed. I’ll post my test results in a new thread.

[emyoulation]

@Nick-Hall

It would be interesting to know what parts of Gramps suffer from performance issues that irritate you most as a user. Perhaps we should start a separate thread to discuss this further.

Philip Wharram (@comeng) created a separate thread:

Performance Issues with GRAMPS

So Nick my input Statistics Number of Individuals 15365 Number of Families 4512 Number of Unique Surnames 2581 Individuals with media objects 6641 Number of media objects 12686 GRAMPS: 5.1.6 Python: 3.10.12 (main, Nov 20 2023, 15:14:05) [G… sqlite: 3.37.2 (2.6.0) LANG: en_GB.UTF-8 OS: Linux Mate on Ubuntu 22 Distribution: 6.5.0-28-generic Only one issue Narrative Web for well known reasons other than that no requirement to improve performance phil

[GeorgeWilmes]

@comeng

this thread appears to have gone way beyond the original
discussion

Agree! but I will anyway squeeze in a few more replies…

@Nick-Hall

I would regard querying the database from outside Gramps as a secondary objective.

Agree, it’s just that I’m more comfortable writing SQL queries than coding python.

@UlrichDemlehner

if you already have a fully relational Gramps database in form of the SQLite export (or my PostgreSQL schema), why should you then make your life more complicated by playing around with JSON objects and functions?

I want to learn how to use the JSON functions and find out which kinds of queries, if any, might be easier to code that way. Or, even if they are not too much more difficult, there would still be the benefit that no export would be required.

@ennoborg

if you clone that, and checout the proper branch, you can build and run that from source

Yes, I could learn to do that. All I really want is an already-converted copy of the “example” database, or any other sample.

[UlrichDemlehner]

@UlrichDemlehner

Agreed. I’ll post my test results in a new thread.

But before I do that, I’m coming back to the initial error with PostgreSQL. After playing around a bit I noticed that the error comes back when trying to access another tree. The sequence is:

(1) I work with a tree in 5.2.2 on PostgreSQL.

(2) I want to open an already existing another tree in 5.2.2 on PostgreSQL. This tree has been worked with before so I know for sure that the tree is ok and can be accessed.

(3) I enter the PostgreSQL user-id/password and the old error (some byte position) pops up.

(4) I will not help if I restart Gramps. If I check the open connections with the PostgreSQL server in pgAdmin, I see that a session for the old tree is still open though it should automatically be shut down when trying to open another tree or when closing Gramps (at least that’s what I’d assume).

(5) In that situation pgAdmin says that a query from the old session is still running:

/*pga4dash*/
SELECT 'session_stats' AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 117574)) AS "Total",
   (SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = 'active' AND datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 117574))  AS "Active",
   (SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = 'idle' AND datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 117574))  AS "Idle"
) t
UNION ALL
SELECT 'tps_stats' AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 117574)) AS "Transactions",
   (SELECT sum(xact_commit) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 117574)) AS "Commits",

It doesn’t help if I kill that session in pgAdmin.

(6) It doesn’t help either if I stop the PostgreSQL service completely and restart it. So it appears that only a full reboot will help.