ALTER TABLE SET STATISTICS requiresAccessExclusiveLock
in [PgSql]
|
From: Simon Riggs on 8 Jul 2010 02:16 On Wed, 2010-07-07 at 22:26 -0400, Robert Haas wrote: > Rereading the thread, I'm a bit confused by why we're proposing to use > a SHARE lock; it seems to me that a self-conflicting lock type would > simplify things. There's a bunch of discussion on the thread about > how to handle pg_class updates atomically, but doesn't using a > self-conflicting lock type eliminate that problem? The use of the SHARE lock had nothing to do with the pg_class update requirement, so that suggestion won't help there. > It strikes me that for the following operations, which don't affect > queries at all, we could use a SHARE UPDATE EXCLUSIVE, which is likely > superior to SHARE for this purpose because it wouldn't lock out > concurrent DML write operations: Yes, we can also use SHARE UPDATE EXCLUSIVE for some of them. The use of SHARE lock was specifically targeted at ADD FOREIGN KEY, to allow multiple concurrent FKs. Not much use for production however, so SUE looks better to me. Not sure I agree with the "don't affect queries at all" bit.... I'll take my previous patch through to completion now, I'm funded to do that work now. Sept commitfest though. -- Simon Riggs www.2ndQuadrant.com -- 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: Simon Riggs on 8 Jul 2010 17:09 On Thu, 2010-07-08 at 06:08 -0400, Robert Haas wrote: > On Thu, Jul 8, 2010 at 2:16 AM, Simon Riggs <simon(a)2ndquadrant.com> wrote: > > On Wed, 2010-07-07 at 22:26 -0400, Robert Haas wrote: > >> Rereading the thread, I'm a bit confused by why we're proposing to use > >> a SHARE lock; it seems to me that a self-conflicting lock type would > >> simplify things. There's a bunch of discussion on the thread about > >> how to handle pg_class updates atomically, but doesn't using a > >> self-conflicting lock type eliminate that problem? > > > > The use of the SHARE lock had nothing to do with the pg_class update > > requirement, so that suggestion won't help there. > > Forgive me if I press on this just a bit further, but ISTM that an > atomic pg_class update functionality isn't intrinsically required, > because if it were the current code would need it. So what is > changing in this patch that makes it necessary when it isn't now? > ISTM it must be that the lock is weaker. What am I missing? Not sure I follow that logic. Discussion on the requirement is in the archives. I don't wish to question that aspect myself. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- 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: Simon Riggs on 9 Jul 2010 13:18 On Fri, 2010-07-09 at 13:04 -0400, Robert Haas wrote: > Tom asked what happens when two transactions attempt to do concurrent > actions on the same table. Your response was that we should handle it > like CREATE INDEX, and handle the update of the pg_class row > non-transactionally. But of course, if you use a self-conflicting > lock at the relation level, then the relation locks conflict and you > never have to worry about how to update the pg_class entry in the face > of concurrent updates. >From memory, Tom was also worried about the prospect of people updating pg_class directly using SQL. That seems a rare, yet valid concern. I've already agreed with your point that we should use SHARE UPDATE EXCLUSIVE. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- 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: Bruce Momjian on 10 Jul 2010 16:17 Robert Haas wrote: > On Wed, Jul 7, 2010 at 9:04 PM, C?dric Villemain > <cedric.villemain.debian(a)gmail.com> wrote: > >>> > I assume this did not get done for 9.0. ?Do we want a TODO item? > >>> > >>> Yes. > >> > >> Added: > >> > >> ? ? ? ?Reduce locking required for ALTER commands > > > > I just faced production issue where it is impossible to alter table to > > adjust autovacuum settings in a pg8.4. (5K tps, 260M rows table, lock > > too much) > > > > Can we add some mechanism to prevent that situation also in the TODO item ? > > > > (alternative is actualy to alter other tables and adjust the > > postgresql.conf for biggest tables, but not an ideal solution anyway) > > > >> > >> ? ? ? ? ? ?* http://archives.postgresql.org/pgsql-hackers/2009-08/msg00533.php > >> ? ? ? ? ? ?* http://archives.postgresql.org/pgsql-hackers/2009-10/msg01083.php > >> ? ? ? ? ? ?* http://archives.postgresql.org/pgsql-hackers/2010-01/msg02349.php > > Bruce, that last link is about something else completely. Here are > some better ones: > > http://archives.postgresql.org/pgsql-hackers/2008-10/msg01248.php > http://archives.postgresql.org/pgsql-hackers/2008-10/msg00242.php Thanks, TODO updated. -- Bruce Momjian <bruce(a)momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- 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: Simon Riggs on 11 Jul 2010 03:54 On Fri, 2010-07-09 at 15:03 -0400, Robert Haas wrote: > On Fri, Jul 9, 2010 at 1:18 PM, Simon Riggs <simon(a)2ndquadrant.com> wrote: > > On Fri, 2010-07-09 at 13:04 -0400, Robert Haas wrote: > >> Tom asked what happens when two transactions attempt to do concurrent > >> actions on the same table. Your response was that we should handle it > >> like CREATE INDEX, and handle the update of the pg_class row > >> non-transactionally. But of course, if you use a self-conflicting > >> lock at the relation level, then the relation locks conflict and you > >> never have to worry about how to update the pg_class entry in the face > >> of concurrent updates. > > > > From memory, Tom was also worried about the prospect of people updating > > pg_class directly using SQL. That seems a rare, yet valid concern. > > Yes, and it's another another reason why we shouldn't use > non-transactional updates. > > http://archives.postgresql.org/pgsql-hackers/2008-11/msg00744.php > > > I've already agreed with your point that we should use SHARE UPDATE > > EXCLUSIVE. > > The point you seem to be missing is that once we make that decision, > we can throw all the heap_inplace_update() stuff out the window, and > the whole problem becomes much simpler. That is a point I missed. Considering this further, it seems we have two conflicting requirements 1. ALTER TABLE ... ADD FOREIGN KEY needs a SHARE mode lock if we want to run that concurrently with itself and CREATE INDEX operations during a pg_restore. This was my original goal. 2. In most other cases, SHARE UPDATE EXCLUSIVE is the most useful lock, especially during heavy operational use. Since adding an FK requires adding triggers also that puts both of the above in direct conflict. ISTM that we should follow (2) and let (1) be added to the TODO for later work, as an option. I'll followu up on (2). -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
|
Next
|
Last
Pages: 1 2 3 4 Prev: patch: preload dictionary new version Next: patch (for 9.1) string functions |