On 7/14/21 2:50 PM, Amit Kapila wrote:
On Wed, Jul 14, 2021 at 3:58 PM Tomas Vondra
<tomas.von...@enterprisedb.com> wrote:
On 7/14/21 7:39 AM, Amit Kapila wrote:
On Wed, Jul 14, 2021 at 6:28 AM Euler Taveira <eu...@eulerto.com> wrote:
On Tue, Jul 13, 2021, at 6:06 PM, Alvaro Herrera wrote:
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.)
+1.
I thought about it but came to the conclusion that it doesn't worth it. Even
with REPLICA IDENTITY FULL expression evaluates to false if the column allows
NULL values. Besides that REPLICA IDENTITY is changed via another DDL (ALTER
TABLE) and you have to make sure you don't allow changing REPLICA IDENTITY
because some row filter uses the column you want to remove from it.
Yeah, that is required but is it not feasible to do so?
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.)
New tuple. The main reason is that new tuple is always there for UPDATEs.
I am not sure if that is a very good reason to use a new tuple.
True. Perhaps we should look at other places with similar concept of
WHERE conditions and old/new rows, and try to be consistent with those?
I can think of:
1) updatable views with CHECK option
2) row-level security
3) triggers
Is there some reasonable rule which of the old/new tuples (or both) to
use for the WHERE condition? Or maybe it'd be handy to allow referencing
OLD/NEW as in triggers?
I think apart from the above, it might be good if we can find what
some other databases does in this regard?
Yeah, that might tell us what the users would like to do with it. I did
some quick search, but haven't found much :-( The one thing I found is
that Debezium [1] allows accessing both the "old" and "new" rows through
value.before and value.after, and use both for filtering.
I haven't found much about how this works in other databases, sadly.
Perhaps the best way forward is to stick to the approach that INSERT
uses new, DELETE uses old and UPDATE works as DELETE+INSERT (probably),
and leave anything fancier (like being able to reference both versions
of the row) for a future patch.
[1]
https://wanna-joke.com/wp-content/uploads/2015/01/german-translation-comics-science.jpg
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
