Horton

Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Amit Langote <amitlangote09@gmail.com>, pgsql-hackers@lists.postgresql.org
Date: 2021-01-08T19:14:33Z
Lists: pgsql-hackers

Attachments

On 2020-Dec-01, Alvaro Herrera wrote:

> On 2020-Nov-30, Justin Pryzby wrote:

> Thanks for all the comments. I'll incorporate everything and submit an
> updated version later.

Here's a rebased version 5, with the typos fixed.  More comments below.

> > The attname "detached" is a stretch of what's intuitive (it's more like
> > "detachING" or half-detached).  But I think psql should for sure show something
> > more obvious to users.  Esp. seeing as psql output isn't documented.  Let's
> > figure out what to show to users and then maybe rename the column that, too.
> 
> OK.  I agree that "being detached" is the state we want users to see, or
> maybe "detach pending", or "unfinisheddetach" (ugh).  I'm not sure that
> pg_inherits.inhbeingdetached" is a great column name.  Opinions welcome.

I haven't changed this yet; I can't make up my mind about what I like
best.

Partition of: parent FOR VALUES IN (1) UNFINISHED DETACH
Partition of: parent FOR VALUES IN (1) UNDER DETACH
Partition of: parent FOR VALUES IN (1) BEING DETACHED

> > ATExecDetachPartition:
> > Doesn't this need to lock the table before testing for default partition ?
> 
> Correct, it does.

I failed to point out that by the time ATExecDetachPartition is called,
the relation has already been locked by the invoking ALTER TABLE support
code.

> > I ended up with apparently broken constraint when running multiple loops around
> > a concurrent detach / attach:
> > 
> > while psql -h /tmp postgres -c "ALTER TABLE p ATTACH PARTITION p1 FOR VALUES FROM (1)TO(2)" -c "ALTER TABLE p DETACH PARTITION p1 CONCURRENTLY"; do :; done&
> > while psql -h /tmp postgres -c "ALTER TABLE p ATTACH PARTITION p1 FOR VALUES FROM (1)TO(2)" -c "ALTER TABLE p DETACH PARTITION p1 CONCURRENTLY"; do :; done&
> > 
> >     "p1_check" CHECK (true)
> >     "p1_i_check" CHECK (i IS NOT NULL AND i >= 1 AND i < 2)
> 
> Not good.

Haven't had time to investigate this problem yet.

-- 
Álvaro Herrera

Commits

  1. Make detach-partition-concurrently-4 less timing sensitive

  2. Improve documentation on DETACH PARTITION lock levels

  3. Track detached partitions more accurately in partdescs

  4. Allow a partdesc-omitting-partitions to be cached

  5. Fix relcache inconsistency hazard in partition detach

  6. Don't add a redundant constraint when detaching a partition

  7. ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY

  8. Let ALTER TABLE Phase 2 routines manage the relation pointer

  9. Check default partitions constraints while descending