Обсуждение: 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

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

"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