Re: [GENERAL] Using an SMP machine to make multiple indices on

Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > Tom Lane wrote:
> >> Also, I'm now a little worried about whether concurrent index creations
> >> will actually work.  Both CREATE INDEX operations will try to update
> >> the pg_class tuple to set relhasindex true.
> 
> > Yes but there's a big difference. It's at the end of the creation
> > not at the beginning. Also note that UpdateStats() updates pg_class
> > tuple in case of B-trees etc before updating relhasindex. I'm
> > suspicios if we should update Stats under the transactional control.
> 
> It would probably be good to fix things so that there's only one update
> done for both stats and relhasindex, instead of two.  

I don't fully agree with you at this point. It's pretty painful
to update relatively irrevalent items at a time in some cases.
UpdateStats() had updated both reltuples and relhasindex before 7.0.
It's me who changed UpdateStats() to not update relhasindex when
I implemented REINDEX command. Reindex has to set relhasindex to 
true after all the indexes of a table were recreated.

> But we *will* get
> failures in simple_heap_update if we continue to use that routine.
> The window for failure may be relatively short but it's real.  It's not
> necessarily short, either; consider multiple CREATE INDEX commands
> executed in a transaction block.
> 
> >> I still don't see any value in LockClassinfoForUpdate, however.
> 
> > ISTM to rely on completely the lock for the corresponding
> > relation is a little misplaced.
> 
> Surely we *must* be able to rely on the relation lock.  For example:
> how does SELECT FOR UPDATE of the relation's pg_class tuple prevent
> writers from adding tuples to the relation? It does not and cannot.

I've never said that the relation lock is unnecessary.
The stuff around relhasindex is(was) an exception that keeps
a (possibly) long term lock for the pg_class tuple apart from 
the relevant relation lock.
What I've mainly intended is to guard our(at least my) code.
If our(my) code acquires an AccessExclusiveLock on a relation
and would update the correspoing pg_class tuple, I'd like to
get the locked tuple not the unlocked one because I couldn't
change unlocked tuples without anxiety. That's almost all.
In most cases the AccessExclusiveLock on the relation would
already block other backends which must be blocked as you
say and so the lock on the pg_class tuple would cause few
additional lock conflicts. Where are disadvantages to get
locked pg_class tuples ?

> Only getting the appropriate relation lock provides a semantically
> correct guarantee that the relation isn't changing underneath us.
> Locking the pg_class tuple only locks the tuple itself, it has no wider
> scope of meaning.
> 
> > For example ALTER TABLE OWNER
> > doesn't acquire any lock on the table but it seems natural to me.
> 
> Seems like a bug to me.  Consider this scenario:
> 
> Backend 1                               Backend 2
> 
> begin;
> 
> lock table1;
> 
> select from table1;  -- works
> 
>                                         alter table1 set owner ...
> 
> select from table1;  -- fails, no permissions
> 
> That should not happen.  It wouldn't happen if ALTER TABLE OWNER
> were acquiring an appropriate lock on the relation.

Hmm ok agreed. One of my intentions is to guard our(my) code
from such careless(?) applications.

> 
> > UPDATE pg_class set .. doesn't acquire any lock on the correspoding
> > relations of the target pg_class tuples but it seems natural to me,
> 
> While we allow knowledgeable users to poke at the system catalogs
> directly, I feel that that is very much a "let the user beware"
> facility.

Me too. Again what I intend is to guard our(my) code from
such knowledgeable users not guarantee them an expected(?)
result.

regards,
Hiroshi Inoue