Tag: alter

Waiting for 9.2 – More rewrite-less ALTER TABLE ALTER TYPEs

Three patches for you today, all committed by Robert Hass:

  • On 7th of February, patch: 
    Add a transform function for numeric typmod coercisions.
 
This enables ALTER TABLE to skip table and index rebuilds when a column
is changed to an unconstrained numeric, or when the scale is unchanged
and the precision does not decrease.
 
Noah Misch, with a few stylistic changes and a fix for an OID
collision by me.
  • also on 7th, patch: 
    Add a transform function for varbit typmod coercisions.
 
This enables ALTER TABLE to skip table and index rebuilds when the
new type is unconstraint varbit, or when the allowable number of bits
is not decreasing.
 
Noah Misch, with review and a fix for an OID collision by me.
  • and a day later final patch: 
    Add transform functions for various temporal typmod coercisions.
 
This enables ALTER TABLE to skip table and index rebuilds in some cases.
 
Noah Misch, with trivial changes by me.

Continue reading Waiting for 9.2 – More rewrite-less ALTER TABLE ALTER TYPEs

Waiting for 9.2 – Avoiding reindexing on alter type of indexed column

On 18th of July, Robert Haas committed patch:

Avoid index rebuild for no-rewrite ALTER TABLE .. ALTER TYPE.
 
Noah Misch.  Review and minor cosmetic changes by me.

Continue reading Waiting for 9.2 – Avoiding reindexing on alter type of indexed column

Waiting for 9.1 – Rewrite-less changing types of column

On 12th of February, Robert Haas committed patch:

Teach ALTER TABLE .. SET DATA TYPE to avoid some table rewrites.
 
When the old type is binary coercible to the new type and the using     
clause does not change the column contents, we can avoid a full table
rewrite, though any indexes on the affected columns will still need
to be rebuilt.  This applies, for example, when changing a varchar
column to be of type text.
 
The prior coding assumed that the set of operations that force a
rewrite is identical to the set of operations that must be propagated
to tables making use of the affected table's rowtype.  This is
no longer true: even though the tuples in those tables wouldn't
need to be modified, the data type change invalidate indexes built
using those composite type columns.  Indexes on the table we're
actually modifying can be invalidated too, of course, but the
existing machinery is sufficient to handle that case.
 
Along the way, add some debugging messages that make it possible
to understand what operations ALTER TABLE is actually performing
in these cases.                                                      
 
Noah Misch and Robert Haas

Later on, on 15th, he committed second patch with few more cases where rewrite can be avoided.

Continue reading Waiting for 9.1 – Rewrite-less changing types of column

Waiting for 9.1 – adding values to enums

Finally, on 25th of October, Tom Lane committed patch which does:

Allow new values to be added to an existing enum type.
 
After much expenditure of effort, we've got this to the point where the
performance penalty is pretty minimal in typical cases.
 
Andrew Dunstan, reviewed by Brendan Jurd, Dean Rasheed, and Tom Lane

Continue reading Waiting for 9.1 – adding values to enums