Re: Catalog Access (was: [GENERAL] Concurrency problem

Ühel kenal päeval, T, 2006-04-25 kell 13:58, kirjutas Tom Lane:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > I think the basic problem is that DDL can't really work within a
> > transaction. If I do an ALTER TABLE, some of these changes need to show
> > up to concurrent transactions (maybe creating a unique index?).
>
> The point is that DDL can't be MVCC. If for instance you add an index
> to a table, once you commit every other transaction must *immediately*
> start updating that index when they modify the table.

How is it done in a way that all other backends see it, but only after
commit ?

Is there some secret transaction isolation mode for DDL? Maybe something
that fits between "read uncommitted" and "read committed" ? Or is it
just that catalog access is always done in read-committed mode, even if
transaction is in "serializable" ?

Would this take effect even inside a single command ? in other words, if
it were possible that an index appeared in middle of a big update, would
the tuples updated after the index becomes visible be also added to the
index ?

The reason I ask, is that I'm still keen on implementin a CONCURRENT
INDEX command, and I want to get as much background info as possible
before diving in.

> They can't use
> the excuse of "not my problem because the catalog change postdates the
> snapshot I'm using". The drop-index case is even worse, since a
> transaction that believes the index is still present is likely to try
> to access/update a disk file that's not there anymore. Adding/dropping
> columns, constraints, triggers, etc all have hazards of the same ilk.

at what point will an add/drop column become visible for parallel
transactions ?

can trigger/constraint appear magically inside a transaction ? so if I
update 5 rows inside one serialisable trx, is it possible that a trigger
added to the table after 2nd update will fire for last 3 updates ?

btw, i don't think that a stored procedure (cached plans) will pick up
something like added/changed default even after commit in both
connections.

> > I think it's like Tom says in that email, it could be done, but the
> > cost/benefit ratio isn't very good...
>
> It's barely possible that we could make this happen, but it would be a
> huge amount of work, and probably a huge amount of instability for a
> very long time until we'd gotten all the corner cases sorted. I think
> there are much more productive uses for our development effort.

True.

-------------------
Hannu