After takin a swig o' Arrakan spice grog, shridhar_daithankar@persistent.co.in ("Shridhar Daithankar") belched out...:
> On 2 Sep 2003 at 10:19, Vivek Khera wrote:
>
>> >>>>> "SD" == Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:
>>
>> >> second largest table, and 5 per index on the third largest, then about
>> >> 90 seconds total for the rest of the tables ;-)
>>
>> SD> Umm.. Since you have only 2.7GB of data, all inclusive, would it
>> SD> be real downtime if you reindex in a transaction, assuming the
>> SD> "downtime" was not due to crunch of IO bandwidth..
>>
>> Reindexing a table takes an exclusive table lock. If I did it inside
>> a transaction, wouldn't it still take that lock and block out all
>> other access?
>
> Well, you donm't need to reindex as such. You can create a new index
> from scratch and drop the old one inside a transaction.
>
> That will be perfectly non-blocking I believe..
That won't block _reads_ on the table.
It will block writes to the table during the duration of the
transaction.
After all, if you insert a row into the table whilst the index
creation is taking place, there's a bit of a conflict:
-> For the system to remain consistent, that row's data either must
be added to the index-in-progress, or be deferred 'til later;
-> Since the index creation is inside the transaction, the insert
shouldn't be able to "see" the index yet.
The insert obviously can't affect an index that it can't yet see, so
what happens in practice is that PostgreSQL blocks the insert until
the index is complete.
>> Perhaps I need to write an 'auto_reindex' script to notice when
>> this is necessary and schedule one to run at the wee hours in the
>> morning at the end of the week...
> Once again, with 7.4, not needed anymore..
I haven't had a chance to verify the non-necessity on real data; I
would very much like to see more of our apps testing on 7.4 so as to
verify this, but there's enough work validating that it's all good on
7.3.4...
--
select 'cbbrowne' || '@' || 'acm.org';
http://www.ntlug.org/~cbbrowne/nonrdbms.html
When I die, I'd like to go peacefully in my sleep like my grandfather,
not screaming in terror like his passengers...
