Hi,
El jue, 26 oct 2023 11:15, Peter J. Holzer <hjp-pgsql@xxxxxx> escribió:
On 2023-10-25 17:48:46 +0200, Olivier Gautherot wrote:
> El mié, 25 oct 2023 16:58, Олег Самойлов <splarv@xxxxx> escribió:
> Okey, I see no one was be able to solve this problem. But I could. May be
> for someone this will be useful too. There is solution.
[...]
> Now query is:
>
> SELECT generate_series(min(id)/10000000, max(id)/10000000) AS n FROM
> delivery) as part_numbers
> WHERE (SELECT max(created_at) from delivery where n*10000000 <=id
> and id < (n+1)*10000000)
> < CURRENT_DATE-'3 month'::interval;
>
> Return the same (number of partition need to archive), accelerated by two
> btree index: on id and created_at. Works very quick, less then second.
[...]
> Your fast solution will work as long as you don't have missing sequences (like
> deleted rows).
Why do you think this would break with missing sequence numbers?
hp
In the suggested query, the return value contains a list of sequential numbers from a min to a max - they seem to be markers of the partitions. Let's assume that a complete partition is deleted in the middle: its index will still be returned by the query, although it doesn't exist any more in the table. It can be an issue if the list of indexes is actually used and partitions are not deleted sequentially.
My cent worth to ensure data integrity.
Regards
Olivier Gautherot
