Thread: Off-label use for pg_repack
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 1
mydata=# delete from pg_attribute where attrelid = 'a_very_large_table_with_oids'::regclass::oid and attname = 'oid';
DELETE 1
mydata=# \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?
On 11/28/23 08:46, CG wrote: > 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 1 > mydata=# delete from pg_attribute where attrelid = > 'a_very_large_table_with_oids'::regclass::oid and attname = 'oid'; > DELETE 1 Not sure about the below, but in the above: ::regclass::oid is redundant, ::regclass will suffice. Also for pg_class you can do where relname = 'a_very_large_table_with_oids'; > mydata=# \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? > > -- Adrian Klaver adrian.klaver@aklaver.com