Re: Oracle to Postgres Migration

Поиск
Список
Период
Сортировка
От M Sarwar
Тема Re: Oracle to Postgres Migration
Дата
Msg-id DM4PR19MB59784C05AC6B67C9053B14C2D3432@DM4PR19MB5978.namprd19.prod.outlook.com
обсуждение исходный текст
Ответ на Re: Oracle to Postgres Migration  (M Sarwar <sarwarmd02@outlook.com>)
Список pgsql-admin
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@outlook.com>
Sent: Thursday, February 1, 2024 11:03 AM
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Kalyani Maity <bimal.af2020@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.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.Wetmore@express-scripts.com>
Sent: Thursday, February 1, 2024 10:52 AM
To: Laurenz Albe <laurenz.albe@cybertec.at>; Kalyani Maity <bimal.af2020@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.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.albe@cybertec.at>
Sent: Thursday, February 1, 2024 4:38 AM
To: Kalyani Maity <bimal.af2020@gmail.com>; pgsql-admin@lists.postgresql.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


В списке pgsql-admin по дате отправления:

Предыдущее
От: M Sarwar
Дата:
Сообщение: Re: Oracle to Postgres Migration
Следующее
От: M Sarwar
Дата:
Сообщение: Re: Oracle to Postgres Migration