Yuxiang Ji created NIFI-16587:
---------------------------------

             Summary: QueryDatabaseTableRecord skips a row committed after a 
later maximum
                 Key: NIFI-16587
                 URL: https://issues.apache.org/jira/browse/NIFI-16587
             Project: Apache NiFi
          Issue Type: Bug
          Components: Extensions
    Affects Versions: 2.12.0
         Environment: Apache NiFi 2.12.0 (Docker image apache/nifi:2.12.0), 
PostgreSQL JDBC driver 42.7.14; PostgreSQL 13.23 and 18.6; Linux, local 
containers
            Reporter: Yuxiang Ji


*Versions.* Apache NiFi 2.12.0 (image {{apache/nifi:2.12.0}}), 
{{QueryDatabaseTableRecord}} with a maximum-value column; PostgreSQL 13.23 and 
18.6.

h2. What happens

A row whose maximum-value column is below a value already seen, because its 
transaction committed later, is never fetched.

h2. Reproduction

{{T1: BEGIN; INSERT}} (id 100) and hold; {{T2: INSERT; COMMIT}} (id 101); the 
processor runs; {{T1: COMMIT}}; the processor runs again. Likewise with a 
{{last_modified}} column set by {{now()}}.

h2. Expected

Both rows fetched.

h2. Observed

T1's row never fetched, in every run (48 of n=48 across eight variants of this 
schedule: id and timestamp columns, inserted and updated rows, rows tied at the 
maximum; PostgreSQL 13.23 and 18.6).

h2. Why

The processor fetches "all rows whose values in the specified Maximum Value 
column(s) are larger than the previously-seen maxima" and suggests a one-up id 
or a {{last_modified}} column for its incremental-load use case (the processor 
documentation in {{nifi-standard-processors-2.12.0}}); it says nothing of rows 
that commit out of order.

h2. A possible remedy

Document the hazard; offer a lag on the maximum value, or on PostgreSQL a bound 
by the transactions still running.

h2. What this does not show

Rows lost under real workloads.



--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

Reply via email to