Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Justin Pryzby <pryzby(at)telsasoft(dot)com>
Cc: Amit Langote <amitlangote09(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY
Date: 2021-03-25 15:50:39
Message-ID: 20210325155039.GA25332@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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)

Attachment Content-Type Size
v9-0001-Let-ALTER-TABLE-exec-routines-deal-with-the-relat.patch text/x-diff 3.6 KB
v9-0002-ALTER-TABLE-.-DETACH-CONCURRENTLY.patch text/x-diff 121.9 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jacob Champion 2021-03-25 15:54:10 Re: DETAIL for wrong scram password
Previous Message torikoshia 2021-03-25 15:33:08 Re: Is it useful to record whether plans are generic or custom?