Discrepancy in FUNCTION count between Oracle and Postgres using ora2pg -t TEST

[gp4git]

Hello,

tested with latest ora2pg commits

when I use ora2pg to test the migration (option -t TEST), I get one wrong result for Function count : [TEST FUNCTION COUNT] ORACLEDB:FUNCTION:0 POSTGRES:FUNCTION:218 [ERRORS FUNCTION COUNT] FUNCTION does not have the same count in source database (0) and in PostgreSQL (218).

I don't have any procedural code in the source oracle DB so 0 is correct but from where comes the 218 count in Postgres ?

Note that I use ora2pg migration in schema mode if it that does make a difference

Thanks in advance for any insight.

Regards

[darold]

yes, ora2pg count the functions present in all PostgreSQL schema, so the count can be different if you have other schemas with functions.

[darold]

An improvement could be to add a directive to restrict the PG schemas for the test. Previous version used to limit the count to PG_SCHEMA value but it gives false results too so I have remove this filter in v21.0.

[gp4git]

Well, I think the current behaviour is far from correct plus the doc states : "

[gp4git]

"Note that this feature will respect the schema name limitation if EXPORT_SCHEMA and SCHEMA or PG_SCHEMA are defined." An improvement would be welcome even if it's a low priority one. Anyway, thanks for your quick reply.

Regards

[darold]

Thanks for the pointer on the documentation I need to fix to correspond to the current behavior.

[darold]

The current behavior is the following and commit 88e3969 fixes the documentation:

Note that this feature respect the schema set in the SCHEMA directive to scan
the Oracle database and also at PostgreSQL side if EXPORT_SCHEMA is enabled.
If PG_SCHEMA is defined and EXPORT_SCHEMA is enabled Ora2Pg will use the list
of schemas defined in PG_SCHEMA to scan PostgreSQL. If EXPORT_SCHEMA is
disabled the entire PostgreSQL database is scanned.

I realize that this is a bit complicated and can lead to bugs and confusion, I will review this part and probably use specific configuration directives to set the list of schema to scan on both side. For example:

# When using the TEST action to compare Oracle vs PostgreSQL migrated database
# Ora2Pg use the schema name set at SCHEMA directive. If no value is provided
# to the SCHEMA directive it will scan all Oracle schema. With this directive
# you can set a comma separated list of schemas to scan.
TEST_ORA_SCHEMA
+
# When using the TEST action to compare Oracle vs PostgreSQL migrated database
# Ora2Pg will scan all PostgreSQL schemas whatever is the value of PG_SCHEMA.
# With this directive you can set a comma separated list of schemas to scan.
TEST_PG_SCHEMA

[gp4git]

Ok, I will test again when implemented.

Regards

[darold]

Commit 30d2a4a fixes this issue. Behavior is:

    Note that this feature respect the schema name limitation if
    EXPORT_SCHEMA and SCHEMA or PG_SCHEMA are defined. If only EXPORT_SCHEMA
    is set all schemes from Oracle and PostgreSQL are scanned. You can
    filter to a single schema using SCHEMA and/or PG_SCHEMA but you can not
    filter on a list of schema. To test a list of schema you will have to
    repeat the calls to Ora2Pg by specifying a single schema each time.

[gp4git]

Hello, I tested with the latest ora2pg version (ora2pg-master) and got the same incorrect result for functions count : [TEST FUNCTION COUNT] ORACLEDB:FUNCTION:0 POSTGRES:FUNCTION:218 [ERRORS FUNCTION COUNT] FUNCTION does not have the same count in source database (0) and in PostgreSQL (218).

Here are the schema directives inside my config file (obfuscating my schema name) : EXPORT_SCHEMA 1 SCHEMA CREATE_SCHEMA 1 COMPILE_SCHEMA 1 PG_SCHEMA PACKAGE_AS_SCHEMA 1

I also tried commenting out PG_SCHEMA but with no luck.

Any explanation ?

Best Regards