Re: Suggestion: provide a "TRUNCATE PARTITION" command

On Fri, Jan 8, 2021 at 2:36 AM Thomas Kellerer <shammat(at)gmx(dot)net> wrote:

> Hello,
>
> I wonder if it made sense to add a "TRUNCATE PARTITION" command to
> Postgres?
>
> Especially during bulk loads it's more efficient to TRUNCATE a partition
> if I know I want to replace all rows, rather than doing a DELETE.
>
> Currently this requires dynamic SQL which isn't always feasible (and might
> get complicated quickly).
>
> So I was thinking that a new command to allow truncating partitions by
> identifying the partitions by "value" rather by name might be helpful in
> that case.
>
> Something along the lines of:
>
> truncate partitions of base_table
> for values in (...);
>
> If the IN part allowed for sub-queries then this could be used to gather
> the partition keys from e.g. a staging table.
>

For me, it seems too easily error prone such that a single typo in the IN
clause may result in an entire partition being removed that wasn't supposed
to be targeted. Given the user still needs to manually generate that list
somehow, I don't see it as a huge effort to query the partitions and run
individual commands to truncate or detach several partitions manually.

Unless it is in the SQL standard, or allows users to do something that
cannot be easily done otherwise, I see it as a bell / whistle that would
unnecessarily complicate the code. Writing a function that finds the
partition table names and gives back a string with the text of the DDL
commands that needs to be run is simple, and would encourage the user to
review which tables are targeted for truncate command.