On Sun, Feb 24, 2019 at 5:43 PM Justin Pryzby <pry...@telsasoft.com> wrote:
> On Sun, Feb 24, 2019 at 04:34:34PM -0500, Corey Huinker wrote: > > I think your solution may be something like this: > > 1. Create a new table, same columns, partitioned on the pending column. > > 2. Rename your existing queue table old_queue to the partitioned table > as a > > default partition. > > 3. Rename new table to queue > > 4. add old_queue as the default partition of queue > > 5. add a new partition for pending = true rows, set the fillfactor kind > of > > FYI, the "default partition" isn't just for various and sundry > uncategorized > tuples (like a relkind='r' inheritence without any constraint). It's for > "tuples which are excluded by every other partition". And "row migration" > doesn't happen during "ALTER..ATTACH", only UPDATE. So you'll be unable to > attach a partition for pending=true if the default partition includes any > such > rows: > > |ERROR: updated partition constraint for default partition "t0" would be > violated by some row > > I think you'll need to schedule a maintenance window, create a new > partitioned > heirarchy, and INSERT INTO queue SELECT * FROM old_queue, or similar. > > Justin > Good point, I forgot about that. I had also considered making a partitioned table, adding a "true" partition to that, and then making the partitioned table an *inheritance* partition of the existing table, then siphoning off rows from the original table until such time as it has no more pending rows, then doing a transaction where you de-inherit the partitioned table, and then attach the original table as the false partition. It's all a lot of acrobatics to try to minimize downtime and it could be done better by having a longer maintenance window, but I got the impression from the OP that big windows were not to be had.
