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-03-19T13:57:37Z
Lists: pgsql-hackers

Attachments

On 2021-Mar-17, Justin Pryzby wrote:

> The v8 patch has the "broken constraint" problem.

Yeah, I had misunderstood what the problem was.  I think a good solution
to this is to have get_partition_parent return the true parent even when
a detach is pending, for one particular callsite.  (This means adjusting
all other callsites.)  Notpatch attached (applies on top of v8).

> Also, it "fails to avoid" adding duplicate constraints:
> 
> Check constraints:
>     "c" CHECK (i IS NOT NULL AND i > 1 AND i < 2)
>     "cc" CHECK (i IS NOT NULL AND i >= 1 AND i < 2)
>     "p1_check" CHECK (true)
>     "p1_i_check" CHECK (i IS NOT NULL AND i >= 1 AND i < 2)

Do you mean the "cc" and "p1_i_check" one?  I mean, if you already have
a constraint in the partition that duplicates the partition constraint,
then during attach we still create our new constraint?  I guess a
solution to this would be to scan all constraints and see if any equals
the expression that the new one would have.  Sounds easy enough now that
write it out loud.

Thanks

-- 
Álvaro Herrera                            39°49'30"S 73°17'W
"Java is clearly an example of money oriented programming"  (A. Stepanov)

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