From: | Thomas Kellerer <shammat(at)gmx(dot)net> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Suggestion: provide a "TRUNCATE PARTITION" command |
Date: | 2021-01-08 16:38:10 |
Message-ID: | 999c4e39-ec8d-35db-f77e-06361861fe4e@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Michael Lewis schrieb am 08.01.2021 um 16:32:
> On Fri, Jan 8, 2021 at 2:36 AM Thomas Kellerer <shammat(at)gmx(dot)net <mailto: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.
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)"
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Lewis | 2021-01-08 16:47:19 | Re: Suggestion: provide a "TRUNCATE PARTITION" command |
Previous Message | Adrian Klaver | 2021-01-08 16:15:16 | Re: How to keep format of views source code as entered? |