Re: Off-label use for pg_repack

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