Re: speedup COPY TO for partitioned table. - Mailing list pgsql-hackers

On Mon, Jul 14, 2025 at 10:02 PM Álvaro Herrera <alvherre@kurilemu.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
partitionedtable \"%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?