> 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