GeoNetwork+PostGIS requires gt_pk_metadata (primary key metadata) because query to get metadata db fails

[simonp]

Ever more complex: this is all to do with geotools deciding whether the spatialindex table is writable or not (ie. can it be used as a feature store as opposed to a read only feature source). If geotools decides that spatialindex is not writable then everything fails as a read only feature source is useless to GeoNetwork. One of the tests to determine whether spatialindex is writable is identifying its primary keys.

Turns out that the gt_pk_metadata table we've been discussing is the first choice for geotools in identifying primary keys of spatialindex ie. if it exists then it is used. If it doesn't exist then geotools uses java.sql.Database to get info (including primary keys) from the database. For postgres/postgis it seems that the query to get the primary keys actually fails! What this means is that we have to have the gt_pk_metadata table present, otherwise geotools can't identify the primary keys and will decide that spatialindex is not writable causing GeoNetwork to fail.

Hmm. Ok - I've rigged up a fix for the create-db-postgis.sql file that creates gt_pk_metadata and populates it correctly (taking into account it might already exist etc). However I'll try a few other paths to see whether we can do this the normal/proper way before suggesting we apply this fix (already tried lots of jdbc driver versions - which didn't help) as we shouldn't need to build/maintain the gt_pk_metadata table.

[simonp]

Seems likely that this is a problem is caused by order of search for primary keys in postgis datastore, which is: first check is a query for gt_pk_metadata (MetadataTablePrimaryKeyFinder) and second check is a query for the database metadata (HeuristicPrimaryKeyFinder) - seems that the first check for gt_pk_metadata aborts the transaction if that table doesn't exist so the second check for database metadata does not return any results. Proved by disabling the first check in geotools - this allows the second check to succeed and GeoNetwork is happy.

[simonp]

This appears to be a bug in geotools (hate to say this as geotools is excellent). Is present in 8.2 and in 8.4 (the latest). Easiest fix for GeoNetwork 2.8.x and later is to create an empty gt_pk_metadata table (if not already present) so that the first metadata primary key check does not abort the transaction and the second metadata primary key check can succeed. This will be a change to the create-db-postgis.sql file only.

[simonp]

Fixed in commit ea018d2ed8561aba285d136ab9a4c4b2aa385dfd