Oops! josh@agliodbs.com (Josh Berkus) was seen spray-painting on a wall:
>> I understand this needs an exclusive lock on the whole table, which is
>> simply not possible more than once a month, if that... Workarounds/hack
>> suggestions are more than welcome :-)
>
> Would it be reasonable to use partial indexes on the table?
Dumb question...
... If you create a partial index, does this lock the whole table
while it is being built, or only those records that are affected by
the index definition?
I expect that the answer to that is "Yes, it locks the whole table,"
which means that a partial index won't really help very much, except
insofar as you might, by having it be restrictive in range, lock the
table for a somewhat shorter period of time.
An alternative that may or may not be viable would be to have a series
of tables:
create table t1 ();
create table t2 ();
create table t3 ();
create table t4 ();
Then create a view:
create view t as select * from t1 union all select * from t2 union
all select * from t13 union all select * from t4;
Then you set this view to be updatable, by having a function that
rotates between the 4 tables based on a sequence.
You do SELECT NEXTVAL('t_controller') and the entries start flooding
into t2 rather than t1, or into t3, or into t4, and after t4, they go
back into t1.
When you need to reindex t1, you switch over to load entries into t2,
do maintenance on t1, and then maybe roll back to t1 so you can do the
same maintenance on t2.
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://www3.sympatico.ca/cbbrowne/lisp.html
Linux is like a Vorlon. It is incredibly powerful, gives terse,
cryptic answers and has a lot of things going on in the background.
