Here's an updated version of this patch.
Apart from rebasing to current master, I made the following changes:
* On the first transaction (the one that marks the partition as
detached), the partition is locked with ShareLock rather than
ShareUpdateExclusiveLock. This means that DML is not allowed anymore.
This seems a reasonable restriction to me; surely by the time you're
detaching the partition you're not inserting data into it anymore.
* In ExecInitPartitionDispatchInfo, the previous version always
excluded detached partitions. Now it does include them in isolation
level repeatable read and higher. Considering the point above, this
sounds a bit contradictory: you shouldn't be inserting new tuples in
partitions being detached. But if you do, it makes more sense: in RR
two queries that insert tuples in the same partition would not fail
mid-transaction. (In a read-committed transaction, the second query
does fail, but to me that does not sound surprising.)
* ALTER TABLE .. DETACH PARTITION FINALIZE now waits for concurrent old
snapshots, as previously discussed. This should ensure that the user
doesn't just cancel the wait during the second transaction by Ctrl-C and
run FINALIZE immediately afterwards, which I claimed would bring
inconsistency.
* Avoid creating the CHECK constraint if an identical one already
exists.
(Note I do not remove the constraint on ATTACH. That seems pointless.)
Still to do: test this using the new hook added by 6f0b632f083b.