Intermittent "ERROR: missing row in percentile_cont" when using LIMIT or Cursors with Ordered-Set Aggregates
Article ID: 447427
Updated On:
Products
Issue/Introduction
When executing a query containing ordered-set aggregate functions (such as percentile_cont) combined with a LIMIT clause, or when fetching results via a client tool using cursors (e.g., DBeaver), the query may intermittently fail with the following error:
ERROR: missing row in percentile_cont (orderedsetaggs.c:615)
This issue typically occurs on specific segments rather than globally, and it is known to be intermittent depending on the timing of query execution.
The query example:
WITH tab1 AS (
...
)
SELECT
event_year,
full_page_url,
percentile_cont(0.75) WITHIN GROUP (ORDER BY lcp) AS lcp
FROM tab1
GROUP BY event_year, full_page_url
LIMIT 10;
Environment
- Product: Greenplum Database (Version below 7.8.2)
- Features: Ordered-set aggregates (
percentile_cont,mode,rank,percent_rank,cume_dist,dense_rankunderWITHIN GROUP),LIMITclause, or Cursor-based client fetches. - Conditions: Computational skew causing disk spills.
- Optimizer: GPORCA (primarily) and Postgres Planner.
Cause
The error is caused by a race condition between the "early query termination" mechanism and the ordered-set aggregate reading its own internal sort.
When a LIMIT is met or a cursor stops fetching, the coordinator (Master) attempts to optimize by broadcasting a "graceful query-finish signal" to the segments. As an optimization, this signal stops in-progress sorts. While safe for standard queries where the remaining sort output would just be discarded, it creates a fatal issue for ordered-set aggregates like percentile_cont, which must consume their own internal sort to produce a final mathematical answer. The aggregate is prematurely cut off from reading its results, triggering the missing row error.
The race window is highly sensitive to plan shapes and execution timing:
1. GPORCA: ORCA’s plan often utilizes an unsorted Gather Motion, which allows the coordinator to send the finish signal before slow, skewed segments have finished their spilled sorts, greatly widening the race window.
2. Computational Skew & Disk Spills: Computational skew forces massive per-group sorts on a single segment. If statement_mem is small, the sort spills to disk, making finalization slow and drastically increasing the likelihood of the finish signal hitting at the exact wrong moment.
Resolution
Workarounds
The core strategy for any workaround is to ensure the ordered-set aggregate is fully materialized before the coordinator can trigger the early query-finish signal.
Workaround A: Blocking-Sort Barrier
Add an ORDER BY clause on the computed percentile column (or any expression not already supplied by the GROUP BY ordering) before the LIMIT.
SELECT
event_year,
full_page_url,
percentile_cont(0.75) WITHIN GROUP (ORDER BY lcp) AS lcp
FROM tab1
GROUP BY event_year, full_page_url
ORDER BY lcp DESC -- <<< Forces a blocking Sort ABOVE the aggregate
LIMIT 10;
Note: Verify via EXPLAIN that a Sort node appears above the GroupAggregate. A Sort is a blocking operator, forcing all percentiles to be finalized before emitting a single row to the coordinator.
Workaround B: Materialize via CTAS (CREATE TEMP TABLE AS)
If modifying the query logic is difficult, materialize the entire result set into a temporary table first.
CREATE TEMP TABLE agg_result AS
SELECT
event_year,
full_page_url,
percentile_cont(0.75) WITHIN GROUP (ORDER BY lcp) AS lcp
FROM tab1
GROUP BY event_year, full_page_url; -- No LIMIT -> drains to EOS -> no finish signal
SELECT * FROM agg_result LIMIT 10; -- Cheap read from a finished table
Workaround C: Disable Cursor Mode in Client Tools
If using a GUI client like DBeaver, configure the connection to fetch all rows at once, or disable cursor mode. This prevents the client from triggering an early-finish signal.
Workaround D: Disable GPORCA (SET optimizer = off)
Using the Postgres Planner significantly reduces the likelihood of this error. The Planner's plan shape almost entirely closes the race window. However, this is not an absolute guarantee and may result in slower execution times.
Permanent Fix
This issue is targeted to be fixed in a future release of Greenplum Database. Please subscribe to this article (see How to subscribe to a Knowledge Article) to be notified of the fix version.
Additional Information
Silent Data Corruption Risk: The R&D team is fully investigating this behavior. Please be aware that for other ordered-set aggregates (mode, rank, percent_rank, cume_dist, and dense_rank under WITHIN GROUP), this same race condition issue may silently produce wrong results instead of throwing an explicit error. Applying the workarounds above is highly recommended until a permanent kernel patch is released.
Feedback
