Migrating to postgresql from oracle

Hi

2017-12-23 19:53 GMT+01:00 Timo Myyrä <timo.myyra@bittivirhe.fi>:

Hi,

I'm preparing migration of our asset management system database from
Oracle 12c to
PostgreSQL 10. I'm using ora2pg and a bit of sed to mangle the SQL ready
for
import to pg but I've hit first problem:
ERROR: referenced relation "..." is not a table

Our Oracle databases uses single 'admin' schema and dedicated schema for
each
customer. For example usergroup mappings are held in admin.usergroup
table. Then
admin schema has customer-specific view on this table
admin.usergroup_customer1
view which limits the full view to just those of that customer. And then
each
customers own schema has synonym to that view like customer1.usergroup. The
applications queries use the "usergroup" table to query the group mappings.

Here's hopefully a bit more detailed description of the database structure:

ADMIN SCHEMA:
TABLES:
usergroup
unit
user
...
VIEWS:
usergroup_customer1
unit_customer1
user_customer1
...
CUSTOMER1 SCHEMA:
TABLES:
resource
...
SYNONYM:
usergroup (refers to admin.usergroup_customer1)
user (refers to admin.user_customer1)
unit (refers to admin.unit_customer1)
...

So it seems that postgresql doesn't support foreign keys in views like
Oracle.
Would you have any suggestions how the above Oracle structure would best be
handled in PostgreSQL? I'm pretty new to PostgreSQL so I might overlook
something if I try to solve this by myself.

try to ask on https://github.com/darold/ora2pg/issues

Regard

Pavel

On Sat, Dec 23, 2017, at 21:05, Pavel Stehule wrote:

Hi

2017-12-23 19:53 GMT+01:00 Timo Myyrä <timo.myyra@bittivirhe.fi>:

Hi,

I'm preparing migration of our asset management system database from
Oracle 12c to>> PostgreSQL 10. I'm using ora2pg and a bit of sed to mangle the SQL
ready for>> import to pg but I've hit first problem:
ERROR: referenced relation "..." is not a table

Our Oracle databases uses single 'admin' schema and dedicated schema
for each>> customer. For example usergroup mappings are held in admin.usergroup
table. Then>> admin schema has customer-specific view on this table
admin.usergroup_customer1>> view which limits the full view to just those of that customer. And
then each>> customers own schema has synonym to that view like
customer1.usergroup. The>> applications queries use the "usergroup" table to query the group
mappings.>>
Here's hopefully a bit more detailed description of the database
structure:>>
ADMIN SCHEMA:
TABLES:
usergroup
unit
user
...
VIEWS:
usergroup_customer1
unit_customer1
user_customer1
...
CUSTOMER1 SCHEMA:
TABLES:
resource
...
SYNONYM:
usergroup (refers to admin.usergroup_customer1)
user (refers to admin.user_customer1)
unit (refers to admin.unit_customer1)
...

So it seems that postgresql doesn't support foreign keys in views
like Oracle.>> Would you have any suggestions how the above Oracle structure would
best be>> handled in PostgreSQL? I'm pretty new to PostgreSQL so I might
overlook>> something if I try to solve this by myself.

try to ask on https://github.com/darold/ora2pg/issues

Regard

Pavel

Timo

Well, I doubt postgresql limitations apply for ora2pg.

I looked this a bit more closely. Postgresql seems to have nice feature
which would apply to this quite nicely called row-level security. I
like to limit the 'admin' schema table rows so that each user sees only
their own rows.
So a bit more about the table structure is needed to understand the
situation.Lets take admin.unit table, this holds the business units of each
customer. The import rows are id and parent_id. Parent_id field might
refer to unit id in the same unit table so units can be nested. The top-
level unit doesn't have parent_id. The admin.unit.id field gives the top-
level id, then theres the admin.project_unit table which has 2 fields,
project_id, unit_id which gives mapping to project. Finally we have
project table which has the actual project id and name which map to
logged in project.
I was testing the row-level security with following query but it gives
syntax error for some reason:
CREATE POLICY unit_customer1 ON admin.unit
FOR ALL
TO customer1
USING ( with recursive e(id,parent_id) as (
select id, parent_id
from admin.bg_unit
where id in (select unit_id from admin.project_unit where
project_id = 'customer1') union all
select f.id, f.parent_id
from admin.bg_unit f, e
where e.id = f.parent_id
) select id from e order by id asc
);

When I run the above query I get just:
ERROR: syntax error at or near "with"
LINE 4: USING ( with recursive e(id,parent_id) as (

Can the above policy made to work for my use-case or am I doing
something fundamentally wrong?
Timo