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)