Thread: [Feature Request] Schema Aliases and Versioned Schemas
Dear PostgreSQL Development Team,
I’d like to propose a new feature for consideration: schema aliases and versions
**Problem Statement:**
For migrating old db to new one, one must use an external tool or define a dedicated migration script where all possible issues could arise
**Proposed Solution:**
there is two ways I may think about
I’d like to propose a new feature for consideration: schema aliases and versions
**Problem Statement:**
For migrating old db to new one, one must use an external tool or define a dedicated migration script where all possible issues could arise
**Proposed Solution:**
there is two ways I may think about
- versioned schemas : where every version act as a separate schema and the `latest` one or the one the program request is the one in use and each new schema could inherit a table, index, view, ... without additional data
- aliases : where every new schema is defined and migrated separately then an alias is set for the one in use
**Benefits:**
- migration process for under dev projects will be more easier
- aliases : where every new schema is defined and migrated separately then an alias is set for the one in use
**Benefits:**
- migration process for under dev projects will be more easier
- App env stability, where no data need to be changed in the app other than the logic of newly modified schema, not the connection nor the schema or db name
- no need for external tools or servers to migrate or rollback
- minimal downtime between migrations and rollback
**why I post here**
it's addition to core pgsql
>> if it's not the right place : I could contact the mailing list you know it's supposed to take feature requests
Thank you for considering this feature request.
**why I post here**
it's addition to core pgsql
>> if it's not the right place : I could contact the mailing list you know it's supposed to take feature requests
Thank you for considering this feature request.
I look forward to feedback or further discussion on this idea.
Best regards,
Abdelaziz sharaf
Best regards,
Abdelaziz sharaf
On Wed, Nov 27, 2024 at 8:45 PM AbdelAziz Sharaf <mickelpower75@gmail.com> wrote: > > Dear PostgreSQL Development Team, > > I’d like to propose a new feature for consideration: schema aliases and versions > > **Problem Statement:** > For migrating old db to new one, one must use an external tool or define a dedicated migration script where all possibleissues could arise > > **Proposed Solution:** > there is two ways I may think about > - versioned schemas : where every version act as a separate schema and the `latest` one or the one the program requestis the one in use and each new schema could inherit a table, index, view, ... without additional data > - aliases : where every new schema is defined and migrated separately then an alias is set for the one in use Isn't this same as adding the required schema name in the search_path? -- Best Wishes, Ashutosh Bapat
No
when specifying search_path, we build another schema, where each duplicate data must be inside
what I suggested is to have versions/aliases for schemas and any duplicate table/view/... could be inherited if duplicate with all data inside and when any version/alias is dropped and another version/alias has the same table it's preserved, with data inside
that's different behavior where tables attach to schema and if inherited from version/table it may create/reuse it with/without data inside
and this behavior is resource lightweight for migrations
it also could introduce shared tables between schemas
Hope this helps
> Note : sorry for repeating the msg, I realized the replay was only to Ashutouh, not the messaging list, think that's why I didn't have any replay, it may be lost
> Note : sorry for repeating the msg, I realized the replay was only to Ashutouh, not the messaging list, think that's why I didn't have any replay, it may be lost
On Thu, Nov 28, 2024 at 1:10 PM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:
On Wed, Nov 27, 2024 at 8:45 PM AbdelAziz Sharaf
<mickelpower75@gmail.com> wrote:
>
> Dear PostgreSQL Development Team,
>
> I’d like to propose a new feature for consideration: schema aliases and versions
>
> **Problem Statement:**
> For migrating old db to new one, one must use an external tool or define a dedicated migration script where all possible issues could arise
>
> **Proposed Solution:**
> there is two ways I may think about
> - versioned schemas : where every version act as a separate schema and the `latest` one or the one the program request is the one in use and each new schema could inherit a table, index, view, ... without additional data
> - aliases : where every new schema is defined and migrated separately then an alias is set for the one in use
Isn't this same as adding the required schema name in the search_path?
--
Best Wishes,
Ashutosh Bapat
On Tue, Dec 17, 2024 at 10:01 AM AbdelAziz Sharaf <mickelpower75@gmail.com> wrote: > > No > when specifying search_path, we build another schema, where each duplicate data must be inside > what I suggested is to have versions/aliases for schemas and any duplicate table/view/... could be inherited if duplicatewith all data inside and when any version/alias is dropped and another version/alias has the same table it's preserved,with data inside > that's different behavior where tables attach to schema and if inherited from version/table it may create/reuse it with/withoutdata inside > and this behavior is resource lightweight for migrations > it also could introduce shared tables between schemas > > Hope this helps > > > Note : sorry for repeating the msg, I realized the replay was only to Ashutouh, not the messaging list, think that'swhy I didn't have any replay, it may be lost > Hi AbdelAziz, IIUC, what you want to do is this 1. Create a schema for every version of data 2. The tables which do not change across versions are shared by the corresponding schemas 3. The tables which change with version are created and copied to the corresponding schema from (older?) schema If that's correct, it might be possible to implement it with updatable views - one view per schema pointing to the underlying shared table. The view might be able to accommodate some changes to table schema without actually creating a new table. -- Best Wishes, Ashutosh Bapat
it' my first time to know about updatable view and may really serve
I really would appreciate if you discuss my approach also even if not in first priority
thanks for your time and solution, I really appreciate it
I really would appreciate if you discuss my approach also even if not in first priority
thanks for your time and solution, I really appreciate it
best regards
On Tue, Dec 17, 2024 at 8:16 AM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:
On Tue, Dec 17, 2024 at 10:01 AM AbdelAziz Sharaf
<mickelpower75@gmail.com> wrote:
>
> No
> when specifying search_path, we build another schema, where each duplicate data must be inside
> what I suggested is to have versions/aliases for schemas and any duplicate table/view/... could be inherited if duplicate with all data inside and when any version/alias is dropped and another version/alias has the same table it's preserved, with data inside
> that's different behavior where tables attach to schema and if inherited from version/table it may create/reuse it with/without data inside
> and this behavior is resource lightweight for migrations
> it also could introduce shared tables between schemas
>
> Hope this helps
>
> > Note : sorry for repeating the msg, I realized the replay was only to Ashutouh, not the messaging list, think that's why I didn't have any replay, it may be lost
>
Hi AbdelAziz,
IIUC, what you want to do is this
1. Create a schema for every version of data
2. The tables which do not change across versions are shared by the
corresponding schemas
3. The tables which change with version are created and copied to the
corresponding schema from (older?) schema
If that's correct, it might be possible to implement it with updatable
views - one view per schema pointing to the underlying shared table.
The view might be able to accommodate some changes to table schema
without actually creating a new table.
--
Best Wishes,
Ashutosh Bapat
Hi AbdelAziz, On Tue, Dec 17, 2024 at 8:39 PM AbdelAziz Sharaf <mickelpower75@gmail.com> wrote: > > it' my first time to know about updatable view and may really serve > I really would appreciate if you discuss my approach also even if not in first priority > thanks for your time and solution, I really appreciate it Glad that I am able to help you. The discussion about existing features usually happens on pgsql-general. You may get more guidance about your problem on that mailing list. -- Best Wishes, Ashutosh Bapat