Re: Deadlock risk while inserting directly into partition?

On Mon, Jun 28, 2021 at 12:58 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> On Fri, Jun 25, 2021 at 10:26 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> > On Thu, 24 Jun 2021 at 12:32, David Rowley <dgrowleyml(at)gmail(dot)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().
>
> This is exactly what I was mulling doing when working on [1] some last
> year, after an off-list discussion with Robert (he suggested the idea
> IIRC), though I never quite finished writing a patch.

Ah, I *had* mentioned this bit in the first email of [1]:

"Another solution suggested to me by Robert Haas in an off-list
discussion is to teach AcquireExecutorLocks() or the nearby code to
perform EXTERN parameter based pruning before passing the plan tree to
the executor and lock partitions that survive that pruning. It's
perhaps doable if we refactor the ExecFindInitialMatchingSubPlans() to
not require a full-blown execution context. Or maybe we could do
something more invasive by rewriting AcquireExecutorLocks() to walk
the plan tree instead of the flat range table, looking for scan nodes
and nodes that support runtime pruning to lock the appropriate
relations."

Alas, I hadn't written down any concrete proposals as to how that
could be done. :(

--
Amit Langote
EDB: http://www.enterprisedb.com