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(dot)klaver(at)aklaver(dot)com