On 5/20/20 11:22 AM, Tory M Blue wrote:
On Tue, May 19, 2020 at 10:06 AM Ron <ronljohnso...@gmail.com
<mailto: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
<mailto:t...@sss.pgh.pa.us>> wrote:
Tory M Blue <tmb...@gmail.com <mailto: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.
Since the index creation would be separate from your application's access,
it doesn't actually matter.
Also this has been running for 16 hours now and still not done. I think
it's forcing index creation regardless.
If it's rewriting the table's pages, then the indexes must be rewritten, too,
When removing OIDs, is the priority to minimize absolute time, or to
minimize downtime/degradation as sen by the application?
--
Angular momentum makes the world go 'round.
