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-25T15:50:39Z
Lists: pgsql-hackers

Attachments

On 2021-Mar-23, Alvaro Herrera wrote:

> I think a possible solution to this problem is that the "detach" flag in
> pg_inherits is not a boolean anymore, but an Xid (or maybe two Xids).
> Not sure exactly which Xid(s) yet, and I'm not sure what are the exact
> rules, but the Xid becomes a marker that indicates an horizon past which
> the partition is no longer visible.  Then, REPEATABLE READ can see the
> partition, but only if its snapshot is older than the Xid.

So a solution to this problem seems similar (but not quite the same) as
pg_index.indcheckxmin: the partition is included in the partition
directory, or not, depending on the pg_inherits tuple visibility for the
active snapshot.  This solves the problem because the RI query uses a
fresh snapshot, for which the partition has already been detached, while
the normal REPEATABLE READ query is using the old snapshot for which the
'detach-pending' row is still seen as in progress.  With this, the weird
hack in a couple of places that needed to check the isolation level is
gone, which makes me a bit more comfortable.

So attached is v9 with this problem solved.

I'll add one more torture test, and if it works correctly I'll push it:
have a cursor in the repeatable read transaction, which can read the
referenced partition table and see the row in the detached partition,
but the RI query must not see that row.  Bonus: the RI query is run from
another cursor that is doing UPDATE WHERE CURRENT OF that cursor.

-- 
Álvaro Herrera                            39°49'30"S 73°17'W
"Estoy de acuerdo contigo en que la verdad absoluta no existe...
El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama)

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