public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alvaro Herrera <[email protected]>
To: Peter Eisentraut <[email protected]>
Cc: Rahila Syed <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: Column Filtering in Logical Replication
Date: Mon, 27 Dec 2021 14:06:18 -0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>

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".

view thread (167+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Column Filtering in Logical Replication
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox