Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

On 2020-Nov-30, Justin Pryzby wrote:

> On Tue, Nov 03, 2020 at 08:56:06PM -0300, Alvaro Herrera wrote:

> > * On the first transaction (the one that marks the partition as
> > detached), the partition is locked with ShareLock rather than
> > ShareUpdateExclusiveLock. This means that DML is not allowed anymore.
> > This seems a reasonable restriction to me; surely by the time you're
> > detaching the partition you're not inserting data into it anymore.
>
> I don't think it's an issue with your patch, but FYI that sounds like something
> I had to do recently: detach *all* partitions of various tabls to promote their
> partition key column from timestamp to timestamptz. And we insert directly
> into child tables, not routed via parent.
>
> I don't your patch is still useful, but not to us. So the documentation should
> be clear about that.

FWIW since you mentioned this detail specifically: I backed away from
doing this (and use ShareUpdateExclusive), because it wasn't buying us
anything anyway. The reason for it is that I wanted to close the hole
for RI queries, and this seemed the simplest fix; but it really *wasn't*
a fix anyway. My later games with the active snapshot (which are
present in the version I pushed) better close this problem. So I don't
think this would be a problem.

--
Álvaro Herrera Valdivia, Chile