partition_data_proc killed automatically after ~30 mins without transferring any data.

[harshil007]

I have a table with >500 million rows that I have partitioned monthly using the online partitioning method specified in the docs. When I ran partition_data_proc the first time, it copied some data and created 3 child tables as they had less rows and then it was killed.

But now whenever I'm running it again, the proc call is just killed after ~20-30 mins without creating the new child table and transferring any data. Can I manually transfer data in batches? Any help would be appreciated

EDIT:
This is what I got in the exception message: invalid transaction termination

[keithf4]

You said you did the online partitioning method, I'm assuming you've added the original table a default. In this situation, data must be moved in a single transaction with the smallest amount of data being moved being the size of your interval. In this case, it must move however many rows are in a single month. It must lock those rows in order to move them since it has to move them from one table to another, so if anything is trying to use those rows, it could be holding up the transaction from completing.

Nothing in pg_partman should be killing the process from running. You may have some other parameters set in PostgreSQL that kill long running transactions. Or something else in your environment could be doing so as well (network configurations).

Also looking around at other possible causes of that error, are you trying to run the partition_data_proc call in your own explicit transaction? Or through some other client that may be trying to do an explicit transaction block of its own? Since this is a procedure that attempts to commit in its own loops, that will not work.

[harshil007]

I did run the proc in another explicit transaction block to check the error and that might explain the exception message. But even before that when running on its own, the proc just gets killed. I have checked my postgres parameters and there is no setting that can potentially impact this. If you know about any particular param that can be the cause then please let me know.

Also this was happening for the online method, I also tried the offline one after online didn't work. You can find more details here.
In summary, I ran the proc CALL partman.partition_data_proc('public.new_table', p_interval := '50000', p_source_table := 'public.old_table'); and it ran for ~15 hours and after that it was killed by postgres. Even after that, all of the data was not transferred from the source table.

[harshil007]

Hey @keithf4 , any comments on why I might be facing this issue?

Also, I have one other question, is it possible to do the data transfer without using the procedures provided by partman?
Do reply if you see this. Thanks.

[keithf4]

So someone did submit a patch to fix a possible issue, but only when you are using the lock-timout parameter for the partitioning function

#435

I am hoping to work on the next release in the near future, but I do not have an exact timeframe currently.

If you were not using the timeout feature, then this is not likely related to what is happening outside of what I had already said. The main issue I've seen people run into with the online method is currently in-use data causing long lock waits. There is nothing in pg_partman itself that would kill the process. That has to either be a timeout setting in PostgreSQL or something external to PostgreSQL killing the connection (application timeout, network timeout, etc).

Some you can look into (from the pg_settings table with default values):

                name                 | setting
-------------------------------------+---------
 authentication_timeout              | 60
 deadlock_timeout                    | 1000
 idle_in_transaction_session_timeout | 0
 lock_timeout                        | 0
 statement_timeout                   | 0

You can certainly move the data not using the pg_partman procedures if you can find one that works better for you. And if you do find one that works better and solves whatever issue was causing it to take so long, I would greatly appreciate sharing that if you didn't mind. The only thing to be aware of is that new child tables must be created with the proper naming pattern for pg_partman to continue recognizing them in the future.

[harshil007]

Thanks for the response.
I wasn't using the lock-timeout parameter for the partitioning function so I don't think. that was the issue.

The main issue I've seen people run into with the online method is currently in-use data causing long lock waits.

I was running this on a separate database which I set up just for testing partitions, so there wasn't any other queries apart from what I was doing for partitioning.

Some you can look into (from the pg_settings table with default values)

I looked into these settings, authentication_timeout and deadlock_timeout are set to default and the others are set to very high values, so that also can't be the issue cause as I said, the proc usually stopped after 20-30 mins.

I do have proc that I wrote to copy data to partitioned table that I used when I setup partitions manually without using the extension. Will try that out and share it if it works

Thanks again!