public inbox for [email protected]
help / color / mirror / Atom feedPlans for partitioning of inheriting tables
10+ messages / 5 participants
[nested] [flat]
* Plans for partitioning of inheriting tables
@ 2024-10-24 19:47 [email protected]
2024-10-24 20:44 ` Re: Plans for partitioning of inheriting tables Adrian Klaver <[email protected]>
2024-10-24 20:58 ` Re: Plans for partitioning of inheriting tables David G. Johnston <[email protected]>
0 siblings, 2 replies; 10+ messages in thread
From: [email protected] @ 2024-10-24 19:47 UTC (permalink / raw)
To: PostgreSQL General <[email protected]>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plans for partitioning of inheriting tables
2024-10-24 19:47 Plans for partitioning of inheriting tables [email protected]
@ 2024-10-24 20:44 ` Adrian Klaver <[email protected]>
2024-10-25 05:33 ` Re: Plans for partitioning of inheriting tables Thiemo Kellner <[email protected]>
1 sibling, 1 reply; 10+ messages in thread
From: Adrian Klaver @ 2024-10-24 20:44 UTC (permalink / raw)
To: [email protected]; PostgreSQL General <[email protected]>
On 10/24/24 12:47 PM, [email protected] 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
> https://wiki.postgresql.org/wiki/Development_information.
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.
>
> Kind regards
>
> Thiemo
>
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plans for partitioning of inheriting tables
2024-10-24 19:47 Plans for partitioning of inheriting tables [email protected]
2024-10-24 20:44 ` Re: Plans for partitioning of inheriting tables Adrian Klaver <[email protected]>
@ 2024-10-25 05:33 ` Thiemo Kellner <[email protected]>
2024-10-25 15:57 ` Re: Plans for partitioning of inheriting tables Adrian Klaver <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Thiemo Kellner @ 2024-10-25 05:33 UTC (permalink / raw)
To: ; +Cc: PostgreSQL General <[email protected]>
Thanks for taking this up.
24.10.2024 22:44:11 Adrian Klaver <[email protected]>:
>
> 1) Have you looked at?:
>
> 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/table...
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plans for partitioning of inheriting tables
2024-10-24 19:47 Plans for partitioning of inheriting tables [email protected]
2024-10-24 20:44 ` Re: Plans for partitioning of inheriting tables Adrian Klaver <[email protected]>
2024-10-25 05:33 ` Re: Plans for partitioning of inheriting tables Thiemo Kellner <[email protected]>
@ 2024-10-25 15:57 ` Adrian Klaver <[email protected]>
2024-10-25 18:45 ` Re: Plans for partitioning of inheriting tables Thiemo Kellner <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Adrian Klaver @ 2024-10-25 15:57 UTC (permalink / raw)
To: Thiemo Kellner <[email protected]>; +Cc: PostgreSQL General <[email protected]>
On 10/24/24 22:33, Thiemo Kellner wrote:
> Thanks for taking this up.
>
> 24.10.2024 22:44:11 Adrian Klaver <[email protected]>:
>
>>
>> 1) Have you looked at?:
>>
>> 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.
This needs a code example to go any further.
>
>>
>> 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/table...
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plans for partitioning of inheriting tables
2024-10-24 19:47 Plans for partitioning of inheriting tables [email protected]
2024-10-24 20:44 ` Re: Plans for partitioning of inheriting tables Adrian Klaver <[email protected]>
2024-10-25 05:33 ` Re: Plans for partitioning of inheriting tables Thiemo Kellner <[email protected]>
2024-10-25 15:57 ` Re: Plans for partitioning of inheriting tables Adrian Klaver <[email protected]>
@ 2024-10-25 18:45 ` Thiemo Kellner <[email protected]>
2024-10-25 18:47 ` Plans for partitioning of inheriting tables Thiemo Kellner <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Thiemo Kellner @ 2024-10-25 18:45 UTC (permalink / raw)
To: PostgreSQL General <[email protected]>
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/table...
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()
);
^ permalink raw reply [nested|flat] 10+ messages in thread
* Plans for partitioning of inheriting tables
2024-10-24 19:47 Plans for partitioning of inheriting tables [email protected]
2024-10-24 20:44 ` Re: Plans for partitioning of inheriting tables Adrian Klaver <[email protected]>
2024-10-25 05:33 ` Re: Plans for partitioning of inheriting tables Thiemo Kellner <[email protected]>
2024-10-25 15:57 ` Re: Plans for partitioning of inheriting tables Adrian Klaver <[email protected]>
2024-10-25 18:45 ` Re: Plans for partitioning of inheriting tables Thiemo Kellner <[email protected]>
@ 2024-10-25 18:47 ` Thiemo Kellner <[email protected]>
2024-10-31 16:57 ` Re: Plans for partitioning of inheriting tables Adrian Klaver <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Thiemo Kellner @ 2024-10-25 18:47 UTC (permalink / raw)
To: PostgreSQL General <[email protected]>
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/table...
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()
);
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plans for partitioning of inheriting tables
2024-10-24 19:47 Plans for partitioning of inheriting tables [email protected]
2024-10-24 20:44 ` Re: Plans for partitioning of inheriting tables Adrian Klaver <[email protected]>
2024-10-25 05:33 ` Re: Plans for partitioning of inheriting tables Thiemo Kellner <[email protected]>
2024-10-25 15:57 ` Re: Plans for partitioning of inheriting tables Adrian Klaver <[email protected]>
2024-10-25 18:45 ` Re: Plans for partitioning of inheriting tables Thiemo Kellner <[email protected]>
2024-10-25 18:47 ` Plans for partitioning of inheriting tables Thiemo Kellner <[email protected]>
@ 2024-10-31 16:57 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Adrian Klaver @ 2024-10-31 16:57 UTC (permalink / raw)
To: Thiemo Kellner <[email protected]>; PostgreSQL General <[email protected]>
On 10/25/24 11:47, Thiemo Kellner wrote:
>
> 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/table... 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.
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
>
>
> 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()
> );
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plans for partitioning of inheriting tables
2024-10-24 19:47 Plans for partitioning of inheriting tables [email protected]
@ 2024-10-24 20:58 ` David G. Johnston <[email protected]>
2024-10-25 05:42 ` Re: Plans for partitioning of inheriting tables Thiemo Kellner <[email protected]>
2024-11-01 15:15 ` Re: Plans for partitioning of inheriting tables Achilleas Mantzios - cloud <[email protected]>
1 sibling, 2 replies; 10+ messages in thread
From: David G. Johnston @ 2024-10-24 20:58 UTC (permalink / raw)
To: [email protected] <[email protected]>; +Cc: PostgreSQL General <[email protected]>
On Thursday, October 24, 2024, <[email protected]> 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/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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plans for partitioning of inheriting tables
2024-10-24 19:47 Plans for partitioning of inheriting tables [email protected]
2024-10-24 20:58 ` Re: Plans for partitioning of inheriting tables David G. Johnston <[email protected]>
@ 2024-10-25 05:42 ` Thiemo Kellner <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Thiemo Kellner @ 2024-10-25 05:42 UTC (permalink / raw)
To: ; +Cc: PostgreSQL General <[email protected]>
24.10.2024 22:58:39 David G. Johnston <[email protected]>:
>
> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plans for partitioning of inheriting tables
2024-10-24 19:47 Plans for partitioning of inheriting tables [email protected]
2024-10-24 20:58 ` Re: Plans for partitioning of inheriting tables David G. Johnston <[email protected]>
@ 2024-11-01 15:15 ` Achilleas Mantzios - cloud <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Achilleas Mantzios - cloud @ 2024-11-01 15:15 UTC (permalink / raw)
To: [email protected]
On 10/24/24 21:58, David G. Johnston wrote:
> On Thursday, October 24, 2024, <[email protected]> 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
> <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.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2024-11-01 15:15 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-24 19:47 Plans for partitioning of inheriting tables [email protected]
2024-10-24 20:44 ` Adrian Klaver <[email protected]>
2024-10-25 05:33 ` Thiemo Kellner <[email protected]>
2024-10-25 15:57 ` Adrian Klaver <[email protected]>
2024-10-25 18:45 ` Thiemo Kellner <[email protected]>
2024-10-25 18:47 ` Thiemo Kellner <[email protected]>
2024-10-31 16:57 ` Adrian Klaver <[email protected]>
2024-10-24 20:58 ` David G. Johnston <[email protected]>
2024-10-25 05:42 ` Thiemo Kellner <[email protected]>
2024-11-01 15:15 ` Achilleas Mantzios - cloud <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox