From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Amit Langote <amitlangote09@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY
Date: Tue, 23 Mar 2021 11:55:54 -0300
Message-ID: <20210323145554.GA7700@alvherre.pgsql> (raw)
In-Reply-To: <20210323141826.GA26281@alvherre.pgsql>
On 2021-Mar-23, Alvaro Herrera wrote:
> So I was about ready to get these patches pushed, when I noticed that in
> REPEATABLE READ isolation mode it is possible to insert rows violating
> an FK referencing the partition that is being detached. I'm not sure
> what is a good solution to this problem.
...
> I had the idea that the RI code, in REPEATABLE READ mode, used a
> different snapshot for the RI queries than the transaction snapshot.
I am definitely right about this. So why doesn't it work? The reason
is that when SPI goes to execute the query, it obtains a new partition
directory, and we tell it to include detached partitions precisely
because we're in REPEATABLE READ mode.
In other words, the idea that we can blanket use the snapshot-isolation
condition to decide whether to include detached partitions or not, is
bogus and needs at least the refinement that for any query that comes
from the RI system, we need a partition directory that does not include
detached partitions.
--
Álvaro Herrera 39°49'30"S 73°17'W
"El sabio habla porque tiene algo que decir;
el tonto, porque tiene que decir algo" (Platon).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@alvh.no-ip.org, pryzby@telsasoft.com, amitlangote09@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY
In-Reply-To: <20210323145554.GA7700@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox