Re: Alternative for vacuuming queue-like tables

Csaba Nagy wrote:
>> 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
>
> OK, I can see this being a problem in the general case.
>
> However, for my queue table the current behavior is a life-saver. Would
> it be possible to still provide a variant of rebuild which is documented
> to not be MVCC compliant ? Something like: I don't care the old
> transactions which did not touch yet this table to see the old data for
> this table, I want to rebuild it.
>
> I actually don't care about clustering in this case, only about
> rebuilding the table once I can get an exclusive lock on it. I guess
> this would be possible with the "switch to a copy" method except the new
> table is a different entity (as mentioned in another post) and it will
> not preserve the dependencies of the original table.
>
> I guess what I'm asking for is a kind of "REBUILD TABLE" which is not
> MVCC by definition but it would be useful in the mentioned queue table
> case.
The general problem seems to be that a transaction has no way to promise
never to touch a specific table. Maybe some kind of "negative lock"
would help here - you'd do "exclude table foo from transaction" at the
start of your transaction, which would cause postgres to raise an error
if you indeed tried to access that table. Vacuum could then ignore your
transaction when deciding which tuples it can safely remove from the
table foo.

This would be a nice feature IMHO - would it be possible to do something
like that, or am I overlooking something.

greetings, Florian Pflug