Re: Column Filtering in Logical Replication

From: Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>
To: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Rahila Syed <rahilasyed90(at)gmail(dot)com>
Cc: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Peter Smith <smithpb2250(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Column Filtering in Logical Replication
Date: 2021-09-06 21:03:50
Message-ID: 4804c901-3888-21cd-08ad-52c3e96858ed@enterprisedb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 9/6/21 7:51 PM, Alvaro Herrera wrote:
> On 2021-Sep-06, Rahila Syed wrote:
>
>>>> ... ugh. Since CASCADE is already defined to be a
>>>> potentially-data-loss operation, then that may be acceptable
>>>> behavior. For sure the default RESTRICT behavior shouldn't do it,
>>>> though.
>>>
>>> That makes sense to me.
>>
>> However, the default (RESTRICT) behaviour of DROP TABLE allows
>> removing the table from the publication. I have implemented the
>> removal of table from publication on drop column (RESTRICT) on the
>> same lines.
>
> But dropping the table is quite a different action from dropping a
> column, isn't it? If you drop a table, it seems perfectly reasonable
> that it has to be removed from the publication -- essentially, when the
> user drops a table, she is saying "I don't care about this table
> anymore". However, if you drop just one column, that doesn't
> necessarily mean that the user wants to stop publishing the whole table.
> Removing the table from the publication in ALTER TABLE DROP COLUMN seems
> like an overreaction. (Except perhaps in the special case were the
> column being dropped is the only one that was being published.)
>
> So let's discuss what should happen. If you drop a column, and the
> column is filtered out, then it seems to me that the publication should
> continue to have the table, and it should continue to filter out the
> other columns that were being filtered out, regardless of CASCADE/RESTRICT.
> However, if the column is *included* in the publication, and you drop
> it, ISTM there are two cases:
>
> 1. If it's DROP CASCADE, then the list of columns to replicate should
> continue to have all columns it previously had, so just remove the
> column that is being dropped.
>
> 2. If it's DROP RESTRICT, then an error should be raised so that the
> user can make a concious decision to remove the column from the filter
> before dropping the column.
>

FWIW I think this is a sensible behavior.

I don't quite see why dropping a column should remove the table from
publication (assuming there are some columns still replicated).

Of course, it may break the subscriber (e.g. when there was NOT NULL
constraint on that column), but DROP RESTRICT (which I assume is the
default mode) prevents that. And if DROP CASCADE is specified, I think
it's reasonable to require the user to fix the fallout.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2021-09-06 21:58:53 Re: The Free Space Map: Problems and Opportunities
Previous Message Zhihong Yu 2021-09-06 20:40:13 Re: SQL:2011 application time