On Thu, 2005-12-08 at 16:23 +0100, Csaba Nagy wrote:
> On Thu, 2005-12-08 at 16:05, Tom Lane wrote:
> [SNIP]
> > There isn't any way for it to do that (ie, be sure everyone else has
> > adjusted to the new state of affairs), short of acquiring some sort of
> > short-term exclusive lock on the table, which is a really bad idea.
> > The pending lock would block other incoming requests on the table until
> > all the current users exited their transactions.
It might be possible to do this with some new form of utility lock that
can be unlocked before end of transaction, then re-locked again later.
That would imply the use of optimistic locking, as already discussed.
Only DDL and VACUUMs *need* to be locked out during the REINDEX.
I'm hand-waving here, so I'll stop. But we do know there *is* a way,
because this is already implemented elsewhere, somehow.
> But it is an acceptable compromise to lock the table until all current
> transactions are over... the alternative for reindexing a big table is
> usually to schedule a down-time, which is even worse...
>
> REINDEX is usually used to fix a big tables big index bloat, and that
> won't fly without a downtime, or, with this short-term full table lock
> in a low-traffic time-slot.
>
> For my usage patterns I would vote with the table lock if it is just a
> means of blocking new transactions until the running ones finish. I'll
> just make sure there are none long running when I issue the REINDEX...
Certainly that is my view. You only schedule these things when you have
to, picking as light traffic period as you can. Most people would accept
some pretty hefty restrictions in order to get it to work.
Best Regards, Simon Riggs