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. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
