Re: Data is copied twice when specifying both child and parent table in publication - Mailing list pgsql-hackers

On Wed, Feb 8, 2023 at 9:21 AM wangw.fnst@fujitsu.com
<wangw.fnst@fujitsu.com> wrote:
>
> I think this failure is caused by the recently commit (b7ae039) in the current
> HEAD. Rebased the patch set and attach them.
>

+ if (server_version >= 160000)
+ {
+ appendStringInfo(&cmd, "SELECT DISTINCT N.nspname, C.relname,\n"
+ "              ( SELECT array_agg(a.attname ORDER BY a.attnum)\n"
+ "                FROM pg_attribute a\n"
+ "                WHERE a.attrelid = GPT.relid AND a.attnum > 0 AND\n"
+ "                      NOT a.attisdropped AND\n"
+ "                      (a.attnum = ANY(GPT.attrs) OR GPT.attrs IS NULL)\n"
+ "              ) AS attnames\n"
+ " FROM pg_class C\n"
+ "   JOIN pg_namespace N ON N.oid = C.relnamespace\n"
+ "   JOIN ( SELECT (pg_get_publication_tables(VARIADIC
array_agg(pubname::text))).*\n"
+ "          FROM pg_publication\n"
+ "          WHERE pubname IN ( %s )) as GPT\n"
+ "       ON GPT.relid = C.oid\n",
+ pub_names.data);

The function pg_get_publication_tables()  has already handled dropped
columns, so we don't need it here in this query. Also, the part to
build attnames should be the same as it is in view
pg_publication_tables. Can we directly try to pass the list of
pubnames to the function pg_get_publication_tables() instead of
joining it with pg_publication?

Can we keep the changes in the else part (fix when publisher < 16) the
same as HEAD and move the proposed change to a separate patch?
Basically, for the HEAD patch, let's just try to fix this when
publisher >=16. I am slightly worried that as this is a corner case
bug and we didn't see any user complaints for this, so introducing a
complex fix for back branches may not be required or at least we can
discuss that separately.

--
With Regards,
Amit Kapila.