renaming the table to "cache_occurrences_functional" and retrying to save i get this:

There was an SQL error: ERROR: column "created_on" of relation "cache_occurrences_functional" does not exist LINE 4: date_start, date_end, date_type, created_on, upd... ^ - INSERT INTO cache_occurrences_functional( id, sample_id, website_id, survey_id, input_form, location_id, location_name, public_geom, date_start, date_end, date_type, created_on, updated_on, verified_on, created_by_id, group_id, taxa_taxon_list_id, preferred_taxa_taxon_list_id, taxon_meaning_id, taxa_taxon_list_external_key, family_taxa_taxon_list_id, taxon_group_id, taxon_rank_sort_order, record_status, record_substatus, certainty, query, sensitive, release_status, marine_flag, data_cleaner_result, training, zero_abundance, licence_id) SELECT distinct on (o.id) o.id, o.sample_id, o.website_id, s.survey_id, COALESCE(sp.input_form, s.input_form), s.location_id, case when o.confidential=true or o.sensitivity_precision is not null or s.privacy_precision is not null then null else coalesce(l.name, s.location_name, lp.name, sp.location_name) end, reduce_precision(coalesce(s.geom, l.centroid_geom), o.confidential, greatest(o.sensitivity_precision, s.privacy_precision), case when s.entered_sref_system is null then l.centroid_sref_system else s.entered_sref_system end) as public_geom, s.date_start, s.date_end, s.date_type, o.created_on, o.updated_on, o.verified_on, o.created_by_id, s.group_id, o.taxa_taxon_list_id, cttl.preferred_taxa_taxon_list_id, cttl.taxon_meaning_id, cttl.external_key, cttl.family_taxa_taxon_list_id, cttl.taxon_group_id, cttl.taxon_rank_sort_order, o.record_status, o.record_substatus, case when certainty.sort_order is null then null when certainty.sort_order <100 then 'C' when certainty.sort_order <200 then 'L' else 'U' end, case when oc1.id is null or o.record_status in ('R','V') then null when oc2.id is null and o.updated_on<=oc1.created_on then 'Q' else 'A' end, o.sensitivity_precision is not null, o.release_status, cttl.marine_flag, case when o.last_verification_check_date is null then null else dc.id is null end, o.training, o.zero_abundance, s.licence_id FROM occurrences o LEFT JOIN cache_occurrences_functional co on co.id=o.id JOIN samples s ON s.id=o.sample_id AND s.deleted=false LEFT JOIN samples sp ON sp.id=s.parent_id AND sp.deleted=false LEFT JOIN locations l ON l.id=s.location_id AND l.deleted=false LEFT JOIN locations lp ON lp.id=sp.location_id AND lp.deleted=false JOIN cache_taxa_taxon_lists cttl ON cttl.id=o.taxa_taxon_list_id LEFT JOIN (occurrence_attribute_values oav JOIN termlists_terms certainty ON certainty.id=oav.int_value JOIN occurrence_attributes oa ON oa.id=oav.occurrence_attribute_id and oa.deleted='f' and oa.system_function='certainty' ) ON oav.occurrence_id=o.id AND oav.deleted='f' LEFT JOIN occurrence_comments oc1 ON oc1.occurrence_id=o.id AND oc1.deleted=false AND oc1.auto_generated=false AND oc1.query=true AND (o.verified_on IS NULL OR oc1.created_on>o.verified_on) LEFT JOIN occurrence_comments oc2 ON oc2.occurrence_id=o.id AND oc2.deleted=false AND oc2.auto_generated=false AND oc2.query=false AND (o.verified_on IS NULL OR oc2.created_on>o.verified_on) AND oc2.id>oc1.id LEFT JOIN occurrence_comments dc ON dc.occurrence_id=o.id AND dc.implies_manual_check_required=true AND dc.deleted=false WHERE o.deleted=false AND co.id IS NULL and o.id in (5435)

Hi Carlo,

The cache_occurrences_functional table is created as part of a significant restructure of the Indicia cache tables to improve performance. There are lots of changes and it is all scripted. It is first created in
\modules\cache_builder\db\version_1_0_0\201512221019_cache_tables_revisions.sql

I think something must have gone wrong with your warehouse upgrade. You may wish to look in the system table in the database to try to get an idea of what upgrades have been run on the core and the cache module.

Jim Bacon.