Project

General

Profile

« Previous | Next » 

Revision 7180

Added by Aaron Marcuse-Kubitza over 11 years ago

sql_io.py: put_table(): DuplicateKeyException: Uniquifying input table to avoid internal duplicate keys: Also filter out duplicate rows in the out_table, so that they don't create duplicate key errors and the resulting index holes

View differences:

inputs/ACAD/Specimen/logs/steps.by_col.log.sql
1 1 
+ date
2 
Fri Jan 11 05:47:34 PST 2013
2 
Fri Jan 11 05:50:11 PST 2013
3 3 
+ time env commit=1 env in_database=vegbien in_schema=ACAD in_table=Specimen out_database=vegbien ../../map Specimen/VegBIEN.csv
4 4 
Processing input rows 0-99
5 5 
Using 1 parallel CPUs
......
2022 2022 
DISTINCT ON ("in#11"."ACAD.Specimen.recordedBy")
2023 2023 
"in#11".*
2024 2024 
FROM "in#11"
2025 
LEFT JOIN "party" ON
2026 
"party"."source_id" = 1
2027 
AND COALESCE("party"."organizationname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
2028 
AND COALESCE("party"."fullname", CAST('\N' AS text)) = COALESCE("in#11"."ACAD.Specimen.recordedBy", CAST('\N' AS text))
2029 
AND COALESCE("party"."surname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
2030 
AND COALESCE("party"."givenname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
2031 
AND COALESCE("party"."middlename", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
2032 
AND COALESCE("party"."suffix", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
2033 
WHERE "party"."party_id" IS NULL
2025 2034 
</code></pre>
2026 2035 
# Trying to insert new rows
2027 2036 
<pre><code class="SQL">
......
2513 2522 
DISTINCT ON ("in#15"."ACAD.Specimen.institutionCode")
2514 2523 
"in#15".*
2515 2524 
FROM "in#15"
2525 
LEFT JOIN "sourcelist" ON
2526 
"sourcelist"."source_id" = 1
2527 
AND "sourcelist"."name" = "in#15"."ACAD.Specimen.institutionCode"
2528 
WHERE "sourcelist"."sourcelist_id" IS NULL
2516 2529 
</code></pre>
2517 2530 
# Trying to insert new rows
2518 2531 
<pre><code class="SQL">
......
2734 2747 
DISTINCT ON ("in#17"."sourcelist_pkeys.out.sourcelist_id", "in#17"."_split(institutionCode).result")
2735 2748 
"in#17".*
2736 2749 
FROM "in#17"
2750 
LEFT JOIN "sourcename" ON
2751 
"sourcename"."sourcelist_id" = "in#17"."sourcelist_pkeys.out.sourcelist_id"
2752 
AND "sourcename"."name" = "in#17"."_split(institutionCode).result"
2753 
WHERE "sourcename"."sourcelist_id" IS NULL
2737 2754 
</code></pre>
2738 2755 
# Trying to insert new rows
2739 2756 
<pre><code class="SQL">
......
3247 3264 
DISTINCT ON ("in#21"."ACAD.Specimen.identifiedBy")
3248 3265 
"in#21".*
3249 3266 
FROM "in#21"
3267 
LEFT JOIN "party" ON
3268 
"party"."source_id" = 1
3269 
AND COALESCE("party"."organizationname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
3270 
AND COALESCE("party"."fullname", CAST('\N' AS text)) = COALESCE("in#21"."ACAD.Specimen.identifiedBy", CAST('\N' AS text))
3271 
AND COALESCE("party"."surname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
3272 
AND COALESCE("party"."givenname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
3273 
AND COALESCE("party"."middlename", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
3274 
AND COALESCE("party"."suffix", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
3275 
WHERE "party"."party_id" IS NULL
3250 3276 
</code></pre>
3251 3277 
# Trying to insert new rows
3252 3278 
<pre><code class="SQL">
......
3413 3439 
DISTINCT ON ("in#22"."ACAD.Specimen.kingdom")
3414 3440 
"in#22".*
3415 3441 
FROM "in#22"
3442 
LEFT JOIN "taxonlabel" ON
3443 
COALESCE("taxonlabel"."parent_id", CAST(2147483647 AS integer)) = COALESCE(NULL, CAST(2147483647 AS integer))
3444 
AND COALESCE("taxonlabel"."taxonepithet", CAST('\N' AS text)) = COALESCE("in#22"."ACAD.Specimen.kingdom", CAST('\N' AS text))
3445 
AND COALESCE("taxonlabel"."rank", CAST('unknown' AS taxonrank)) = 'kingdom'
3446 
AND "taxonlabel"."source_id" = 1
3447 
AND COALESCE("taxonlabel"."sourceaccessioncode", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
3448 
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
3449 
WHERE "taxonlabel"."taxonlabel_id" IS NULL
3416 3450 
</code></pre>
3417 3451 
# Trying to insert new rows
3418 3452 
<pre><code class="SQL">
......
3584 3618 
DISTINCT ON ("in#23"."taxonlabel[rank='kingdom'].out.taxonlabel_id", "in#23"."ACAD.Specimen.phylum")
3585 3619 
"in#23".*
3586 3620 
FROM "in#23"
3621 
LEFT JOIN "taxonlabel" ON
3622 
COALESCE("taxonlabel"."parent_id", CAST(2147483647 AS integer)) = COALESCE("in#23"."taxonlabel[rank='kingdom'].out.taxonlabel_id", CAST(2147483647 AS integer))
3623 
AND COALESCE("taxonlabel"."taxonepithet", CAST('\N' AS text)) = COALESCE("in#23"."ACAD.Specimen.phylum", CAST('\N' AS text))
3624 
AND COALESCE("taxonlabel"."rank", CAST('unknown' AS taxonrank)) = 'phylum'
3625 
AND "taxonlabel"."source_id" = 1
3626 
AND COALESCE("taxonlabel"."sourceaccessioncode", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
3627 
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
3628 
WHERE "taxonlabel"."taxonlabel_id" IS NULL
3587 3629 
</code></pre>
3588 3630 
# Trying to insert new rows
3589 3631 
<pre><code class="SQL">
......
3756 3798 
DISTINCT ON ("in#24"."taxonlabel[rank='phylum'].out.taxonlabel_id", "in#24"."ACAD.Specimen.class")
3757 3799 
"in#24".*
3758 3800 
FROM "in#24"
3801 
LEFT JOIN "taxonlabel" ON
3802 
COALESCE("taxonlabel"."parent_id", CAST(2147483647 AS integer)) = COALESCE("in#24"."taxonlabel[rank='phylum'].out.taxonlabel_id", CAST(2147483647 AS integer))
3803 
AND COALESCE("taxonlabel"."taxonepithet", CAST('\N' AS text)) = COALESCE("in#24"."ACAD.Specimen.class", CAST('\N' AS text))
3804 
AND COALESCE("taxonlabel"."rank", CAST('unknown' AS taxonrank)) = 'class'
3805 
AND "taxonlabel"."source_id" = 1
3806 
AND COALESCE("taxonlabel"."sourceaccessioncode", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
3807 
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
3808 
WHERE "taxonlabel"."taxonlabel_id" IS NULL
3759 3809 
</code></pre>
3760 3810 
# Trying to insert new rows
3761 3811 
<pre><code class="SQL">
......
3928 3978 
DISTINCT ON ("in#25"."taxonlabel[rank='class'].out.taxonlabel_id", "in#25"."ACAD.Specimen.order")
3929 3979 
"in#25".*
3930 3980 
FROM "in#25"
3981 
LEFT JOIN "taxonlabel" ON
3982 
COALESCE("taxonlabel"."parent_id", CAST(2147483647 AS integer)) = COALESCE("in#25"."taxonlabel[rank='class'].out.taxonlabel_id", CAST(2147483647 AS integer))
3983 
AND COALESCE("taxonlabel"."taxonepithet", CAST('\N' AS text)) = COALESCE("in#25"."ACAD.Specimen.order", CAST('\N' AS text))
3984 
AND COALESCE("taxonlabel"."rank", CAST('unknown' AS taxonrank)) = 'order'
3985 
AND "taxonlabel"."source_id" = 1
3986 
AND COALESCE("taxonlabel"."sourceaccessioncode", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
3987 
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
3988 
WHERE "taxonlabel"."taxonlabel_id" IS NULL
3931 3989 
</code></pre>
3932 3990 
# Trying to insert new rows
3933 3991 
<pre><code class="SQL">
......
4100 4158 
DISTINCT ON ("in#26"."taxonlabel[rank='order'].out.taxonlabel_id", "in#26"."ACAD.Specimen.family")
4101 4159 
"in#26".*
4102 4160 
FROM "in#26"
4161 
LEFT JOIN "taxonlabel" ON
4162 
COALESCE("taxonlabel"."parent_id", CAST(2147483647 AS integer)) = COALESCE("in#26"."taxonlabel[rank='order'].out.taxonlabel_id", CAST(2147483647 AS integer))
4163 
AND COALESCE("taxonlabel"."taxonepithet", CAST('\N' AS text)) = COALESCE("in#26"."ACAD.Specimen.family", CAST('\N' AS text))
4164 
AND COALESCE("taxonlabel"."rank", CAST('unknown' AS taxonrank)) = 'family'
4165 
AND "taxonlabel"."source_id" = 1
4166 
AND COALESCE("taxonlabel"."sourceaccessioncode", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
4167 
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
4168 
WHERE "taxonlabel"."taxonlabel_id" IS NULL
4103 4169 
</code></pre>
4104 4170 
# Trying to insert new rows
4105 4171 
<pre><code class="SQL">
......
4272 4338 
DISTINCT ON ("in#27"."taxonlabel[rank='family'].out.taxonlabel_id", "in#27"."ACAD.Specimen.genus")
4273 4339 
"in#27".*
4274 4340 
FROM "in#27"
4341 
LEFT JOIN "taxonlabel" ON
4342 
COALESCE("taxonlabel"."parent_id", CAST(2147483647 AS integer)) = COALESCE("in#27"."taxonlabel[rank='family'].out.taxonlabel_id", CAST(2147483647 AS integer))
4343 
AND COALESCE("taxonlabel"."taxonepithet", CAST('\N' AS text)) = COALESCE("in#27"."ACAD.Specimen.genus", CAST('\N' AS text))
4344 
AND COALESCE("taxonlabel"."rank", CAST('unknown' AS taxonrank)) = 'genus'
4345 
AND "taxonlabel"."source_id" = 1
4346 
AND COALESCE("taxonlabel"."sourceaccessioncode", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
4347 
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
4348 
WHERE "taxonlabel"."taxonlabel_id" IS NULL
4275 4349 
</code></pre>
4276 4350 
# Trying to insert new rows
4277 4351 
<pre><code class="SQL">
......
4444 4518 
DISTINCT ON ("in#28"."taxonlabel[rank='genus'].out.taxonlabel_id", "in#28"."ACAD.Specimen.specificEpithet")
4445 4519 
"in#28".*
4446 4520 
FROM "in#28"
4521 
LEFT JOIN "taxonlabel" ON
4522 
COALESCE("taxonlabel"."parent_id", CAST(2147483647 AS integer)) = COALESCE("in#28"."taxonlabel[rank='genus'].out.taxonlabel_id", CAST(2147483647 AS integer))
4523 
AND COALESCE("taxonlabel"."taxonepithet", CAST('\N' AS text)) = COALESCE("in#28"."ACAD.Specimen.specificEpithet", CAST('\N' AS text))
4524 
AND COALESCE("taxonlabel"."rank", CAST('unknown' AS taxonrank)) = 'species'
4525 
AND "taxonlabel"."source_id" = 1
4526 
AND COALESCE("taxonlabel"."sourceaccessioncode", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
4527 
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
4528 
WHERE "taxonlabel"."taxonlabel_id" IS NULL
4447 4529 
</code></pre>
4448 4530 
# Trying to insert new rows
4449 4531 
<pre><code class="SQL">
......
5062 5144 
DISTINCT ON ("in#33"."taxonlabel[rank='species'].out.taxonlabel_id", "in#33"."ACAD.Specimen.infraspecificEpithet", "in#33"."ACAD.Specimen.taxonRank::taxonrank")
5063 5145 
"in#33".*
5064 5146 
FROM "in#33"
5147 
LEFT JOIN "taxonlabel" ON
5148 
COALESCE("taxonlabel"."parent_id", CAST(2147483647 AS integer)) = COALESCE("in#33"."taxonlabel[rank='species'].out.taxonlabel_id", CAST(2147483647 AS integer))
5149 
AND COALESCE("taxonlabel"."taxonepithet", CAST('\N' AS text)) = COALESCE("in#33"."ACAD.Specimen.infraspecificEpithet", CAST('\N' AS text))
5150 
AND COALESCE("taxonlabel"."rank", CAST('unknown' AS taxonrank)) = COALESCE("in#33"."ACAD.Specimen.taxonRank::taxonrank", CAST('unknown' AS taxonrank))
5151 
AND "taxonlabel"."source_id" = 1
5152 
AND COALESCE("taxonlabel"."sourceaccessioncode", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
5153 
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
5154 
WHERE "taxonlabel"."taxonlabel_id" IS NULL
5065 5155 
</code></pre>
5066 5156 
# Trying to insert new rows
5067 5157 
<pre><code class="SQL">
......
6206 6296 
DISTINCT ON ("in#42"."_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_jo.result")
6207 6297 
"in#42".*
6208 6298 
FROM "in#42"
6299 
LEFT JOIN "taxonlabel" ON
6300 
"taxonlabel"."source_id" = 1
6301 
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE("in#42"."_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_jo.result", CAST('\N' AS text))
6302 
WHERE "taxonlabel"."taxonlabel_id" IS NULL
6209 6303 
</code></pre>
6210 6304 
# Trying to insert new rows
6211 6305 
<pre><code class="SQL">
......
6341 6435 
DISTINCT ON ("in#43"."taxonlabel[rank=taxonRank]#1.out.taxonlabel_id")
6342 6436 
"in#43".*
6343 6437 
FROM "in#43"
6438 
LEFT JOIN "taxonverbatim" ON
6439 
"taxonverbatim"."taxonlabel_id" = "in#43"."taxonlabel[rank=taxonRank]#1.out.taxonlabel_id"
6440 
AND COALESCE("taxonverbatim"."morphospecies", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
6441 
WHERE "taxonverbatim"."taxonverbatim_id" IS NULL
6344 6442 
</code></pre>
6345 6443 
# Trying to insert new rows
6346 6444 
<pre><code class="SQL">
......
7029 7127 
DISTINCT ON ("in#47"."_nullIf(decimalLatitude).result::double", "in#47"."_nullIf(decimalLongitude).result::double")
7030 7128 
"in#47".*
7031 7129 
FROM "in#47"
7130 
LEFT JOIN "coordinates" ON
7131 
"coordinates"."source_id" = 1
7132 
AND "coordinates"."latitude_deg" = "in#47"."_nullIf(decimalLatitude).result::double"
7133 
AND "coordinates"."longitude_deg" = "in#47"."_nullIf(decimalLongitude).result::double"
7134 
AND COALESCE("coordinates"."verbatimlatitude", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
7135 
AND COALESCE("coordinates"."verbatimlongitude", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
7136 
AND COALESCE("coordinates"."verbatimcoordinates", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
7137 
AND COALESCE("coordinates"."footprintgeometry_dwc", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
7138 
WHERE "coordinates"."coordinates_id" IS NULL
7032 7139 
</code></pre>
7033 7140 
# Trying to insert new rows
7034 7141 
<pre><code class="SQL">
......
7529 7636 
DISTINCT ON ("in#50"."_nullIf(decimalLatitude)#1.result::double", "in#50"."_nullIf(decimalLongitude)#1.result::double")
7530 7637 
"in#50".*
7531 7638 
FROM "in#50"
7639 
LEFT JOIN "coordinates" ON
7640 
"coordinates"."source_id" = 25
7641 
AND "coordinates"."latitude_deg" = "in#50"."_nullIf(decimalLatitude)#1.result::double"
7642 
AND "coordinates"."longitude_deg" = "in#50"."_nullIf(decimalLongitude)#1.result::double"
7643 
AND COALESCE("coordinates"."verbatimlatitude", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
7644 
AND COALESCE("coordinates"."verbatimlongitude", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
7645 
AND COALESCE("coordinates"."verbatimcoordinates", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
7646 
AND COALESCE("coordinates"."footprintgeometry_dwc", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
7647 
WHERE "coordinates"."coordinates_id" IS NULL
7532 7648 
</code></pre>
7533 7649 
# Trying to insert new rows
7534 7650 
<pre><code class="SQL">
......
7664 7780 
DISTINCT ON ("in#51"."ACAD.Specimen.country", "in#51"."ACAD.Specimen.stateProvince", "in#51"."ACAD.Specimen.county", "in#51"."coordinates_pkeys#1.out.coordinates_id")
7665 7781 
"in#51".*
7666 7782 
FROM "in#51"
7783 
LEFT JOIN "place" ON
7784 
"place"."source_id" = 25
7785 
AND COALESCE("place"."continent", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
7786 
AND COALESCE("place"."country", CAST('\N' AS text)) = COALESCE("in#51"."ACAD.Specimen.country", CAST('\N' AS text))
7787 
AND COALESCE("place"."stateprovince", CAST('\N' AS text)) = COALESCE("in#51"."ACAD.Specimen.stateProvince", CAST('\N' AS text))
7788 
AND COALESCE("place"."county", CAST('\N' AS text)) = COALESCE("in#51"."ACAD.Specimen.county", CAST('\N' AS text))
7789 
AND COALESCE("place"."coordinates_id", CAST(2147483647 AS integer)) = COALESCE("in#51"."coordinates_pkeys#1.out.coordinates_id", CAST(2147483647 AS integer))
7790 
WHERE "place"."place_id" IS NULL
7667 7791 
</code></pre>
7668 7792 
# Trying to insert new rows
7669 7793 
<pre><code class="SQL">
......
7851 7975 
DISTINCT ON ("in#52"."ACAD.Specimen.continent", "in#52"."ACAD.Specimen.country", "in#52"."ACAD.Specimen.stateProvince", "in#52"."ACAD.Specimen.county", "in#52"."coordinates_pkeys.out.coordinates_id")
7852 7976 
"in#52".*
7853 7977 
FROM "in#52"
7978 
LEFT JOIN "place" ON
7979 
"place"."source_id" = 1
7980 
AND COALESCE("place"."continent", CAST('\N' AS text)) = COALESCE("in#52"."ACAD.Specimen.continent", CAST('\N' AS text))
7981 
AND COALESCE("place"."country", CAST('\N' AS text)) = COALESCE("in#52"."ACAD.Specimen.country", CAST('\N' AS text))
7982 
AND COALESCE("place"."stateprovince", CAST('\N' AS text)) = COALESCE("in#52"."ACAD.Specimen.stateProvince", CAST('\N' AS text))
7983 
AND COALESCE("place"."county", CAST('\N' AS text)) = COALESCE("in#52"."ACAD.Specimen.county", CAST('\N' AS text))
7984 
AND COALESCE("place"."coordinates_id", CAST(2147483647 AS integer)) = COALESCE("in#52"."coordinates_pkeys.out.coordinates_id", CAST(2147483647 AS integer))
7985 
WHERE "place"."place_id" IS NULL
7854 7986 
</code></pre>
7855 7987 
# Trying to insert new rows
7856 7988 
<pre><code class="SQL">
......
7989 8121 
</code></pre>Inserted 1325 new rows into database
7990 8122 
Processed 100 input rows
7991 8123 
Encountered 0 error(s)
7992 
       11.82 real         4.23 user         0.45 sys
8124 
       12.05 real         4.37 user         0.45 sys
lib/sql_io.py
693 693 
                return sql.value(sql.select(db, out_table, [out_pkey_col],
694 694 
                    join_cols, order_by=None))
695 695
696 
            # Uniquify input table to avoid internal duplicate keys
696 
            # Uniquify and filter input table to avoid (most) duplicate keys
697 
            # (Additional duplicates may be added concurrently and will be
698 
            # filtered out separately upon insert.)
697 699 
            insert_in_table = sql.distinct_table(db, insert_in_table,
698 
                join_cols.values())
700 
                join_cols.values(), [insert_in_table,
701 
                sql_gen.Join(out_table, join_cols, sql_gen.filter_out)])
699 702 
            insert_in_tables.append(insert_in_table)
700 703 
        except sql.NullValueException, e:
701 704 
            if not log_exc(e): break

Also available in: Unified diff