Обсуждение: AW: AW: AW: relation ### modified while in use

> 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

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