Recently I've seen case like:
Why? How to avoid the problem?
Continue reading How to run short ALTER TABLE without long locking concurrent queries
On 30th of July, Andrew Dunstan committed patch:
Add IF NOT EXISTS processing to ALTER TABLE ADD COLUMN Fabrízio de Royes Mello, reviewed by Payal Singh, Alvaro Herrera and Michael Paquier.
Continue reading Waiting for 9.6 – Add IF NOT EXISTS processing to ALTER TABLE ADD COLUMN
On 7th of December, Simon Riggs committed patch:
Event Trigger for table_rewrite Generate a table_rewrite event when ALTER TABLE attempts to rewrite a table. Provide helper functions to identify table and reason. Intended use case is to help assess or to react to schema changes that might hold exclusive locks for long periods. Dimitri Fontaine, triggering an edit by Simon Riggs Reviewed in detail by Michael Paquier
Continue reading Waiting for 9.5 – Event Trigger for table_rewrite
On 22nd of August, Alvaro Herrera committed patch:
Implement ALTER TABLE .. SET LOGGED / UNLOGGED This enables changing permanent (logged) tables to unlogged and vice-versa. (Docs for ALTER TABLE / SET TABLESPACE got shuffled in an order that hopefully makes more sense than the original.) Author: Fabrízio de Royes Mello Reviewed by: Christoph Berg, Andres Freund, Thom Brown Some tweaking by Álvaro Herrera
Continue reading Waiting for 9.5 – Implement ALTER TABLE .. SET LOGGED / UNLOGGED
Three patches for you today, all committed by Robert Hass:
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.
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.
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
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
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
On 28th of July, Simon Riggs committed patch which:
Log Message: ----------- Reduce LOCK levels OF CREATE TRIGGER AND SOME ALTER TABLE, CREATE RULE actions. Avoid hard-coding lockmode used FOR many altering DDL commands, allowing easier future changes OF LOCK levels. Implementation OF initial analysis ON DDL sub-commands, so that many LOCK levels are now at ShareUpdateExclusiveLock OR ShareRowExclusiveLock, allowing certain DDL NOT TO block reads/writes. FIRST OF NUMBER OF planned changes IN this area; additional docs required WHEN FULL project complete.
Continue reading Waiting for 9.1 – Reduced lock levels for ALTER TABLE