Re: Plans for partitioning of inheriting tables

[Adrian Klaver]

On 11/1/24 12:16, thi...@gelassene-pferde.biz wrote:
Thanks, I shall have a look into it. I was under the assumption the the 
create table like would create no more than a structural copy.


Not sure what you mean by structural copy, but the table created by 
CREATE TABLE LIKE will not have any association with the table it was 
created from.


https://www.postgresql.org/docs/current/sql-createtable.html

"Unlike INHERITS, the new table and original table are completely 
decoupled after creation is complete. Changes to the original table will 
not be applied to the new table, and it is not possible to include data 
of the new table in scans of the original table."




Torsten Förtsch mailto:tfoertsch...@gmail.com>> 
escribió:



Thiemo,
it looks to me like you are using inheritance just to make sure your 
SOURCES and TOPO_FILES tables have some common columns. If you are not 
actually querying the TEMPLATE_TECH table and expect to see all the 
rows from the other 2 tables in that one table combined, then you 
could use CREATE TABLE (LIKE ...) instead of inheritance. That way 
your "child" tables would become normal tables and you could use 
declarative partitioning on them.
Even if you are querying the TEMPLATE_TECH table, you could still do 
that by turning the TEMPLATE_TECH table into a view which performs a 
UNION ALL over the other tables.





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Plans for partitioning of inheriting tables

[Adrian Klaver]

On 11/1/24 16:10, thi...@gelassene-pferde.biz wrote:


Adrian Klaver  escribió:


On 11/1/24 13:47, Thiemo Kellner wrote:
It looks to me basically to be a "create table A as select * from B 
where false".


No it more capable then that.


Yes, I wrote basically, not exactly.

CREATE TABLE  LIKE  has  like_option which 
allows to transfer over more attributes of the table, for example 
defaults, constraints, indexes, etc.


But, to my understanding, no primary nor unique nor foreign constraint.




"INCLUDING INDEXES

Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the 
original table will be created on the new table. Names for the new 
indexes and constraints are chosen according to the default rules, 
regardless of how the originals were named. (This behavior avoids 
possible duplicate-name failures for the new indexes.)

"

FK's are not in the the INCLUDINGs, nor triggers.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Plans for partitioning of inheriting tables

[thiemo]

Adrian Klaver  escribió:

Even if there where plans, any changes would happen in the future  
and would not be help the now problem.


Yes and no. I can live without the partitioning, as I do not intend to  
load data from more than one source. Other might. But until others  
want to load data from different sources, a comment in the source  
might do that partitioning of inheriting tables will be supported in  
the future. But, that is an academic point now.



That is contradicted by your statement below:

Either performance is important or it is not.


Not quite. If the performance penalty by suboptimal choice in  
partitioning does not matter in the current project because the  
raster/bytea stuff does affect performance much more, it does not mean  
that I cannot work on other project where it can matter. And even if  
the latter is not the case, I can be just curious about it.


If TILE is referring to the same thing you are dealing with in  
related question on psycopg list then you are talking about bytea  
storage. You should take a look at:


https://www.postgresql.org/docs/current/storage-toast.html


Indeed, it does. Thanks for the hint.

Re: Plans for partitioning of inheriting tables

[Thiemo Kellner]

It looks to me basically to be a "create table A as select * from B where 
false".

01.11.2024 20:38:15 Adrian Klaver :

> On 11/1/24 12:16, thi...@gelassene-pferde.biz wrote:
>> Thanks, I shall have a look into it. I was under the assumption the the 
>> create table like would create no more than a structural copy.
> 
> Not sure what you mean by structural copy, but the table created by CREATE 
> TABLE LIKE will not have any association with the table it was created from.
> 
> https://www.postgresql.org/docs/current/sql-createtable.html
> 
> "Unlike INHERITS, the new table and original table are completely decoupled 
> after creation is complete. Changes to the original table will not be applied 
> to the new table, and it is not possible to include data of the new table in 
> scans of the original table."
> 
>> Torsten Förtsch mailto:tfoertsch...@gmail.com>> 
>> escribió:
>> Thiemo,
>>> it looks to me like you are using inheritance just to make sure your 
>>> SOURCES and TOPO_FILES tables have some common columns. If you are not 
>>> actually querying the TEMPLATE_TECH table and expect to see all the rows 
>>> from the other 2 tables in that one table combined, then you could use 
>>> CREATE TABLE (LIKE ...) instead of inheritance. That way your "child" 
>>> tables would become normal tables and you could use declarative 
>>> partitioning on them.
>>> Even if you are querying the TEMPLATE_TECH table, you could still do that 
>>> by turning the TEMPLATE_TECH table into a view which performs a UNION ALL 
>>> over the other tables.
>> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: Plans for partitioning of inheriting tables

[Adrian Klaver]

On 11/1/24 10:21 AM, thi...@gelassene-pferde.biz wrote:


Adrian Klaver  escribió:

Changing that would count as a major change. Even if you where to 
convince the developers to make the change the earliest it would 
released would be with the next major release in Fall of 2025. That 
assumes you can convince then early enough or at all.


I was not trying to convince anyone to do anything about the 
implementation of declarative partitioning. I have been just curious if 
there were plans. If I have raised the impression of the former, I am 
sorry.
Even if there where plans, any changes would happen in the future and 
would not be help the now problem.



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.


Hard to say without some firm numbers and/or testing.


Sure, I was hoping those test would have been done some day. But in the 
end, to me, it is not important.


That is contradicted by your statement below:

"I mean to say that I believe that loading that much data into one field 
will take much more time than runtime difference of 
trigger/rules/declarative partitioning solutions would to sort the data 
into the correct partition."


Either performance is important or it is not.

If TILE is referring to the same thing you are dealing with in related 
question on psycopg list then you are talking about bytea storage. You 
should take a look at:


https://www.postgresql.org/docs/current/storage-toast.html

In any case assuming you are not entering/reading/updating all the bytea 
data at one time then you are looking at fetching only that bytea data 
that are filtered by other attributes of the rows. I would strongly 
suggest running some tests on a single table with the data and see if 
you can live with the performance results before complicating things 
with partitioning.




Also this "... I have fairly few records that are in themselves rather 
big" could use some explanation. In other words what makes you think 
that partitioning is the answer to this issue?


I was not thinking that partitioning was the answer to a performance 
problem. Partitioning might be an answer to the maintenance of records, 
specifically if entire sources are affected. The size of the tif files 
to get loaded into the raster attribute TILE range from 112 kB to 32 MB. 
I am complete unaware of the inner storing mechanisms of raster in 
PostGIS, but on first sight, it seems that the rest of a records of 
TOPO_FILES is negligible compared to the TILE. The total number of files 
to be loaded in my case are 3273, even though that only encompasses a 
small part of the world, I do not think, the latter would surpass 10 
records. Not much for a database table, afaik. I mean to say that I 
believe that loading that much data into one field will take much more 
time than runtime difference of trigger/rules/declarative partitioning 
solutions would to sort the data into the correct partition.




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Plans for partitioning of inheriting tables

[Achilleas Mantzios - cloud]

On 10/24/24 21:58, David G. Johnston wrote:

On Thursday, October 24, 2024,  wrote:


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
.


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.


David J.

Re: Plans for partitioning of inheriting tables

[thiemo]

Adrian Klaver  escribió:

Changing that would count as a major change. Even if you where to  
convince the developers to make the change the earliest it would  
released would be with the next major release in Fall of 2025. That  
assumes you can convince then early enough or at all.


I was not trying to convince anyone to do anything about the  
implementation of declarative partitioning. I have been just curious  
if there were plans. If I have raised the impression of the former, I  
am sorry.


What I getting at is that you need to start thinking of another way  
of doing this if this is a current project. The choices are:


1) Declarative partitioning, where you cannot have your partition  
parent inherit from another table.


2) Partition by inheritance where you build the structure manually.


I very much agree. Shying the effort involved for 2), I still tend to  
1). I could break the inheritance pattern by explicitly putting the  
technical attributes into partitioned tables. My self, I probably  
won't use more than one source, but others might have several source  
for comparison or whatever.


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.


Hard to say without some firm numbers and/or testing.


Sure, I was hoping those test would have been done some day. But in  
the end, to me, it is not important.


Also this "... I have fairly few records that are in themselves  
rather big" could use some explanation. In other words what makes  
you think that partitioning is the answer to this issue?


I was not thinking that partitioning was the answer to a performance  
problem. Partitioning might be an answer to the maintenance of  
records, specifically if entire sources are affected. The size of the  
tif files to get loaded into the raster attribute TILE range from 112  
kB to 32 MB. I am complete unaware of the inner storing mechanisms of  
raster in PostGIS, but on first sight, it seems that the rest of a  
records of TOPO_FILES is negligible compared to the TILE. The total  
number of files to be loaded in my case are 3273, even though that  
only encompasses a small part of the world, I do not think, the latter  
would surpass 10 records. Not much for a database table, afaik. I  
mean to say that I believe that loading that much data into one field  
will take much more time than runtime difference of  
trigger/rules/declarative partitioning solutions would to sort the  
data into the correct partition.

Re: Plans for partitioning of inheriting tables

[Adrian Klaver]

On 11/1/24 01:41, thi...@gelassene-pferde.biz wrote:


Adrian Klaver  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?


From here:

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

5.12.2.3. Limitations

"Individual partitions are linked to their partitioned table using 
inheritance behind-the-scenes. However, it is not possible to use all of 
the generic features of inheritance with declaratively partitioned 
tables or their partitions, as discussed below. Notably, a partition 
cannot have any parents other than the partitioned table it is a 
partition of, nor can a table inherit from both a partitioned table and 
a regular table. That means partitioned tables and their partitions 
never share an inheritance hierarchy with regular tables."



Changing that would count as a major change. Even if you where to 
convince the developers to make the change the earliest it would 
released would be with the next major release in Fall of 2025. That 
assumes you can convince then early enough or at all. What I getting at 
is that you need to start thinking of another way of doing this if this 
is a current project. The choices are:


1) Declarative partitioning, where you cannot have your partition parent 
inherit from another table.


2) Partition by inheritance where you build the structure manually.





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?


Yes, I would stay away from rules. They are included in the 
documentation for completeness. You have enough on your plate without 
trying to figure out what rules do.




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.


Hard to say without some firm numbers and/or testing.

Also this "... I have fairly few records that are in themselves rather 
big" could use some explanation. In other words what makes you think 
that partitioning is the answer to this issue?




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.



From the docs:

"The schemes shown here assume that the values of a row's key column(s) 
never change, or at least do not change enough to require it to move to 
another partition. An UPDATE that attempts to do that will fail because 
of the CHECK constraints. If you need to handle such cases, you can put 
suitable update triggers on the child tables, but it makes management of 
the structure much more complicated."


So yes, they would be more complicated as you are looking at possibly 
changing tables.


Personally, I think you are heading to declarative partitioning. Either 
via your own scripts or something like 
pg_partman(https://github.com/pgpartman/pg_partman).



- 
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





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: pg_wal folder high disk usage

[Greg Sabino Mullane]

On Fri, Nov 1, 2024 at 2:40 AM Muhammad Usman Khan 
wrote:

> For immediate space, move older files from pg_Wal to another storage but
> don't delete them.
>

No, do not do this! Figure out why WAL is not getting removed by Postgres
and let it do its job once fixed. Please recall the original poster is
trying to figure out what to do because they are not the database admin, so
having them figure out which WAL are "older" and safe to move is not good
advice.

Resizing the disk is a better option. Could also see if there are other
large files on that volume that can be removed or moved elsewhere, esp.
large log files.

Hopefully all of this is moot because their DBA is back from leave. :)

Cheers,
Greg

Re: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed

[user]

Hello Adrian,
My apology for answering so late.

Indeed I have not splitted my transactions correctly in my first example.
BUT, if you change the syntaxt so that attach is performed in its own
transaction, result is the same. Try it out!

Also you have told me that I am seeing ExclusiveLock on refs table because
it is from foreign key constraint. Well this is incorrect, foreign key does
not take the ExclusiveLock in any situation.
https://pglocks.org/?pgcommand=ALTER%20TABLE%20ADD%20FOREIGN%20KEY%20(CHILD)

Could you try again with only attach being in its own transaction? You
should reproduce it.
Regards

On Mon, 21 Oct 2024 at 20:31, Adrian Klaver 
wrote:

>
>
> On 10/21/24 1:40 AM, user wrote:
> > ** forwarding to mailing list, forgot to add header
> >
> >
> > Thanks for answering.
> > I think one misunderstanding happened.
> > The parent table has the foreign key constraint.
> > So attach partition will add this constraint for table being attached.
> > (How this compares to foreign keys not being considered, not sure).
> >
> > Why is it that attach_partition does not require exclusive lock when
> > creating a constraint automatically?
> >
> > What is more, you have provided a quote that states the lock is needed
> > because the table needs to be checked that all entries comply with the
> > NEW constraint.
> >
> > Well it is not new when I manually create it before I attach.
> > It is new when I run attach command without previous manual constraint
> > creation, but then the lock is not created.
>
> 1) Case 1
>
>
> test=# \d films
>  Partitioned table "public.films"
>   Column | Type  | Collation | Nullable | Default
> +---+---+--+-
>   id | integer   |   |  |
>   code   | character(5)  |   |  |
>   title  | character varying(40) |   | not null |
>   did| integer   |   | not null |
> Partition key: LIST (code)
> Foreign-key constraints:
>  "films_did_fkey" FOREIGN KEY (did) REFERENCES refs(id)
> Number of partitions: 0
>
> create table films_partition (LIKE films INCLUDING ALL);
> CREATE TABLE
>
> test=# \d+ films_partition
>  Table "public.films_partition"
>   Column | Type  | Collation | Nullable | Default |
> Storage  | Compression | Stats target | Description
>
> +---+---+--+-+--+-+--+-
>   id | integer   |   |  | |
> plain| |  |
>   code   | character(5)  |   |  | |
> extended | |  |
>   title  | character varying(40) |   | not null | |
> extended | |  |
>   did| integer   |   | not null | |
> plain| |  |
> Access method: heap
>
>
> ALTER TABLE films ATTACH PARTITION films_partition for values in ('dr');
> ALTER TABLE
>
> test=# \d+ films
> Partitioned table
> "public.films"
>   Column | Type  | Collation | Nullable | Default |
> Storage  | Compression | Stats target | Description
>
> +---+---+--+-+--+-+--+-
>   id | integer   |   |  | |
> plain| |  |
>   code   | character(5)  |   |  | |
> extended | |  |
>   title  | character varying(40) |   | not null | |
> extended | |  |
>   did| integer   |   | not null | |
> plain| |  |
> Partition key: LIST (code)
> Foreign-key constraints:
>  "films_did_fkey" FOREIGN KEY (did) REFERENCES refs(id)
> Partitions: films_partition FOR VALUES IN ('dr   ')
>
> test=# \d+ films_partition
>  Table "public.films_partition"
>   Column | Type  | Collation | Nullable | Default |
> Storage  | Compression | Stats target | Description
>
> +---+---+--+-+--+-+--+-
>   id | integer   |   |  | |
> plain| |  |
>   code   | character(5)  |   |  | |
> extended | |  |
>   title  | character varying(40) |   | not null | |
> extended | |  |
>   did| integer   |   | not null | |
> plain| |  |
> Partition of: films FOR VALUES IN ('dr   ')
> Partition constraint: ((code IS NOT NULL) AND (c

Re: Plans for partitioning of inheriting tables

[Torsten Förtsch]

Thiemo,

it looks to me like you are using inheritance just to make sure your
SOURCES and TOPO_FILES tables have some common columns. If you are not
actually querying the TEMPLATE_TECH table and expect to see all the rows
from the other 2 tables in that one table combined, then you could use
CREATE TABLE (LIKE ...) instead of inheritance. That way your "child"
tables would become normal tables and you could use declarative
partitioning on them.

Even if you are querying the TEMPLATE_TECH table, you could still do that
by turning the TEMPLATE_TECH table into a view which performs a UNION ALL
over the other tables.

Just my 2 cents

--
Torsten

On Fri, Nov 1, 2024 at 7:01 PM Adrian Klaver 
wrote:

>
>
> On 11/1/24 10:21 AM, thi...@gelassene-pferde.biz wrote:
> >
> > Adrian Klaver  escribió:
> >
> >> Changing that would count as a major change. Even if you where to
> >> convince the developers to make the change the earliest it would
> >> released would be with the next major release in Fall of 2025. That
> >> assumes you can convince then early enough or at all.
> >
> > I was not trying to convince anyone to do anything about the
> > implementation of declarative partitioning. I have been just curious if
> > there were plans. If I have raised the impression of the former, I am
> > sorry.
> Even if there where plans, any changes would happen in the future and
> would not be help the now problem.
>
>
> >>> 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.
> >>
> >> Hard to say without some firm numbers and/or testing.
> >
> > Sure, I was hoping those test would have been done some day. But in the
> > end, to me, it is not important.
>
> That is contradicted by your statement below:
>
> "I mean to say that I believe that loading that much data into one field
> will take much more time than runtime difference of
> trigger/rules/declarative partitioning solutions would to sort the data
> into the correct partition."
>
> Either performance is important or it is not.
>
> If TILE is referring to the same thing you are dealing with in related
> question on psycopg list then you are talking about bytea storage. You
> should take a look at:
>
> https://www.postgresql.org/docs/current/storage-toast.html
>
> In any case assuming you are not entering/reading/updating all the bytea
> data at one time then you are looking at fetching only that bytea data
> that are filtered by other attributes of the rows. I would strongly
> suggest running some tests on a single table with the data and see if
> you can live with the performance results before complicating things
> with partitioning.
>
> >
> >> Also this "... I have fairly few records that are in themselves rather
> >> big" could use some explanation. In other words what makes you think
> >> that partitioning is the answer to this issue?
> >
> > I was not thinking that partitioning was the answer to a performance
> > problem. Partitioning might be an answer to the maintenance of records,
> > specifically if entire sources are affected. The size of the tif files
> > to get loaded into the raster attribute TILE range from 112 kB to 32 MB.
> > I am complete unaware of the inner storing mechanisms of raster in
> > PostGIS, but on first sight, it seems that the rest of a records of
> > TOPO_FILES is negligible compared to the TILE. The total number of files
> > to be loaded in my case are 3273, even though that only encompasses a
> > small part of the world, I do not think, the latter would surpass 10
> > records. Not much for a database table, afaik. I mean to say that I
> > believe that loading that much data into one field will take much more
> > time than runtime difference of trigger/rules/declarative partitioning
> > solutions would to sort the data into the correct partition.
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>

Re: Used memory calculation in containers - docker stats and file cache

[Peter J. Holzer]

On 2024-10-27 16:23:44 +0100, Costa Alexoglou wrote:
> The container limit was 16GB of RAM, and as soon as this limit was reached,
> there was no restart or OOM errors, rather than a huge drop in memory (image
> `ContainerRelativeAbsolute`).
[...]
> 2. What is happening on the OS level when suddenly 15GB of file cache is
> getting erased (image `ContainerRelativeAbsolute`)? I would expect for
> incremental deletes rather than so many GB of cache being evicted.

I don't know if Docker does anything strange here. I can think of two
scenarios which would normally result in a sudden drop in filesystem
cache size:

1) A large file (or many smaller files) which is cached is deleted
2) Something else briefly needs a lot of RAM, evicting data from the
   cache.

Both can happen in a database (for example, a large sort operation might
need a few GBs of either RAM or temporary files, depending on your
work_mem settings), but I wouldn't expect them to happen just before the
configured limit is reached. So I'd double check the logs if there are
any errors.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Plans for partitioning of inheriting tables

[thiemo]

 Thanks, I shall have a look into it. I was under the assumption the  
the create table like would create no more than a structural copy.


Torsten F��rtsch  escribi��:


Thiemo,  ��
   it looks to me like you are using inheritance just to make sure  
your SOURCES and TOPO_FILES tables have some common columns. If you  
are not actually querying the TEMPLATE_TECH table and expect to see  
all the rows from the other 2 tables in that one table combined,  
then you could use CREATE TABLE (LIKE ...) instead of inheritance.  
That way your "child" tables would become normal tables and you  
could use declarative partitioning on them.

   ��
   Even if you are querying the TEMPLATE_TECH table, you could still  
do that by turning the TEMPLATE_TECH table into a view which  
performs a UNION ALL over the other tables.

Re: Plans for partitioning of inheriting tables

[Adrian Klaver]

On 11/1/24 13:47, Thiemo Kellner wrote:

It looks to me basically to be a "create table A as select * from B where 
false".


No it more capable then that.

CREATE TABLE  AS  is bare bones, you get the 
column names, types and data(or not) and that is it.


CREATE TABLE  LIKE  has  like_option which 
allows to transfer over more attributes of the table, for example 
defaults, constraints, indexes, etc.


See

https://www.postgresql.org/docs/current/sql-createtable.html

LIKE source_table [ like_option ... ]




01.11.2024 20:38:15 Adrian Klaver :


On 11/1/24 12:16, thi...@gelassene-pferde.biz wrote:

Thanks, I shall have a look into it. I was under the assumption the the create 
table like would create no more than a structural copy.


Not sure what you mean by structural copy, but the table created by CREATE 
TABLE LIKE will not have any association with the table it was created from.

https://www.postgresql.org/docs/current/sql-createtable.html

"Unlike INHERITS, the new table and original table are completely decoupled after 
creation is complete. Changes to the original table will not be applied to the new table, 
and it is not possible to include data of the new table in scans of the original 
table."


Torsten Förtsch mailto:tfoertsch...@gmail.com>> 
escribió:
Thiemo,

it looks to me like you are using inheritance just to make sure your SOURCES and 
TOPO_FILES tables have some common columns. If you are not actually querying the 
TEMPLATE_TECH table and expect to see all the rows from the other 2 tables in that one 
table combined, then you could use CREATE TABLE (LIKE ...) instead of inheritance. That 
way your "child" tables would become normal tables and you could use 
declarative partitioning on them.
Even if you are querying the TEMPLATE_TECH table, you could still do that by 
turning the TEMPLATE_TECH table into a view which performs a UNION ALL over the 
other tables.




--
Adrian Klaver
adrian.kla...@aklaver.com





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Plans for partitioning of inheriting tables

[thiemo]

Adrian Klaver  escribió:


On 11/1/24 13:47, Thiemo Kellner wrote:
It looks to me basically to be a "create table A as select * from B  
where false".


No it more capable then that.


Yes, I wrote basically, not exactly.

CREATE TABLE  LIKE  has  like_option which  
allows to transfer over more attributes of the table, for example  
defaults, constraints, indexes, etc.


But, to my understanding, no primary nor unique nor foreign constraint.

Re: Plans for partitioning of inheriting tables

[thiemo]

Adrian Klaver  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