Re: Copy From & Insert UNLESS

On Mon, 6 Feb 2006, James William Pye wrote:

> 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. ;]

Only parts of it. :)

> > 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. =\

Yeah, it's pretty odd in the insert case. It's easy in the update case to
make a case where it matters, definately less so for insert.

> 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". =)

I'd say that if we were going to check the constraints at a different
time, we'd want a better name/description than "UNLESS CONSTRAINT
VIOLATION" since the unadorned INSERT or COPY might run with no constraint
violations.