Re: Question on Table creation

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.

>
>