WIP: generalized index constraints
in [PgSql]
|
Prev: corrupted double-linked list
Next: Timestamp to time_t
From: Tom Lane on 20 Sep 2009 12:31 Jeff Davis <pgsql(a)j-davis.com> writes: > 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 You get a deadlock failure, because both transactions will wait for each other. So what? It's an error in any case, and you can get a reported deadlock in constraint-enforcement scenarios today (conflicting FK changes, for instance). 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 20 Sep 2009 12:44 On Sun, 2009-09-20 at 12:31 -0400, Tom Lane wrote: > > T1: inserts into index > > T2: inserts into index > > T1: checks index for conflicts, finds T2 > > T2: checks index for conflicts, finds T1 > > You get a deadlock failure, because both transactions will wait for each > other. So what? It's an error in any case, and you can get a reported > deadlock in constraint-enforcement scenarios today (conflicting FK > changes, for instance). Well, in theory, one of the transactions may have been destined to be aborted later anyway, and the deadlock detector might kill the wrong one. But I agree, perhaps I'm over-thinking this one. Aside: I just realized that my solution to the deadlock problem won't work with your simpler idea anyway. When reading the index we've long since lost the information about the specific insert of the specific command of the other transaction. I'll make the change. 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 20 Sep 2009 13:01 BTW, I just thought of an issue that might change some of these conclusions: what about supporting deferred constraint checking, as we just recently managed to do for straight UNIQUE constraints? I don't say that this has to be in the first cut, but it's something we ought to try to leave room for down the road. The current infrastructure for deferred uniqueness requires that the thing actually be a constraint, with an entry in pg_constraint that can carry the deferrability options. So unless we want to rethink that, this might be a sufficient reason to override my arguments about not wanting to use CONSTRAINT syntax. As far as implementation goes, I think there would be very little choice but to use the insert-the-index-entry-first, check-later approach; so your ideas involving extra state in shared memory seem to fall to the ground anyhow. 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 20 Sep 2009 13:08 On Sun, 2009-09-20 at 13:01 -0400, Tom Lane wrote: > The current infrastructure for deferred uniqueness requires that the > thing actually be a constraint, with an entry in pg_constraint that > can carry the deferrability options. So unless we want to rethink > that, this might be a sufficient reason to override my arguments > about not wanting to use CONSTRAINT syntax. Ok. Using the word EXCLUSION would hopefully guard us against future changes to SQL, but you know more about the subtle dangers of language changes than I do. So, do I still omit it from information_schema? > As far as implementation goes, I think there would be very little > choice but to use the insert-the-index-entry-first, check-later > approach; so your ideas involving extra state in shared memory > seem to fall to the ground anyhow. True. 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 20 Sep 2009 13:16
Jeff Davis <pgsql(a)j-davis.com> writes: > So, do I still omit it from information_schema? My thought is yes --- any representation of it within information_schema would be so inaccurate/incomplete as to be worse than useless, IMO. Peter might have a different idea though ... 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 |