WIP: generalized index constraints
in [PgSql]
|
Prev: corrupted double-linked list
Next: Timestamp to time_t
From: Jeff Davis on 19 Sep 2009 17:20 On Sat, 2009-09-19 at 16:43 -0400, Tom Lane wrote: > I don't understand why this isn't handled exactly the way unique > constraints are done now. Frankly, the amount of added complexity you > propose below is enough to make me want to reject the patch forthwith; > given that it's going to be a relatively little-used feature, the bugs > are never going to be out of it completely if we do it like this. Unique constraints lock the index page while the insert is happening. How am I supposed to do that, when the conflicting values might be anywhere in the index (circles have no total order)? It may sound complex, but it basically boils down to a two stage process: 1. test for conflicts with concurrently-inserting backends 2. test for conflicts that already exist in the index (dirty or not) I don't think that it's ridiculously complex. In fact, I think there are relatively few scenarios that will make any real difference, and those scenarios can be tested with gdb pretty thoroughly. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 19 Sep 2009 18:00 Jeff Davis <pgsql(a)j-davis.com> writes: > On Sat, 2009-09-19 at 16:43 -0400, Tom Lane wrote: >> I don't understand why this isn't handled exactly the way unique >> constraints are done now. Frankly, the amount of added complexity you >> propose below is enough to make me want to reject the patch forthwith; >> given that it's going to be a relatively little-used feature, the bugs >> are never going to be out of it completely if we do it like this. > Unique constraints lock the index page while the insert is happening. > How am I supposed to do that, when the conflicting values might be > anywhere in the index (circles have no total order)? Well, you can't do it *exactly* the same way btree does, but what I would envision is first insert the index tuple and then do a dirty-snapshot search for conflicting tuples. The interlock against conflicting concurrent inserts doesn't need all this new infrastructure you propose; just wait to see if conflicting transactions commit, same as we do now. And I do maintain that that sort of code has a high risk of undetected bugs. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Jeff Davis on 19 Sep 2009 18:53 On Sat, 2009-09-19 at 18:00 -0400, Tom Lane wrote: > Well, you can't do it *exactly* the same way btree does, but what > I would envision is first insert the index tuple and then do a > dirty-snapshot search for conflicting tuples. The interlock against > conflicting concurrent inserts doesn't need all this new infrastructure > you propose; just wait to see if conflicting transactions commit, same > as we do now. And I do maintain that that sort of code has a high risk > of undetected bugs. How do you prevent deadlocks in the following case? T1: inserts into index T2: inserts into index T1: checks index for conflicts, finds T2 T2: checks index for conflicts, finds T1 We can't say "only wait if your xid is higher" because xid 200 may both insert and check the index before xid 100 even inserts. The way I solve this in my current patch is by assigning a sequence number in a shared memory table for each insert. The sequence number works because a higher sequence number will always be able to see a lower sequence number's tuple, so we can safely say "only wait if you have a higher sequence number". I can tack the same solution onto your idea, but I would need to keep my shared memory table and probably some other infrastructure. It may be less complex than it is currently, however. Simpler ideas welcome. And to clarify the syntax issue, I assume this means that: ((a||b)::circle &&) would look for the column in the index that matches that expression, and then use that attribute number when scanning the index? I'm OK with that; I don't see a lot of obvious value in having separate expressions for the constraint and the index (even if it did have value, it would take some real creativity to find it ;) Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 19 Sep 2009 23:15 On Sat, Sep 19, 2009 at 2:51 PM, Jeff Davis <pgsql(a)j-davis.com> wrote: > On Sat, 2009-09-19 at 14:05 -0400, Tom Lane wrote: >> What about them? It's not clear why you think this requires anything >> special. > > >From a syntax standpoint, I need to represent one operator for every > index column involved in the constraint. So, if there's a functional > index on ((a||b)::circle), I clearly can't have an exclusion constraint > like (a =, b =). > > I see two options: > > 1. (<expr> <op>), where <expr> is an expression over table attributes > that must have the exact signature as the expression for the index. > 2. (<index_col> <op>), and then read the expression from the index I was wondering if we couldn't introduce a dummy tuple name similar to OLD and NEW, called, say, OTHER. Then instead of writing a =, you could write a = OTHER.a ... or perhaps a = OTHER.b ... although that might also open the door to more things than you want to support at this point. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Jeff Davis on 20 Sep 2009 12:16
On Sat, 2009-09-19 at 23:15 -0400, Robert Haas wrote: > I was wondering if we couldn't introduce a dummy tuple name similar to > OLD and NEW, called, say, OTHER. Then instead of writing a =, you > could write a = OTHER.a ... or perhaps a = OTHER.b ... although that > might also open the door to more things than you want to support at > this point. Interesting idea. At this point though, there is enough disagreement over the language that I just want to take the least-invasive route that has the lowest chance of causing a problem. It looks like ALTER INDEX might be the path of least resistance. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |