[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
- 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
- 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.
I look forward to feedback or further discussion on this idea.
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 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
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
On Thu, Nov 28, 2024 at 1:10 PM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
wrote:
Show quoted text
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 definea 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 andthe `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 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 schemasHope 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
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
best regards
On Tue, Dec 17, 2024 at 8:16 AM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
wrote:
Show quoted text
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 eachduplicate 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 insidethat'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 schemasHope 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 lostHi 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?) schemaIf 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