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+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>
 <9d5073b7-e461-d0f3-89b7-0ddb09936f73@enterprisedb.com>
 <CAA4eK1LTCaGy2rnFR148SGmwqJw65uXf-j9hvkE7z6uuH-xBKg@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <4f87a67c-bf95-ae53-9632-475415b4a7db@enterprisedb.com>
Date: Sat, 25 Sep 2021 12:06:25 +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: 
 <CAA4eK1LTCaGy2rnFR148SGmwqJw65uXf-j9hvkE7z6uuH-xBKg@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/4f87a67c-bf95-ae53-9632-475415b4a7db%40enterprisedb.com>
Precedence: bulk


On 9/25/21 6:23 AM, Amit Kapila wrote:
> On Sat, Sep 25, 2021 at 3:30 AM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
>>
>> On 9/24/21 7:20 AM, Amit Kapila wrote:
>>>
>>> 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.
>>
> 
> It will just solve one part of the puzzle (related to database access)
> but it is better to avoid the risk of broken replication by explicit
> marking especially for UDFs or other user-defined objects. You seem to
> be okay documenting such risk but I am not sure we have an agreement
> on that especially because that was one of the key points of
> discussions in this thread and various people told that we need to do
> something about it. I personally feel we should do something if we
> want to allow user-defined functions or operators because as reported
> in the thread this problem has been reported multiple times. I think
> we can go ahead with IMMUTABLE built-ins for the first version and
> then allow UDFs later or let's try to find a way for explicit marking.
> 

Well, I know multiple people mentioned that issue. And I certainly agree 
just documenting the risk would not be an ideal solution. Requiring the 
functions to be labeled helps, but we've seen people marking volatile 
functions as immutable in order to allow indexing, so we'll have to 
document the risks anyway.

All I'm saying is that allowing built-in functions/operators but not 
user-defined variants seems like an annoying break of extensibility. 
People are used that user-defined stuff can be used just like built-in 
functions and operators.

>> 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.
>>
> 
> Agreed and I think we can't do anything about this.
> 
>>>>
>>>> 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.
>>
> 
> Yeah, that is one and I am not sure that adding extra WAL is the best
> or only solution for this problem. As mentioned in my previous
> response, I think we eventually need to find a way to access user
> tables to support UDFs (that access database) or sub-query which other
> databases already support, and for that, we might need to enhance the
> current snapshot mechanism after which we might not need any
> additional WAL even for additional columns in row filter. I don't
> think anyone of us has evaluated in detail the different ways this
> problem can be solved and the pros/cons of each approach, so limiting
> the scope for this purpose doesn't seem like a bad idea to me.
> 

Understood. I don't have a very good idea which of those options is the 
best one either, although I think enhancing the snapshot mechanism would 
be rather tricky.


regards

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