Re: ALTER TABLE SET STATISTICS requires AccessExclusiveLock

On Fri, 2010-07-09 at 15:03 -0400, Robert Haas wrote:
> On Fri, Jul 9, 2010 at 1:18 PM, Simon Riggs <simon(at)2ndquadrant(dot)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