Re: clustering without locking

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: clustering without locking
Date: 2008-05-03 18:33:18
Message-ID: 12367.1209839598@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> writes:
> Begin a transaction and free the first chunk (2 tuples in this case, but
> obviously many more in a real case):

> -----------
> ..473612058
> -----------

> Use that freed space to store the first ordered tuples:

> -----------
> 014736.2.58
> -----------

> Commit, and when the last transaction to which the "." tuples above are
> still visible completes mark them as free with VACUUM or similar.

> -----------
> 014736 2 58
> -----------

Oh, the problem is that you're misexplaining this. You can't do it like
that: you can't overwrite the moved-up "." tuples until after they
aren't visible to any other transaction. So you need two transactions
to do the above. I'm not sure if you need two "wait for all others" or
just one --- it's not clear to me what's the urgency of clearing out the
moved-down tuples after they're moved down. (You *would* want to vacuum
every so often, but this is to reclaim index space, not so much heap
space because you'll reuse that anyway.)

Anyway I think the main practical problem would be with deadlocks
against other transactions trying to update/delete tuples at the same
times you need to move them. Dealing with uncommitted insertions would
be tricky too --- I think you'd need to wait out the inserting
transaction, which would add more possibilities of deadlock.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Craig Ringer 2008-05-03 18:34:38 Re: large query by offset and limt
Previous Message Craig Ringer 2008-05-03 18:27:54 Re: Executing dynamic procedure call