Re: Alternative for vacuuming queue-like tables

nagy(at)ecircle-ag(dot)com (Csaba Nagy) writes:
> On Fri, 2006-04-28 at 15:20, kmh496 wrote:
>> 2006-04-28 (금), 14:40 +0200, Csaba Nagy 쓰시길:
>> > I placed a cron job to
>> > cluster the queue table on it's PK index.
>> what does that mean?
>
> Means execute:
>
> CLUSTER pk_queue_table ON queue_table;
>
> See http://www.postgresql.org/docs/8.1/static/sql-cluster.html for more
> details on CLUSTER.
>
> This will actually rebuild the table I guess after it can lock it
> exclusively. Due to the fact that the queue transactions are very short
> lived, the exclusive lock is fast acquired and the clustering operation
> is also fast as the table has only a few entries.
>
> I'm not sure how this operation can work in the presence of other long
> running transactions which did not touch the queue table yet, but it
> actually does work, I can confirm that. Is it violating MVCC maybe ?

There is, I believe, a problem there; there is a scenario where data
can get "dropped out from under" those old connections.

This has been added to the TODO...

http://www.postgresql.org/docs/faqs.TODO.html

* Make CLUSTER preserve recently-dead tuples per MVCC requirements
--
(format nil "~S(at)~S" "cbbrowne" "ntlug.org")
http://cbbrowne.com/info/sgml.html
Should vegetarians eat animal crackers?