Fix test not detecting tables with missing primary keys and missing replica identities, then add more replica identities. (#16647)

* Fix the CI query that did not detect all cases of missing primary keys

* Add more missing REPLICA IDENTITY entries

* Newsfile

Signed-off-by: Olivier Wilkinson (reivilibre) <oliverw@matrix.org>

---------

Signed-off-by: Olivier Wilkinson (reivilibre) <oliverw@matrix.org>

1 files changed, 4 insertions, 3 deletions

diff --git a/tests/storage/test_database.py b/tests/storage/test_database.py
index d60176b1d4..aa8c76f187 100644
--- a/tests/storage/test_database.py
+++ b/tests/storage/test_database.py
@@ -313,9 +313,10 @@ class PostgresReplicaIdentityTestCase(unittest.HomeserverTestCase):
                     AND table_schema not in ('pg_catalog', 'information_schema')
                     AND NOT EXISTS (
                         SELECT 1
-                        FROM information_schema.key_column_usage kcu
-                        WHERE kcu.table_name = tbl.table_name
-                            AND kcu.table_schema = tbl.table_schema
+                        FROM information_schema.table_constraints tc
+                        WHERE tc.constraint_type = 'PRIMARY KEY'
+                            AND tc.table_schema = tbl.table_schema
+                            AND tc.table_name = tbl.table_name
                     )
             )
             SELECT pg_class.oid::regclass FROM tables_no_pkey INNER JOIN pg_class ON pg_class.oid::regclass = table_name::regclass