ALTER TABLE SET STATISTICS requires AccessExclusiveLock
in [PgSql]
|
Prev: pg_dump and join aliases (was Re: [BUGS] ERROR: cannot handle unplanned sub-select)
Next: patch: preload dictionary new version
From: =?ISO-8859-1?Q?C=E9dric_Villemain?= on 7 Jul 2010 21:04 2010/3/3 Bruce Momjian <bruce(a)momjian.us>: > Peter Eisentraut wrote: >> On m?n, 2010-02-22 at 10:32 -0500, Bruce Momjian wrote: >> > Simon Riggs wrote: >> > > On Mon, 2009-10-19 at 12:56 -0300, Alvaro Herrera wrote: >> > > > Simon Riggs wrote: >> > > > > >> > > > > On Fri, 2009-08-07 at 15:58 -0400, Alvaro Herrera wrote: >> > > > > > Tom Lane wrote: >> > > > > > > Peter Eisentraut <peter_e(a)gmx.net> writes: >> > > > > > > > Is there a good reason for $subject, other than that the code is entangled >> > > > > > > > with other ALTER TABLE code? >> > > > > > > >> > > > > > > I think it could be lower, but it would take nontrivial restructuring of >> > > > > > > the ALTER TABLE support. �In particular, consider what happens when you >> > > > > > > have a list of subcommands that don't all require the same lock level. >> > > > > > > I think you'd need to scan the list and find the highest required lock >> > > > > > > level before starting ... >> > > > > > >> > > > > > IIRC there was a patch from Simon to address this issue, but it had some >> > > > > > holes which he didn't have time to close, so it sank. �Maybe this can be >> > > > > > resurrected and fixed. >> > > > > >> > > > > I was intending to finish that patch in this release cycle. >> > > > >> > > > Since you're busy with Hot Standby, any chance you could pass it on? >> > > >> > > If you'd like. It's mostly finished, just one last thing to finish: >> > > atomic changes to pg_class via an already agreed API. >> > >> > 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 Momjian �<bruce(a)momjian.us> � � � �http://momjian.us > �EnterpriseDB � � � � � � � � � � � � � � http://enterprisedb.com > > �PG East: �http://www.enterprisedb.com/community/nav-pg-east-2010.do > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- C�dric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- 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 8 Jul 2010 06:08 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? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 9 Jul 2010 13:04 On Thu, Jul 8, 2010 at 5:09 PM, Simon Riggs <simon(a)2ndquadrant.com> wrote: > 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. The relevant link from the archives is here: http://archives.postgresql.org/pgsql-hackers/2008-10/msg00242.php 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. Which, come to think of it, is probably a good thing, because on further reflection I'm pretty sure the proposed approach will fall down completely for some of these operations. heap_inplace_update() can only be used when (a) the new tuple is identical in size to the old tuple, and (b) no action is required on rollback. That's fine for updating things like relpages and reltuples (which are just hints anyway) but it ain't gonna work for changing, say, reloptions, which is variable-length. It's also not going to work for changing things like attstorage, even though a change there can't affect the tuple size, because modifying the tuple in place won't handle rollbacks correctly. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 9 Jul 2010 15:03 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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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: Josh Berkus on 7 Jul 2010 21:37
On 7/7/10 6:04 PM, Cédric Villemain wrote: > 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) We could try to resolve the COMMENT ON issue with the same mechanism. What we need is a table lock which blocks other DDL statements, but not DML. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.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 |