Horton

Re: Add SPLIT PARTITION/MERGE PARTITIONS commands

Alexander Korotkov <aekorotkov@gmail.com>

View thread
From: Alexander Korotkov <aekorotkov@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Dmitry Koval <d.koval@postgrespro.ru>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2024-03-25T10:28:36Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Adjust errcode in checkPartition()

  2. Fix usage of palloc() in MERGE/SPLIT PARTITION(s) code

  3. Implement ALTER TABLE ... SPLIT PARTITION ... command

  4. Implement ALTER TABLE ... MERGE PARTITIONS ... command

  5. Calculate agglevelsup correctly when Aggref contains a CTE.

  6. Use PqMsg_* macros in applyparallelworker.c.

  7. Restrict psql meta-commands in plain-text dumps.

  8. Ensure we have a snapshot when updating various system catalogs.

  9. Use specific collation where needed in new test

  10. Expand virtual generated columns in the planner

  11. Virtual generated columns

  12. Define PG_LOGICAL_DIR for path pg_logical/ in data folder

  13. Revert support for ALTER TABLE ... MERGE/SPLIT PARTITION(S) commands

  14. Avoid repeated table name lookups in createPartitionTable()

  15. Provide deterministic order for catalog queries in partition_split.sql

  16. Don't copy extended statistics during MERGE/SPLIT partition operations

  17. Fix the name collision detection in MERGE/SPLIT partition operations

  18. Fix regression tests conflict in 3ca43dbbb6

  19. Add permission check for MERGE/SPLIT partition operations

  20. Fix one more portability shortcoming in new test_pg_dump test.

  21. Inherit parent's AM for partition MERGE/SPLIT operations

  22. Add tab completion for partition MERGE/SPLIT operations

  23. Rename tables in tests of partition MERGE/SPLIT operations

  24. Make new partitions with parent's persistence during MERGE/SPLIT

  25. Document the way partition MERGE/SPLIT operations create new partitions

  26. Change the way ATExecMergePartitions() handles the name collision

  27. Grammar fixes for split/merge partitions code

  28. Checks for ALTER TABLE ... SPLIT/MERGE PARTITIONS ... commands

  29. Fix some grammer errors from error messages and codes comments

  30. Support TZ and OF format codes in to_timestamp().

  31. Support identity columns in partitioned tables

  32. Fix indentation in twophase.c

  33. Fix corner-case planner failure for MERGE.

  34. Doc: fix documentation example for bytea hex output format.

  35. Avoid repeated name lookups during table and index DDL.

On Tue, Sep 20, 2022 at 3:21 PM Robert Haas <robertmhaas@gmail.com> wrote:
> On Mon, Sep 19, 2022 at 4:42 PM Dmitry Koval <d.koval@postgrespro.ru> wrote:
> > 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.
>
> Yeah, me neither.
>
> > 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).
>
> I think that a built-in DDL command can't really assume that the user
> won't modify anything. You'd have to take a ShareLock.
>
> But you might be able to have a CONCURRENTLY variant of the command
> that does the same kind of multi-transaction thing as, e.g., CREATE
> INDEX CONCURRENTLY. You would probably have to be quite careful about
> race conditions (e.g. you commit the first transaction and before you
> start the second one, someone drops or detaches the partition you were
> planning to merge or split). Might take some thought, but feels
> possibly doable. I've never been excited enough about this kind of
> thing to want to put a lot of energy into engineering it, because
> doing it "manually" feels so much nicer to me, and doubly so given
> that we now have ATTACH CONCURRENTLY and DETACH CONCURRENTLY, but it
> does seem like a thing some people would probably use and value.

+1
Currently people are using external tools to implement this kind of
task.  However, having this functionality in core would be great.
Implementing concurrent merge/split seems quite a difficult task,
which needs careful design.  It might be too hard to carry around the
syntax altogether.  So, I think having basic syntax in-core is a good
step forward.  But I think we need a clear notice in the documentation
about the concurrency to avoid wrong user expectations.

------
Regards,
Alexander Korotkov