Re: AW: AW: relation ### modified while in use

Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)at> writes:
>> As for locks,weak locks doesn't pass intensive locks. Dba
>> seems to be able to alter a table at any time.

> Sorry, I don't understand this sentence. Tom suggested placing a
> shared lock on any table that is accessed until end of tx. Noone can
> alter table until all users have closed their txns and not accessed
> tables again.

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.

regards, tom lane