Re: HOT for PostgreSQL 8.3

On Thu, 2007-02-08 at 14:47 +0000, Heikki Linnakangas wrote:

> However, the easiest solution would be to make CREATE INDEX wait until 
> the old tuple is dead. That should be ok at least for concurrent CREATE 
> INDEX, because it already has that kind of a wait between 1st and 2nd 
> phase.

I'm not sure this part of the idea is possible; the rest sounded good.
Looking at DefineIndex() the wait happens only for transactions that
already have a lock on the table being indexed, which may not be very
many. AFAICS the ref page for CREATE INDEX CONCURRENTLY isn't fully
accurate (any more?) when it says "[CREATE INDEX] must wait for all
existing transactions to terminate".

Waiting until an arbitrary Xid dies could be deadlock-prone, if the lock
isn't taken carefully. Imagine a pg_dump coming towards you and then
waiting on the locked table. You'd need to wait at the beginning of the
command, before locks were taken. However, that would means CREATE INDEX
would only be possible outside of transaction blocks, which I don't
think is acceptable.

I wanted this for VACUUM FULL also, but same problem exists.

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com