-
|
We started seeing a Here is the exact error: When I try to attach a partition manually there seems to be no problems. Any help is much appreciated. |
Beta Was this translation helpful? Give feedback.
Replies: 2 comments 8 replies
-
|
Only thing within pg_partman I could think of that could cause this is calling the maintenance procedure more than once at the same time. Only other thing I can think of is to double-check that you don't have multiple cronjobs for maintenance scheduled. If that's not the case, are you able to get the full deadlock log out of the PG logs that shows what all the actual queries for each process were doing at the time of the deadlock? You may have to adjust your log_line_prefix to ensure it includes the PID so you can match things up. If you are seeing two different PIDs both calling maintenance at the same time, then something else is calling maintenance at the same time as your cron. |
Beta Was this translation helpful? Give feedback.
-
|
|
Beta Was this translation helpful? Give feedback.
-
|
Do you have any data in your default? |
Beta Was this translation helpful? Give feedback.
-
|
Cron job failure error: (The one I pasted above was from an adhoc run of the Looking at the logs, the deadlock is caused by the maintenance process
Similarly Classic deadlock! Also, we recently moved to a new schema using weekly partitions. We were previously using a daily partition strategy and we did not see these issues. We checked on cron job run details and did not find any failures on the daily partitioned DB. Again, really appreciate your help in this regard. 🙇 |
Beta Was this translation helpful? Give feedback.
-
|
Also, we don't have any data in the default. |
Beta Was this translation helpful? Give feedback.
-
|
partman itself isn't taking any specific locks during routine maintenance, it's the underlying declarative postgresql commands. The default table does have a constraint on it and if you look at it you'll notice it's basically an anti-constraint of all the currently existing children. When a new child table is added, that constraint needs to be updated. Also if there is data in the default, it has to scan all of that data to ensure the new child table's constraint doesn't match any rows (it fails if it does). Not quite sure why it needs such a heavy lock to do so, especially if the default is empty. Would have to pose that question to the general or hackers mailing list. My only suggestion for now would be to maybe try scheduling this one table's maintenance manually with the regular Or if you don't really need the default you could drop it. Any attempt to insert data outside the tables covered by the children would then just result in an error. Also, just an FYI for the upcoming 5.0 version, the weekly partition method changed a bit. Looking to have it out this fall, but then not sure how long it will take RDS to adopt it. https://github.com/pgpartman/pg_partman/blob/5.0.0-beta/doc/pg_partman_5.0.0_upgrade.md |
Beta Was this translation helpful? Give feedback.
-
Alright, we have set this up in our staging env. However, we have not seen these deadlocks in in our lower environments, lets see what happens. The scheduled job is set to run tonight. Also, is there a difference between what is being run by the procedure and the function
This is an interesting point. We ran the alter table DDL (see below) in our production environment to attach the partition manually, which also created the anti-constraints on the default table. The command execution was almost instanteneous. If this was an issue with the underlying
Having a default table is useful when we may have to support data inserts outside the partition ranges. This paractically does not |
Beta Was this translation helpful? Give feedback.
-
|
The procedure just calls the function in a loop over all the partition sets. But the procedure runs individual transactions for each partition set vs one giant transaction if you called just the regular function. So it's mostly just a different transaction state. One other thing I thought of that you're likely not doing when you make a child table manually is an analyze on the partition set. In the past, the analyze was enabled by default, but later versions of pg_partman disabled it by default (https://github.com/pgpartman/pg_partman/blob/master/sql/functions/run_maintenance.sql#L146). It doesn't look like you're enabling it from what I can see, but it's something else to check. The deadlock really just seems to be a timing thing. Do you have any timings on how long the procedure call takes to run? Is it taking longer than it did in the past? partman's manitenance is never going to be as quick as simply adding a new child table since there are more actions taken inside the function. But that's also what using the procedure vs the function to do maintenance is supposed to help with, only leaving a transaction open long enough for a single set. But, calling a single partition set directly via the function should be a little faster as well. |
Beta Was this translation helpful? Give feedback.
The procedure just calls the function in a loop over all the partition sets. But the procedure runs individual transactions for each partition set vs one giant transaction if you called just the regular function. So it's mostly just a different transaction state.
One other thing I thought of that you're likely not doing when you make a child table manually is an analyze on the partition set. In the past, the analyze was enabled by default, but later versions of pg_partman disabled it by default (https://github.com/pgpartman/pg_partman/blob/master/sql/functions/run_maintenance.sql#L146). It doesn't look like you're enabling it from what I can see, but it's something else to check.
The dead…