Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Amit Langote <amitlangote09(at)gmail(dot)com>
Cc: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY
Date: 2020-11-03 23:56:06
Message-ID: 20201103235606.GA17495@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Attachment Content-Type Size
v4-0001-ALTER-TABLE-.-DETACH-CONCURRENTLY.patch text/x-diff 90.1 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2020-11-04 00:22:14 Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
Previous Message Christoph Berg 2020-11-03 22:14:42 Re: Collation versioning