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.