Audit
PostgreSQL Migrator helps auditing a migration project by pointing migration issues and scoring complexity of the migration.
Complexity score is an abstract number used to compare projects or objects. Several issues increase complexity of an object:
- properties like name, type, datatype, etc.
- transpilation errors.
- inconsistencies with other objects.
- manual migration required.
- missing feature in PostgreSQL Migrator.
- etc.
PostgreSQL Migrator identifies issue using a contextualized string called an issue id. Here is the reference of all migration issues. Unless specified, the complexity score of an issue is 1.
authentication: *¶
Oracle authentication method.
May require pg_hba.conf configuration in PostgreSQL.
Default score is 0.
e.g. authentication: EXTERNAL.
column datatype: *¶
Column datatype.
e.g. column datatype: enum
Default score is 0. Here is the score for some known datatypes:
| Type | Complexity ID | Score |
|---|---|---|
| MySQL enum | column datatype: enum |
0.5 |
| MySQL set | column datatype: set |
2 |
common role¶
Oracle common role. Default score is 0.
composite partition key¶
Table is partitioned using multiple columns as key.
csv table¶
MySQL CSV table engine. Requires a foreign data wrapper in PostgreSQL. Default score is 10.
DBLink¶
Oracle synonym points to an Oracle Database link.
duplicate identifier¶
Object identifier will conflict with another in PostgreSQL. For example, PostgreSQL indexes are schema-wide, not table-wide. Also, PostgreSQL shares namespace for sequences, table, views, indexes and more. Use Convert.RenameConstraints, Convert.RenameIndexes, or Convert.Rules.Name to work around this.
expired¶
Expired job. Default score is 0.
federated table¶
MySQL federated table. Default score is 10.
identifier is a reserved keyword¶
Object name is a reserved keyword. Object must be renamed or queries must quote the identifier.
identifier too long¶
Object name is longer than PostgreSQL limit. PostgreSQL will truncate identifier longer than 63 characters. This may lead to duplicate identifiers.
identifier with mixed case¶
Object name mixes lower and upper case. Sometimes it’s a typo. PostgreSQL Migrator won’t lowercase the identifier and preserve mixed-case identifier.
identity with scale¶
Oracle identity column has a scale. PostgreSQL accepts only integer for identity column.
incremental refresh¶
Oracle materialized view has fast refresh. Requires pg_ivm extension in PostgreSQL. Default score is 50.
index organized table¶
Oracle IOT. Use covering index in PostgreSQL. Default score is 5.
invisible index¶
Index masked to planner.
irregular index type¶
Index uses an exotic method. May need an extension. Default score is 5.
job¶
Embedded job in RDBMS scheduler. Default score is 0.
job chain¶
Oracle chain of job. Requires pg_timetable extension in PostgreSQL. Default score is 50.
large datatype: *¶
Large object. Oracle only.
Includes BLOB, CLOB, NCLOB, LONG and LONG RAW.
e.g. large datatype: CLOB.
line of code¶
A single line of code from view or procedural code. Used to estimate complexity based on count of lines of code.
Default score is 0.1.
members¶
Oracle type member. Default score is 1 for each member of type.
missing precision¶
Oracle NUMBER column misses precision. Override column datatype with a specific number datatype like smallint, integer, bigint.
missing primary key¶
Table without primary key. Default score is 0.1.
missing source¶
MySQL routine does not have source code available. Maybe a privilege issue while inspecting.
negative scale¶
Oracle NUMBER column is rounded by Oracle.
not implemented conversion: *¶
PostgreSQL Migrator does not yet implement migration of this type of object. File a feature request or contribute.
e.g. not implemented conversion: foreign servers
on update current timestamp¶
Table column with builtin update to transaction timestamp. Requires a trigger.
precision mismatch¶
Table column references a foreign NUMBER column with a different precision.
public database link¶
Oracle database link is shared.
public synonym¶
Oracle synonym is shared. Default score is 2.
read only view¶
Oracle read-only view. You need to adjust privileges. Default score is 5.
routine parameter¶
Occurrence of a parameter of a function or a procedure. Each parameter increases routine score. Default score for each parameter is 0.1.
same identifier as table¶
Constraint is named after its table. Default score is 0.
scheduled refresh¶
Oracle materialized view is refreshed automatically. Requires a pg_cron job in PostgreSQL. Default score is 10.
subtype¶
Oracle type inherits from a super type. Default score is 10.
system trigger¶
Oracle database and schema trigger. Default score is 50. Use PostgreSQL event trigger.
temporary table¶
Oracle temporary table. Requires pgtt extension in PostgreSQL. Default score is 10.
transqlate: rewrite panic¶
Transpiler panicked while converting to PostgreSQL. Likely a bug in the transpiler.
transqlate: parser panic¶
Transpiler panicked while parsing expression. Likely a bug in transpiler.
transqlate: parse error¶
Transpiler encounters an unknown expression. Missing feature in transpiler.
transqlate: *¶
Transpilation conversion error. Requires manual conversion or a feature request.
type: *¶
Object type.
This is not an annotation, simply a scored migration issue.
e.g. type: Column, type: TemporaryTable, etc.
Default complexity of a type is 1, but PostgreSQL Migrator adjust complexity score of the following object types:
| Type | Issue ID | Score |
|---|---|---|
| Global catalog | type: Catalog |
0 |
| Table column | type: Column |
0.1 |
| Check constraint | type: Check |
0.1 |
| Oracle Database link | type: DatabaseLink |
2 |
| MySQL Foreign Server | type: ForeignServer |
2 |
| Table and view index | type: Index |
0.1 |
| Index column | type: IndexColumn |
0.1 |
| JAVA Source | type: JavaSource |
100 |
| Job | type: Job |
50 |
| Table primary and unique key | type: Key |
0.1 |
| Foreign key | type: ForeignKey |
0.1 |
| Schema | type: Schema |
0.1 |
| Sequence | type: Sequence |
0.1 |
| Oracle Synonym | type: Synonym |
2 |
| Table | type: Table |
0.1 |
| View | type: View |
0.1 |
type mismatch¶
Local column and foreign column does not have same datatype.
e.g. a local column numeric references a bigint column.
unhandled partition type¶
Partition type is not supported by PostgreSQL.
unparsed statements¶
Transpiler parser encounters an unknown syntax and skips to next statement. Some idioms are likely ignored. Requires manual review.