dup-return¶
The dup-return operation implements the SQL pseudo-statement INSERT IGNORE RETURNING. This is a cross between MySQL's INSERT IGNORE (which does not provide INSERT RETURNING functionality) and PostgreSQL's INSERT RETURNING (which throws an error on duplicate instead of returning the row).
This is an optimization1 which is only possible if you use natural pkeys2. If you instead use serial pkeys, you must use the slower dup-select instead.
Note that this optimization only happens when the pkey constraint itself is violated3. This is most often the case, because the pkey is generated from the fields used in unique constraints. However, the 2nd copy of a row will sometimes not fill in enough fields to cause the same pkey to be generated (e.g. it will provide enough to form id_by_source but not enough to form a natural key, causing id_by_source to be used for id instead). In this case, the algorithm must fall back to using dup-select to determine the actual pkey value used for the existing row.
1 it reduces the insert time by up to half because the index scan does not need to be performed twice, once to test the unique constraint and again (for some rows) to select the existing row
2 natural keys are a new thing for vegetation data (and for SQL databases in general). virtually all of VegBIEN's datasources use serial keys, so VegCore's switch to natural keys would be a significant step forward.
3 the pkey will be checked before the other unique constraints, so if it is violated, it will be the constraint to trigger the exception
.
PostgreSQL¶
This is implemented similarly to dup-select, but the INSERT RETURNING can just return the row it's trying to insert (which now has the pkey populated by a trigger), instead of needing to fetch the pkey for the existing row (which will be the same as the generated key)
Unfortunately, unlike dup-select, the pkey autopopulation triggers must be copied over to the inserter view, so that they run before the insert is tried.
in <9.1¶
Extract the function bodies of the pkey autopopulation triggers and paste them into the inserter function.
in 9.1+¶
Copy the pkey autopopulation triggers themselves to the inserter view. Using an INSTEAD OF trigger rather than an INSTEAD rule allows you to copy the entire trigger rather than having to extract the body of it and paste that into the inserter function.