Re: [HACKERS] Question about RI checks

2014-11-14 Thread Alvaro Herrera
Kevin, are you handling this? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/p

Re: [HACKERS] Question about RI checks

2014-10-24 Thread Florian Pflug
On Oct24, 2014, at 22:50 , Kevin Grittner wrote: > I need to spend some more time looking at it, and I have another > couple things in front of this on my personal TODO list, but I > think that if we had a row lock which was stronger than current > SELECT FOR UPDATE behavior, and the delete of a p

Re: [HACKERS] Question about RI checks

2014-10-24 Thread Kevin Grittner
Florian Pflug wrote: > On Oct24, 2014, at 20:24 , Robert Haas wrote: >> On Fri, Oct 24, 2014 at 2:12 PM, Florian Pflug wrote: What about doing one scan using SnapshotAny and then testing each returned row for visibility under both relevant snapshots? See whether there is any tupl

Re: [HACKERS] Question about RI checks

2014-10-24 Thread Robert Haas
On Fri, Oct 24, 2014 at 2:58 PM, Florian Pflug wrote: > I believe the best way forward is to first find a solution for SERIALIZABLE > transactions, and then check if it can be applied to REPEATABLE READ > mode too. For SERIALIZABLE mode, it's at least clear what we're aiming > for -- offering true

Re: [HACKERS] Question about RI checks

2014-10-24 Thread Florian Pflug
On Oct24, 2014, at 20:24 , Robert Haas wrote: > On Fri, Oct 24, 2014 at 2:12 PM, Florian Pflug wrote: >>> What about doing one scan using SnapshotAny and then testing each >>> returned row for visibility under both relevant snapshots? See >>> whether there is any tuple for which they disagree. >

Re: [HACKERS] Question about RI checks

2014-10-24 Thread Robert Haas
On Fri, Oct 24, 2014 at 2:12 PM, Florian Pflug wrote: >> What about doing one scan using SnapshotAny and then testing each >> returned row for visibility under both relevant snapshots? See >> whether there is any tuple for which they disagree. > > See my other mail - testing whether the snapshots

Re: [HACKERS] Question about RI checks

2014-10-24 Thread Florian Pflug
On Oct24, 2014, at 19:32 , Robert Haas wrote: > On Fri, Oct 24, 2014 at 1:28 PM, Florian Pflug wrote: >> The only other option I see would be so teach the executor to check >> whether *any* snapshot between the transaction's snapshot and a current >> snapshot would see a different set of rows. Si

Re: [HACKERS] Question about RI checks

2014-10-24 Thread Robert Haas
On Fri, Oct 24, 2014 at 1:28 PM, Florian Pflug wrote: > The only other option I see would be so teach the executor to check > whether *any* snapshot between the transaction's snapshot and a current > snapshot would see a different set of rows. Simply checking whether both > the current snapshot an

Re: [HACKERS] Question about RI checks

2014-10-24 Thread Florian Pflug
On Oct24, 2014, at 18:42 , Robert Haas wrote: > I don't think you can count on being able to figure out all of the > recent lockers by looking at xmax; it can get overwritten. For > example, suppose transaction A locks the row and then commits. Then > transaction B comes along and again locks th

Re: [HACKERS] Question about RI checks

2014-10-24 Thread Robert Haas
On Thu, Oct 23, 2014 at 2:41 PM, Florian Pflug wrote: > The only reason we need the crosscheck snapshot to do that is because > children may have been added (and the change committed) *after* the > transaction which removed the parent has taken its snapshot, but *before* > that transaction locks t

Re: [HACKERS] Question about RI checks

2014-10-24 Thread Florian Pflug
On Oct23, 2014, at 17:45 , Kevin Grittner wrote: > Every way I look at it, inside a REPEATABLE READ or SERIALIZABLE > transaction a check for child rows when validating a parent DELETE > should consider both rows which exist according to the transaction > snapshot and according to a "current" snap

Re: [HACKERS] Question about RI checks

2014-10-23 Thread Florian Pflug
On Oct23, 2014, at 17:45 , Kevin Grittner wrote: > Every way I look at it, inside a REPEATABLE READ or SERIALIZABLE > transaction a check for child rows when validating a parent DELETE > should consider both rows which exist according to the transaction > snapshot and according to a "current" snap

Re: [HACKERS] Question about RI checks

2014-10-23 Thread Kevin Grittner
Kevin Grittner wrote: > Florian Pflug wrote: >> Also, note that after the DELETE FROM parent, further SELECTS in >> the same transaction will use the original snapshot again, und >> thus will see the conflicting child rows again that were ignored >> by the RI trigger. But they won't, of course,

Re: [HACKERS] Question about RI checks

2014-10-22 Thread Kevin Grittner
Florian Pflug wrote: >> This should not be considered a problem for repeatable read >> transactions because the change in visible rows meet the >> definition of phantom reads, which are allowed in repeatable >> read: "A transaction re-executes a query returning a set of rows >> that satisfy a sea

Re: [HACKERS] Question about RI checks

2014-10-22 Thread Florian Pflug
> This should not be considered a problem for repeatable read > transactions because the change in visible rows meet the definition > of phantom reads, which are allowed in repeatable read: "A > transaction re-executes a query returning a set of rows that > satisfy a search condition and finds that

Re: [HACKERS] Question about RI checks

2014-10-22 Thread Kevin Grittner
Florian Pflug wrote: > Florian Pflug wrote: > But that's wrong. The transaction's snapshot *would* see that row, so we > ought to raise an error. Note that this applies also to mode SERIALIZABLE, and > breaks true serializability in some cases, since we don't do conflict > detection > for RI en

Re: [HACKERS] Question about RI checks

2014-10-22 Thread Florian Pflug
Florian Pflug wrote: > So in conclusion, the lock avoids raising constraint violation errors in > a few cases in READ COMMITTED mode. In REPEATABLE READ mode, it converts some > constraint violation errors into serialization failures. Or at least that's > how it looks to me. I go the REPEATABLE R

Re: [HACKERS] Question about RI checks

2014-10-21 Thread Nick Barnes
On Wed, Oct 22, 2014 at 3:19 AM, Kevin Grittner wrote: > > It doesn't seem like this analysis considers all of the available ON > DELETE and ON UPDATE behaviors available. Besides RESTRICT there is > CASCADE, SET NULL, SET DEFAULT, and NO ACTION. Some of those > require updating the referencing

Re: [HACKERS] Question about RI checks

2014-10-21 Thread Nick Barnes
Thanks! I've been mulling this over for weeks; nice to know it wasn't just staring me in the face... So in conclusion, the lock avoids raising constraint violation errors in > a few cases in READ COMMITTED mode. In REPEATABLE READ mode, it converts > some > constraint violation errors into seriali

Re: [HACKERS] Question about RI checks

2014-10-21 Thread Kevin Grittner
Florian Pflug wrote: > So in conclusion, the lock avoids raising constraint violation errors in > a few cases in READ COMMITTED mode. In REPEATABLE READ mode, it converts some > constraint violation errors into serialization failures. Or at least that's > how it looks to me. It doesn't seem lik

Re: [HACKERS] Question about RI checks

2014-10-21 Thread Florian Pflug
(CCing Alvaro, since he implemented KEY SHARE locks) On Oct16, 2014, at 15:51 , Nick Barnes wrote: > One of the queries in ri_triggers.c has be a little baffled. > > For (relatively) obvious reasons, a FK insert triggers a SELECT 1 FROM pk_rel > ... FOR KEY SHARE. > For not-so-obvious reasons,

[HACKERS] Question about RI checks

2014-10-16 Thread Nick Barnes
One of the queries in ri_triggers.c has be a little baffled. For (relatively) obvious reasons, a FK insert triggers a SELECT 1 FROM pk_rel ... FOR KEY SHARE. For not-so-obvious reasons, a PK delete triggers a SELECT 1 FROM fk_rel ... FOR KEY SHARE. I can't see what the lock on fk_rel achieves. Bo