pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
To: CG <cgg007@yahoo.com>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Removing oids with pg_repack
Date: Wed, 29 Nov 2023 23:54:43 +0200
Message-ID: <e0bebc33-86cc-4332-a7b4-3fb3d00086da@cloud.gatewaynet.com> (raw)
In-Reply-To: <270392623.4500442.1701096716538@mail.yahoo.com>
References: <1375662821.3508272.1700592084461.ref@mail.yahoo.com>
	<1375662821.3508272.1700592084461@mail.yahoo.com>
	<164bcbcf-73a5-4c8c-9016-7a185584d96e@cloud.gatewaynet.com>
	<1411824597.3692573.1700658864193@mail.yahoo.com>
	<6d98a345-0c20-4c7c-9521-f8074ec9cca5@cloud.gatewaynet.com>
	<270392623.4500442.1701096716538@mail.yahoo.com>

Στις 27/11/23 16:51, ο/η CG έγραψε:
>
>
> On Wednesday, November 22, 2023 at 12:38:54 PM EST, Achilleas Mantzios 
> <a.mantzios@cloud.gatewaynet.com> wrote:
>
>
> Στις 22/11/23 15:14, ο/η CG έγραψε:
>
>
> On Wednesday, November 22, 2023 at 01:20:18 AM EST, Achilleas Mantzios 
> <a.mantzios@cloud.gatewaynet.com> 
> <mailto:a.mantzios@cloud.gatewaynet.com> wrote:
>
>
> Στις 21/11/23 20:41, ο/η CG έγραψε:
> I have a very large PostgreSQL 9.5 database that still has very large 
> tables with oids. I'm trying to get rid of the oids with as little 
> downtime as possible so I can prep the database for upgrade past 
> PostgreSQL 11. I had a wild idea to mod pg_repack to write a new table 
> without oids. I think it almost works.
>
> To test out my idea I made a new table wipe_oid_test with oids. I 
> filled it with a few rows of data.
> ........
>> But PostgreSQL still thinks that the table has oids:
>>
>> mydata=# \d+ wipe_oid_test
>>                    Table "public.wipe_oid_test"
>>  Column | Type | Modifiers | Storage  | Stats target | Description
>> --------+------+-----------+----------+--------------+-------------
>>  k      | text | not null  | extended |              |
>>  v      | text |           | extended |              |
>> Indexes:
>>     "wipe_oid_test_pkey" PRIMARY KEY, btree (k)
>> Has OIDs: yes
> Except where does it mention in the pg_repack docs (or source) that it 
> is meant to be used for NO OIDS conversion ?
>
> It does not-- I was trying to leverage and tweak the base 
> functionality of pg_repack which sets up triggers and migrates data. I 
> figured if the target table was created without OIDs that when 
> pg_repack did the "swap" operation that the new table would take over 
> with the added bonus of not having oids.
>
>> I can modify pg_class and set relhasoids = false, but it isn't 
>> actually eliminating the oid column. `\d+` will report not report 
>> that it has oids, but the oid column is still present and returns the 
>> same result before updating pg_class.
>>
> Just Dont!
>
> Noted. ;)
>
>> So I'm definitely missing something. I really need a point in the 
>> right direction.... Please help! ;)
>>
> There are a few of methods to get rid of OIDs :
>
> - ALTER TABLE .. SET WITHOUT OIDS (just mentioning, you already 
> checked that)
>
> This makes the database unusable for hours and hours and hours because 
> it locks the table entirely while it performs the operation. That's 
> just something that we can't afford.
>
> - Use table copy +  use of a trigger to log changes : 
> https://dba.stackexchange.com/questions/259359/eliminating-oids-while-upgrading-postgresql-from-9-4-... 
> <https://dba.stackexchange.com/questions/259359/eliminating-oids-while-upgrading-postgresql-from-9-4-...;
>
> That SO is not quite the effect I'm going for. The poster of that SO 
> was using OIDS in their application and needed a solution to maintain 
> those values after conversion. I simply want to eliminate them without 
> the extraordinary downtime the database would experience during ALTER 
> operations.
> Sorry I meant this one : Stripping OIDs from tables in preparation for 
> pg_upgrade 
> <https://dba.stackexchange.com/questions/286453/stripping-oids-from-tables-in-preparation-for-pg-upgr...;
>
>
>
>
> 	
>
>
> 	
>
>
>     Stripping OIDs from tables in preparation for pg_upgrade
>
> I have a postgres database in RDS, file size approaching 1TB. We 
> started in 2005, using ruby/activerecord/rails...
>
> <https://dba.stackexchange.com/questions/286453/stripping-oids-from-tables-in-preparation-for-pg-upgr...;
>
>
> This is the same idea as the percona ETL strategy, and essentially 90% 
> of what pg_repack already does (creates new tables, sets up triggers, 
> locks the tables, and swaps new for old at the end of the process)
>
>
>>
>> - Use of Inheritance (the most neat solution I have seen, this is 
>> what I used for a 2TB table conversion) : 
>> https://www.percona.com/blog/performing-etl-using-inheritance-in-postgresql/ 
>> <https://www.percona.com/blog/performing-etl-using-inheritance-in-postgresql/;
>>
>> This is closest to the effect I was going for. pg_repack essentially 
>> creates a second table and fills it with the data from the first 
>> table while ensuring standard db operations against that table 
>> continue to function while the data is being moved from the old table 
>> to the new table. The process outlined in the Percona ETL strategy 
>> has to be repeated per-table, which is work I was hoping to avoid by 
>> leveraging 95% of the functionality of pg_repack while supplying my 
>> own 5% as the resulting table would not have oids regardless of the 
>> source table's configuration.
>>
>> For my experiment, Table A did have oids. Table B (created by 
>> pg_repack) did not (at least at creation). When the "swap" operation 
>> happened in pg_repack, the metadata for Table A was assigned to Table 
>> B. I'm just trying to figure out what metadata I need to change in 
>> the system tables to reflect the actual table structure.
>>
>> I have the fallback position for the Percona ETL strategy. But I feel 
>> like I'm REALLY close with pg_repack and I just don't understand 
>> enough about the system internals to nudge it to correctness and need 
>> some expert assistance to tap it in the hole.
> Why don't just inspect the code pg_repack ?
>
> I have, and I have modified pg_repack (modification was shown in my 
> first post) to create and write to a new table without oids, the 
> problem is when the "swap" operation happens the old tabledefs with 
> all the old oid baggage gets mapped on top of the new table that 
> doesn't have oids in it. I need to know what PostgreSQL is seeing in 
> the tabledefs that makes it think this new table, swapped out with the 
> old table, has oids. My thought is if I correct those values in 
> pg_class and elsewhere, the tabledefs will match what is actually on 
> the filesystem after my modified pg_repack has finished processing the 
> tables.


Hi, I think pg_repack eventually calls : 
src/backend/access/heap/heapam.c : heap_update , and this just makes 
sure all cols of both old and new pg_class  have same vals.

That's why, the new table ends up with hasoids = true.

>
>>
>>
>>> CG
>>>
>>>
>>>
>>>
>> -- 
>> Achilleas Mantzios
>>   IT DEV - HEAD
>>   IT DEPT
>>   Dynacom Tankers Mgmt
> -- 
> Achilleas Mantzios
>   IT DEV - HEAD
>   IT DEPT
>   Dynacom Tankers Mgmt

-- 
Achilleas Mantzios
  IT DEV - HEAD
  IT DEPT
  Dynacom Tankers Mgmt

view thread (8+ messages)

Message-ID: <e0bebc33-86cc-4332-a7b4-3fb3d00086da@cloud.gatewaynet.com>
Permalink:  ../e0bebc33-86cc-4332-a7b4-3fb3d00086da@cloud.gatewaynet.com/
Also on:    postgresql.org/message-id/e0bebc33-86cc-4332-a7b4-3fb3d00086da@cloud.gatewaynet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: a.mantzios@cloud.gatewaynet.com, cgg007@yahoo.com
  Subject: Re: Removing oids with pg_repack
  In-Reply-To: <e0bebc33-86cc-4332-a7b4-3fb3d00086da@cloud.gatewaynet.com>
  References: <1375662821.3508272.1700592084461.ref@mail.yahoo.com> <1375662821.3508272.1700592084461@mail.yahoo.com> <164bcbcf-73a5-4c8c-9016-7a185584d96e@cloud.gatewaynet.com> <1411824597.3692573.1700658864193@mail.yahoo.com> <6d98a345-0c20-4c7c-9521-f8074ec9cca5@cloud.gatewaynet.com> <270392623.4500442.1701096716538@mail.yahoo.com> <e0bebc33-86cc-4332-a7b4-3fb3d00086da@cloud.gatewaynet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox