From: | Dmitry Koval <d(dot)koval(at)postgrespro(dot)ru> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Add SPLIT PARTITION/MERGE PARTITIONS commands |
Date: | 2022-09-19 20:42:38 |
Message-ID: | 277c8df2-ce99-36b6-817a-ed510c7e8b82@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Thanks for comments and advice!
I thought about this problem and discussed about it with colleagues.
Unfortunately, I don't know of a good general solution.
19.09.2022 22:56, Robert Haas пишет:
> If you know that a certain partition is not changing, and you would
> like to split it, you can create two or more new standalone tables and
> populate them from the original partition using INSERT .. SELECT. Then
> you can BEGIN a transaction, DETACH the existing partitions, and
> ATTACH the replacement ones. By doing this, you take an ACCESS
> EXCLUSIVE lock on the partitioned table only for a brief period. The
> same kind of idea can be used to merge partitions.
But for specific situation like this (certain partition is not changing)
we can add CONCURRENTLY modifier.
Our DDL query can be like
ALTER TABLE...SPLIT PARTITION [CONCURRENTLY];
With CONCURRENTLY modifier we can lock partitioned table in
ShareUpdateExclusiveLock mode and split partition - in
AccessExclusiveLock mode. So we don't lock partitioned table in
AccessExclusiveLock mode and can modify other partitions during SPLIT
operation (except split partition).
If smb try to modify split partition, he will receive error "relation
does not exist" at end of operation (because split partition will be drop).
--
With best regards,
Dmitry Koval
Postgres Professional: http://postgrespro.com
From | Date | Subject | |
---|---|---|---|
Next Message | Jonathan S. Katz | 2022-09-19 20:52:06 | Re: why can't a table be part of the same publication as its schema |
Previous Message | David Rowley | 2022-09-19 20:38:14 | Re: Fix typos in code comments |