Re: ERROR: invalid memory alloc request size 1073741824 - Mailing list pgsql-general

Hi,

We've upgraded to PostgreSQL 11.5 (postgresql.org rhel 6 rpm) and I have 
had another occurrence of this invalid alloc of 1GB. Apologies for never 
providing a query plan when discussing this two years ago; we decided to 
move to a newer PostgreSQL to see if the issue went away but took a 
while to complete the move.

The invalid alloc still only occurs occasionally every few months on a 
query that we run every minute; so unfortunately we still don't have a 
contained reproducible test case.

This is the SQL we are running with a query plan - the query plan is 
from an new empty database so the planner has no stats.

CREATE OR REPLACE FUNCTION
create_table()
RETURNS TABLE (a INTEGER, b INTEGER)
AS $$
     UPDATE y
     SET c = true,
         d = false
     WHERE e IS NOT true
         AND f IS NOT true
         AND g = 1
         AND h = 0
         AND i = 0
         AND (j IS NULL
             OR j > 0)
     RETURNING y.a, y.b;
$$ LANGUAGE SQL;

-- Prepared statement (PQprepare)
CREATE TEMPORARY TABLE temp_table AS
SELECT * FROM create_table()

-- Prepared statement (PQprepare)
SELECT y.a, y.b,
      x.k,
      x.l,
      y.m,
      y.n,
      y.o
  FROM temp_table
      INNER JOIN y ON temp_table.b = y.b
          AND temp_table.a = y.a
      INNER JOIN x ON x.a = y.a

-- The two prepared statements are executed one after another
-- in the order shown many times an hour.

The query plan for the second prepared statement is:

  Nested Loop  (cost=17.14..64.38 rows=16 width=112)
    ->  Hash Join  (cost=17.00..61.47 rows=16 width=80)
          Hash Cond: ((temp_table.b = y.b) AND (temp_table.a = y.a))
          ->  Seq Scan on temp_table  (cost=0.00..32.60 rows=2260 width=8)
          ->  Hash  (cost=12.80..12.80 rows=280 width=76)
                ->  Seq Scan on y  (cost=0.00..12.80 rows=280 width=76)
    ->  Index Scan using x_pkey on x  (cost=0.14..0.18 rows=1 width=40)
          Index Cond: (a = temp_table.a)

Thanks,
Stefan

On 31/01/2018 21:23, Tomas Vondra wrote:
> 
> 
> On 01/31/2018 09:51 PM, Jan Wieck wrote:
>>
>>
>> On Wed, Jan 31, 2018 at 12:32 PM, Stefan Blanke
>> <stefan.blanke@framestore.com <mailto:stefan.blanke@framestore.com>> wrote:
>>
>>      >
>>      > I'll bet you it's not that. It's quite unlikely that would fail with
>>      > exactly 1GB request size. It seems much more like a buffer that we keep
>>      > to be power of 2. The question is which one.
>>
>>      I had dismissed corruption before writing in. It's exactly 1GB every
>>      time this has happened - and we can dump the full dataset
>>      periodically without issue.
>>
>>      >> I have my money on a corrupted TOAST entry. Is this happening on
>>      >> trustworthy hardware or beige box with no ECC or RAID?
>>
>>      It's good quality commercial hardware in our colo - no exactly sure
>>      what.
>>
>>
>> If it is a sporadic issue and you can dump the full dataset, then I just
>> lost my money (Tomas, you coming to PGConf in Jersey City?).
>>
> 
> Unfortunately no, but I'm sure there will be other opportunities to buy
> me a beer ;-) Like pgcon, for example.
> 
>>
>> But then, if this is a plain COPY to stdout ... I am wondering what
>> could possibly be in that path that wants to allocate 1GB. Or is this
>> not so plain but rather a COPY ... SELECT ...?
>>
> 
> That's what I've been guessing, and why I was asking for a query plan.
> 
> 
> regards
>