Errors on localdb_1 initialisation: ERROR: relation "idetect_fact_api" does not exist

[simonwibberley]

Hi there,
There seems to be (at least) 2 missing fields in a database schema (or something), idetect_fact_api and simple_english which generates multiple errors like the below.

Have I missed an initialisation step?

Many thanks,

localdb_1    | ERROR:  relation "idetect_fact_api" does not exist at character 917
localdb_1    | STATEMENT:  SELECT count(*) AS count_1 
localdb_1    | 	FROM (SELECT idetect_fact_api.document_identifier AS idetect_fact_api_document_identifier, idetect_fact_api.source_common_name AS idetect_fact_api_source_common_name, idetect_fact_api.gdelt_day AS idetect_fact_api_gdelt_day, idetect_fact_api.fact AS idetect_fact_api_fact, idetect_fact_api.unit AS idetect_fact_api_unit, idetect_fact_api.term AS idetect_fact_api_term, idetect_fact_api.specific_reported_figure AS idetect_fact_api_specific_reported_figure, idetect_fact_api.vague_reported_figure AS idetect_fact_api_vague_reported_figure, idetect_fact_api.iso3 AS idetect_fact_api_iso3, idetect_fact_api.location AS idetect_fact_api_location, idetect_fact_api.gkg_id AS idetect_fact_api_gkg_id, idetect_fact_api.category AS idetect_fact_api_category, idetect_fact_api.content_id AS idetect_fact_api_content_id, idetect_fact_api.location_ids_num AS idetect_fact_api_location_ids_num 
localdb_1    | 	FROM idetect_fact_api) AS anon_1

and

localdb_1    | ERROR:  text search configuration "simple_english" does not exist at character 4250
...

[simonwibberley]

Is it potentially something to do with source/data/update.sql not being run on init?

[milanoleonardo]

Hi @simonwibberley, yes, source/data/update.sql should do the trick

[simonwibberley]

Hi @milanoleonardo thanks for your reply!

Running the script on the db in the instance fixed the idetect_fact_api errors; however, there are two remaining errors:

localdb_1    | ERROR:  relation "idetect_validation" does not exist at character 3209
...
localdb_1    | ERROR:  text search configuration "simple_english" does not exist at character 4250

The update.sql script throws a few errors, and simple_english still cannot be found:

root@42735a9d5e4e:/docker-entrypoint-initdb.d# psql -U postgres idetect -f update.sql 
DROP MATERIALIZED VIEW
DROP MATERIALIZED VIEW
SELECT 0
ALTER TABLE
SELECT 0
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
psql:update.sql:59: WARNING:  hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
CREATE INDEX
CREATE INDEX
psql:update.sql:65: ERROR:  column "content_ts" of relation "idetect_document_contents" already exists
psql:update.sql:68: ERROR:  relation "idetect_document_contents_gin" already exists
psql:update.sql:71: ERROR:  duplicate key value violates unique constraint "pg_ts_dict_dictname_index"
DETAIL:  Key (dictname, dictnamespace)=(simple_english, 2200) already exists.
psql:update.sql:74: ERROR:  duplicate key value violates unique constraint "pg_ts_config_cfgname_index"
DETAIL:  Key (cfgname, cfgnamespace)=(simple_english, 2200) already exists.
ALTER TEXT SEARCH CONFIGURATION
UPDATE 0
psql:update.sql:85: ERROR:  extension "intarray" already exists
psql:update.sql:88: ERROR:  relation "gkg_identifier_idx" already exists
psql:update.sql:114: ERROR:  relation "idetect_map_wroroot@42735a9d5e4e:/docker-entroot@42735a9d5e4e:/docker-entrroot@4273rroot@42735a9d5e4e:/docker-entrypoint-initdb.d# sw206@inf900439:~/git/idetect/docker/postgres/docker-entrypoint-initdb.d$ docker exec -it idetect_localdb_1 bash

and the corresponding log from the image:

localdb_1    | WARNING:  hash indexes are not WAL-logged and their use is discouraged
localdb_1    | ERROR:  column "content_ts" of relation "idetect_document_contents" already exists
localdb_1    | STATEMENT:  ALTER TABLE idetect_document_contents ADD COLUMN content_ts tsvector;
localdb_1    | ERROR:  relation "idetect_document_contents_gin" already exists
localdb_1    | STATEMENT:  CREATE INDEX idetect_document_contents_gin
localdb_1    | 	  ON idetect_document_contents using GIN (content_ts);
localdb_1    | ERROR:  duplicate key value violates unique constraint "pg_ts_dict_dictname_index"
localdb_1    | DETAIL:  Key (dictname, dictnamespace)=(simple_english, 2200) already exists.
localdb_1    | STATEMENT:  CREATE TEXT SEARCH DICTIONARY simple_english
localdb_1    | 	   (TEMPLATE = pg_catalog.simple, STOPWORDS = english);
localdb_1    | ERROR:  duplicate key value violates unique constraint "pg_ts_config_cfgname_index"
localdb_1    | DETAIL:  Key (cfgname, cfgnamespace)=(simple_english, 2200) already exists.
localdb_1    | STATEMENT:  CREATE TEXT SEARCH CONFIGURATION simple_english
localdb_1    | 	   (copy = english);
localdb_1    | ERROR:  extension "intarray" already exists
localdb_1    | STATEMENT:  CREATE EXTENSION intarray;
localdb_1    | ERROR:  relation "gkg_identifier_idx" already exists
localdb_1    | STATEMENT:  CREATE INDEX gkg_identifier_idx on gkg (document_identifier);
localdb_1    | ERROR:  relation "idetect_map_week_mview" already exists
localdb_1    | STATEMENT:  CREATE MATERIALIZED VIEW idetect_map_week_mview AS (
localdb_1    | 	          WITH input_table AS (
localdb_1    | 	         SELECT date_trunc('week'::text, (to_date(substr((gkg.date)::text, 1, 8), 'YYYYMMDD'::text))::timestamp with time zone) AS gdelt_day,
localdb_1    | 	            idetect_locations.id AS location_id,
localdb_1    | 	            split_part((idetect_locations.latlong)::text, ','::text, 1) AS latitude,
localdb_1    | 	            split_part((idetect_locations.latlong)::text, ','::text, 2) AS longitude,
localdb_1    | 	            idetect_analyses.category,
localdb_1    | 	            count(*) AS count
localdb_1    | 	           FROM (((((idetect_facts
localdb_1    | 	             JOIN idetect_fact_locations ON ((idetect_facts.id = idetect_fact_locations.fact)))
localdb_1    | 	             JOIN idetect_locations ON ((idetect_fact_locations.location = idetect_locations.id)))
localdb_1    | 	             JOIN idetect_analysis_facts ON ((idetect_facts.id = idetect_analysis_facts.fact)))
localdb_1    | 	             JOIN idetect_analyses ON ((idetect_analysis_facts.analysis = idetect_analyses.gkg_id)))
localdb_1    | 	             JOIN gkg ON ((idetect_analyses.gkg_id = gkg.id)))
localdb_1    | 	            WHERE ((idetect_facts.specific_reported_figure < 100000000) AND (idetect_analyses.category IS NOT NULL))
localdb_1    | 	          GROUP BY (date_trunc('week'::text, (to_date(substr((gkg.date)::text, 1, 8), 'YYYYMMDD'::text))::timestamp with time zone)), idetect_locations.id, (split_part((idetect_locations.latlong)::text, ','::text, 1)), (split_part((idetect_locations.latlong)::text, ','::text, 2)), idetect_analyses.category
localdb_1    | 	          ORDER BY (date_trunc('week'::text, (to_date(substr((gkg.date)::text, 1, 8), 'YYYYMMDD'::text))::timestamp with time zone))
localdb_1    | 	        ), entries AS (
localdb_1    | 	         SELECT row_to_json(input_table.*) AS entry
localdb_1    | 	           FROM input_table
localdb_1    | 	        )
localdb_1    | 	 SELECT jsonb_agg(entries.entry) AS entries
localdb_1    | 	   FROM entries
localdb_1    | 	);

Should the script drop simple_english before creating it? I can't see a reference to idetect_validation anywhere.

Thanks for your help!

S

[milanoleonardo]

Regarding the validation_value that's something internal to IDMC (the reference is in fact_api.py), you can try to start only the workers and it should be fine.
You can try to drop manually simple_english and recreate it, let me know if it works!

[simonwibberley]

It turns out that simple_english was missing on the idetect_test database, so was failing the unit test on start.

A load of the unit tests also fail: unittests_1 | FAILED (failures=14, errors=7, skipped=1)

Otherwise it seems to be functioning!