Re: Copy From & Insert UNLESS

On Sun, 5 Feb 2006, James William Pye wrote:

> On Sun, Feb 05, 2006 at 02:08:12PM -0800, Stephan Szabo wrote:
> > Have you considered how this might work with spec-compliant constraint
> > timing?
>
> I haven't gone so far as to look into the spec, yet. [Noise of rustling papers]
>
> However, constraints referenced in an UNLESS clause that are deferred, in any
> fashion, should probably be "immediated" within the context of the command.
> Perhaps a WARNING or NOTICE would be appropriately informative if UNLESS were
> to actually alter the timing of a given constraint.

The problem is that even immediate constraints are supposed to be checked
at end of statement, not at row time. Our implementation of UNIQUE is
particularly bad for this. Basically a violation at the time the row is
created is irrelevant if the violation is gone by the end of statement.

> > 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.
>
> Any facility that can alter the tuple before it being inserted into the heap
> should probably be exercised prior to the application of the tuple against
> UNLESS's behavior.

The problem is that you can un-violate a unique constraint by changing
some other row that's already in the table. And I think that it might even
be legal to do so in an after trigger (and in fact, some other row's after
trigger).

This isn't necessarily a killer to the idea though, it probably just means
the semantics are harder to nail down.