problem¶

this applies to 358 location rows:

SELECT * FROM location
WHERE
    source_id = (SELECT source_by_shortname('VegBank'))
AND authorlocationcode IS NULL

these location rows are not present in the input data:

SELECT * FROM "VegBank".plot
WHERE "locationID" IN
(
SELECT sourceaccessioncode::integer FROM location
WHERE
    source_id = (SELECT source_by_shortname('VegBank'))
AND authorlocationcode IS NULL
)
; -- 0 rows

this is most likely due to a missing foreign key at the time the embargoed plots are cascadingly deleted

verify this:

SELECT * FROM "VegBank".observation_ WHERE "locationID" = 80872; -- 1 row
SELECT * FROM "VegBank".observation WHERE plot_id = 80872; -- 0 rows
SELECT * FROM "VegBank".plot WHERE "locationID" = 80872; -- 0 rows
SELECT * FROM "VegBank".plot_ WHERE "locationID" = 80872; -- 0 rows

this is in fact a dangling row, but it occurs only in observation_, so the bug is in the creation of observation_ from observation

try remaking it:

rm=1 inputs/VegBank/observation_/run

the dangling rows are now gone, so the problem is solved:

SELECT * FROM "VegBank".observation_
WHERE "locationID" IN
(
SELECT sourceaccessioncode::integer FROM location
WHERE
    source_id = (SELECT source_by_shortname('VegBank'))
AND authorlocationcode IS NULL
)
; -- 0 rows

refresh VegBank to propagate the fix to the normalized DB:

inputs/VegBank/taxon_observation.\*\*/run
make inputs/VegBank/reimport_scrub by_col=1 &