On Tue, Nov 6, 2018 at 2:10 PM Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > On 2018-Nov-06, Robert Haas wrote: > > If you don't throw an error when a partition is concurrently detached > > and then someone routes a tuple to that portion of the key space, what > > DO you do? Continue inserting tuples into the table even though it's > > no longer a partition? > > Yes -- the table was a partition when the query started, so it's still > a partition from the point of view of that query's snapshot.
I think it's important to point out that DDL does not in general respect the query snapshot. For example, you can query a table that was created by a transaction not visible to your query snapshot. You cannot query a table that was dropped by a transaction not visible to your query snapshot. If someone runs ALTER FUNCTION on a function your query uses, you get the latest committed version, not the version that was current at the time your query snapshot was created. So, if we go with the semantics you are proposing here, we will be making this DDL behave differently from pretty much all other DDL. Possibly that's OK in this case, but it's easy to think of other cases where it could cause problems. To take an example that I believe was discussed on-list a number of years ago, suppose that ADD CONSTRAINT worked according to the model that you are proposing for ATTACH PARTITION. If it did, then one transaction could be concurrently inserting a tuple while another transaction was adding a constraint which the tuple fails to satisfy. Once both transactions commit, you have a table with a supposedly-valid constraint and a tuple inside of it that doesn't satisfy that constraint. Obviously, that's no good. I'm not entirely sure whether there are any similar dangers in the case of DETACH PARTITION. I think it depends a lot on what can be done with that detached partition while the overlapping transaction is still active. For instance, suppose you attached it to the original table with a different set of partition bounds, or attached it to some other table with a different set of partition bounds. If you can do that, then I think it effectively creates the problem described in the previous paragraph with respect to the partition constraint. IOW, we've got to somehow prevent this: setup: partition is attached with bounds 1 to a million S1: COPY begins S2: partition is detached S2: partition is reattached with bounds 1 to a thousand S1: still-running copy inserts a tuple with value ten thousand -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
