Add retention period configuration

[v0idpwn]

For SQS feature parity. Messages too old should be automatically deleted.

[caffeinatedgaze]

Is this currently implemented (e.g. pg_partman takes care of it) or library users have to sort it out on their own? This also looks like something that should be on the README. 🤔

I could probably contribute with your help.

[caffeinatedgaze]

Ah, it is already there. One has to create the table as partitioned from the very beginning. I wish I knew this earlier.

For everybody else, see this: https://github.com/tembo-io/pgmq?tab=readme-ov-file#partitioned-queues

[ChuckHend]

We don't have an API for it, but a queue should be able to change from regular to a partitioned queue through manual steps. All of the read/write/delete operations are compatible with both.

That would be a really cool contribution.

[caffeinatedgaze]

I am thinking of doing this in two steps:

1. Create a new table partitioned by key.
2. Move data from the old table to the new one that happens to be inside the retention interval.

The downside is that any operations on the queue must be paused.

I am really new to partition management. Please, share your thoughts 😄 @ChuckHend

[ChuckHend]

pg_partman's background worker handles the partition management, such as creating new partitions and dropping old partitions.

The pgmq pgmq_create_partitioned() api handles configuring pg_partman for the queue. retention_interval can be either the number of messages to retain, or the number of days of messages to retain. Anything beyond that is retention_interval is dropped by pg_partman. Our docs could be more clear about this for sure.

pgmq/src/api.rs

Lines 126 to 130 in e00e716

	fn pgmq_create_partitioned(
	queue_name: &str,
	partition_interval: default!(String, "'10000'"),
	retention_interval: default!(String, "'100000'"),
	) -> Result<(), PgmqExtError> {

What I was thinking is that we could create some new function: alter_queue() and this would handle taking a partitioned queue, and turning it into non-partitioned. Or non-partitioned into a partitioned queue.

In case of non-partitioned -> partitioned, we'd probably need it to execute the migration_to_partman outlined in pg_partman's 4.7.1 docs