Re: Deadlock risk while inserting directly into partition? - Mailing list pgsql-hackers

From David Rowley
Subject Re: Deadlock risk while inserting directly into partition?
Date
Msg-id CAApHDvoSnkwhDdL20nPHUK26MECozyKk=2xYfSMS0CdNwTmE9g@mail.gmail.com
Whole thread Raw
In response to Re: Deadlock risk while inserting directly into partition?  (David Rowley <dgrowleyml@gmail.com>)
Responses Re: Deadlock risk while inserting directly into partition?
List pgsql-hackers
On Thu, 24 Jun 2021 at 12:32, David Rowley <dgrowleyml@gmail.com> wrote:
> The overhead of taking these locks is pretty significant for
> partitioned tables with lots of partitions where only 1 of them
> survives run-time partition pruning.  That's really terrible for
> people that want to PREPARE queries and just look up a single row from
> a single partition.  That seems like a pretty big use case that we're
> just terrible at today.

I wonder, since we can't delay taking locks until after run-time
pruning due to being unable to invalidate cached plans, maybe instead
we could tag on any PartitionPruneInfo onto the PlannedStmt itself and
do the init plan run-time prune run during AcquireExecutorLocks().

A lock would need to be taken on each partitioned table before we
prune for it. So if there was multi-level partitioning, we'd need to
lock the partitioned table, do pruning for that partitioned table,
then lock any sub-partitioned tables before doing pruning on those.

I don't immediately see why it couldn't be made to work, it's just
that it adds quite a lot of complexity to what's being done in
AcquireExecutorLocks(), which today is a very simple function.

David



pgsql-hackers by date:

Previous
From: Masahiro Ikeda
Date:
Subject: Re: Transactions involving multiple postgres foreign servers, take 2
Next
From: Masahiro Ikeda
Date:
Subject: Re: Transactions involving multiple postgres foreign servers, take 2