`IMPORT FOREIFN SCHEMA don't transfer comments from Oracle

[mkgrgis]

After IMPORT FOREIFN SCHEMA command we have foreign tables without any comments. Original comments of Oracle tables, view, materialized views and it's columns are very useful in PostgreSQL, because usually it's nothing to comment in foreign tables which is not existed Oracle comment.

[laurenz]

That's a good idea, but I don't think it will be possible to implement that with IMPORT FOREIGN SCHEMA. IMPORT FOREIGN SCHEMA returns a list of CREATE FOREIGN TABLE statements to PostgreSQL, and you cannot specify a comment as part of the CREATE statement.

[mkgrgis]

IMPORT FOREIGN SCHEMA returns a list of CREATE FOREIGN TABLE statements to PostgreSQL

Could you please explain me, is this limitation of current oracle_fdw implementation or this is result of some standards? In https://www.postgresql.org/docs/current/sql-importforeignschema.html thee is a description which don't exclude comments as part of table representation:

IMPORT FOREIGN SCHEMA creates foreign tables that represent tables existing on a foreign server.

How do you think @laurenz , is additional loop for COMMENT commands possible in current implementation? Or it will be better add this commands after CREATE TABLE?

[laurenz]

If you look at the documentation, you'll see:

This function is called when executing IMPORT FOREIGN SCHEMA, and is passed the parse tree for that statement, as well as the OID of the foreign server to use. It should return a list of C strings, each of which must contain a CREATE FOREIGN TABLE command. These strings will be parsed and executed by the core server.

So what is limiting us here is the foreign data wrapper API of PostgreSQL. There is nothing oracle_fdw can do about it.

But you could create a foreign table for the Oracle comment metadata and import them yourself.

[mkgrgis]

Thanks for clarification, @laurenz ! Maybe we can describe not simple import of comments in documentation of oracle_fdw?

[laurenz]

I think that is too specialized for the main documentation. But there is the Wiki that may be perfect for this kind of information: https://github.com/laurenz/oracle_fdw/wiki

[mkgrgis]

But there is the Wiki that may be perfect for this kind of information

Thanks. I have a plans to find a query for updating comments against Oracle source and something like minus with PostgreSQL information_schema metadata. How do you think, @laurenz , will non-supruser function with the functional useful for oracle_fdw?

[laurenz]

I think this is too narrow for inclusion in oracle_fdw. If anything, I could see a place for this in ora_migrator.

[mkgrgis]

@laurenz , could you help me about query for import of comments for columns? Unfortunately oracle_fdw have no column or column_name option like sqlite_fdw or firebird_fdw. This cause to some unusual predicate of joining between PostgreSQL and Oracle metadata. Let's use PostgreSQL metadata:

with
topt as ( -- non original name of a table
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 ( -- non original name of a schema
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'
),
pg_foreign_columns as (
select foreign_table_catalog,
       foreign_table_schema,
       foreign_table_name,
       foreign_server_name,
       c.column_name,
       t.table_name,
       s.schema_name
  from information_schema."columns" c
 inner join information_schema.foreign_tables ft  
    on c.table_catalog = foreign_table_catalog
   and c.table_schema = foreign_table_schema
   and c.table_name = foreign_table_name 
  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)
 )
select * from pg_foreign_columns;

What is the best way to join this metadata with oracle metadata about columns? In my case there is experimental and ugly and (acc.column_name = pgfc.column_name or acc.column_name = upper(pgfc.column_name)), please refer full context as continue of previous query

select 'comment on column "' || foreign_table_schema || '"."' || foreign_table_name || '"."' || pgfc.column_name || '" '
       || 'is ''' || replace(acc."comments", '''', '''''')  || ''';' "SQL",
       acc."owner" "ORA schema",
       acc.table_name "ORA table",
       acc.column_name "ORA column",
       acc."comments"
  from pg_foreign_columns pgfc
 inner join sys.all_col_comments acc -- foreign table for Oracle column comments
    on pgfc.foreign_server_name = (select foreign_server_name
                                   from information_schema.foreign_tables ft2
                                  where ft2.foreign_table_schema ='sys'
                                    and foreign_table_name = 'all_col_comments') 
   and acc."owner" = coalesce(pgfc.schema_name, pgfc.foreign_table_schema)
   and acc.table_name = coalesce(pgfc.table_name, pgfc.foreign_table_name) 
   and (acc.column_name = pgfc.column_name
    or acc.column_name = upper(pgfc.column_name))
 where acc."comments" is not null
   and pgfc.foreign_table_catalog = current_catalog;

The main problem is some schemas were imported with case = keep, some other with default case = smart option. This information look like is not saving in PostgreSQL metadata and exists only as result. May a column order be helpful in this case?

[laurenz]

Sorry for my confusing comment in cybertec-postgresql/ora_migrator#44; we should really continue the discussion there.

I'm closing this issue as "won't fix", since I have no idea how it could fit well within the limits of a foreign data wrapper, except that you can create a foreign table to access Oracle table comments.

[mkgrgis]

I'm closing this issue as "won't fix", since I have no idea how it could fit well within the limits of a foreign data wrapper...

Really this migration of comments is not simple for C implementation during current FDW limitations. It will be enough if comment migration queries will be discussed here. Could you @laurenz please give me some advices before closing this issue about corresponding PostgreSQL and Oracle columns in https://github.com/laurenz/oracle_fdw/issues/660#issuecomment-2042226849 because join predicate between PostgreSQL and Oracle metadata touches some internal structures of oracle_fdw?

[laurenz]

I didn't understand your query above, and I don't really see the problem. If I wanted to copy comments from Oracle to PostgreSQL, I would create foreign tables for ALL_TAB_COMMENTS and ALL_COL_COMMENTS and then run something like

DO
$$DECLARE
   com text;
   col text;
BEGIN
   SELECT comments INTO com
   FROM all_tab_comments
   WHERE lower(table_name) = 'mytable';

   EXECUTE format('COMMENT ON FOREIGN TABLE mytable IS %L', com);

   FOR col IN
      SELECT column_name
      FROM information_schema.columns
      WHERE table_name = 'mytable'
   LOOP
      SELECT comments INTO com
      FROM all_col_comments
      WHERE lower(table_name) = 'mytable'
        AND lower(column_name) = col;

      EXECUTE format(
                 'COMMENT ON COLUMN %I.%I IS %L',
                 'mytable',
                 col,
                 com
              );
   END LOOP;
END;$$;

[mkgrgis]

Thanks @laurenz , I saw a column connection predicate between PostgreSQL and Oracle lower(column_name) = col; in your example with procedure. This predicate works for my examples except to Oracle table with x number and X RAW(16) columns. How can we differ this columns in this context?

[laurenz]

Well, that was not a 100% solution that covers exotic cases like that, where a table has two columns that only differ in case.

You'd probably have to rewrite oracle_fdw's function fold_case() in SQL.

[mkgrgis]

@laurenz , yes, this problem touches fold_case() function, but not covers. fold_case() function works during IMPORT FOREIGN SCHEMA, but my question is about universal predicate of joining independent of IMPORT FOREIGN SCHEMA and case option. How does oracle_fdw recognise "this foreign table column represents that Oracle column" if there are no original column names in PostgreSQL foreign table metadata? Maybe after CREATE FOREIGN TABLE some column order is used? It's hard for me to find C code about this in the FDW, I have seen fdwState->oraTable->cols[i] where there are oratype and pgtype , but not column names.

[laurenz]

There is no way to reliably map Oracle table names to foreign table names. For one, if you don't use IMPORT FOREIGN SCHEMA, you can give the foreign table an unrelated name. You could try consulting the table option, but that could be a query, so there is no way to do that reliably.

Any solution will have to contain some kind of heuristic.

[mkgrgis]

There is no way to reliably map Oracle table names to foreign table names.

I did it in the query from https://github.com/cybertec-postgresql/ora_migrator/issues/44 , @laurenz . Unfortunately, you are wrong here, but maybe the query is not universal.

For one, if you don't use IMPORT FOREIGN SCHEMA, you can give the foreign table an unrelated name. You could try consulting the table option, but that could be a query, so there is no way to do that reliably.

Yes, there is a query with such metadata connection predicate as all_tab_comments.table_name = coalesce( information_schema.foreign_table_options.table_name, information_schema.foreign_tables.foreign_table_name) The predicate works with both result of IMPORT FOREIGN SCHEMA and some not automated foreign tables with existed table names in Oracle.

I am sorry, my question was not about table names, but about column names. I have no problems with mapping (connection predicate between PostgreSQL and Oracle metadata) for tables, but frustrated about columns in well-mapped foreign table. How does oracle_fdw stores Oracle references for fdwState->oraTable->cols[i]->name in PostgreSQL metadata? FDW have a data structure with both fdwState->oraTable->cols[i]->name and fdwState->oraTable->cols[i]->pgname, but how does this structure fills from metadata PostgreSQL tables? I have found only https://github.com/laurenz/oracle_fdw/blob/33c4086ac346f2afc6153a4ce59a4cf8c63c505a/oracle_fdw.c#L5669 and reverse https://github.com/laurenz/oracle_fdw/blob/33c4086ac346f2afc6153a4ce59a4cf8c63c505a/oracle_fdw.c#L5536 , look like column_data comes from fdw_private , but https://github.com/laurenz/oracle_fdw/blob/33c4086ac346f2afc6153a4ce59a4cf8c63c505a/oracle_fdw.c#L2879 function contains nothing about oracle name.

Any solution will have to contain some kind of heuristic.

But oracle_fdw implements in C some deterministic logic can be implemented also for metadata tables in SQL. Please help me to understand this logic.

[laurenz]

I think I am starting to understand what you mean: you want to know how Oracle columns are mapped to PostgreSQL columns.

That's pretty simple: the first Oracle column will be mapped to the first PostgreSQL column, the second to the second, and so on. This is independent of the column name. So if you use information_schema.columns as a data source, the join condition would be on ordinal_position.

It looks like this might be ALL_TAB_COLUMNS.COLUMN_ID in Oracle, but the description is a bit unclear: will there be a gap in the sequence if you drop a column?

[mkgrgis]

Thanks, @laurenz ! This is the answer on my question. I'll write a query to verify your version and I'll make experiment about effect in foreign table after DROP COLUMN in base Oracle table .

will there be a gap in the sequence if you drop a column?

[mkgrgis]

will there be a gap in the sequence if you drop a column?

Let's prepare a table and metadata views for both sides - Oracle and PostgreSQL.

Oracle table:

CREATE TABLE テスト."test" (
    "c1" VARCHAR2(100) NULL,
    "c2" DATE NULL,
    "c3" NCHAR(100) NULL,
    "c4" DECIMAL(38,0) NULL,
    "c5" SMALLINT NULL
);
COMMENT ON TABLE テスト."test" IS 'test c';
COMMENT ON COLUMN テスト."test"."c1" IS 'c1 c';
COMMENT ON COLUMN テスト."test"."c2" IS 'c2 c';
COMMENT ON COLUMN テスト."test"."c3" IS 'c3 c';
COMMENT ON COLUMN テスト."test"."c4" IS 'c4 c';
COMMENT ON COLUMN テスト."test"."c5" IS 'c5 c';

Following PostgreSQL metadata view will be actual data after IMPORT FOREIGN SCHEMA:

create view sys.pg_foreign_column_metadata as (
with
topt as ( -- 'table' option value, table name can be rewritten
select o.foreign_table_catalog,
       o.foreign_table_schema,
       o.foreign_table_name,
       o.option_value opt_table_name
  from information_schema.foreign_table_options o
 where o.option_name = 'table'
),
sopt as ( -- 'schema' option value, schema name can be rewritten
select o.foreign_table_catalog,
       o.foreign_table_schema,
       o.foreign_table_name,
       o.option_value opt_schema_name       
  from information_schema.foreign_table_options o
 where o.option_name = 'schema'
),
pg_foreign_columns as ( -- PostgreSQL metadata for joining with Oracle metadata
select foreign_table_catalog,
       foreign_server_name,
       foreign_table_schema,
       foreign_table_name,
       c.column_name, 
       c.ordinal_position,
       coalesce (t.opt_table_name, foreign_table_name) table_name,
       coalesce (s.opt_schema_name, foreign_table_schema) schema_name       
  from information_schema."columns" c
 inner join information_schema.foreign_tables ft  
    on c.table_catalog = foreign_table_catalog
   and c.table_schema = foreign_table_schema
   and c.table_name = foreign_table_name 
  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) 
 where foreign_table_catalog = current_catalog
)
select * from pg_foreign_columns
);

Let's inspect the data

Let's create Oracle metadata view in PostgreSQL imported sys schema:

create view sys.oracle_column_comments as (
select acc."owner" schema_name,
       acc.table_name,
       acc.column_name,
       atc.column_id,
       acc."comments"
  from sys.all_col_comments acc -- foreign table for Oracle column comments
 inner join sys.all_tab_columns atc
    on acc."owner" = atc."owner" 
   and acc.table_name = atc.table_name 
   and acc.column_name = atc.column_name
 where acc."comments" is not null
 );

Let's inspect the data

Really there are no problems if information_schema.columns.ordinal_position = ALL_TAB_COLUMNS.COLUMN_ID, this is very good connection predicate between Oracle and PostgreSQL metadata about columns of some Oracle table.

Let's test Oracle table definition change:

ALTER TABLE テスト."test" DROP COLUMN c2;
INSERT INTO テスト."test" ("c1","c3","c4","c5") VALUES ('a','b',5,1);

Oracle metadata view shows

In this case oracle_fdw gives non informative error "column "c2" (1114) of foreign table "test" cannot be converted to or from Oracle data type (1)" for PostgreSQL SQL SELECT c1, c2, c3, c4, c5 FROM "テスト import".test;

In my case import query for comments of columns also gives wrong result and this result cannot be changed because of oracle_fdw based on column order only. for summary query

select 'comment on column "' || foreign_table_schema || '"."' || foreign_table_name || '"."' || pgfc.column_name || '" '
       || 'is ''' || replace(ora_md."comments", '''', '''''')  || ''';' "SQL",
       ora_md.schema_name "ORA schema",
       ora_md.table_name "ORA table",
       ora_md.column_name "ORA column",
       ora_md."comments",
       '"' || foreign_table_schema || '"."' || foreign_table_name || '"."' || pgfc.column_name  || '"' pg_obj,
       ora_md.column_id,
       pgfc.ordinal_position
  from sys.pg_foreign_column_metadata pgfc
 inner join sys.oracle_column_comments ora_md
    on ora_md.schema_name = pgfc.schema_name
   and ora_md.table_name = pgfc.table_name 
   and ora_md.column_id = pgfc.ordinal_position

Note: some other FDWs gives "no such column" error in this case because PostgreSQL column name c2 deparsed to remote DB.

@laurenz , should I open a new issue about SELECT c1, c2, c3, c4, c5 FROM "テスト import".test; with deleted c2? I can close this issue because of query for joining Oracle and PostgreSQL metadata for foreign columns have published and you have answered about metadata connection predicate for columns between PostgreSQL and Oracle.

[laurenz]

ALTER TABLE テスト."test" DROP COLUMN c2;

In this case oracle_fdw gives non informative error

column "c2" (1114) of foreign table "test" cannot be converted to or from Oracle data type (1)

for PostgreSQL SQL SELECT c1, c2, c3, c4, c5 FROM "テスト import".test;

That is working as expected. the second column of the Oracle and the PostgreSQL table now have incompatible data types. You'd have to adjust the foreign table:

ALTER FOREIGN TABLE "テスト import".test DROP c2;

Note: some other FDWs gives "no such column" error in this case because PostgreSQL column name c2 deparsed to remote DB.

Other FDWs map columns by name, not by position. The cause for that is the oracle_fdw was written before other FDWs, even before postgres_fdw, before CREATE FOREIGN TABLE allowed column options, so that you could map them by name.

[mkgrgis]

Thanks for your clarification @laurenz ! This issue closed as "won't fix" because of limitation of PostgreSQL FDW API: "only CREATE TABLE SQL commands". Queries for generating SQL COMMENT... commands for foreign table and its columns are in the message about columns metadata and in the issue about table comments export. This queries also will be published on the wiki.