Re: speedup COPY TO for partitioned table.

On Mon, Jul 14, 2025 at 10:02 PM Álvaro Herrera <alvherre(at)kurilemu(dot)de> wrote:
>
> On 2025-Jul-02, jian he wrote:
>
> > @@ -673,11 +680,34 @@ 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 == RELKIND_FOREIGN_TABLE)
> > + {
> > + char *relation_name;
> > +
> > + relation_name = get_rel_name(childreloid);
> > + ereport(ERROR,
> > + errcode(ERRCODE_WRONG_OBJECT_TYPE),
> > + errmsg("cannot copy from foreign table \"%s\"", relation_name),
> > + errdetail("Partition \"%s\" is a foreign table in the partitioned table \"%s\"",
> > + relation_name, RelationGetRelationName(rel)),
> > + errhint("Try the COPY (SELECT ...) TO variant."));
> > + }
>
> This code looks like it's duplicating what you could obtain by using
> RelationGetPartitionDesc and then observe the ->isleaf bits. Maybe have
> a look at the function RelationHasForeignPartition() in the patch at
> https://postgr.es/m/CANhcyEW_s2LD6RiDSMHtWQnpYB67EWXqf7N8mn7dOrnaKMfROg@mail.gmail.com
> which looks very similar to what you need here. I think that would also
> have the (maybe dubious) advantage that the rows will be output in
> partition bound order rather than breadth-first (partition hierarchy)
> OID order.
>
hi.

else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
{
PartitionDesc pd = RelationGetPartitionDesc(rel, true);
for (int i = 0; i < pd->nparts; i++)
{
Relation partRel;
if (!pd->is_leaf[i])
continue;
partRel = table_open(pd->oids[i], AccessShareLock);
if (partRel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
ereport(ERROR,
errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("cannot copy from foreign table
\"%s\"", RelationGetRelationName(partRel)),
errdetail("Partition \"%s\" is a foreign
table in the partitioned table \"%s\"",

RelationGetRelationName(partRel), RelationGetRelationName(rel)),
errhint("Try the COPY (SELECT ...) TO
variant."));
table_close(partRel, NoLock);
scan_oids = lappend_oid(scan_oids, RelationGetRelid(partRel));
}
}

I tried the above code, but it doesn't work because RelationGetPartitionDesc
only retrieves the immediate partition descriptor of a partitioned relation, it
doesn't recurse to the lowest level.

Actually Melih Mutlu raised this question at
https://postgr.es/m/CAGPVpCQou3hWQYUqXNTLKdcuO6envsWJYSJqbZZQnRCjZA6nkQ%40mail.gmail.com
I kind of ignored it...
I guess we have to stick with find_all_inheritors here?