Re: Delay locking partitions during INSERT and UPDATE

On Wed, Feb 20, 2019 at 11:03 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Right, that's the same thing I was trying to say.

OK, thanks.

> > ... So my question is - what do
> > you mean by the parenthetical note about the partitioning info not
> > changing? Regardless of whether it does or does not, I think the same
> > property holds.
>
> What I was wondering about was the possibility of the set of
> tables-that-need-to-be-locked-at-all changing. Maybe that won't
> create an issue either, but I'm not quite sure.

That's pretty much what I was thinking, too. I think it might be fair
to say, however, that if it does give rise to deadlock situations,
they will be corner cases. For instance, suppose you lock are busy
locking top-down and, meanwhile, somebody detaches a partition you
haven't gotten around to locking yet and tries to attach it someplace
higher up in the partition hierarchy. I think that there's a
more-or-less unavoidable deadlock there. And there may be other cases
where it is practically avoidable but we will fail to avoid it. But I
don't think it's such a common scenario that we have two concurrent
DDL commands on the same partitioning hierarchy that we should stress
about it too much. If the common cases work OK, a theoretical
deadlock risk in some more obscure case seems acceptable to me, if it
means we get a significant performance boost.

> I do have to say
> that I find the idea of somebody changing the partitioning data
> concurrently with queries on that partitioned table to be stone
> cold petrifying. I don't think I believe you can make that work.

I guess you won't be surprised to hear that I believe otherwise, but I
agree it's a pretty thorny issue. I would welcome your comments on
the patches and issues discussed on the ATTACH/DETACH CONCURRENTLY
thread. To get things to (apparently) work, I have had to separately
handle: (1) the case where things change as we are building the
PartitionDesc, (2) the case where the same PartitionDesc changes
during a planning cycle, (3) the case where the PartitionDesc changes
between planning and execution, and (4) the case where the
PartitionDesc changes during execution. And even then I'm only able
so far to handle cases where a partition is added, just by ignoring
it, not cases where a partition is removed. But it does seem to work.
There isn't that much code yet that depends on the PartitionDesc not
changing, so it seems to be an easier problem than, say, allowing a
column to be added concurrently. As David says, we probably shouldn't
hijack this thread to talk about that in detail, but I would
definitely like it if you would comment on what I've done over there.
I think it's good work but it is very easy to be wrong about such
things -- and even if the basic approach is sound, there may be more
cases of which I have not thought.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company