ERROR: XX000: could not find memoization table entry (reproducible) - Mailing list pgsql-bugs

Hello,

 we are getting this error:

ERROR:  XX000: could not find memoization table entry

 reproducibly, on two servers and two postgresql versions:

psql (PostgreSQL) 17.6 (Ubuntu 17.6-2.pgdg22.04+1)

psql (PostgreSQL) 18.0 (Ubuntu 18.0-1.pgdg24.04+3)

 However I can NOT reproduce the issue on my laptop (psql (PostgreSQL) 17.6, Fedora linux 42).

We can reproduce this issue everytime on these hosts with this postgresql dump (created with '-F c'):

And also this SQL query:

SELECT DISTINCT ON(c.col3)

c.col3, c.col2 FROM

table1 c

INNER JOIN table2 tea ON

tea.col1 = c.col3 AND

tea.col2 <= c.col1 AND

tea.col3 >= c.col1 AND

tea.col4 = 't' AND tea.col5 = 'f'

WHERE

c.col1 >= '2025-10-01' AND

c.col1 <= '2025-10-31' AND

c.col3 = any(ARRAY[567,568,569,561,570,522,571,155,515,572,450,474,484,467,557,437,85,383,195,82,21,17,95,96,147,112,118,269,508,472,86,88,94,14,105,216,217,218,219,220,80,29,6,52,150,501,548,18,504,171,199,156,170,238,139,249,39,119,128,253,197,198,145,100,194,135,229,204,210,51,283,48,293,505,401,214,476,307,309,8,542,306,319,320,322,321,323,324,325,326,327,328,329,330,331,332,334,336,339,340,341,109,137,509,312,512,142,73,154,344,514,516,372,369,288,520,521,305,190,510,360,265,333,345,549,473,215,271,347,130,159,4,551,196,433,7,552,165,511,518,523,524,525,529,530,531,533,534,111,252,250,191,127,377,188,385,468,262,538,424,554,555,426,532,391,98,417,172,558,403,365,272,300,131,294,536,291,409,454,384,71,559,393,138,355,180,539,133,541,543,544,546,540,153,285,513,359,318,92,176,115,456,114,335,45,482,547,74,390,376,489,490,491,483,492,374,427,299,494,495,206,348,144,418,47,246,255,486,475,496,416,479,441,301,497,545,152,179,292,42,464,126,478,498,308,175,174,158,40,41,181,178,227,108,443,422,213,166,346,168,354,366,406,435,440,447,392,452,429,296,453,167,451,460,461,160,87,162,314,517,286,200,260,466,203,442,103,560,562,353,563,564,565,566,1,23,25,35,68]) AND

c.col4 <= 2204007 AND

c.col5 = 'f'

ORDER BY

c.col3, c.col1, c.col4 DESC;

When the issue occurs... With \set verbose, psql displays:

ERROR:  XX000: could not find memoization table entry
LOCATION:  cache_reduce_memory, nodeMemoize.c:484

Explain analyse fails with the same error, hower a simple "explain" on an host exhibiting the issue outputs:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Unique  (cost=296161.37..296734.58 rows=495 width=16)

   ->  Sort  (cost=296161.37..296447.98 rows=114642 width=16)

         Sort Key: c.col3, c.col1, c.col4 DESC

         ->  Nested Loop  (cost=0.45..286527.56 rows=114642 width=16)

               ->  Seq Scan on table2 tea  (cost=0.00..26.27 rows=1327 width=12)

                     Filter: (col4 AND (NOT col5))

               ->  Memoize  (cost=0.45..515.00 rows=84 width=16)

                     Cache Key: tea.col2, tea.col3, tea.col1

                     Cache Mode: binary

                     ->  Index Scan using table1_three on table1 c  (cost=0.44..514.99 rows=84 width=16)

                           Index Cond: ((col3 = tea.col1) AND (col3 = ANY ('{567,568,569,561,570,522,571,155,515,572,450,474,484,467,557,437,85,383,195,82,21,17,95,96,147,112,118,269,508,472,86,88,94,14,105,216,217,218,219,220,80,29,6,52,150,501,548,18,504,171,199,156,170,238,139,249,39,119,128,253,197,198,145,100,194,135,229,204,210,51,283,48,293,505,401,214,476,307,309,8,542,306,319,320,322,321,323,324,325,326,327,328,329,330,331,332,334,336,339,340,341,109,137,509,312,512,142,73,154,344,514,516,372,369,288,520,521,305,190,510,360,265,333,345,549,473,215,271,347,130,159,4,551,196,433,7,552,165,511,518,523,524,525,529,530,531,533,534,111,252,250,191,127,377,188,385,468,262,538,424,554,555,426,532,391,98,417,172,558,403,365,272,300,131,294,536,291,409,454,384,71,559,393,138,355,180,539,133,541,543,544,546,540,153,285,513,359,318,92,176,115,456,114,335,45,482,547,74,390,376,489,490,491,483,492,374,427,299,494,495,206,348,144,418,47,246,255,486,475,496,416,479,441,301,497,545,152,179,292,42,464,126,478,498,308,175,174,158,40,41,181,178,227,108,443,422,213,166,346,168,354,366,406,435,440,447,392,452,429,296,453,167,451,460,461,160,87,162,314,517,286,200,260,466,203,442,103,560,562,353,563,564,565,566,1,23,25,35,68}'::integer[])) AND (col1 >= tea.col2) AND (col1 <= tea.col3) AND (col1 >= '2025-10-01'::date) AND (col1 <= '2025-10-31'::date))

                           Filter: ((NOT col5) AND (col4 <= 2204007))

 JIT:

   Functions: 18

   Options: Inlining false, Optimization false, Expressions true, Deforming true

(15 rows)

Also relevant, in the log file we see the following:

2025-10-22 07:20:19.529 GMT,"postgres","flatman_test7",349111,"[local]",68f885b0.553b7,4,"SELECT",2025-10-22 07:20:16 GMT,75/232,0,ERROR,XX000,"could not find memoization table entry",,,,,,"SELECT DISTINCT ON(c.col3)

c.col3, c.col2 FROM

table1 c

INNER JOIN table2 tea ON

tea.col1 = c.col3 AND

tea.col2 <= c.col1 AND

tea.col3 >= c.col1 AND

tea.col4 = 't' AND tea.col5 = 'f'

WHERE

c.col1 >= '2025-10-01' AND

c.col1 <= '2025-10-31' AND

c.col3 = any(ARRAY[567,568,569,561,570,522,571,155,515,572,450,474,484,467,557,437,85,383,195,82,21,17,95,96,147,112,118,269,508,472,86,88,94,14,105,216,217,218,219,220,80,29,6,52,150,501,548,18,504,171,199,156,170,238,139,249,39,119,128,253,197,198,145,100,194,135,229,204,210,51,283

,48,293,505,401,214,476,307,309,8,542,306,319,320,322,321,323,324,325,326,327,328,329,330,331,332,334,336,339,340,341,109,137,509,312,512,142,73,154,344,514,516,372,369,288,520,521,305,190,510,360,265,333,345,549,473,215,271,347,130,159,4,551,196,433,7,552,165,511,518,523,524,525,529

,530,531,533,534,111,252,250,191,127,377,188,385,468,262,538,424,554,555,426,532,391,98,417,172,558,403,365,272,300,131,294,536,291,409,454,384,71,559,393,138,355,180,539,133,541,543,544,546,540,153,285,513,359,318,92,176,115,456,114,335,45,482,547,74,390,376,489,490,491,483,492,374,

427,299,494,495,206,348,144,418,47,246,255,486,475,496,416,479,441,301,497,545,152,179,292,42,464,126,478,498,308,175,174,158,40,41,181,178,227,108,443,422,213,166,346,168,354,366,406,435,440,447,392,452,429,296,453,167,451,460,461,160,87,162,314,517,286,200,260,466,203,442,103,560,5

62,353,563,564,565,566,1,23,25,35,68]) AND

c.col4 <= 2204007 AND

c.col5 = 'f'

ORDER BY

c.col3, c.col1, c.col4 DESC;",,,"psql","client backend",,0

2025-10-22 07:20:19.532 GMT,"postgres","flatman_test7",349111,"[local]",68f885b0.553b7,5,"SELECT",2025-10-22 07:20:16 GMT,75/0,0,LOG,00000,"temporary file: path ""base/pgsql_tmp/pgsql_tmp349111.0"", size 7593984",,,,,,,,,"psql","client backend",,0

It may be important that everytime we hit the issue, a temporary file is created.

Regards,

Emmanuel