trigger-based import¶

this does what our XML mappings currently do, but uses Postgres triggers instead of XML to normalize the denormalized row

special considerations¶

dup-select¶

because trigger normalization uses triggers, the dup-select operation must be implemented within Postgres as a trigger, rather than externally as in column-based import

NOT NULL columns used in unique constraints¶

since a row can be validly identified using only the columns in one unique constraint, the stub entry for a referenced table (which is intended to resolve to an existing row) may validly be missing columns. unfortunately, when this is the case, Postgres will raise a NOT NULL constraint error before even checking the unique constraint, preventing dup-select from working correctly (because it does not receive the correct error message).

to fix this, the applicable NOT NULL constraints can be translated to constraint triggers, which are run after the unique constraints are checked (and thus don't prevent the correct error from being generated). alternatively, NOT NULL constraint errors can simply be ignored when a (derived) pkey is successfully autopopulated, because only the pkey is needed in order for dup-select to be able to return something.

translated constraints for the errors tables¶

in column-based import, these are currently mapped to the input schema by put_table(), using map_expr(). under trigger-based import, this would instead be done in Postgres using expression substitution, which is a more general technique.