Re: Oracle to Postgres Migration

Synonym can be used if the object has high frequency of usage in the application.
Admin/ Developer need to ensure that there is no conflict in using the naming convention.
If you suspect a fraction of naming convention conflict, that needs to be prefixed by a schema name and that should not be used as a synonym.
There are my thoughts.

Thanks,
Sarwar
________________________________
From: M Sarwar <sarwarmd02(at)outlook(dot)com>
Sent: Thursday, February 1, 2024 11:03 AM
To: Wetmore, Matthew (CTR) <Matthew(dot)Wetmore(at)express-scripts(dot)com>; Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>; Kalyani Maity <bimal(dot)af2020(at)gmail(dot)com>; pgsql-admin(at)lists(dot)postgresql(dot)org <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Re: Oracle to Postgres Migration

I have worked on Federal, State and commercial projects and this is how any database object is referenced.
Not prefixing schema name along with the database object name will lead to several chaos situation in my opinion too.
Thanks,
Sarwar

________________________________
From: Wetmore, Matthew (CTR) <Matthew(dot)Wetmore(at)express-scripts(dot)com>
Sent: Thursday, February 1, 2024 10:52 AM
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>; Kalyani Maity <bimal(dot)af2020(at)gmail(dot)com>; pgsql-admin(at)lists(dot)postgresql(dot)org <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Oracle to Postgres Migration

I disagree a little with this. Setting search_path to fix non schema qualified SQL, is not a Best Practice.

You CAN do this and it will work, but it CAN cause trouble if your database has things of the same name. (again a not Best Practice).

My personal opinion on this, is to correct your SQL to include the schema qualified syntax (schema.whatever.) This way you are always 100% sure of what you are doing.

Just my $0.02

-----Original Message-----
From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Sent: Thursday, February 1, 2024 4:38 AM
To: Kalyani Maity <bimal(dot)af2020(at)gmail(dot)com>; pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: [EXTERNAL] Re: Oracle to Postgres Migration

On Thu, 2024-02-01 at 16:20 +0530, Kalyani Maity wrote:
> I have one scenario where one synonym created as below in oracle DB:
>
> create synonym 'schema1.procedure1' for 'schema2.procedure1'
>
> procedure1 only exist in schema2.
>
> I have migrated both schema 1 and schema 2 in postgres.
>
> How to create this synonym in postgres.

You don't. Instead, you set "search_path" to include both schemas.

Yours,
Laurenz Albe