Moving smaller interval from explicit source with partition_data_proc does not work

[fgit-hubber]

Hello.

Using an existing regular table as model I created a partitioned table (no initial partitions). I created also a compatible template.
Then I successfully created a parent table partition with interval 1 month and adjusted retention to 4 months:
This is the result:

   parent_table   | control  | time_encoder | time_decoder | partition_interval | partition_type | premake | automatic_maintenance |      template_table       | retention | retention_schema | retention_keep_index | retention_keep_table | epoch | constraint_cols | optimize_constraint | infinite_time_partitions | datetime_string | jobmon | sub_partition_set_full | undo_in_progress | inherit_privileges | constraint_valid | ignore_default_data | date_trunc_interval | maintenance_order | retention_keep_publication | maintenance_last_run
------------------+----------+--------------+--------------+--------------------+----------------+---------+-----------------------+---------------------------+-----------+------------------+----------------------+----------------------+-------+-----------------+---------------------+--------------------------+-----------------+--------+------------------------+------------------+--------------------+------------------+---------------------+---------------------+-------------------+----------------------------+----------------------
 testnsp.test_tbl | event_ts |              |              | 1 mon              | range          |       2 | on                    | testnsp.test_tbl_template | 4 months  |                  | f                    | f                    | none  |                 |                  30 | f                        | YYYYMMDD        | t      | f                      | f                | f                  | t                | t                   |                     |                   | f                          |

Now I want to copy data from original table (testnsp.z_test_tbl) with partition_data_proc(), but using '1 day' instead of '1 month' because the source table is large-ish.
If I understood correctly the documentation, that should be possible, for external_source. Yes, I understand, cleaning the default partition cannot be done in smaller intervals. This is not the case here.

So, I try calling partition_data_proc(). Unfortunately:

testdb=> call partman.partition_data_proc(
        p_parent_table := 'testnsp.test_tbl'
        , p_loop_count := 100
        , p_interval := '1 day'
        , p_wait := 1
        , p_order := 'DESC'
        , p_source_table := 'testnsp.z_test_tbl'
    );
ERROR:  relation "testuat.test_tbl_p20250825" does not exist
LINE 3:                          INSERT INTO testuat.test_tbl_p20250...
                                             ^
QUERY:  WITH partition_data AS (
                            DELETE FROM ONLY testuat.z_test_tbl WHERE event_ts >= '2025-08-25 11:57:18.169+03' AND event_ts < '2025-09-01 00:00:00+03' RETURNING *)
                         INSERT INTO testuat.test_tbl_p20250825 (id,row_type,event_ts,src_type,src_info) SELECT id,row_type,event_ts,src_type,src_info FROM partition_data
CONTEXT:  PL/pgSQL function partman.partition_data_time(text,integer,interval,numeric,text,boolean,text,text[]) line 258 at EXECUTE
SQL statement "SELECT partman.partition_data_time (p_parent_table := 'testuat.test_tbl', p_lock_wait := '0', p_order := 'DESC', p_analyze := false, p_batch_interval := '1 day', p_source_table := 'testuat.z_test_tbl')"
PL/pgSQL function partman.partition_data_proc(text,integer,text,integer,integer,integer,text,text,text[],boolean) line 86 at EXECUTE

It looks like it's trying to access directly some partition by composing the name according to the '1 day' interval, not to the normal interval of the parent_table.

I tried to look into partition_data_time.sql and it seems that around line 223 of the function (line 237 of the file) the v_min_partition_timestamp is not passed through some logic to determine properly the correct partition name.
Something like applying some truncation logic to v_min_partition_timestamp according to the parent_table's interval.
But that is only a guess, as the overall code is too complicated to fix properly without a significant amount of additional investigation.

Thank you for looking into this.

Florin

[fgit-hubber]

Maybe the fix could be as simple as this:

diff --git a/sql/functions/partition_data_time.sql b/sql/functions/partition_data_time.sql
index 34ca397..9ca8e5e 100644
--- a/sql/functions/partition_data_time.sql
+++ b/sql/functions/partition_data_time.sql
@@ -39,6 +39,7 @@ v_source_tablename          text;
 v_rowcount                  bigint;
 v_start_control             timestamptz;
 v_total_rows                bigint := 0;
+v_trunc_string              text;

 BEGIN
 /*
@@ -233,8 +234,24 @@ FOR i IN 1..p_batch_count LOOP
         END IF;
     END IF;

-    -- This suffix generation code is in create_partition_time() as well
-    v_partition_suffix := to_char(v_min_partition_timestamp, v_datetime_string);
+    -- Similar suffix generation code is in create_partition_time() as well
+    -- Here we must account for the fact that our copy interval might be smaller
+    -- than actual partition interval
+    if v_partition_interval < '7 days' then
+        v_trunc_string := 'day';
+    elseif v_partition_interval = '7 days' then
+        v_trunc_string := 'week';
+    elseif v_partition_interval < '1 month' then
+        v_trunc_string := 'week';
+    elseif v_partition_interval <= '1 month' then
+        v_trunc_string := 'month';
+    else
+        v_trunc_string := 'year';
+    end if;
+    v_partition_suffix := to_char(
+            date_trunc(v_trunc_string, v_min_partition_timestamp::timestamp)
+            , v_datetime_string
+        );
     v_current_partition_name := @extschema@.check_name_length(v_parent_tablename, v_partition_suffix, TRUE);

     IF v_default_exists THEN

Yes, it's not fail-proof and hack-ish ugly. Unusual intervals may not be compatible with this fix. Only way to be fail-proof would be to navigate the existing set of child tables and pick the right one. But see below.

One more thought:
Inserting directly into the partitioned table when source is external might be much simpler, as long as proper partitions are in place. The existing code does not create partitions on-the-fly anyway.
I understand that for the "old" trigger-based mechanisms it might have been better to go directly and probably that is the reason the code is like this.
I also understand that moving data out of default requires direct partition access.
But with current declarative partitioning, the two logical paths are entirely distinct, leading possibly to distinct procedures.
OR
You could compute the proper target table name and data-moving interval depending on the usage pattern, then do whatever lock/copy/delete is needed in unified code.
For "default" usage, the target would be a partition child table (determined with a bit of effort, like now) and the interval the natural one.
For the "external" usage the target would be the entire partition table (simple case) and the interval the specified one.