Re: [HACKERS] DROP COLUMN (was RFC: Restructuring pg_aggregate)
[ way past time to change the title of this thread ] Christopher Kings-Lynne [EMAIL PROTECTED] writes: OK, sounds fair. However, is there a more aggressive way of reclaiming the space? The problem with updating all the rows to null for that column is that the on-disk size is doubled anyway, right? So, could a VACUUM FULL process do the nulling for us? Vacuum works outside of normal transaction constraints anyway...? No, VACUUM has the same transactional constraints as everyone else (unless you'd like a crash during VACUUM to trash your table...) I do not think that we necessarily need to provide a special mechanism for this at all. The docs for DROP COLUMN could simply explain that the DROP itself doesn't reclaim the space, but that the space will be reclaimed over time as extant rows are updated or deleted. If you want to hurry the process along you could do UPDATE table SET othercol = othercol VACUUM FULL to force all the rows to be updated and then reclaim space. But given the peak-space-is-twice-as-much behavior, this is not obviously a win. I'd sure object to an implementation that *forced* that approach on me, whether during DROP itself or the next VACUUM. Also, it seems to me that at some point we are forced to break client compatibility. Either we add attisdropped field to pg_attribute, or we use Hiroshi's (-1 * attnum - offset) idea. Both Tom and Hiroshi have good reasons for each of these - would it be possible for you guys to post with your reasons for and against both the techniques. Er, didn't we do that already? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] DROP COLUMN (was RFC: Restructuring pg_aggregate)
On Sat, 2002-04-13 at 17:29, Tom Lane wrote: [ way past time to change the title of this thread ] Christopher Kings-Lynne [EMAIL PROTECTED] writes: OK, sounds fair. However, is there a more aggressive way of reclaiming the space? The problem with updating all the rows to null for that column is that the on-disk size is doubled anyway, right? So, could a VACUUM FULL process do the nulling for us? Vacuum works outside of normal transaction constraints anyway...? No, VACUUM has the same transactional constraints as everyone else (unless you'd like a crash during VACUUM to trash your table...) But can't it do the SET TO NULL thing if it knows that the transaction that dropped the column has committed. This could probably even be done in the light version of vacuum with a special flag (VACUUM RECLAIM). Of course running this this makes sense only if the dropped column had some significant amount of data . I do not think that we necessarily need to provide a special mechanism for this at all. The docs for DROP COLUMN could simply explain that the DROP itself doesn't reclaim the space, but that the space will be reclaimed over time as extant rows are updated or deleted. If you want to hurry the process along you could do UPDATE table SET othercol = othercol VACUUM FULL If only we could do it in namageable chunks: FOR i IN 0 TO (size(table)/chunk) DO UPDATE table SET othercol = othercol OFFSET i*chunk LIMIT chunk VACUUM FULL; END FOR; or even better - VACUUM FULL OFFSET i*chunk LIMIT chunk and then make chunk == 1 :) -- Hannu ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] DROP COLUMN (was RFC: Restructuring pg_aggregate)
Hannu Krosing [EMAIL PROTECTED] writes: No, VACUUM has the same transactional constraints as everyone else (unless you'd like a crash during VACUUM to trash your table...) But can't it do the SET TO NULL thing if it knows that the transaction that dropped the column has committed. Hmm, you're thinking of allowing VACUUM to overwrite tuples in-place? Strikes me as unsafe, but I'm not really sure. In any case it's not that easy. If the column is wide enough that reclaiming its space is actually worth doing, then presumably most of its entries are just TOAST links, and what has to be done is not just rewrite the main tuple but mark the TOAST rows deleted. This is not something that VACUUM does now; I'd be rather concerned about the locking implications (especially for lightweight VACUUM). regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] DROP COLUMN (was RFC: Restructuring pg_aggregate)
No, VACUUM has the same transactional constraints as everyone else (unless you'd like a crash during VACUUM to trash your table...) Seriously, you can run VACUUM in a transaction and rollback the movement of a tuple on disk? What do you mean by same transactional constraints? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
