Re: Question on Table creation

Thank you so much.

I tested and it worked as you mentioned i.e the template table is helping
to add the extra indexes or constraints to the child table/partitions (by
inheriting those from the template table if defined), those are not defined
there in the main table. Also dropping the default partition does no harm
to the new partition creation through the automatic maintenance job.

Though I am unable to visualize the situation in which we want the child
table/partitions to be having additional indexes or constraints as compared
to the main table. But I also see that the template table will be phased
out quickly. So I believe it's better to just go with the default template
table with current Pg version 16.

https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md

IMPORTANT NOTES:

-

The template table feature is only a temporary solution to help speed up
declarative partitioning adoption. As things are handled better in core,
the use of the *template table will be phased out quickly *from
pg_partman. If a feature that was managed by the template is supported in
core in the future, it will eventually be removed from template management
in pg_partman, so please plan ahead for that during major version upgrading
if it applies to you.

On Thu, Feb 29, 2024 at 1:58 AM veem v <veema0000(at)gmail(dot)com> wrote:

>
> On Wed, 28 Feb 2024 at 01:24, sud <suds1434(at)gmail(dot)com> wrote:
>
>> While testing the pg_partman extension I see it by default creates a
>> "default partition" even if we provide the parameter p_start_partition as
>> "current date". But if someone purposely doesn't want to have the default
>> partitions , so as to avoid any unwanted data entering the table , which
>> may be required from a data quality perspective. Is there any option in the
>> create_parent function to achieve that? Or do we have to manually drop the
>> default partition after getting this created through create_parent function?
>>
>> I am not able to understand the significance of the "p_template_table"
>> parameter. When we create the partitions without passing this parameter the
>> template table gets created automatically in the partman schema. Checking
>> the details of the template table, it doesn't show any indexes or
>> constraints present in it, but still the child partitions get created with
>> all the necessary indexes and constraints as those are there defined for
>> the main table. So wondering , in what real life scenario do we really need
>> the template table to be defined and how will it help if the partitions are
>> different then from the main table structure?
>>
>> Lastly , the template table is by default created in the partman schema
>> but it also works without error, if we pass the template table to be
>> created in the application schema. So is there any downside of having the
>> template table reside in the application schema?
>>
>>
>
> As per my understanding , you can't control the creation of the default
> partition. If you somehow don't want to keep it then, you can drop it post
> creation of the partition for the first time i.e after the create_parent
> function call.
>
> Template_table is necessary when someone needs the child partitions to
> have a difference in structure as compared to main table like difference in
> indexes or constraints. But I think the number and data types of columns
> should be the same in main table and template tables. I have not tried
> though.
>
> I think keeping a template table in an application schema is not a good
> idea considering the fact that it's not a business table but a technical
> one, and nobody should accidentally alter this.
>
>>
>>
>