-1

I have a partitioned table named logs with a lot of INSERT transactions.

Every day I run a cron job with a query for CREATE PARTITION on this logs table.

But it seems INSERT statements block the partition creation, so I have a message like:

DETAIL: Process 10343 waits for AccessExclusiveLock on relation 184661 of database 14007; blocked by process 25424.
Process 25424 waits for RowExclusiveLock on relation 184654 of database 14007; blocked by process 10343.
deadlock detected 

It makes sense to put a lock on INSERT but why does this lock block the partition creation? Is there any workaround?

  • There is no CREATE PARTITION command in PostgreSQL. Can you share what exactly you are doing? Are there multiple statements running in one transaction? – Laurenz Albe Jun 25 at 6:15
  • @LaurenzAlbe, what I mean is smth like CREATE TABLE "logs_01-01-2019" PARTITION OF logs FOR VALUES FROM ... TO ... There are several running transcations, but they are in the idle state most of the time. I've applied the solution from SQLRaptor and rechecked today. Seems like it solves the problem. Thank you. – Ximik Jun 26 at 4:18
0

Instead of splitting your currently active partition to accommodate the latest inserts for tomorrow, create at least one empty partition 'in advance', so that every time you need to create a new one it doesn't interfere with your regular workload.

For example: Today is 6/24/19, and you should have a partition for today where your rows will be stored, and an empty one for 6/25/19. Before midnight, when rows will start going into the 6/25/19 partition, create a new one for 6/26/19, which will split the empty 6/25/19 partition, avoiding any interference with your inserts, or any other DML.

HTH

  • That's how it's done. The problem is when i try to create 6/26/19 partition on 6/25/19 11pm I have this lock issue. – Ximik Jun 25 at 0:04
  • The "RowExclusiveLock" suggests that row-level locks are taking place. Can you verify the partition is indeed empty when you attempt the split? Perhaps you have rogue "future date" rows in there that should be cleaned up? – SQLRaptor Jun 25 at 0:23
  • 1
    Just to clarify and make sure we are on the same page - you say " i try to create 6/26/19 partition on 6/25/19 11pm" - that's not what I meant... On 6/25/19 at 11:00 PM you should be creating the 6/27/19 partition which will guarantee that the 6/26/19 which is being split is empty... – SQLRaptor Jun 25 at 0:27
  • Thanks, got it. However we have the same issue with table partitioned by value, not by range (for each tenant there is one partition). So I don't think that's the root of the issue. – Ximik Jun 25 at 1:10
  • I've applied your solution and was monitoring database today. Seems like everything goes great now. Thank you! – Ximik Jun 26 at 4:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.