
> Until existing xacts using that table have closed, yes.  But I believe
> the lock manager has some precedence rules that will allow the pending
> request for AccessExclusiveLock to take precedence over new requests
> for lesser locks.  So you're only held off for a long time if you have
> long-running xacts that use the target table.
> 
> I consider that behavior *far* safer than allowing schema changes to
> be seen mid-transaction.  Consider the following example:
> 
> 	Session 1			Session 2
> 
> 	begin;
> 
> 	INSERT INTO foo ...;
> 
> 					ALTER foo ADD constraint;
> 
> 	INSERT INTO foo ...;
> 
> 	end;
> 
> Which, if any, of session 1's insertions will be subject to the
> constraint?  What are the odds that the dba will like the result?
> 
> With my proposal, session 2's ALTER would wait for session 1 
> to commit,
> and then the ALTER's own scan to verify the constraint will check all
> the rows added by session 1.
> 
> Under your proposal, I think the rows inserted at the beginning of
> session 1's xact would be committed without having been checked.

No, the above is not a valid example, because Session 2 won't
get the exclusive lock until Session 1 commits, since Session 1 already 
holds a lock on foo (for the inserted row). 

You were talking about the "select only" case (and no for update eighter). 
I think that select statements need a shared lock for the duration of their 
execution only.

Andreas

Mon, 23 Oct 2000 16:30:36 +0200
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
AW: AW: AW: relation ### modified while in use 
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
> No, the above is not a valid example, because Session 2 won't
> get the exclusive lock until Session 1 commits, since Session 1 already 
> holds a lock on foo (for the inserted row). 

> You were talking about the "select only" case (and no for update eighter). 
> I think that select statements need a shared lock for the duration of their 
> execution only.

You seem to think that locks on individual tuples conflict with
table-wide locks.  AFAIK that's not true.  The only way to prevent
another xact from gaining AccessExclusiveLock on a table is to be
holding some lock *on the table*.

As for your claim that read-only xacts don't need to worry about
preventing schema updates, what of adding/deleting ON SELECT rules?

			regards, tom lane

Mon, 23 Oct 2000 10:36:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: AW: AW: AW: relation ### modified while in use 