Re: Scaling up deferred unique checks and the after trigger queue

2009/10/25 Jeff Davis <pgsql(at)j-davis(dot)com>:
> On Mon, 2009-10-19 at 17:48 +0100, Dean Rasheed wrote:
>> This is a WIP patch to replace the after-trigger queues with TID bitmaps
>> to prevent them from using excessive amounts of memory. Each round of
>> trigger executions is a modified bitmap heap scan.
>
> Can you please take a look at my patch here:
> http://archives.postgresql.org/message-id/1256499249.12775.20.camel@jdavis
>
> to make sure that we're not interfering with eachother? I implemented
> deferred constraint checking in my operator exclusion constraints patch
> (formerly "generalized index constraints").
>

Yes, I've been following this, and I'm looking forward to this new
functionality.

> After looking very briefly at your approach, I think that it's entirely
> orthogonal, so I don't expect a problem.
>

I agree. I think that the 2 are orthogonal.

Possibly they could both share some common bulk checking code, but I've
not thought much about how to do that yet.

> I have a git repo here:
> http://git.postgresql.org/gitweb?p=users/jdavis/postgres.git;a=shortlog;h=refs/heads/operator-exclusion-constraints
>
> which may be helpful if you just want to look at the commit for deferred
> constraint checking. Any comments welcome.
>

I did a quick bit of testing, and I think that there is a
locking/concurrency problem :-(

Attached is a (rather crappy) python script (using PyGreSQL) that I
used to test consistency while I was working on the deferrable
uniqueness constraints patch. Basically it just spawns a bunch of
threads, each of which does random CRUD, with heavy contention and
lots of constraint violations and deadlocks, which are rolled back.

I modified the script to enforce uniqueness with an exclusion constraint,
and the script is able to break the constraint, forcing invalid data into
the table.

I haven't looked at your code in depth, but I hope that this is not a
difficult problem to fix. It seems like it ought to be similar to the btree
code.

- Dean