Re: WIP: Deferrable unique constraints

Dean Rasheed <dean(dot)a(dot)rasheed(at)googlemail(dot)com> writes:
> Actually I see 2 parts to this:
> (1). make trigger queue scalable with easy mechanism to switchover to
> wholesale check
> (2). implement wholesale check for UNIQUE
> but (1) seems like to lion's share of the work.
> (and then maybe (3). wholesale check for RI constraints)

We already have those wholesale checks. It might take a bit of
refactoring to make them conveniently callable in this context,
but there shouldn't be a lot of new code there.

I think that "scalable" is sort of missing the mark as far as the needed
queue behavior goes. "Lossy" might be a better adjective. The main
reason nobody has implemented spill-to-disk for the existing trigger
queue is that by the time you've filled memory with the queue,
performance already sucks, because it's gonna take forever to fire all
those trigger events. Being able to make the queue even bigger isn't
going to make it suck less, quite the opposite. You need some way to
slide into bulk instead of retail checking strategies, just like join
strategies vary depending on the number of rows involved.

(In fact, in a real sense these ARE join problems ... maybe we should
stop thinking of them as fire-a-bunch-of-triggers and instead think of
executing a single check query with appropriate WHERE clause ...)

regards, tom lane