Looong time ago, I wrote a piece about removing bloat by moving rows away from the end of table, and vacuuming it.
This is/was very slow, and was optimized (to some extent) by Nathan Thom, but his blogpost vanished. Besides, later on we got great tool: pg_reorg (or, as it's currently named: pg_repack).
But recently I was in position where I couldn't pg_reorg. So I had to look for other way. And I found it 🙂
![[ CuTE kitten ]](/wp-content/uploads/2012/12/kitten600.jpg)
In PostgreSQL 8.4 we got CTE – Common Table Expressions. Since then we have this great tool available, but apparently for some people it's still black magic. CuTE, but still magic. I'll try to make it a bit less magical, and more understandable.
On 25th of February, Tom Lane committed patch:
Support data-modifying commands (INSERT/UPDATE/DELETE) IN WITH. This patch implements data-modifying WITH queries according TO the semantics that the updates ALL happen WITH the same command counter VALUE, AND IN an unspecified ORDER. Therefore one WITH clause can't see the effects of another, nor can the outer query see the effects other than through the RETURNING values. And attempts to do conflicting updates will have unpredictable results. We'll need TO document ALL that. This commit just fixes the code; documentation updates are waiting ON author. Marko Tiikkaja AND Hitoshi Harada