[YSQL][Pg Partman] New Partitions Not Created Due to infinite_time_partitions Setting in Time-Based Partitioning

[shishir2001-yb]

Jira Link: DB-13959

Description

Version: 2024.2.0.0-b116
Logs: Added in Jira

In one of our tests, we encountered an issue where new time-based partitions were not created, even after the last partition was full and run_maintenance was continuously being executed. This occurred because setting infinite_time_partitions to True causes the system to use time-based checks to determine if new partitions are needed, rather than checking the data volume in the latest partition.

Note: If we set infinite_time_partitions=false then the issue is no longer but we cannot set it to true because of the other issue(https://github.com/yugabyte/yugabyte-db/issues/24764).

noncolocateddbnoncolocatedtable2_p2024_11_09_1200

noncolocateddb1=# \d
                                           List of relations
Schema |                              Name                               |       Type        |  Owner   
--------+-----------------------------------------------------------------+-------------------+----------
public | custom_time_partitions                                          | table             | yugabyte
public | noncolocateddbnoncolocatedmaterializedviews1                    | materialized view | yugabyte
public | noncolocateddbnoncolocatedmaterializedviews2                    | materialized view | yugabyte
public | noncolocateddbnoncolocatedtable1                                | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_age_seq                        | sequence          | yugabyte
public | noncolocateddbnoncolocatedtable1_i2_seq                         | sequence          | yugabyte
public | noncolocateddbnoncolocatedtable1_i3_seq                         | sequence          | yugabyte
public | noncolocateddbnoncolocatedtable1_p0                             | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p0_p0                          | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p0_p500                        | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p1000                          | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p1000_p1000                    | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p1000_p1500                    | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p2000                          | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p2000_p2000                    | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p2000_p2500                    | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p3000                          | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p3000_p3000                    | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p3000_p3500                    | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p4000                          | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p4000_p4000                    | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p4000_p4500                    | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p5000                          | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p5000_p5000                    | table             | yugabyte
public | noncolocateddbnoncolocatedtable1_p5000_p5500                    | table             | yugabyte
public | noncolocateddbnoncolocatedtable2                                | table             | yugabyte
public | noncolocateddbnoncolocatedtable2_i2_seq                         | sequence          | yugabyte
public | noncolocateddbnoncolocatedtable2_i3_seq                         | sequence          | yugabyte
public | noncolocateddbnoncolocatedtable2_p2024_11_06_2000               | table             | yugabyte
public | noncolocateddbnoncolocatedtable2_p2024_11_06_2_p2024_11_06_2000 | table             | yugabyte
public | noncolocateddbnoncolocatedtable2_p2024_11_06_2_p2024_11_07_0400 | table             | yugabyte
public | noncolocateddbnoncolocatedtable2_p2024_11_07_1200               | table             | yugabyte
public | noncolocateddbnoncolocatedtable2_p2024_11_07_1_p2024_11_07_1200 | table             | yugabyte
public | noncolocateddbnoncolocatedtable2_p2024_11_07_1_p2024_11_07_2000 | table             | yugabyte
public | noncolocateddbnoncolocatedtable2_p2024_11_08_0400               | table             | yugabyte
public | noncolocateddbnoncolocatedtable2_p2024_11_08_0_p2024_11_08_0400 | table             | yugabyte
public | noncolocateddbnoncolocatedtable2_p2024_11_08_0_p2024_11_08_1200 | table             | yugabyte
public | noncolocateddbnoncolocatedtable2_p2024_11_08_2000               | table             | yugabyte
public | noncolocateddbnoncolocatedtable2_p2024_11_08_2_p2024_11_08_2000 | table             | yugabyte
public | noncolocateddbnoncolocatedtable2_p2024_11_08_2_p2024_11_09_0400 | table             | yugabyte
public | noncolocateddbnoncolocatedtable2_p2024_11_09_1200               | table             | yugabyte
public | noncolocateddbnoncolocatedtable2_p2024_11_09_1_p2024_11_09_1200 | table             | yugabyte
public | noncolocateddbnoncolocatedtable2_p2024_11_09_1_p2024_11_09_2000 | table             | yugabyte
public | part_config                                                     | table             | yugabyte
public | part_config_sub                                                 | table             | yugabyte
public | table_privs                                                     | view              | yugabyte
public | template_public_noncolocateddbnoncolocatedtable1                | table             | yugabyte
public | template_public_noncolocateddbnoncolocatedtable2                | table             | yugabyte
public | timestamp_seq_noncolocateddb1_noncolocateddbnoncolocatedtable2  | sequence          | yugabyte
(49 rows)

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TYPE complex AS (re float8, im float8);

 CREATE DOMAIN postal_code AS TEXT CHECK(VALUE ~ '^\d{5}$'OR VALUE ~ '^\d{5}-\d{4}$');
CREATE TYPE e_details AS ENUM ('Email', 'Sms', 'Phone');

CREATE SEQUENCE timestamp_seq_noncolocateddb1_noncolocateddbnoncolocatedtable2 INCREMENT BY 1 START WITH 1;

CREATE TABLE noncolocateddbnoncolocatedtable2(id text, uuid_col uuid DEFAULT uuid_generate_v4 (), name text, c complex, info json, contact JSONB, arr smallint[], cash money, i inet, m macaddr, i2 serial, i3 bigserial, val smallint, details e_details, age int, collated_data text collate "POSIX", date DATE, n NUMERIC (3, 2), r real, c1 CHAR(1), created_at timestamptz DEFAULT '2024-11-08 04:31:19'::timestamptz + (nextval('timestamp_seq_noncolocateddb1_noncolocateddbnoncolocatedtable2') * interval '1 minute'), uuid0 uuid DEFAULT uuid_nil(), uuid1 uuid DEFAULT uuid_generate_v1(), p1 POINT, t1 TIME, ts1 TIMESTAMP, i4 INTERVAL, p2 path, p3 polygon, b box, c2 circle, l line, l1 lseg, a2 text[][], zip postal_code, PRIMARY KEY(created_at)) PARTITION BY RANGE (created_at);


CREATE INDEX noncolocateddbnoncolocatedtable2_noncolocateddb2_index ON noncolocateddbnoncolocatedtable2(created_at );

CREATE MATERIALIZED VIEW noncolocateddbnoncolocatedmaterializedviews2 AS SELECT * FROM noncolocateddbnoncolocatedtable2;


SELECT create_parent(
        'public.noncolocateddbnoncolocatedtable2', 'created_at', 'native', '960 minutes',
        NULL::text[],
        2,
        'on',
        NULL::text,
        true,
        'none',
        '',
        NULL::text[],
        true,
        NULL::text,
        true,
        NULL::text
    );

SELECT create_sub_parent(
        'public.noncolocateddbnoncolocatedtable2', 'created_at', 'native', '480 minutes',
        'yes',
        NULL::text[],
        10,
        NULL::text,
        true,
        'none',
        '',
        true,
        true,
        NULL::text
    );

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.