undo_partition needs to be called twice if there is data in the partitions

[Woodz]

I am calling

SELECT partman.undo_partition(
  p_parent_table => 'my_partitioned_table',
  p_target_table => 'my_unpartitioned_table',
  -- Do not keep the old partitions as tables
  p_keep_table => FALSE
);
SELECT * FROM information_schema.tables WHERE table_schema = 'my_schema';

however the behaviour is different whether there is data in my_partitioned_table or not:

Without data:

Moved 0 row(s) to the target table. Removed 10 partitions.
(10,0)

my_db	my_schema	my_partitioned_table	BASE TABLE

With 1 row of data:

Moved 1 row(s) to the target table. Removed 1 partitions.
(1,1)

my_db	my_schema	my_partitioned_table	BASE TABLE
my_db	my_schema	my_partitioned_table_p2024_03_11	BASE TABLE
my_db	my_schema	my_partitioned_table_p2024_03_18	BASE TABLE
my_db	my_schema	my_partitioned_table_p2024_03_25	BASE TABLE
my_db	my_schema	my_partitioned_table_p2024_04_01	BASE TABLE
my_db	my_schema	my_partitioned_table_p2024_04_08	BASE TABLE
my_db	my_schema	my_partitioned_table_p2024_04_15	BASE TABLE
my_db	my_schema	my_partitioned_table_p2024_04_22	BASE TABLE
my_db	my_schema	my_partitioned_table_p2024_04_29	BASE TABLE
my_db	my_schema	my_partitioned_table_p2024_05_06	BASE TABLE

This means that I need to call undo_partition twice if there is data in it, but only once if there is no data. This behaviour is very unexpected and complicated. Please correct so that undo_partition deletes all partitions, not just partitions with data, when there is data in the table.

[keithf4]

Just for clarification, you're talking about a trigger-based partition set where there is data in the parent table?

[Woodz]

Yes, correct.

…

On Fri, 12 Apr 2024, 21:05 Keith Fiske, ***@***.***> wrote: Just for clarification, you're talking about a trigger-based partition set where there is data in the parent table? — Reply to this email directly, view it on GitHub <#651 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAHL4DOP72TEPJE5QHI6V7DY47L2XAVCNFSM6AAAAABGDS5OHOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDANJRG4ZDKOJVHE> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[keithf4]

Trigger-based partitioning has now been deprecated from pg_partman as of version 5.0. There will not be any further development of the 4.x series outside of critical bug fixes, so I don't think this will be getting fixed at the moment.

I would highly encourage you to migrate to native, declarative partitioning. Documentation for several scenarios is available here - https://github.com/pgpartman/pg_partman/tree/master/doc

[keithf4]

Closing this issue for now. Please feel free to re-open if you have any further questions concerning this.

[Woodz]

Apologies. This bug was actually observed in pg_partman v4.7.3 using time-based declarative partitioning, e.g.

CREATE TABLE "my_schema"."my_partitioned_table" (
  "created_at" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  "id" TEXT NOT NULL,
  CONSTRAINT "my_partitioned_table__pkey" PRIMARY KEY ("id", "created_at")
) PARTITION BY RANGE ("created_at");

CREATE TABLE "partman"."my_partitioned_table-template" (LIKE "my_schema"."my_partitioned_table");

SELECT partman.create_parent(
  p_parent_table => 'my_schema.my_partitioned_table',
  p_control => 'created_at',
  p_type => 'native',
  p_interval => '1 week',
  p_template_table => 'partman.my_partitioned_table-template',
  p_date_trunc_interval => 'week'
);

[keithf4]

If you know you want to undo the entire partition set, I'd recommend just using undo_partition_proc() instead. Without setting the loop count, it will by default unpartition the entire set and commit after each interval batch completes to help avoid longer running transactions

However undo_partition() by default, and as you've called it in your example, will only work on a single interval batch at at time. If you need it to run through more than a single interval batch, there are additional parameters to set

* `p_batch_count` - an optional argument, this sets how many times to move the amount of data equal to the `p_batch_interval` argument (or default partition interval if not set) in a single run of the function. Defaults to 1.
 
* `p_batch_interval` - optional argument. A time or id interval of how much of the data to move. This can be smaller than the partition interval, allowing for very large sized partitions to be broken up into smaller commit batches. Defaults to the configured partition interval if not given or if you give an interval larger than the partition interval. Note that the value must be given as text to this parameter.

So if you need the entire partition set to be unpartitioned, you need to set p_batch_count high enough to account for as many batches of the interval it will need to run with. You can set this to an arbitrarily high number and it will stop as soon as all the data is moved.

https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#destruction-functions

Note the link above is for version 5.0, so the parameters have different names now. But the basic function of undo_partition hasn't changed.

Let me know if that clarifies things for you.