The partitions are not generated as expected.

[lfelguetac]

Sorry if this is something basic but I have created a new table partitioned by a timestamp-type key field. In short I run this:

CREATE TABLE backoffice.flatt_intentions (
id varchar NOT NULL,
state varchar NULL,
intention_createtime timestamptz NOT NULL,
)
PARTITION BY RANGE(intention_createtime);

CREATE INDEX ON backoffice.flatt_intentions(intention_createtime);

SELECT partman.create_parent( 'backoffice.flatt_intentions', 'intention_createtime', 'native', 'daily', 0);

but here comes the problem, when I run my script that fills this table with mostly historical data it generates partitions according to today's date but the date I'm processing (intention_createtime) is from the past (some days in March).

So I would expect that since intention_createtime corresponds to the first 3 days of March, that partam creates partitions for the days 1, 2 and 3 of March and not with the date of these last days.

partitions:

part_config

real data

did I do something wrong?

Greetings to everybody and thanks

[keithf4]

Creation of a new partition set for time is always based on the current datetime for creating the initial children. You can provide a value for the p_partition_start column if you'd like it to initially create earlier partitions than the default. pg_partman does not create child tables on demand if there is no child table for them. If there is no matching child table for the data it goes into the default table (if there is one) or throws an error (if there's isn't).

You can partition that data out of the default afterwards using the partition_data_proc() procedure.

Also curious what your intention with the final parameter value of 0 to create_parent() is there? The 5th parameter is the constraint columns and typically needs a column list, but can just be left NULL. Also I do not recommend reducing the premake value below the default of 4 unless you have a specific need to. pg_partman precreates child tables ahead of when they are needed to avoid contention during new child table creation.

[lfelguetac]

Thanks @keithf4 for answering, it really strikes me that Partman creates the partitions according to today's date and not according to the date of the key field (in this case _intentioncreatetime) that is, according to the real data as one might expect it to be. Sorry to insist, is there a way to configure it to work that way?

[keithf4]

What child table is the data in that pg_partman would know at the time you call create_parent()? A partitioned table is empty when first created and the top level can contain no data. And pg_partman is typically run against that top level table to create the initial children. So unless you already have a child table attached to it, how would it know when your intended starting point is?

As I said, you can use the p_start_partition parameter to create_parent() to tell it which child table you want it to start at. It will then start with that child table, then also create all child tables up to the current date + the premake.

[keithf4]

Just to give you an example, today is June 28th, but I want the child partitions to start on June 1st for daily partitioning

keith=# SELECT partman.create_parent( 'backoffice.flatt_intentions', 'intention_createtime', 'native', 'daily', p_start_partition:= '2023-06-01');
 create_parent 
---------------
 t
(1 row)

keith=# \d+ backoffice.flatt_intentions
                                            Partitioned table "backoffice.flatt_intentions"
        Column        |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
----------------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id                   | character varying        |           | not null |         | extended |             |              | 
 state                | character varying        |           |          |         | extended |             |              | 
 intention_createtime | timestamp with time zone |           | not null |         | plain    |             |              | 
Partition key: RANGE (intention_createtime)
Partitions: backoffice.flatt_intentions_p2023_06_01 FOR VALUES FROM ('2023-06-01 00:00:00-04') TO ('2023-06-02 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_02 FOR VALUES FROM ('2023-06-02 00:00:00-04') TO ('2023-06-03 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_03 FOR VALUES FROM ('2023-06-03 00:00:00-04') TO ('2023-06-04 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_04 FOR VALUES FROM ('2023-06-04 00:00:00-04') TO ('2023-06-05 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_05 FOR VALUES FROM ('2023-06-05 00:00:00-04') TO ('2023-06-06 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_06 FOR VALUES FROM ('2023-06-06 00:00:00-04') TO ('2023-06-07 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_07 FOR VALUES FROM ('2023-06-07 00:00:00-04') TO ('2023-06-08 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_08 FOR VALUES FROM ('2023-06-08 00:00:00-04') TO ('2023-06-09 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_09 FOR VALUES FROM ('2023-06-09 00:00:00-04') TO ('2023-06-10 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_10 FOR VALUES FROM ('2023-06-10 00:00:00-04') TO ('2023-06-11 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_11 FOR VALUES FROM ('2023-06-11 00:00:00-04') TO ('2023-06-12 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_12 FOR VALUES FROM ('2023-06-12 00:00:00-04') TO ('2023-06-13 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_13 FOR VALUES FROM ('2023-06-13 00:00:00-04') TO ('2023-06-14 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_14 FOR VALUES FROM ('2023-06-14 00:00:00-04') TO ('2023-06-15 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_15 FOR VALUES FROM ('2023-06-15 00:00:00-04') TO ('2023-06-16 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_16 FOR VALUES FROM ('2023-06-16 00:00:00-04') TO ('2023-06-17 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_17 FOR VALUES FROM ('2023-06-17 00:00:00-04') TO ('2023-06-18 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_18 FOR VALUES FROM ('2023-06-18 00:00:00-04') TO ('2023-06-19 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_19 FOR VALUES FROM ('2023-06-19 00:00:00-04') TO ('2023-06-20 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_20 FOR VALUES FROM ('2023-06-20 00:00:00-04') TO ('2023-06-21 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_21 FOR VALUES FROM ('2023-06-21 00:00:00-04') TO ('2023-06-22 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_22 FOR VALUES FROM ('2023-06-22 00:00:00-04') TO ('2023-06-23 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_23 FOR VALUES FROM ('2023-06-23 00:00:00-04') TO ('2023-06-24 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_24 FOR VALUES FROM ('2023-06-24 00:00:00-04') TO ('2023-06-25 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_25 FOR VALUES FROM ('2023-06-25 00:00:00-04') TO ('2023-06-26 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_26 FOR VALUES FROM ('2023-06-26 00:00:00-04') TO ('2023-06-27 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_27 FOR VALUES FROM ('2023-06-27 00:00:00-04') TO ('2023-06-28 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_28 FOR VALUES FROM ('2023-06-28 00:00:00-04') TO ('2023-06-29 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_29 FOR VALUES FROM ('2023-06-29 00:00:00-04') TO ('2023-06-30 00:00:00-04'),
            backoffice.flatt_intentions_p2023_06_30 FOR VALUES FROM ('2023-06-30 00:00:00-04') TO ('2023-07-01 00:00:00-04'),
            backoffice.flatt_intentions_p2023_07_01 FOR VALUES FROM ('2023-07-01 00:00:00-04') TO ('2023-07-02 00:00:00-04'),
            backoffice.flatt_intentions_p2023_07_02 FOR VALUES FROM ('2023-07-02 00:00:00-04') TO ('2023-07-03 00:00:00-04'),
            backoffice.flatt_intentions_default DEFAULT

[keithf4]

Just checking if you still need any assistance with this issue

[lfelguetac]

sorry my friend, we decided to temporarily use mongoDb. I was not able to test the proposal you gave me, and I don't know if the partitioning worked by filling a table with data backwards in time

[keithf4]

No problem! Glad you found a solution that works for you.