Re: Column Filtering in Logical Replication

On 2021-Dec-27, Tom Lane wrote:

> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> > 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.
>
> Maybe better to rethink why we allow elements of prattrs to be null?

What I'm doing is an unnest of all arrays and then aggregating them
back into a single array. If one array is null, the resulting aggregate
contains a null element.

Hmm, maybe I can in parallel do a bool_or() aggregate of "array is null" to
avoid that. ... ah yes, that works:

with published_cols as (
select pg_catalog.bool_or(pr.prattrs is null) as all_columns,
pg_catalog.array_agg(distinct unnest order by unnest) 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 = :table 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(:table)),
published_cols
WHERE a.attnum > 0::pg_catalog.int2
AND NOT a.attisdropped and a.attgenerated = ''
AND a.attrelid = :table
AND (all_columns OR attnum = ANY(published_cols.attrs))
ORDER BY a.attnum ;

--
Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/