Re: Column Filtering in Logical Replication - Mailing list pgsql-hackers

From Alvaro Herrera
Subject Re: Column Filtering in Logical Replication
Date
Msg-id 202112271831.ckar2sy4s5kv@alvherre.pgsql
Whole thread Raw
In response to Re: Column Filtering in Logical Replication  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
On 2021-Dec-27, Tom Lane wrote:

> Alvaro Herrera <alvherre@alvh.no-ip.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/



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Foreign key joins revisited
Next
From: Peter Eisentraut
Date:
Subject: Re: sequences vs. synchronous replication