How can I drop the data from default partition with retention rule?

[ivandedkov]

Hi!
Thanks for the extension! I would like to ask how can I remove data from default partition automatically by partman? I have table with log data and it is partitioned by logget_at field. Sometimes app inserts logs with past time. This kind of data inserts into default partition and collets there

[keithf4]

Moving this to a discussion since it's not really an issue.

There is nothing built in to automatically do this. If you don't want to keep any data outside of the existing children, you can simply drop the default table. This will result in errors being raised when data that doesn't fit in a child being inserted.

If you want to keep the data and move it out to the relevant child, you could use something like pg_cron to automatically call the partitioning functions on a regular interval to keep it empty. I'd be cautious with this, though, in case there is A LOT of data that gets inserted to the default. That could cause some unexpected load on data that you may not even want to keep.

The other thing you can do is monitor for there to be any data in the defaults of any child tables managed by pg_partman using the check_default() function. Then when you see an alert go off, you can investigate and act on it then.

[ivandedkov]

Thanks for attention!
My idea is avoiding insert errors and dont write additional handle logic in my app. I would like to set up config that would allow to truncate default partition with using run_maintenance function

Yes, I aware that I can add truncate command close to my run_maintenance call, but it looks like good feature for extension

[keithf4]

This is a rather uncommon operation and I'd rather not add this sort of logic into partman. It's probably better to handle at the application level even though that's not what you want to do right now. Either by better validating the data that's getting inserted or catching the expected exception that is returned when data is inserted outside the existing children with no default.

Or again you could schedule a pg_cron job that runs on a regular basis to truncate the default. Just be careful since I'm not 100% of the lock that that operation would do on the partition set as a whole.

[ivandedkov]

I got it. Okay, thank you!