Message-ID: <f716f584-65d0-fe83-2e84-53426631739a@enterprisedb.com>
Date: Thu, 2 Dec 2021 16:08:02 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:91.0)
 Gecko/20100101 Thunderbird/91.3.2
Subject: Re: Skipping logical replication transactions on subscriber side
Content-Language: en-US
To: Amit Kapila <amit.kapila16@gmail.com>,
 "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, vignesh C <vignesh21@gmail.com>,
 Greg Nancarrow <gregn4422@gmail.com>,
 "tanghy.fnst@fujitsu.com" <tanghy.fnst@fujitsu.com>,
 "osumi.takamichi@fujitsu.com" <osumi.takamichi@fujitsu.com>,
 Alexey Lesovsky <lesovsky@gmail.com>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: 
 <CAA4eK1KzZp5YC34exW3Yb4Vk9v5iMW0YuJCQsWeTOeF7ejLdXw@mail.gmail.com>
 <CAA4eK1JfJBJZyTEmG-z=Uu32aMTx8d+aLLorzDXt6s=ERa_ykA@mail.gmail.com>
 <CALDaNm2_WY2t2GnzW1-QL823+xk5cuC0zc4Eep8fjYeLB0jfRA@mail.gmail.com>
 <CAA4eK1JheT_MTmQiGfWZJeHSEJGzd-mgPPwBwA=YWjhcTfykPA@mail.gmail.com>
 <CAD21AoBu2f2R6Bc9qWpky8v_h822zdkm0419j60Ph_T9ciaVsw@mail.gmail.com>
 <CAD21AoAnexmfLz5dA4mPXT10b=wt0DEsp-CcAbLCZbhuh8zZoQ@mail.gmail.com>
 <OS0PR01MB5716D985D4F2430E817CE30B94689@OS0PR01MB5716.jpnprd01.prod.outlook.com>
 <CAA4eK1K_yFm_vLyNi-unqJNYk1edNkuOrqjBRWVTF5bFaTSsQg@mail.gmail.com>
 <CAD21AoAUogU_4+yPrwJeaZhOUV06USE+TFJHt5OD48O43NFvnQ@mail.gmail.com>
 <CAA4eK1JOsqPHZTOeifp+Q8pWgrn1qzks3ojZnSHp-1QZFcjyQQ@mail.gmail.com>
 <CAD21AoAqDVCVzSpv6DOMLqLeO+m4Jh=dKv9a8Cc4qthdpYzqFw@mail.gmail.com>
 <OS0PR01MB571603F90202D6709B5DF5B594689@OS0PR01MB5716.jpnprd01.prod.outlook.com>
 <CAA4eK1KD_C_0LSxaYB0UbG59VOgjf4mXBeSYbVWCLXAnnuqnPw@mail.gmail.com>
From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
In-Reply-To: 
 <CAA4eK1KD_C_0LSxaYB0UbG59VOgjf4mXBeSYbVWCLXAnnuqnPw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/f716f584-65d0-fe83-2e84-53426631739a%40enterprisedb.com>
Precedence: bulk


On 02.12.21 07:48, Amit Kapila wrote:
> a. ALTER SUBSCRIPTION ... [SET|RESET] SKIP TRANSACTION xxx;
> b. Alter Subscription <sub_name> SET ( subscription_parameter [=value]
> [, ... ] );
> c. Alter Subscription <sub_name> On Error ( subscription_parameter
> [=value] [, ... ] );
> d. Alter Subscription <sub_name> SKIP ( subscription_parameter
> [=value] [, ... ] );
> where subscription_parameter can be one of:
> xid = <xid_val>
> lsn = <lsn_val>
> ...

> As per discussion till now, option (d) seems preferable.

I agree.

> In this, we
> need to see how and what to allow as options. The simplest way for the
> first version is to just allow one xid to be specified at a time which
> would mean that specifying multiple xids should error out. We can also
> additionally allow specifying operations like 'insert', 'update',
> etc., and then relation list (list of oids). What that would mean is
> that for a transaction we can allow which particular operations and
> relations we want to skip.

I don't know how difficult it would be, but allowing multiple xids might 
be desirable.  But this syntax gives you flexibility, so we can also 
start with a simple implementation.

> I am not sure what exactly we can provide to users to allow skipping
> initial table sync as we can't specify XID there. One option that
> comes to mind is to allow specifying a combination of copy_data and
> relid to skip table sync for a particular relation. We might think of
> not doing anything for table sync workers but not sure if that is a
> good option.

I don't think this feature should affect tablesync.  The semantics are 
not clear, and it's not really needed.  If the tablesync doesn't work, 
you can try the setup again from scratch.