Received-SPF: pass (ha01s018.org-dns.com: connection is authenticated)
Content-Type: multipart/alternative;
 boundary="------------eybuGgrjGGHEZCPBYWvwIFWM"
Message-ID: <5757acdc-39ed-4642-a449-26e47b1bcf95@gelassene-pferde.biz>
Date: Fri, 25 Oct 2024 20:47:11 +0200
MIME-Version: 1.0
From: Thiemo Kellner <thiemo@gelassene-pferde.biz>
Subject: Plans for partitioning of inheriting tables
References: <ea963edb-5437-4de6-bd2c-97c141a391b8@gelassene-pferde.biz>
To: PostgreSQL General <pgsql-general@lists.postgresql.org>
In-Reply-To: <ea963edb-5437-4de6-bd2c-97c141a391b8@gelassene-pferde.biz>
Archived-At: <https://www.postgresql.org/message-id/5757acdc-39ed-4642-a449-26e47b1bcf95%40gelassene-pferde.biz>
Precedence: bulk

This is a multi-part message in MIME format.
--------------eybuGgrjGGHEZCPBYWvwIFWM
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit


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.
>
> This needs a code example to go any further.

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()
);


--------------eybuGgrjGGHEZCPBYWvwIFWM
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <br>
    <div class="moz-cite-prefix">Am 25.10.2024 um 17:57 schrieb Adrian
      Klaver:<br>
    </div>
    <blockquote type="cite"
      cite="mid:2225b081-1f55-4fc7-8b59-e190f6fdd0c4@aklaver.com"><br>
      <blockquote type="cite">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. <br>
      </blockquote>
      <br>
      This needs a code example to go any further. <br>
    </blockquote>
    <p>Sorry, my bad. I posted the URL of the table that is inherited
      from. The recepient is
      <a class="moz-txt-link-freetext"
href="https://sourceforge.net/p/treintaytres/code/HEAD/tree/trunk/code_files/data_storage/PostgreSQL/tables/TOPO_FILES.pg_sqlcreate">https://sourceforge.net/p/treintaytres/code/HEAD/tree/trunk/code_files/data_storage/PostgreSQL/tables/TOPO_FILES.pg_sqlcreate</a>
      table if not exists TOPO_FILES (</p>
    <pre>     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 is <a class="moz-txt-link-freetext"
href="https://sourceforge.net/p/treintaytres/code/HEAD/tree/trunk/code_files/data_storage/PostgreSQL/tables/TEMPLATE_TECH.pg_sql">https://sourceforge.net/p/treintaytres/code/HEAD/tree/trunk/code_files/data_storage/PostgreSQL/tables/TEMPLATE_TECH.pg_sql</a>

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()
);</pre>
    <p><br>
    </p>
  </body>
</html>

--------------eybuGgrjGGHEZCPBYWvwIFWM--