Received-SPF: pass (ha01s018.org-dns.com: connection is authenticated)
Date: Fri, 01 Nov 2024 09:41:06 +0100
Message-ID: 
 <20241101094106.Horde.TwPSs9YUgJ5v-_M-4ltwoeh@webmail.gelassene-pferde.biz>
From: thiemo@gelassene-pferde.biz
To: PostgreSQL General <pgsql-general@lists.postgresql.org>
Subject: Re: Plans for partitioning of inheriting tables
References: <ea963edb-5437-4de6-bd2c-97c141a391b8@gelassene-pferde.biz>
 <5757acdc-39ed-4642-a449-26e47b1bcf95@gelassene-pferde.biz>
 <d9d6a2f4-379b-45f3-9005-ab047625c1a4@aklaver.com>
In-Reply-To: <d9d6a2f4-379b-45f3-9005-ab047625c1a4@aklaver.com>
Accept-Language: de
Content-Type: text/plain; charset=utf-8; format=flowed; DelSp=Yes
MIME-Version: 1.0
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/20241101094106.Horde.TwPSs9YUgJ5v-_M-4ltwoeh%40webmail.gelassene-pferde.biz>
Precedence: bulk


Adrian Klaver <adrian.klaver@aklaver.com> escribió:


> It is just not the way you want to do it, see:
>
> https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE

Thanks for your patience. Maybe I am not clever enough to understand  
you. I shall try to explain what I try to do.

In my project, I have several tables. Each table has some basic  
technical attributes. For the time being, those are the surrogate key  
(ID) and a timestamp (ENTRY_PIT) to track the point in time when a  
record was inserted into the table. To improve consistency and reduce  
effort, I created a template table those attributes get inherited from  
by all other tables, e.g. TOPO_FILES. TOPO_FILES can contain  
GeoTIFF/raster data from different sources. For ease of data  
management, e.g. wipe all the data of one source, I tried to partition  
it by SOURCE_ID. And there the error rises that it is not possible to  
partition a table that is an heir of another table.

I feel, you are trying to make me partition TOPO_SOURCES by using  
inheritance, but I cannot see... now I do see how I could achieve my  
desires. However, there pop up questions in my mind.

To me, it seems, that partitioning using inheritance will not reduce  
maintenance but greatly increase it. It feels to me very much that I  
build manually with inheritance, what is done with the partitioning  
clause. Am I mistaken?

In the description, there is the statement that instead of triggers,  
one could use rules. I am quite sure that, quite a while ago, I was  
advised in one of the mailing lists against the use of rules other  
than for inserts as the workings of update and delete rules are almost  
impenetrable. For me, at least, they were. Are my memories wrong about  
that?

Is there experience on the efficiency/speed comparing partitioning  
with inheritance using triggers/rules and using the declarative way? I  
don't think that partition speed is an issue in my case, as I have  
fairly few records that are in themselves rather big.

Remarks to the documentation:
- There are examples for the insert path. However, not for the update  
or delete path. I feel, that those tend to be the more complex ones,  
especially if my memory is correct about the advice to avoid update  
and delete rules.
-  
https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-INHERITANCE-MAINTENANCE misses out on a sentence not to forget to adapt the  
triggers/rules.

Kind regards

Thiemo