Re: Copy From & Insert UNLESS

On Fri, 3 Feb 2006, James William Pye wrote:

> Despite the fact that my experimental patch uses error trapping, that is *not*
> what I have in mind for the implementation. I do not want to trap errors upon
> insert or copy from. Rather, I wish to implement functionality that would allow
> alternate destinations for tuples that violate user specified constraints on
> the table, which, by default, will be to simply drop the tuple.
>
> My proposed syntax is along the lines of:
>
>    INSERT INTO table [ ( column [, ...] ) ]
> *   [UNLESS CONSTRAINT VIOLATION
>      [ON (constraint_name [, ...]) [THEN INSERT INTO table]] [, OR ...]]
>     { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }
>
>  and
>
>    COPY tablename [ ( column [, ...] ) ]
>     FROM { 'filename' | STDIN }
> *   [UNLESS CONSTRAINT VIOLATION
>      [ON (constraint_name [, ...]) [THEN INSERT INTO table]] [, OR ...]]
>    ...
>
> The purpose of the arguably unintuitive THEN INSERT INTO x portion is to provide
> the mechanism in which a user can specify the destination table for tuples that
> violated the associated set of constraints. Using the OR portion allows the user
> to specify additional sets of constraints for different destinations.
>
> A tuple will be withheld from the target table if ANY of the constraints
> listed in any of the constraint_name sets is violated. Constraint sets should
> not [may not?] reference the same constraint multiple times, even among
> different sets.
>
> Example:
>
>  \d dest_table
>    Table "public.dest_table"
>   Column |  Type   | Modifiers
>  --------+---------+-----------
>   i      | integer | not null
>   j      | integer |
>  Indexes:
>      "dest_table_pkey" PRIMARY KEY, btree (i)
>  Check constraints:
>      "dest_table_j_check" CHECK (j > 0)
>
>  CREATE TEMP TABLE pkey_failures (i int, j int);
>  CREATE TEMP TABLE check_failures (i int, j int);
>
>  COPY dest_table FROM STDIN
>   UNLESS CONSTRAINT VIOLATION
>    ON (dest_table_pkey) THEN INSERT INTO pkey_failures
>    OR (dest_table_j_check) THEN INSERT INTO check_failures;
>
> For most constraints, this proposed implementation should be fairly easy to
> implement.

Have you considered how this might work with spec-compliant constraint
timing?  I think even in inserting cases, a later trigger before statement
end could in some cases un-violate a constraint, so checking before insert
won't actually be the same behavior as the normal constraint handling
which seems bad for this kind of system.