Fix cta-catalogue-schema-verify index checker for PostgreSQL
cta-catalogue-schema-verify
was modified to check that all columns used in a foreign key constraint have an index (#1158 (closed)).
This works on Oracle DBs, but does not work on PostgreSQL:
Preparing CTA configuration for tests
Schema version : 10.0
Checking indexes...
SUCCESS
Checking tables, columns and constraints...
SUCCESS
Status of the checking : SUCCESS
Aborting: executeQuery failed: executeQuery failed for SQL statement SELECT A.TABLE_NAME || '.' || A.COLUMN_NAME AS FQ_COL_NAME FROM USER_CONS_COL...: Preparing a statement: Database library reported: ERROR: relation "user_cons_columns" does not existLINE 1: ..._NAME || '.' || A.COLUMN_NAME AS FQ_COL_NAME FROM USER_CONS_... ^ (DB Result Status:7 SQLState:42P01)
ERROR: failed to verify the catalogue DB schema
ERROR: failed to prepare namespace for the tests
This is because Postgres has a different mechanism to obtain lists of constraints and indexes.
-
Check if Postgres has the same requirement to have indexes for foreign key constraints. If Postgres takes care of this by itself, this check is not necessary. -
If the indexes are necessary, add a Postgres-specific query to get the list of columns which are missing an index.
Edited by Michael Davis