Blog › ICP guides

OpenRPT developer on retainer: PostgreSQL FOR UPDATE cursor lock hold, xTuple OpenRPT, GNU OpenRPT developer on monthly retainer

October 8, 2026 · ~15 min read

An OpenRPT developer was maintaining a set of custom inventory reports for a wholesale distribution company running xTuple ERP (the open-source ERP suite built on PostgreSQL). The company’s custom “Reorder Analysis” report was built as an OpenRPT report definition (.xml report file) with an embedded SQL query that used a SELECT inv_item_id, inv_qoh, inv_reorder_point FROM invitem WHERE inv_reorder_point > 0 FOR UPDATE statement against the xTuple PostgreSQL database. The developer had added the FOR UPDATE clause to the SQL query to prevent inventory quantity-on-hand (inv_qoh) from being modified by warehouse users during the time the report was reading each row — ensuring consistent inventory data throughout the report. OpenRPT’s Qt-based report engine executes the SQL query through the Qt SQL driver, which opens a database transaction automatically when the report begins execution. The SELECT FOR UPDATE acquires PostgreSQL row-level exclusive locks on all rows returned by the query at the time the cursor iterates over each row. The OpenRPT rendering engine reads all the data rows into its internal result set, then begins the rendering phase — generating the PDF or on-screen report. The Qt SQL database transaction, and therefore the PostgreSQL row-level locks acquired by the FOR UPDATE clause, remained open and held throughout the rendering phase. The rendering phase for the Reorder Analysis report took approximately 15–45 seconds for a typical dataset. During that window, 3 inventory item rows that appeared in the report had their PostgreSQL row-level exclusive locks held by the reporting session. Concurrent xTuple order-entry users attempting to update inventory quantities for those 3 items (via the xTuple postReceipts or enterPoReceipt functions, which issue UPDATE invitem SET inv_qoh = ... WHERE inv_item_id = ...) received PostgreSQL lock-wait timeouts after the configured lock_timeout elapsed (default: no timeout in vanilla PostgreSQL; xTuple typically configures 30 seconds via SET lock_timeout). The fix: the custom report script was modified to fetch all rows into a local result set, issue a COMMIT to release the PostgreSQL row locks before the rendering phase began, then render from the local result set. PostgreSQL row-level lock-wait timeouts from Reorder Analysis report: 3 per report run → 0.

The root cause was using SELECT FOR UPDATE in an OpenRPT embedded SQL query when the intent was only to ensure consistent reads, not to prevent concurrent updates for the duration of report rendering. PostgreSQL SELECT FOR UPDATE acquires row-level FOR UPDATE locks (equivalent to exclusive locks) on each row as the cursor iterates over them. These locks are released only when the surrounding transaction commits or rolls back — not when the cursor is closed, not when the application reads all rows into its result set, and not when any amount of application-side processing completes. OpenRPT uses Qt SQL to execute queries, and Qt SQL opens an implicit database transaction when the first query executes in a connection; this implicit transaction is not committed until the Qt SQL connection explicitly calls commit() or until the Qt application closes the connection. The OpenRPT rendering engine — which generates PDF pages, applies grouping bands, calculates totals, and renders formatting — operates entirely on data already fetched from the database; it does not issue additional SQL queries. But it runs within the same Qt SQL transaction that was open when the SELECT FOR UPDATE ran. If the developer’s intent was only to get a consistent snapshot of inventory quantities at report start time, the correct PostgreSQL approach is either (a) SELECT without FOR UPDATE inside a REPEATABLE READ or SERIALIZABLE transaction (which guarantees a consistent snapshot without acquiring FOR UPDATE locks), or (b) fetch all rows, COMMIT to release locks, then render. For an OpenRPT custom report script, option (b) is the operationally simplest fix.

OpenRPT is an open-source report designer and runtime engine originally developed as part of the xTuple ERP project (and available standalone). Report definitions are stored as XML files (.xml format) that describe bands (title, page header, column header, detail, group header/footer, page footer, summary), fields, labels, and embedded SQL queries. OpenRPT uses Qt’s QSqlQuery for database access: queries are prepared and executed using the application’s QSqlDatabase connection; results are fetched row-by-row via QSqlQuery::next(). In xTuple ERP deployments, the xTuple client application manages a PostgreSQL connection pool; OpenRPT reports share a database connection with the xTuple application session. Custom report scripts can be embedded as Qt Script (ECMAScript-compatible) blocks within the OpenRPT XML definition, executed before or after the main query. The PostgreSQL lock_timeout parameter (set via SET lock_timeout = '30s' at the session level or in postgresql.conf globally) controls how long a session waits for a conflicting lock before raising ERROR: canceling statement due to lock timeout. In the absence of a lock_timeout, a PostgreSQL SELECT ... FOR UPDATE session that holds locks indefinitely will cause other sessions to wait indefinitely (hanging). The xTuple community and OpenRPT documentation both advise against using FOR UPDATE in report queries for exactly this reason — but developers occasionally add it from other contexts (e.g., copying from a transactional UPDATE procedure) without recognizing the lock-duration implications.

OpenRPT, xTuple ERP, and the PostgreSQL locking model for report queries

OpenRPT originated as the reporting engine inside xTuple ERP (formerly OpenMFG), an open-source ERP platform for manufacturing, distribution, and accounting organizations, built on PostgreSQL and released under the GNU General Public License. xTuple ships in several editions: PostBooks (the community open-source edition covering core financials, CRM, and inventory management), Manufacturing Edition (adding production planning, work orders, and BOM management), and Distribution Edition (adding advanced warehouse and purchasing features). The xTuple desktop client is a Qt-based C++ application that connects directly to a PostgreSQL database server; there is also a xTuple REST API for web and mobile integration. OpenRPT is distributed as a standalone package as well, making it usable as a general-purpose PostgreSQL (and other Qt-supported databases) report designer outside of xTuple ERP deployments.

The OpenRPT Designer is a Qt-based GUI application for building report definitions visually. Developers drag and drop band sections onto a canvas, define SQL queries using the built-in SQL query editor (which supports parameter binding using named parameters prefixed with <? and ?>), configure field expressions, group-by specifications, and formatting properties, and preview the report against a live database connection. The finished report is saved as an XML .xml file that can be loaded into any OpenRPT runtime — either the xTuple client (which embeds the OpenRPT rendering engine), or the standalone renderapp command-line tool for batch PDF generation. Report parameters (date ranges, item filters, warehouse selections) are defined in the report XML and presented to users as a parameter dialog when the report is launched from the xTuple client.

PostgreSQL row-level locking has four modes relevant to OpenRPT report query development. FOR UPDATE acquires the strongest row-level exclusive lock: it blocks concurrent UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR NO KEY UPDATE on the same rows. FOR NO KEY UPDATE is a weaker exclusive lock that does not block SELECT FOR KEY SHARE but still blocks UPDATE and DELETE. FOR SHARE acquires a shared lock: it blocks concurrent UPDATE, DELETE, and SELECT FOR UPDATE but allows concurrent SELECT FOR SHARE. FOR KEY SHARE is the weakest: it blocks only SELECT FOR UPDATE and DELETE. All four locking modes share the same critical property for report developers: locks are held until the enclosing transaction commits or rolls back — not until the cursor is closed, not until the QSqlQuery object goes out of scope, and not until the application finishes processing the result set. PostgreSQL’s MVCC (Multi-Version Concurrency Control) model means that a plain SELECT without any locking clause never acquires row-level locks at all; it reads a snapshot of the database as of the start of the statement (or as of the start of the transaction in REPEATABLE READ or SERIALIZABLE isolation). For a report that needs consistent reads, REPEATABLE READ isolation with a plain SELECT achieves the consistency goal without any row-level lock acquisition.

The xTuple schema that is most relevant to OpenRPT inventory report development includes: invitem (inventory item master — inv_item_id, inv_qoh for quantity-on-hand, inv_reorder_point, inv_warehous_id); itemsite (item-site combination; stores per-warehouse item data); coitem (sales order line items); poitem (purchase order line items); and xTuple stored functions including postReceipts (post warehouse receipts, updating inv_qoh) and enterPoReceipt (enter purchase order receipt quantities). The postReceipts and enterPoReceipt functions both issue UPDATE invitem SET inv_qoh = ... statements that require row-level write access to invitem rows — the same rows that a Reorder Analysis report with SELECT FOR UPDATE holds with exclusive locks during the rendering phase. PostgreSQL advisory locks (pg_try_advisory_lock, pg_advisory_lock) are a non-row-locking alternative for serializing report runs: rather than locking individual invitem rows, a report script can acquire a session-level advisory lock at report start and release it at report end, preventing concurrent report runs while not blocking row-level UPDATE operations from xTuple order-entry users. PostgreSQL SKIP LOCKED (introduced in PostgreSQL 9.5) provides another option for report queries that can tolerate skipping currently-locked rows rather than blocking on them.

Typical OpenRPT developer retainer work and what it looks like in a work log

SELECT FOR UPDATE in report query holding locks during rendering is the canonical OpenRPT invisible production lock bug. The pattern is consistent: a custom Reorder Analysis report definition embeds a SELECT inv_item_id, inv_qoh, inv_reorder_point FROM invitem WHERE inv_reorder_point > 0 FOR UPDATE query; the OpenRPT Qt SQL driver opens an implicit transaction when the query executes; FOR UPDATE locks are acquired on all 3 matching invitem rows as the cursor iterates; OpenRPT reads all rows into its internal result set and enters the rendering phase (15–45 seconds for PDF generation); the Qt SQL transaction — and the FOR UPDATE locks — remain open throughout rendering; 3 concurrent xTuple users attempting enterPoReceipt on those 3 items receive PostgreSQL lock-wait timeout. Fix: custom report script modified to fetch all rows into a JavaScript array via Qt Script, issue db.commit() to release FOR UPDATE locks, then render from the local array. Concurrent lock-wait timeouts per report run: 3 → 0. Work log: “Reorder Analysis report; SELECT FOR UPDATE on invitem; 3 invitem rows locked for 15–45s rendering phase; 3 xTuple users received lock-wait timeout per report run; fix: fetch all rows into local result set, COMMIT before render; lock-wait timeouts: 3 → 0; 1.5h.”

Report script with unbounded result set causing PostgreSQL query plan change is the second most common OpenRPT retainer pattern in growing xTuple deployments. A custom inventory report runs a query against invitem that was performing well when the xTuple installation had 500 inventory items; after the distribution company expanded its product catalog to 15,000 items, the same report query began taking 45 seconds instead of the previous 2 seconds. The PostgreSQL query planner had switched from an index scan to a full-table sequential scan on invitem because the inv_reorder_point column used in the WHERE inv_reorder_point > 0 predicate lacked an index — at 500 rows, the planner chose a sequential scan because it was faster; at 15,000 rows, the sequential scan was the only option because no index existed. Fix: CREATE INDEX ON invitem(inv_reorder_point); PostgreSQL query planner switched to index scan; report query time: 45s → 2s. Work log: “Reorder Analysis report; WHERE inv_reorder_point > 0; invitem grew from 500 to 15,000 rows; full-table sequential scan; missing index on inv_reorder_point; CREATE INDEX ON invitem(inv_reorder_point); index scan; 45s → 2s; 2h.”

OpenRPT parameter binding with incorrect type coercion is the third common OpenRPT retainer pattern. A custom sales analysis report defines a startDate parameter that is passed to the embedded SQL query as a filter on the orderdate column (a PostgreSQL date type column). The report parameter was defined in the OpenRPT XML with type string instead of date; the Qt SQL driver bound the parameter as a text string rather than a date value. PostgreSQL received the query with the parameter as a text type; the implicit type coercion from text to date prevented the query optimizer from using the existing B-tree index on the orderdate column; the query performed a sequential scan on a 500,000-row orders table. Fix: updated the parameter type binding in the report XML from string to date; the Qt SQL driver bound the parameter as a PostgreSQL date value; the query optimizer selected the index scan on orderdate. Work log: “Sales Analysis report; startDate parameter bound as text not date; type mismatch prevented index use on orderdate (date type); sequential scan on 500,000-row orders table; fix: parameter type updated to date in report XML; index scan restored; 1h.”

How HourTab tracks OpenRPT developer retainer hours

OpenRPT FOR UPDATE lock-hold bugs are invisible in single-user testing by the same mechanism that makes them damaging in production: the Reorder Analysis report runs correctly; the PDF is generated; the OpenRPT Designer preview shows all expected rows; the Qt application exits cleanly. The FOR UPDATE locks are held during the rendering phase, but in a single-user test there are no concurrent PostgreSQL sessions trying to update invitem rows, so no lock-wait timeout ever fires. In production, the first time a warehouse manager runs the Reorder Analysis report during receiving hours, 3 receiving clerks attempting to post receipts for those items receive a generic xTuple error dialog — “ERROR: canceling statement due to lock timeout” — with no indication of which session is holding the lock or why. Diagnosing the root cause requires querying the PostgreSQL pg_locks system view joined to pg_stat_activity: SELECT blocking.pid, blocking.query, blocked.pid, blocked.query FROM pg_stat_activity blocked JOIN pg_locks bl ON bl.pid = blocked.pid JOIN pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid JOIN pg_stat_activity blocking ON blocking.pid = kl.pid WHERE NOT blocked.granted. This query identifies the blocking session’s PID and its current query; correlating the blocking PID back to the OpenRPT report session requires examining the application_name field in pg_stat_activity, which xTuple sets to identify report sessions.

The work log must name the mechanism to be auditable: which report (Reorder Analysis), the SQL query (SELECT inv_item_id, inv_qoh, inv_reorder_point FROM invitem WHERE inv_reorder_point > 0 FOR UPDATE), the FOR UPDATE clause and its lock type (row-level exclusive), the lock hold duration (15–45 seconds rendering phase), the concurrent lock-wait timeout count before and after fix (3 per report run → 0), the fix (fetch all rows into local result set in Qt Script, issue db.commit() before rendering phase, render from local result set), and the verification (confirmed via pg_locks monitoring during report execution that no FOR UPDATE locks appear on invitem rows after the commit; confirmed 0 lock-wait timeout errors in xTuple application log during subsequent report runs). A log entry that says “fixed report locking issue, 1.5h” is not auditable. A log entry that names the Reorder Analysis report, the FOR UPDATE clause, the 15–45 second lock hold duration, and the COMMIT-before-render fix with before/after timeout count is auditable and defensible to the wholesale distribution client. HourTab gives OpenRPT developers a public retainer-hours URL they send to clients — wholesale distributors, manufacturers, and accounting-focused small businesses that chose xTuple ERP (or its predecessor OpenMFG) for its open-source PostgreSQL architecture, maintained today by the original implementation developer or a successor retainer consultant.

Comparative context: OpenRPT FOR UPDATE lock-hold bugs are structurally identical to the same pattern in other report and cursor contexts where row-level exclusive locks outlive the data-reading phase. Progress OpenEdge retainers cover ABL record lock scope — a different platform with the same “lock held too long” class of invisible production bug, where an ABL FOR EACH record lock persists beyond the logical processing boundary. Genero BDL retainers cover FOR EACH ... UPDATE without COMMIT WORK in Informix 4GL — structurally the same pattern: a cursor opened with update intent holds row-level locks until an explicit commit releases them, and the Genero rendering or processing phase runs before the commit. Gupta SQLWindows retainers cover SqlPrepare FOR UPDATE cursor lock hold on SQL Server — the same FOR UPDATE cursor lock pattern as PostgreSQL, where the cursor holds locks on fetched rows until the transaction is explicitly committed, not until the cursor is closed.

FAQ: OpenRPT developer retainers