Re: Column Filtering in Logical Replication

Determining that an array has a NULL element seems convoluted. I ended
up with this query, where comparing the result of array_positions() with
an empty array does that. If anybody knows of a simpler way, or any
situations in which this fails, I'm all ears.

with published_cols as (
select case when
pg_catalog.array_positions(pg_catalog.array_agg(unnest), null) <> '{}' then null else
pg_catalog.array_agg(distinct unnest order by unnest) end AS attrs
from pg_catalog.pg_publication p join
pg_catalog.pg_publication_rel pr on (p.oid = pr.prpubid) left join
unnest(prattrs) on (true)
where prrelid = 38168 and p.pubname in ('pub1', 'pub2')
)
SELECT a.attname,
a.atttypid,
a.attnum = ANY(i.indkey)
FROM pg_catalog.pg_attribute a
LEFT JOIN pg_catalog.pg_index i
ON (i.indexrelid = pg_get_replica_identity_index(38168)),
published_cols
WHERE a.attnum > 0::pg_catalog.int2
AND NOT a.attisdropped and a.attgenerated = ''
AND a.attrelid = 38168
AND (published_cols.attrs IS NULL OR attnum = ANY(published_cols.attrs))
ORDER BY a.attnum;

This returns all columns if at least one publication has a NULL prattrs,
or only the union of columns listed in all publications, if all
publications have a list of columns.

(I was worried about obtaining the list of publications, but it turns
out that it's already as a convenient list of OIDs in the MySubscription
struct.)

With this, we can remove the second query added by Rahila's original patch to
filter out nonpublished columns.

I still need to add pg_partition_tree() in order to search for
publications containing a partition ancestor. I'm not yet sure what
happens (and what *should* happen) if an ancestor is part of a
publication and the partition is also part of a publication, and the
column lists differ.

--
Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/
Al principio era UNIX, y UNIX habló y dijo: "Hello world\n".
No dijo "Hello New Jersey\n", ni "Hello USA\n".