hi. rebased and polished patch attached, test case added. However there is a case (the following) where ``COPY(partitioned_table)`` is much slower (around 25% in some cases) than ``COPY (select * from partitioned_table)``.
If the partition attribute order is not the same as the partitioned table, then for each output row, we need to create a template TupleTableSlot and call execute_attr_map_slot, i didn't find a work around to reduce the inefficiency. Since the master doesn't have ``COPY(partitioned_table)``, I guess this slowness case is allowed? ----------- the follow case is far slower than ``COPY(select * From pp) TO `` drop table if exists pp; CREATE TABLE pp (id INT, val int ) PARTITION BY RANGE (id); create table pp_1 (val int, id int); create table pp_2 (val int, id int); ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5); ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10); insert into pp select g, 10 + g from generate_series(1,9) g; copy pp to stdout(header);
From eaf3869c4fb5fdacba5efd562f73ca06a0251ac4 Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> Date: Fri, 7 Mar 2025 18:39:56 +0800 Subject: [PATCH v2 1/1] support "COPY partitioned_table TO" drop table if exists pp; CREATE TABLE pp (id INT, val int ) PARTITION BY RANGE (id); create table pp_1 (val int, id int); create table pp_2 (val int, id int); ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5); ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10); insert into pp select g, 10 + g from generate_series(1,9) g; copy pp to stdout(header); the above case is much slower (around 25% some case) than ``COPY (select * from pp) to stdout(header);`` but this is still a new feature, since master does not support ``COPY (partitioned_table)``. discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=i...@mail.gmail.com --- src/backend/commands/copyto.c | 80 ++++++++++++++++++++++++++--- src/test/regress/expected/copy2.out | 16 ++++++ src/test/regress/sql/copy2.sql | 11 ++++ 3 files changed, 101 insertions(+), 6 deletions(-) diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c index 84a3f3879a8..966b6741530 100644 --- a/src/backend/commands/copyto.c +++ b/src/backend/commands/copyto.c @@ -19,6 +19,8 @@ #include <sys/stat.h> #include "access/tableam.h" +#include "access/table.h" +#include "catalog/pg_inherits.h" #include "commands/copyapi.h" #include "commands/progress.h" #include "executor/execdesc.h" @@ -82,6 +84,7 @@ typedef struct CopyToStateData List *attnumlist; /* integer list of attnums to copy */ char *filename; /* filename, or NULL for STDOUT */ bool is_program; /* is 'filename' a program to popen? */ + List *partitions; /* oid list of partition oid for copy to */ copy_data_dest_cb data_dest_cb; /* function for writing data */ CopyFormatOptions opts; @@ -643,6 +646,8 @@ BeginCopyTo(ParseState *pstate, PROGRESS_COPY_COMMAND_TO, 0 }; + List *children = NIL; + List *scan_oids = NIL; if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION) { @@ -670,11 +675,19 @@ BeginCopyTo(ParseState *pstate, errmsg("cannot copy from sequence \"%s\"", RelationGetRelationName(rel)))); else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) - ereport(ERROR, - (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("cannot copy from partitioned table \"%s\"", - RelationGetRelationName(rel)), - errhint("Try the COPY (SELECT ...) TO variant."))); + { + children = find_all_inheritors(RelationGetRelid(rel), + AccessShareLock, + NULL); + foreach_oid(childreloid, children) + { + char relkind = get_rel_relkind(childreloid); + if (RELKIND_HAS_PARTITIONS(relkind)) + continue; + + scan_oids = lappend_oid(scan_oids, childreloid); + } + } else ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), @@ -710,6 +723,7 @@ BeginCopyTo(ParseState *pstate, cstate->rel = rel; tupDesc = RelationGetDescr(cstate->rel); + cstate->partitions = list_copy(scan_oids); } else { @@ -1066,7 +1080,61 @@ DoCopyTo(CopyToState cstate) cstate->routine->CopyToStart(cstate, tupDesc); - if (cstate->rel) + /* + * if COPY TO source table is a partitioned table, then open each + * partition and process each individual partition. + */ + if (cstate->rel && cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + { + processed = 0; + + foreach_oid(scan_oid, cstate->partitions) + { + TupleTableSlot *slot; + TableScanDesc scandesc; + Relation scan_rel; + TupleDesc scan_tupdesc; + AttrMap *map; + TupleTableSlot *root_slot = NULL; + TupleTableSlot *original_slot = NULL; + + scan_rel = table_open(scan_oid, AccessShareLock); + scan_tupdesc = RelationGetDescr(scan_rel); + map = build_attrmap_by_name_if_req(tupDesc, scan_tupdesc, false); + + scandesc = table_beginscan(scan_rel, GetActiveSnapshot(), 0, NULL); + slot = table_slot_create(scan_rel, NULL); + + while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot)) + { + CHECK_FOR_INTERRUPTS(); + + /* Deconstruct the tuple ... */ + if (map != NULL) + { + original_slot = slot; + root_slot = MakeSingleTupleTableSlot(tupDesc, &TTSOpsBufferHeapTuple); + slot = execute_attr_map_slot(map, slot, root_slot); + } + else + slot_getallattrs(slot); + + /* Format and send the data */ + CopyOneRowTo(cstate, slot); + + pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED, + ++processed); + + if (original_slot != NULL) + ExecDropSingleTupleTableSlot(original_slot); + }; + + ExecDropSingleTupleTableSlot(slot); + table_endscan(scandesc); + table_close(scan_rel, AccessShareLock); + } + } + else if (cstate->rel) { TupleTableSlot *slot; TableScanDesc scandesc; diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out index 64ea33aeae8..dcd97ae45b7 100644 --- a/src/test/regress/expected/copy2.out +++ b/src/test/regress/expected/copy2.out @@ -929,3 +929,19 @@ truncate copy_default; -- DEFAULT cannot be used in COPY TO copy (select 1 as test) TO stdout with (default '\D'); ERROR: COPY DEFAULT cannot be used with COPY TO +-- COPY TO with partitioned table +CREATE TABLE pp (id INT, val int ) PARTITION BY RANGE (id); +create table pp_1 (val int, id int); +create table pp_2 (val int, id int); +ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (3); +ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (3) TO (7); +insert into pp select g, 10 + g from generate_series(1,6) g; +copy pp to stdout(header); +id val +1 11 +2 12 +3 13 +4 14 +5 15 +6 16 +DROP TABLE PP; diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql index 45273557ce0..56d7c1ffc8f 100644 --- a/src/test/regress/sql/copy2.sql +++ b/src/test/regress/sql/copy2.sql @@ -707,3 +707,14 @@ truncate copy_default; -- DEFAULT cannot be used in COPY TO copy (select 1 as test) TO stdout with (default '\D'); + +-- COPY TO with partitioned table +CREATE TABLE pp (id INT, val int ) PARTITION BY RANGE (id); +create table pp_1 (val int, id int); +create table pp_2 (val int, id int); +ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (3); +ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (3) TO (7); +insert into pp select g, 10 + g from generate_series(1,6) g; +copy pp to stdout(header); + +DROP TABLE PP; \ No newline at end of file -- 2.34.1
