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 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 now gone, so the problem is solved: 

 <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 
 ) 
 ; -- 0 rows 
 </code></pre> 

 refresh VegBank to propagate the fix to the normalized DB: 

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

Back