Subject: Plans for partitioning of inheriting tables

From: [email-masked]
Date: Thu, 24 Oct 2024 21:47:02 +0200

Hi

Up to version 17, partitioning of tables inheriting from other tables  
is not possible.
> psql:../code_files/data_storage/PostgreSQL/tables/TOPO_FILES.pg_sql:68:  
> ERROR:  no se puede crear una tabla particionada como hija de herencia

Are there plans to support this in the future? I could not find any  
hint in the documentation or in  
https://wiki.postgresql.org/wiki/Development_information.

Kind regards

Thiemo

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 24 Oct 2024 13:44:04 -0700

On 10/24/24 12:47 PM, [email-masked] wrote:
> Hi
> 
> Up to version 17, partitioning of tables inheriting from other tables is 
> not possible.
>> psql:../code_files/data_storage/PostgreSQL/tables/TOPO_FILES.pg_sql:68: ERROR:  no se puede crear una tabla particionada como hija de herencia
> 
> Are there plans to support this in the future? I could not find any hint 
> in the documentation or in 

1) Have you looked at?:

https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE

2) Provide the SQL you ran that got the above error.

-- 
Adrian Klaver
[email-masked]

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 24 Oct 2024 13:58:35 -0700

On Thursday, October 24, 2024, <[email-masked]> wrote:

> in the documentation or in https://wiki.postgresql.org/wi
> ki/Development_information.
>

My impression of things is that directly using “inherit” for table creation
is considered deprecated at this point.  No one has interest in expanding
on the feature nor even recommends it be used in new development.  That
particular unique feature of PostgreSQL hasn’t caught on.

David J.

---

From: Thiemo Kellner <[email-masked]>
Date: Fri, 25 Oct 2024 07:33:16 +0200 (GMT+02:00)

Thanks for taking this up.

> https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE

I do not feel it applies to my case. I tried to create a partitioned table that inherits columns from a base table. The documentation you provided the URL seems to speak of realising partitioning by using inheritance.

> 
> 2) Provide the SQL you ran that got the above error?

https://sourceforge.net/p/treintaytres/code/HEAD/tree/trunk/code_files/data_storage/PostgreSQL/tables/TEMPLATE_TECH.pg_sql

---

From: Thiemo Kellner <[email-masked]>
Date: Fri, 25 Oct 2024 07:42:49 +0200 (GMT+02:00)

24.10.2024 22:58:39 David G. Johnston <[email-masked]>:
> 
> My impression of things is that directly using “inherit” for table creation is considered deprecated at this point.  No one has interest in expanding on the feature nor even recommends it be used in new development.  That particular unique feature of PostgreSQL hasn’t caught on.
> 
> David J.
>  
Thanks for sharing your experience. I wonder if this is the general take on inheritance for spreading common attributes throughout a database.

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 25 Oct 2024 08:57:31 -0700

> I do not feel it applies to my case. I tried to create a partitioned table that inherits columns from a base table. The documentation you provided the URL seems to speak of realising partitioning b…

This needs a code example to go any further.

---

From: Thiemo Kellner <[email-masked]>
Date: Fri, 25 Oct 2024 20:45:19 +0200

Am 25.10.2024 um 17:57 schrieb Adrian Klaver:
>
>> I do not feel it applies to my case. I tried to create a partitioned 
>> table that inherits columns from a base table. The documentation you 
>> provided the URL seems to speak of realising partitioning by using 
>> inheritance.
>

Sorry, my bad. I posted the URL of the table that is inherited from. The 
recepient is 
https://sourceforge.net/p/treintaytres/code/HEAD/tree/trunk/code_files/data_storage/PostgreSQL/tables/TOPO_FILES.pg_sqlcreate 
table if not exists TOPO_FILES (

      SOURCE_ID          uuid
         constraint TOPO_FILES␟FK_01
             references SOURCES (ID)
             match full
         not null
     ,FILE_NAME          text
         not null
     ,TILE               raster
         not null
     ,FILE_CREATION_PIT  timestamp(6) with time zone
         not null
     ,FILE_HASH          text
         not null
     ,constraint TOPO_FILES␟PK primary key (ID)
     ,constraint TOPO_FILES␟UQ unique (SOURCE_ID
                                      ,FILE_NAME)
)
inherits(TEMPLATE_TECH);
-- partition by hash (source_id); --NOTE Up to PG 17, partitioning with inheriting table is not possible.

The spender table ishttps://sourceforge.net/p/treintaytres/code/HEAD/tree/trunk/code_files/data_storage/PostgreSQL/tables/TEMPLATE_TECH.pg_sql

create table if not exists TEMPLATE_TECH (
      ID                 uuid
         constraint TEMPLATE_TECH␟PK primary key
         not null
         default gen_random_uuid()
     ,ENTRY_PIT          timestamp(6) with time zone
         not null
         default clock_timestamp()
);

---

From: Thiemo Kellner <[email-masked]>
Date: Fri, 25 Oct 2024 20:47:11 +0200

SOURCE_ID          uuid

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 31 Oct 2024 09:57:06 -0700

On 10/25/24 11:47, Thiemo Kellner wrote:
> https://sourceforge.net/p/treintaytres/code/HEAD/tree/trunk/code_files/data_storage/PostgreSQL/tables/TOPO_FILES.pg_sqlcreate table if not exists TOPO_FILES (
> 
>       SOURCE_ID          uuid

It is just not the way you want to do it, see:

---

From: Achilleas Mantzios - cloud <[email-masked]>
Date: Fri, 1 Nov 2024 16:15:54 +0100

On 10/24/24 21:58, David G. Johnston wrote:
>     Up to version 17, partitioning of tables inheriting from other
>     tables is not possible.
>
>     Are there plans to support this in the future? I could not find
>     any hint in the documentation or in
>     https://wiki.postgresql.org/wiki/Development_information
>     <https://wiki.postgresql.org/wiki/Development_information>.
>
>
> My impression of things is that directly using “inherit” for table 
> creation is considered deprecated at this point.  No one has interest 
> in expanding on the feature nor even recommends it be used in new 
> development.  That particular unique feature of PostgreSQL hasn’t 
> caught on.
Hi, opinions vary, IMHO inheritance it is a nice feature to have 
especially in multi-tenant situation where tenants represent a division 
or subsidiary rather than a completely foreign entity which should live 
in total isolation, plus the ability to have data on the top owning or 
managing organization. IMHO nothing beats inheritance in fitting to the 
above model. Partitioning comes close but partitioned tables cannot have 
any data on their own. There are workarounds of course to that, but they 
don't fit like a glove. But again I have not tested in heavy xactional 
envs TBT, I am just saying the feature is handy for many applications 
and models. I use it personally in my company and love it. Could I do 
without it? of course, but it would be ugly.