LP#1847805: PCRUD can miss valid rows in LIMIT/OFFSET queries
authorMike Rylander <mrylander@gmail.com>
Fri, 11 Oct 2019 22:29:10 +0000 (18:29 -0400)
committerMike Rylander <mrylander@gmail.com>
Mon, 21 Oct 2019 18:10:13 +0000 (14:10 -0400)
commita492f10e2a6f6cc92b785051005de3cce8d4e415
tree3a44a207abbbeed7aa43a470fc86f19c785b58db
parent8ab553cf19a5cd1f0ec3260a3054d23a93cc7aef
LP#1847805: PCRUD can miss valid rows in LIMIT/OFFSET queries

Because we check authz for rows retrieved by open-ils.pcrud calls after
the underlying query is executed, including any limit/offset-based
paging, we can fail to return results when the first "page" is full of
rows that the calling user cannot see.

With this commit, we switch to SQL cursors and page through results in
the application logic, which guarantees that the situation above will
not impact legitimate row visibility.

Additionally, this may prove to increase speed of some queries,
especially because Postgres has the option to optimize for "fast-start"
plans.

There is a small amount over overhead added to single-row retrieval
requests, on the order of 0.25ms-0.5ms total for cursor declaration and
then cursor closing.

Signed-off-by: Mike Rylander <mrylander@gmail.com>
Open-ILS/src/c-apps/oils_sql.c