Re: Copy From & Insert UNLESS
От | James William Pye |
---|---|
Тема | Re: Copy From & Insert UNLESS |
Дата | |
Msg-id | 20060206182505.GA22529@lit.jwp.name обсуждение исходный текст |
Ответ на | Re: Copy From & Insert UNLESS (Stephan Szabo <sszabo@megazone.bigpanda.com>) |
Ответы |
Re: Copy From & Insert UNLESS
Re: Copy From & Insert UNLESS |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: slow information schema with thausand users, seq.scan pg_authid