Equivalence between '1 week' and '7 days'.

[fgit-hubber]

Hello Keith,

Maybe it's just my setup, but I am still writing this, in case it's worth following.

Some of my tables are partitioned on a weekly basis. In part_config the column partition_interval says "7 days".
For all common purposes this was OK, but when I tried to use "partition_data_time(...)" directly, I had a problem because the created partitions were attempted to be created of one day "width". Well, actually their names were "day-aligned".
The actual operation I tried was to fill the partitioned table with data from other source using partition_data_proc in slices of '1 day' (regardless of the actual slice of the target table). Like this:

        call partman.partition_data_proc(
            p_parent_table := p_tableName
            , p_loop_count := 100
            , p_interval := '1 day'
            , p_wait := 1
            , p_order := 'DESC'
            , p_source_table := p_theSrcTableName
        );

And I got into trouble for week-partitioned tables.

In my case, I fixed this by the following (diff is against an older commit, not the latest one, but the latest one still handles the name in the same way):

@@ -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

The basic idea is to map the '7 days' special case to 'week' interval, not to 'day'.

I do not claim the above is the best approach or even fully correct but I hope it does illustrate the possible issue.

Thank you,
F.

[keithf4]

Interesting find. I'll definitely come back and review this further after I get this next patch release out., but your fix makes sense. Thank you for finding the issue and providing a potential solution!

[keithf4]

Do you happen to have a use-case where this breaks consistently that I can use to test myself as well?

[fgit-hubber]

Vague memories: I was trying to import smaller slices from external large table. The target was partitioned by week but one week worth of data looked too much for me (I think it came over some dblink). So I tried to do smaller chunks (1 day size). The auto-create partition created misaligned weeks.