No table comments migration

[mkgrgis]

Here is proposed query for PostgreSQL comment commands over current database after

CREATE FOREIGN TABLE sys.all_tab_comments (
    "owner" varchar(30) NOT NULL,
    table_name varchar(30) NOT NULL,
    table_type varchar(11) NULL,
    "comments" varchar(4000) NULL
)
SERVER ....
OPTIONS (schema 'SYS', table 'ALL_TAB_COMMENTS', readonly 'true');

A query for PostgreSQL table comments

with
topt as(
select o.foreign_table_catalog,
       o.foreign_table_schema,
       o.foreign_table_name,
       o.option_value table_name
  from information_schema.foreign_table_options o
 where o.option_name = 'table'
),
sopt as(
select o.foreign_table_catalog,
       o.foreign_table_schema,
       o.foreign_table_name,
       o.option_value schema_name       
  from information_schema.foreign_table_options o
 where o.option_name = 'schema'
)
select 'comment on foreign table "' || foreign_table_schema || '"."' || foreign_table_name || '" '
       || 'is ''' || replace(atc."comments", '''', '''''')  || ''';' "SQL",
       atc."owner" "ORA schema",
       atc.table_name "ORA table",
       atc."comments",
       foreign_table_catalog ft_db,
       foreign_table_schema ft_sc,
       foreign_table_name ft_tn
  from information_schema.foreign_tables ft 
  left join topt t
 using (foreign_table_catalog, foreign_table_schema, foreign_table_name)
  left join sopt s
 using (foreign_table_catalog, foreign_table_schema, foreign_table_name)
  left join sys.all_tab_comments atc -- foreign table for Oracle table comments
    on ft.foreign_server_name = (select foreign_server_name
                                   from information_schema.foreign_tables ft2
                                  where ft2.foreign_table_schema ='sys'
                                    and foreign_table_name = 'all_tab_comments') 
    atc."owner" = coalesce(s.schema_name, foreign_table_schema)
   and atc.table_name = coalesce(t.table_name, foreign_table_name)
 where atc."comments" is not null
   and ft.foreign_table_catalog = current_catalog

This feature request opened after https://github.com/laurenz/oracle_fdw/issues/660.

[laurenz]

I think that the approach should be as follows:

• in the db_migrator project, the tables and columns tables in the PostgreSQL staging schema should grow another column comment
• at the same time, the corresponding foreign tables in the FDW staging schema created by ora_migrator should grow the same columns

Then db_migrator could migrate comments too. There should probably be an option indicating if you want to migrate comments or not.

Note that this is about migrating data from Oracle or other databases to PostgreSQL, not about adding comments to foreign tables like your original proposal for oracle_fdw suggested.

[mkgrgis]

Note that this is about migrating data from Oracle or other databases to PostgreSQL, not about adding comments to foreign tables like your original proposal for oracle_fdw suggested.

Look like in this workaround foreign tables are something like temporary objects for one time data transferring, not for continuous usage?

[mkgrgis]

Also I have noticed there is no DBA_TAB_COMMENTS or ALL_TAB_COMMENTS in https://github.com/cybertec-postgresql/ora_migrator?tab=readme-ov-file#prerequisites now, hence there is no migration of table comments at all.

[laurenz]

Yes, there are no provisions for that yet; that would have to be added. But if your goal is not migration from Oracle, perhaps we should leave the discussion in laurenz/oracle_fdw#660.

[laurenz]

Shall we close this one too?

[mkgrgis]

Shall we close this one too?

I'll try to adopt the query for migration context, @laurenz . I want to try ora_migrator next week, now I am studying code of this utility.

[mkgrgis]

@laurenz , look like I should add a query result in unified format as tab_comments_sql variable for table comments after this block https://github.com/cybertec-postgresql/ora_migrator/blob/87adf42a34d3ca02eea31feb701cc6150f97a586/ora_migrator--1.1.0.sql#L631 and then use like EXECUTE format(columns_sql, schema, server, v_max_long); ? Did I ignored some important aspects?

[laurenz]

Yes, you would add a foreign table that provides the information. Ideally, you would add support for migrating these data to db_migrator. I don't know if all that is worth the effort...

[mkgrgis]

Closed after merged implementation in https://github.com/cybertec-postgresql/ora_migrator/pull/46.