Re: Copy From & Insert UNLESS

On Sun, Feb 05, 2006 at 07:14:49PM -0800, Stephan Szabo wrote:
> On Sun, 5 Feb 2006, James William Pye wrote:
> > 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.

I see. "Immediated" is not the word that I am actually looking for then. :(
Perhaps Postgres should specify our current immediate as a "new" constraint mode.
"instant", maybe? Sadly, I think it will be difficult to get away from using that or
some other synonym if such an idea were to be "implemented".

[Getting the feeling that this has been discussed before. ;]

> Our implementation of UNIQUE is particularly bad for this.

Yes. Changing how UNIQUE constraints are implemented will likely be the first
step in this patch.

> > 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).
> [join]
> Basically a violation at the time the row is
> created is irrelevant if the violation is gone by the end of statement.

Okay. I can't help but think such a trigger as being questionable at best.
However, per spec, it should be possible. =\

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

Aye. I figured there would be some details that might take a while.

Once the UNIQUE constraint code is relocated, I think implementing more
standards compliant constraint timing might be substantially easier. However, I
don't think this should effect UNLESS. Rather, I think UNLESS should, more or
less, demand that specified constraints be checked at the same time as they are
currently. This is meant to be an optimization at multiple levels; reduce code
redundancy(rewriting constraint checks for use prior to the actual insertion),
computational redundancy(potentially, running the rewritten checks more than
once), and reduce unnecessary I/O(avoiding heap_insert()'ing an evil tuple
into the target table despite the fact that the statement may later "inviolate"
it). Although, perhaps, it could be configurable with an option;
"INSERT INTO t UNLESS [DEFERRED] CONSTRAINT VIOLATION". =)
--
Regards, James William Pye