Partitioning/inherited tables vs FKs
in [PgSql]
|
From: Marko Tiikkaja on 11 May 2010 08:59 This is getting way off topic, but: On 5/11/10 3:55 PM +0300, Nicolas Barbier wrote: > T2> SELECT i FROM a WHERE i = 1 FOR SHARE; -- Lock a with i = 1 FOR SHARE. > i > --- > 1 > (1 Zeile) > > T2> SELECT a_id FROM b WHERE a_id = 1; -- Check whether it's got > anything pointing to it. > a_id > ------ > (0 Zeilen) > > T2> DELETE FROM a WHERE i = 1; -- Nope, so delete a with i = 1 (this > blocks, because T1 is still holding the lock). Obviously you wouldn't delete anything with a SHARE lock. Regards, Marko Tiikkaja -- 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: Nicolas Barbier on 11 May 2010 09:07 2010/5/11 Marko Tiikkaja <marko.tiikkaja(a)cs.helsinki.fi>: > This is getting way off topic, but: > > On 5/11/10 3:55 PM +0300, Nicolas Barbier wrote: >> >> T2> Â SELECT i FROM a WHERE i = 1 FOR SHARE; -- Lock a with i = 1 FOR >> SHARE. >> Â i >> --- >> Â 1 >> (1 Zeile) >> >> T2> Â SELECT a_id FROM b WHERE a_id = 1; -- Check whether it's got >> anything pointing to it. >> Â a_id >> ------ >> (0 Zeilen) >> >> T2> Â DELETE FROM a WHERE i = 1; -- Nope, so delete a with i = 1 (this >> blocks, because T1 is still holding the lock). > > Obviously you wouldn't delete anything with a SHARE lock. So where would you put a SELECT ... FOR SHARE to fix the problem? (Per "Will SELECT ... FOR SHARE not help?".) I agree that my second FOR SHARE doesn't really make a lot of sense, but that doesn't disprove the fact that the first FOR SHARE fails to ensure consistency. Nicolas -- 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: Marko Tiikkaja on 11 May 2010 09:11 On 5/11/10 4:07 PM +0300, Nicolas Barbier wrote: > 2010/5/11 Marko Tiikkaja<marko.tiikkaja(a)cs.helsinki.fi>: > >> This is getting way off topic, but: >> >> On 5/11/10 3:55 PM +0300, Nicolas Barbier wrote: >>> >>> T2> SELECT i FROM a WHERE i = 1 FOR SHARE; -- Lock a with i = 1 FOR >>> SHARE. >>> i >>> --- >>> 1 >>> (1 Zeile) >>> >>> T2> SELECT a_id FROM b WHERE a_id = 1; -- Check whether it's got >>> anything pointing to it. >>> a_id >>> ------ >>> (0 Zeilen) >>> >>> T2> DELETE FROM a WHERE i = 1; -- Nope, so delete a with i = 1 (this >>> blocks, because T1 is still holding the lock). >> >> Obviously you wouldn't delete anything with a SHARE lock. > > So where would you put a SELECT ... FOR SHARE to fix the problem? (Per > "Will SELECT ... FOR SHARE not help?".) I agree that my second FOR > SHARE doesn't really make a lot of sense, but that doesn't disprove > the fact that the first FOR SHARE fails to ensure consistency. I took the "SELECT ... FOR SHARE" suggestion in a more general way, suggesting the use of row-level locks. T2 should be holding an exclusive row-level lock (SELECT ... FOR UPDATE) when checking for references. Regards, Marko Tiikkaja -- 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: Marko Tiikkaja on 11 May 2010 09:19 On 5/11/10 4:11 PM +0300, I wrote: > I took the "SELECT ... FOR SHARE" suggestion in a more general way, > suggesting the use of row-level locks. T2 should be holding an > exclusive row-level lock (SELECT ... FOR UPDATE) when checking for > references. Hmm. Right, that transaction wouldn't see the rows in a serializable transaction so this doesn't solve the problem. Regards, Marko Tiikkaja -- 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 11 May 2010 10:03 Marko Tiikkaja <marko.tiikkaja(a)cs.helsinki.fi> writes: > On 5/11/10 4:11 PM +0300, I wrote: >> I took the "SELECT ... FOR SHARE" suggestion in a more general way, >> suggesting the use of row-level locks. T2 should be holding an >> exclusive row-level lock (SELECT ... FOR UPDATE) when checking for >> references. > Hmm. Right, that transaction wouldn't see the rows in a serializable > transaction so this doesn't solve the problem. Yeah. The hidden "magic" in the built-in FK code is not locking (it does actually use SELECT FOR SHARE to lock rows). Rather, it's about doing tuple liveness checks using snapshots that aren't available at the SQL level, particularly in serializable transactions. 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
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: [HACKERS] Partitioning/inherited tables vs FKs Next: LD_LIBRARY_PATH versus rpath |