On 2021-Jul-13, Euler Taveira wrote: > + <para> > + The <literal>WHERE</literal> clause should contain only columns that are > + part of the primary key or be covered by <literal>REPLICA > + IDENTITY</literal> otherwise, <command>DELETE</command> operations will > not > + be replicated. That's because old row is used and it only contains primary > + key or columns that are part of the <literal>REPLICA IDENTITY</literal>; > the > + remaining columns are <literal>NULL</literal>. For > <command>INSERT</command> > + and <command>UPDATE</command> operations, any column might be used in the > + <literal>WHERE</literal> clause. New row is used and it contains all > + columns. A <literal>NULL</literal> value causes the expression to evaluate > + to false; avoid using columns without not-null constraints in the > + <literal>WHERE</literal> clause. The <literal>WHERE</literal> clause does > + not allow functions and user-defined operators. > + </para>
There's a couple of points in this paragraph .. 1. if you use REPLICA IDENTITY FULL, then the expressions would work even if they use any other column with DELETE. Maybe it would be reasonable to test for this in the code and raise an error if the expression requires a column that's not part of the replica identity. (But that could be relaxed if the publication does not publish updates/deletes.) 2. For UPDATE, does the expression apply to the old tuple or to the new tuple? You say it's the new tuple, but from the user point of view I think it would make more sense that it would apply to the old tuple. (Of course, if you're thinking that the R.I. is the PK and the PK is never changed, then you don't really care which one it is, but I bet that some people would not like that assumption.) I think it is sensible that it's the old tuple that is matched, not the new; consider what happens if you change the PK in the update and the replica already has that tuple. If you match on the new tuple and it doesn't match the expression (so you filter out the update), but the old tuple does match the expression, then the replica will retain the mismatching tuple forever. 3. You say that a NULL value in any of those columns causes the expression to become false and thus the tuple is not published. This seems pretty unfriendly, but maybe it would be useful to have examples of the behavior. Does ExecInitCheck() handle things in the other way, and if so does using a similar trick give more useful behavior? <para> The WHERE clause may only contain references to columns that are part of the table's replica identity. If <>DELETE</> or <>UPDATE</> operations are published, this restriction can be bypassed by making the replica identity be the whole row with <command>ALTER TABLE .. SET REPLICA IDENTITY FULL</command>. The <literal>WHERE</literal> clause does not allow functions or user-defined operators. </para> -- Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/ "The Gord often wonders why people threaten never to come back after they've been told never to return" (www.actsofgord.com)
