pg_publication_tables show dropped columns
Hi everyone,
Just trying the new column/row filter on v15, I found this issue that
could be replicated very easily.
"""
postgres=# create table t1(i serial primary key);
CREATE TABLE
postgres=# alter table t1 drop i;
ALTER TABLE
postgres=# alter table t1 add id serial primary key;
ALTER TABLE
postgres=# create publication pub_t1 for table t1;
CREATE PUBLICATION
postgres=# select * from pg_publication_tables where pubname = 'pub_t1' \gx
-[ RECORD 1 ]---------------------------------
pubname | pub_t1
schemaname | public
tablename | t1
attnames | {........pg.dropped.1........,id}
rowfilter |
"""
This could be solved by adding a "NOT attisdropped", simple patch
attached.
--
Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL
Attachments:
ignore_dropped_cols_publication_tables.patchtext/x-diff; charset=us-asciiDownload
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index fedaed533b..431864648c 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -369,7 +369,7 @@ CREATE VIEW pg_publication_tables AS
P.pubname AS pubname,
N.nspname AS schemaname,
C.relname AS tablename,
- ( SELECT array_agg(a.attname ORDER BY a.attnum)
+ ( SELECT array_agg(a.attname ORDER BY a.attnum) FILTER (WHERE NOT a.attisdropped)
FROM unnest(CASE WHEN GPT.attrs IS NOT NULL THEN GPT.attrs
ELSE (SELECT array_agg(g) FROM generate_series(1, C.relnatts) g)
END) k
Jaime Casanova <jcasanov@systemguards.com.ec> writes:
Just trying the new column/row filter on v15, I found this issue that
could be replicated very easily.
Bleah. Post-beta4 catversion bump, here we come.
This could be solved by adding a "NOT attisdropped", simple patch
attached.
That view seems quite inefficient as written --- I wonder if we
can't do better by nuking the join-to-unnest business and putting
the restriction in a WHERE clause on the pg_attribute scan.
The query plan that you get for it right now is certainly awful.
regards, tom lane
On Tuesday, September 6, 2022 11:13 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jaime Casanova <jcasanov@systemguards.com.ec> writes:
Just trying the new column/row filter on v15, I found this issue that
could be replicated very easily.Bleah. Post-beta4 catversion bump, here we come.
Oh, Sorry for the miss.
This could be solved by adding a "NOT attisdropped", simple patch
attached.That view seems quite inefficient as written --- I wonder if we can't do better by
nuking the join-to-unnest business and putting the restriction in a WHERE
clause on the pg_attribute scan.
The query plan that you get for it right now is certainly awful.
I agree and try to improve the query as suggested.
Here is the new version patch.
I think the query plan and cost looks better after applying the patch.
Best regards,
Hou zj
Attachments:
v2-0001-Ignore-dropped-columns-in-pg_publication_tables.patchapplication/octet-stream; name=v2-0001-Ignore-dropped-columns-in-pg_publication_tables.patchDownload
From 718df54759fb4261c2bf6b02ee3a792ffc3f7eb8 Mon Sep 17 00:00:00 2001
From: "houzj.fnst" <houzj.fnst@cn.fujitsu.com>
Date: Tue, 6 Sep 2022 15:20:13 +0800
Subject: [PATCH] Ignore dropped columns in pg_publication_tables
Ignore dropped columns in pg_publication_tables.
While on it, improve the query by removing the join-to-unnest business and
putting the restriction in a WHERE clause on the pg_attribute scan.
---
src/backend/catalog/system_views.sql | 10 +++++-----
src/test/regress/expected/rules.out | 9 ++-------
2 files changed, 7 insertions(+), 12 deletions(-)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 5a844b63a1..091912c4f1 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -370,11 +370,11 @@ CREATE VIEW pg_publication_tables AS
N.nspname AS schemaname,
C.relname AS tablename,
( SELECT array_agg(a.attname ORDER BY a.attnum)
- FROM unnest(CASE WHEN GPT.attrs IS NOT NULL THEN GPT.attrs
- ELSE (SELECT array_agg(g) FROM generate_series(1, C.relnatts) g)
- END) k
- JOIN pg_attribute a
- ON (a.attrelid = GPT.relid AND a.attnum = k)
+ FROM pg_attribute a
+ WHERE a.attrelid = GPT.relid AND
+ NOT a.attisdropped AND
+ a.attnum > 0 AND
+ (a.attnum = ANY(GPT.attrs) OR GPT.attrs IS NULL)
) AS attnames,
pg_get_expr(GPT.qual, GPT.relid) AS rowfilter
FROM pg_publication P,
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 7ec3d2688f..de87e707a8 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1440,13 +1440,8 @@ pg_publication_tables| SELECT p.pubname,
n.nspname AS schemaname,
c.relname AS tablename,
( SELECT array_agg(a.attname ORDER BY a.attnum) AS array_agg
- FROM (unnest(
- CASE
- WHEN (gpt.attrs IS NOT NULL) THEN (gpt.attrs)::integer[]
- ELSE ( SELECT array_agg(g.g) AS array_agg
- FROM generate_series(1, (c.relnatts)::integer) g(g))
- END) k(k)
- JOIN pg_attribute a ON (((a.attrelid = gpt.relid) AND (a.attnum = k.k))))) AS attnames,
+ FROM pg_attribute a
+ WHERE ((a.attrelid = gpt.relid) AND (NOT a.attisdropped) AND (a.attnum > 0) AND ((a.attnum = ANY ((gpt.attrs)::smallint[])) OR (gpt.attrs IS NULL)))) AS attnames,
pg_get_expr(gpt.qual, gpt.relid) AS rowfilter
FROM pg_publication p,
LATERAL pg_get_publication_tables((p.pubname)::text) gpt(relid, attrs, qual),
--
2.18.4
"houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com> writes:
Here is the new version patch.
I think the query plan and cost looks better after applying the patch.
LGTM, pushed.
regards, tom lane
On Wednesday, September 7, 2022 6:01 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Subject: Re: pg_publication_tables show dropped columns
"houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com> writes:
Here is the new version patch.
I think the query plan and cost looks better after applying the patch.LGTM, pushed.
Thanks for pushing.
Best regards,
Hou zj