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

Hannu Krosing wrote:
> Ü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?

Sort of. Catalog accesses normally use SnapshotNow, instead of
ActiveSnapshot which is normally used by regular access. The use of
ActiveSnapshot is what makes a transaction read committed or
serializable; in a serializable transaction, ActiveSnapshot will point
to SerializableSnapshot, while on a read committed transaction,
ActiveSnapshot will point to a snapshot acquired at the beggining of the
command by GetSnapshotData. Have a look at GetTransactionSnapshot() in
tqual.c.

(The trick is grokking the differences among the various
HeapTupleSatisfies routines.)

> 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 ?

This can't happen, because an UPDATE to a table requires acquiring a
lock (RowExclusiveLock) which conflicts with a lock taken by the CREATE
INDEX (ShareLock). You can see the conflict table in lock.c,
the LockConflicts array.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support