On Tue, May 19, 2020 at 10:06 AM Ron <ronljohnso...@gmail.com> wrote:
> On 5/19/20 11:51 AM, Tory M Blue wrote: > > > > On Tue, May 19, 2020 at 6:40 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > >> Tory M Blue <tmb...@gmail.com> writes: >> > The command i'm using is >> > ALTER TABLE tablename SET WITHOUT OIDS; >> > Would a drop column oid be better? >> >> Unfortunately, you're kind of stuck. OIDs are not like regular columns >> (at least before v12) --- they are integrated into the tuple header in >> a hackish way, and so there's no way to get rid of them without a table >> rewrite. >> >> regards, tom lane >> > > Poop :) kind of figured that, so it's just painful. > > But I guess if it's doing a table rewrite, is there any configuration > params I could boost to help it? Shared_buffers, give it more, work mem, > maintenance mem, temp buffers anything you can think of? > > > There's an alternative if this is a "transaction table" (named, in this > example, FOO) which never gets updated (only inserted into and selected > from). > > Create a new, partitioned, oid-free copy of the table (named NEW_FOO) > that's populated with *most* of the records (all except the most > recent). When ready to cut over, you'd stop the applications, copy over > the most current records from FOO to NEW_FOO and then rename FOO to OLD_FOO > and FOO to OLD_FOO. > > Then you can drop OLD_FOO. > > -- > Angular momentum makes the world go 'round. > Thanks Ron, Looked into this but we have large indexes that take 8-12 hours to create. So my gut says this would not buy us anytime. Also this has been running for 16 hours now and still not done. I think it's forcing index creation regardless. Really a crappy situation!!! Tory
