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@xxxxxxxxxxx
