Hi fellow list members. I hit a brick wall with my last question. I'd like to
try this again.
I need to remove OIDs from tables without locking the tables for long periods
of time. I have developed a strategy that seems to work, but I would like the
experts to weigh in since I'm planning on doing things to the system tables
that are generally frowned upon.
Prior to running pg_repack I perform these modifications:
mydata=# update pg_class set relhasoids = false where oid =
'a_very_large_table_with_oids'::regclass::oid;UPDATE 1mydata=# delete from
pg_attribute where attrelid = 'a_very_large_table_with_oids'::regclass::oid and
attname = 'oid';DELETE 1mydata=# \d+ a_very_large_table_with_oids;
Table "public.a_very_large_table_with_oids" Column | Type | Modifiers | Storage
| Stats target | Description
--------+------+-----------+----------+--------------+------------- k |
text | not null | extended | | v | text | |
extended | | Indexes: "a_very_large_table_with_oids_pkey"
PRIMARY KEY, btree (k)
mydata=# select oid,* from a_very_large_table_with_oids;ERROR: column "oid"
does not exist
So far so good. I can insert update and delete rows, but the table structure on
disk is unchanged.
So after those modifications I repack the table with vanilla pg_repack. That
copies the data to a fresh new table, sans oids.
Before I start performing these operations on-line on the production data I
wanted some expert eyes on this process since this is mission critical stuff.
On the very large tables we will be in this limbo state for an extended period
of time where pg_class and pg_attribute will have those forced modifications
while pg_repack works its magic. Is there anything to be concerned about if
insert/update/delete seems to be working? My insert/update/deletes on the
tables while pg_repack is running seem to work fine. I also tried this on
tables that have toast tables attached and upon first glance, everything seems
to be in order.
What have I missed?
