Re: Deadlock risk while inserting directly into partition?

On Mon, 28 Jun 2021 at 15:59, 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:
> > 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. I have planned
> to revisit this topic ("locking overhead in generic plans") for v15,
> now that we have *some* proposals mentioned in [1] committed to v14,
> so can look into this.

I thought about this only a little bit more from when I wrote the
above. I think it would require adding yet another stage of when we
do run-time pruning. It should be possible to do pruning when there's
GeneratePruningStepsContext.has_exec_param == true. However, I'm not
so sure that we could do GeneratePruningStepsContext.has_mutable_arg.
Evaluating the value for those requires some level of actual
execution. That's a pity as we'd still need to take a bunch of extra
locks in a case like: SELECT * FROM time_parted WHERE ts >= NOW() -
INTERVAL '1 hour';

I see the param values are fairly easily accessible a couple of levels
up from AcquireExecutorLocks() in GetCachedPlan().

> Anyway, do you agree with starting a thread to discuss possible
> approaches to attack this?

Agreed about the separate thread. We can discuss it further there.

David

> [1] https://www.postgresql.org/message-id/CA+HiwqG7ZruBmmih3wPsBZ4s0H2EhywrnXEduckY5Hr3fWzPWA@mail.gmail.com