Thread: Detach partition concurrently from pg cron

On Mon, Jul 8, 2024 at 5:43 AM Sathish Reddy <sathishreddy.postgresql@gmail.com> wrote:

Hi 

  Please help me on partman detach partition concurrently using pg cron run from postgres database to another database in detail.

Thanks 

Sathish Reddy 

Concurrent detach is not possible with pg_partman at this time. No concurrent operations can be called within a function's transaction block.

See this issue for more information - https://github.com/pgpartman/pg_partman/issues/387

If you need to detach concurrently, you will have to use some separate process to do it at this time. Not sure if that can be done with pg_cron since I'm not sure if that is also doing its own calls within a functional transaction. 

--

Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

Thanks for the advice.please help me on configure pg_cron schedule job trigger from postgres database to another database will run job

Thanks
Sathish Reddy

On Mon, Jul 8, 2024, 6:46 PM Keith Fiske <keith.fiske@crunchydata.com> wrote:

On Mon, Jul 8, 2024 at 5:43 AM Sathish Reddy <sathishreddy.postgresql@gmail.com> wrote:

Hi 

  Please help me on partman detach partition concurrently using pg cron run from postgres database to another database in detail.

Thanks 

Sathish Reddy 

Concurrent detach is not possible with pg_partman at this time. No concurrent operations can be called within a function's transaction block.

See this issue for more information - https://github.com/pgpartman/pg_partman/issues/387

If you need to detach concurrently, you will have to use some separate process to do it at this time. Not sure if that can be done with pg_cron since I'm not sure if that is also doing its own calls within a functional transaction. 

--

Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

We had a similar request and we have written a postgresql function to concurrently detach old partition in version 15, unfortunately we cannot use concurrently inside a postgresql function. We have to apply a logic and run individual detach commands for each partition via pg_cron.

On Tue, 9 Jul, 2024, 9:55 am Sathish Reddy, <sathishreddy.postgresql@gmail.com> wrote:

Thanks for the advice.please help me on configure pg_cron schedule job trigger from postgres database to another database will run job

Thanks
Sathish Reddy

On Mon, Jul 8, 2024, 6:46 PM Keith Fiske <keith.fiske@crunchydata.com> wrote:

On Mon, Jul 8, 2024 at 5:43 AM Sathish Reddy <sathishreddy.postgresql@gmail.com> wrote:

Hi 

  Please help me on partman detach partition concurrently using pg cron run from postgres database to another database in detail.

Thanks 

Sathish Reddy 

Concurrent detach is not possible with pg_partman at this time. No concurrent operations can be called within a function's transaction block.

See this issue for more information - https://github.com/pgpartman/pg_partman/issues/387

If you need to detach concurrently, you will have to use some separate process to do it at this time. Not sure if that can be done with pg_cron since I'm not sure if that is also doing its own calls within a functional transaction. 

--

Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com