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

> Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)at> writes:
> > Yes, and holding a row exclusive lock must imho at least
> grab a shared
> > table lock
>
> As indeed it does. Our disagreement seems to be just on the point of
> whether it's safe to allow a read-only transaction to release its
> AccessShareLock locks partway through.

Yes, imho it must release the locks after each (read only) statement.

> My opinion about that is colored by the known bugs that we have because
> the parser/rewriter/planner currently do just that. You can cause the
> system to become mighty confused if the report of a table schema change
> arrives partway through the parse/plan process, because decisions
> already made are no longer valid.

I won't argue against that. I agree that the locks should be grabbed on first access
and not released until statement end. But imho we can't hold them until tx end.

> While we can probably patch the holes
> in this area by holding a lock throughout processing of one statement,
> I think that will just push the problem up to the application level.
> How many apps are likely to be coded in a way that will be robust
> against intra-transaction schema changes?

I would not know one single of our programs, where adding a column,
creating an index or changing the schema in any other intended way
would have an impact on an application that is still supposed to work with
this new schema. (One of the first SQL rules is e.g. to not use select *)

And besides I do not think that this is a problem that we are allowed to solve
on the db side, because it would flood us with locks.
Remember that some other db's are always inside a transaction and
it is standard to not do any commits if you work read only.

The only case where I do agree that the lock needs to be held until tx end
is in serializable transaction isolation.

Andreas