Michael Lewis schrieb am 08.01.2021 um 16:32:
On Fri, Jan 8, 2021 at 2:36 AM Thomas Kellerer <sham...@gmx.net 
<mailto:sham...@gmx.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.

I don't see how this is more dangerous then:

    delete from base_table
    where partition_key in (...);

which would serve the same purpose, albeit less efficient.

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.

Well, the list could come from e.g. a staging table, e.g. "for values IN (select 
some_column from staging_table)"



Reply via email to