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?