Subject: Re: row filtering for logical replication
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Ajin Cherian <itsajin@gmail.com>, Euler Taveira <euler@eulerto.com>,
 Peter Smith <smithpb2250@gmail.com>, Rahila Syed <rahilasyed90@gmail.com>,
 Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
 =?UTF-8?B?w5ZuZGVyIEthbGFjxLE=?= <onderkalaci@gmail.com>,
 japin <japinli@hotmail.com>, Michael Paquier <michael@paquier.xyz>,
 David Steele <david@pgmasters.net>, Craig Ringer <craig@2ndquadrant.com>,
 Tomas Vondra <tomas.vondra@2ndquadrant.com>,
 Amit Langote <amitlangote09@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: 
 <CA+HiwqH+9NK5m-=BOYpzjH5UEpeWjT_3HEm4XBYNrda88naGMQ@mail.gmail.com>
 <CAHut+Pvn+S8upyrkBi3m=rc4b5Ttw3xGsg09rNec=qJK0pD5mQ@mail.gmail.com>
 <CAA4eK1J1FNhDhj85Am1X_5VTxKOYo_nSBMuuMteB3n5YVPMdpw@mail.gmail.com>
 <CAHut+PtbKUmWNjTEDMVhP9abjgph6JB7vUFYF7ioa_4uRPZb=A@mail.gmail.com>
 <CAA4eK1LPhubcm4o1Xg0RwZdatqwfz3KKYcdun8QvRyqFthyJ1g@mail.gmail.com>
 <CAHut+PtFxoP477E8odkxkpyDoH_tiNBSiJH-j88NR728nxPErQ@mail.gmail.com>
 <CAA4eK1+tio46goUKBUfAKFsFVxtgk8nOty=TxKoKH-gdLzHD2g@mail.gmail.com>
 <CAHut+PtdRU74kUZd2RugdCqB0Ya1L+BbZk45czM5ufQs5E8jmw@mail.gmail.com>
 <CAHut+PsgRHymwLhJ9t3By6+KNaVDzfjf6Y4Aq=JRD-y8t1mEFg@mail.gmail.com>
 <0c2464d4-65f4-4d91-aeb2-c5584c1350f5@www.fastmail.com>
 <CAFPTHDb7bpkuc4SxaL9B5vEvF2aEi0EOERdrG+xgVeAyMJsF=Q@mail.gmail.com>
 <CAFPTHDYZk4P1kD=y6qMBnzh66+CgnfV3VTK7yS-Tk1eGrFOVZg@mail.gmail.com>
 <574b4e78-2f35-acf3-4bdc-4b872582e739@enterprisedb.com>
 <CAA4eK1JWpdUhwueSa-uc5Begez+kFW0vf+4DOZKeLThn3-TrXg@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <9d5073b7-e461-d0f3-89b7-0ddb09936f73@enterprisedb.com>
Date: Sat, 25 Sep 2021 00:00:32 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: 
 <CAA4eK1JWpdUhwueSa-uc5Begez+kFW0vf+4DOZKeLThn3-TrXg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/9d5073b7-e461-d0f3-89b7-0ddb09936f73%40enterprisedb.com>
Precedence: bulk


On 9/24/21 7:20 AM, Amit Kapila wrote:
> On Thu, Sep 23, 2021 at 6:03 PM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
>>
>> 6) parse_oper.c
>>
>> I'm having some second thoughts about (not) allowing UDFs ...
>>
>> Yes, I get that if the function starts failing, e.g. because querying a
>> dropped table or something, that breaks the replication and can't be
>> fixed without a resync.
>>
> 
> The other problem is that users can access/query any table inside the
> function and that also won't work in a logical decoding environment as
> we use historic snapshots using which we can access only catalog
> tables.
> 

True. I always forget about some of these annoying issues. Let's 
document all of this in some comment / README. I see we still don't have

   src/backend/replication/logical/README

which is a bit surprising, considering how complex this code is.

>> That's pretty annoying, but maybe disallowing anything user-defined
>> (functions and operators) is maybe overly anxious? Also, extensibility
>> is one of the hallmarks of Postgres, and disallowing all custom UDF and
>> operators seems to contradict that ...
>>
>> Perhaps just explaining that the expression can / can't do in the docs,
>> with clear warnings of the risks, would be acceptable.
>>
> 
> I think the right way to support functions is by the explicit marking
> of functions and in one of the emails above Jeff Davis also agreed
> with the same. I think we should probably introduce a new marking for
> this. I feel this is important because without this it won't be safe
> to access even some of the built-in functions that can access/update
> database (non-immutable functions) due to logical decoding environment
> restrictions.
> 

I agree that seems reasonable. Is there any reason why not to just use 
IMMUTABLE for this purpose? Seems like a good match to me.

Yes, the user can lie and label something that is not really IMMUTABLE, 
but that's his fault. Yes, it's harder to fix than e.g. for indexes.

>>
>> 12) misuse of REPLICA IDENTITY
>>
>> The more I think about this, the more I think we're actually misusing
>> REPLICA IDENTITY for something entirely different. The whole purpose of
>> RI was to provide a row identifier for the subscriber.
>>
>> But now we're using it to ensure we have all the necessary columns,
>> which is entirely orthogonal to the original purpose. I predict this
>> will have rather negative consequences.
>>
>> People will either switch everything to REPLICA IDENTITY FULL, or create
>> bogus unique indexes with extra columns. Which is really silly, because
>> it wastes network bandwidth (transfers more data) or local resources
>> (CPU and disk space to maintain extra indexes).
>>
>> IMHO this needs more infrastructure to request extra columns to decode
>> (e.g. for the filter expression), and then remove them before sending
>> the data to the subscriber.
>>
> 
> Yeah, but that would have an additional load on write operations and I
> am not sure at this stage but maybe there could be other ways to
> extend the current infrastructure wherein we build the snapshots using
> which we can access the user tables instead of only catalog tables.
> Such enhancements if feasible would be useful not only for allowing
> additional column access in row filters but for other purposes like
> allowing access to functions that access user tables. I feel we can
> extend this later as well seeing the usage and requests. For the first
> version, this doesn't sound too limiting to me.
> 

I'm not really buying the argument that this means overhead for write 
operations. Well, it does, but the current RI approach is forcing users 
to either use RIF or add an index covering the filter attributes. 
Neither of those options is free, and I'd bet the extra overhead of 
adding just the row filter columns would be actually lower.

If the argument is merely to limit the scope of this patch, fine. But 
I'd bet the amount of code we'd have to add to ExtractReplicaIdentity 
(or maybe somewhere close to it) would be fairly small. We'd need to 
cache which columns are needed (like RelationGetIndexAttrBitmap), and 
this might be a bit more complex, due to having to consider all the 
publications etc.

regards

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