Updating auto_renewal column added in 3.2.0 can take hours or days on large systems
| Affects | Status | Importance | Assigned to | Milestone | |
|---|---|---|---|---|---|
| Evergreen |
Won't Fix
|
Low
|
Unassigned | ||
| 3.2 |
Won't Fix
|
Low
|
Unassigned | ||
| 3.3 |
Won't Fix
|
Low
|
Unassigned | ||
| 3.4 |
Won't Fix
|
Low
|
Unassigned | ||
| 3.5 |
Won't Fix
|
Low
|
Unassigned | ||
| 3.6 |
Won't Fix
|
Undecided
|
Unassigned | ||
Bug Description
The auto_renewal column was originally added in 3.2.0 (update 1123) to the action.circulation and action.
It was subsequently recognized (in bug # 1839002, update 1188, found in the 3.2.9-3.2.10, 3.3.3-3.3.5, and 3.3.3-3.4.0 updates) that the column should be NOT NULL and should default to FALSE. This required an UPDATE statement to set all auto_renewal columns with a NULL value to FALSE prior to issuing the ALTER TABLE statement for the tables.
On active systems such as PINES where the circulation tables are very large, the update could take days. Sites have also reported that the unexpected amount of logs generated due to the volume of updates filled all available disk space, crashing replication servers, etc.
By modifying the upgrade 1123 & version upgrade 3.1.5-3.2.0 scripts to add the DEFAULT FALSE NOT NULL attributes to the initial ADD COLUMN statements, we can help other systems that might not yet be on 3.2 avoid this pain.
PostgreSQL treats the subsequent ALTER COLUMN statement as a no-op, and the corresponding UPDATE will not have to touch any rows, so we can leave update 1188 and its version-upgrade equivalents as-is.
| Dan Scott (denials) wrote | #1 |
| Changed in evergreen: | |
| milestone: | none → 3.next |
| Changed in evergreen: | |
| status: | New → Confirmed |
| milestone: | 3.next → 3.5-alpha |
| importance: | Undecided → Low |
| tags: | added: pullrequest |
| Changed in evergreen: | |
| milestone: | 3.5-beta → 3.5.0 |
| Changed in evergreen: | |
| milestone: | 3.5.0 → 3.5.1 |
| Changed in evergreen: | |
| milestone: | 3.5.1 → 3.5.2 |
| Changed in evergreen: | |
| milestone: | 3.5.2 → 3.6.1 |
| Changed in evergreen: | |
| milestone: | 3.6.1 → 3.6.2 |
| Changed in evergreen: | |
| milestone: | 3.6.2 → 3.6.3 |
| Changed in evergreen: | |
| milestone: | 3.6.3 → 3.6.4 |
| Changed in evergreen: | |
| milestone: | 3.6.4 → 3.7.2 |
| Jason Boyer (jboyer) wrote | #2 |
| Jason Stephenson (jstephenson) wrote | #3 |
| Changed in evergreen: | |
| status: | Confirmed → Won't Fix |
| Changed in evergreen: | |
| milestone: | 3.7.2 → none |
I've pushed a branch to user/dbs/
This should be applied to master and backported to all branches.
Would be nice to include in 3.2 as an exception to the "security only" policy.