Ignore dropped columns when checking the column list in logical replication

From: "shiy(dot)fnst(at)fujitsu(dot)com" <shiy(dot)fnst(at)fujitsu(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Ignore dropped columns when checking the column list in logical replication
Date: 2023-01-04 06:58:00
Message-ID: OSZPR01MB631087C65BA81E1FEE5A60D2FDF59@OSZPR01MB6310.jpnprd01.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

I saw a problem related to column list.

There's a restriction that different column lists for same table can't be used
in the publications of single subscription. But we will get unexpected errors in
some cases because the dropped columns are not ignored.

For example:
-- publisher
create table tbl1 (a int primary key, b int, c int);
create publication pub1 for table tbl1(a,b);
create publication pub2 for table tbl1;
alter table tbl1 drop column c;

-- subscriber
create table tbl1 (a int primary key, b int, c int);
create subscription sub connection 'dbname=postgres port=5432' publication pub1, pub2;

-- publisher
insert into tbl1 values (1,2);

The publisher and subscriber will report the error:
ERROR: cannot use different column lists for table "public.tbl1" in different publications

This is caused by:
a. walsender (in pgoutput_column_list_init())
/*
* If column list includes all the columns of the table,
* set it to NULL.
*/
if (bms_num_members(cols) == RelationGetNumberOfAttributes(relation))
{
bms_free(cols);
cols = NULL;
}

The returned value of RelationGetNumberOfAttributes() contains dropped columns.

b. table synchronization (in fetch_remote_table_info())
appendStringInfo(&cmd,
"SELECT DISTINCT"
" (CASE WHEN (array_length(gpt.attrs, 1) = c.relnatts)"
" THEN NULL ELSE gpt.attrs END)"
" FROM pg_publication p,"
" LATERAL pg_get_publication_tables(p.pubname) gpt,"
" pg_class c"
" WHERE gpt.relid = %u AND c.oid = gpt.relid"
" AND p.pubname IN ( %s )",
lrel->remoteid,
pub_names.data);

If the result of the above SQL contains more than one tuple, an error will be
report (cannot use different column lists for table ...). In this SQL, attrs is
NULL if `array_length(gpt.attrs, 1) = c.relnatts`, but `c.relnatts` contains
dropped columns, what we want is the count of alive columns.

I tried to fix them in the attached patch.

Regards,
Shi yu

Attachment Content-Type Size
v1-0001-Ignore-dropped-columns-when-checking-the-column-l.patch application/octet-stream 4.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message vignesh C 2023-01-04 07:07:59 Re: [DOCS] Stats views and functions not in order?
Previous Message Peter Geoghegan 2023-01-04 06:52:51 Re: pgsql: Delay commit status checks until freezing executes.