Re: SELECT blocking on ALTER TABLE ADD FOREIGN KEY

"Jim C. Nasby" <jim(at)nasby(dot)net> writes:
> On Thu, Jun 12, 2003 at 06:23:12PM -0400, Tom Lane wrote:
>>> Even stuff like
>>> drop and rename should be protected by versioning, no?
>>
>> No. System-catalog changes are always READ COMMITTED mode.

> Yeah, so the catalog changes shouldn't be visible to anyone until after
> the ALTER is complete, right?

The point is that they become visible *immediately* when the ALTER
commits; if the other transaction is in the midst of some operation on
the table, it's likely to fail badly. Locking is what we have to do
to prevent that.

An example of the sort of problem I'm afraid of is that any change in
the tuple descriptor of a table (adding or renaming a column, flipping
the NOT NULL constraint, etc) will cause replacement of the tuple
descriptor in the table's relcache entry as soon as the other backend
notices the cache-inval message from the altering backend. This would
break any code that has a pointer to the tuple descriptor. Now with
sufficiently draconian programming rules we could probably avoid holding
references to cached tuple descriptors anywhere ... but it would be
mighty fragile, and mistakes would lead to failures that would be nigh
impossible to replicate or debug. Right now the rule is "you can use a
relcache entry as long as you have some kind of lock on the relation".
This is relatively easy to ensure.

regards, tom lane