Horton
Menu

Re: Column Filtering in Logical Replication

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: Rahila Syed <rahilasyed90@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2021-12-27T17:06:18Z
Lists: pgsql-hackers 
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".

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Doc: Explain about Column List feature.

  2. Doc: fix column list vs. replica identity rules.

  3. Prohibit combining publications with different column lists.

  4. Fix the check to limit sync workers.

  5. Wait for subscription to sync in t/031_column_list.sql

  6. Move prattrs to the pg_publication_rel section in docs

  7. Allow specifying column lists for logical replication

  8. Fix row filters with multiple publications

  9. Fix publish_as_relid with multiple publications

  10. Add some additional tests for row filters in logical replication.

  11. Add index on pg_publication_rel.prpubid

  12. Avoid using DefElemAction in AlterPublicationStmt

  13. Small cleanups related to PUBLICATION framework code

  14. Add PublicationTable and PublicationRelInfo structs

  15. Fix various concurrency issues in logical replication worker launching