Bug #965
Updated by Aaron Marcuse-Kubitza over 9 years ago
h3. problem this applies to 358 @location@ rows: <pre><code class="SQL"> SELECT * FROM location WHERE source_id = (SELECT source_by_shortname('VegBank')) AND authorlocationcode IS NULL </code></pre> these @location@ rows are _not_ present in the input data: <pre><code class="SQL"> 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 returns no rows </code></pre> this is most likely due to a missing foreign key at the time the embargoed plots are cascadingly deleted verify this: <pre><code class="SQL"> 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 </code></pre> 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: <pre> rm=1 inputs/VegBank/observation_/run </pre> the dangling rows are row is now gone, so the problem is solved: gone: <pre><code class="SQL"> SELECT * FROM "VegBank".observation_ WHERE "locationID" IN ( SELECT sourceaccessioncode::integer FROM location WHERE source_id = (SELECT source_by_shortname('VegBank')) AND authorlocationcode IS NULL ) ; 80872; -- 0 rows </code></pre> refresh VegBank to propagate verify that this fixes the fix to the normalized DB: <pre> make inputs/VegBank/reimport_scrub by_col=1 & </pre> problem