CODEWITHSUNDEEP
cockroachdb
Yusuff Sodiq
Yusuff Sodiq

Reputation: 945

CockroachDb memory budget constraint

I have a local installation of CockroachDb on my windows PC and when I run a particular select query, I get the following error message:

7143455 bytes requested, 127403581 currently allocated, 134217728 bytes in budget.

I have read a blog post here but I haven't found a solution. I will appreciate a help on how to increase this budget limit.

Upvotes: 1

Views: 536

Answers (1)

Ben Darnell
Ben Darnell

Reputation: 22154

CockroachDB versions between 2.0 and 2.0.2 have a bug in memory accounting for JSONB columns, leading to this error. The bug will be fixed in version 2.0.3, due in mid-June.

As a workaround, you may be able to rewrite this query to be more efficient (This might reduce the memory usage enough to work even with the bug. Even if it doesn't, it'll speed up the query when 2.0.3 is available). If I'm reading your query correctly, this is equivalent to

SELECT ID, JsonData,PrimaryIDs,IsActive,IsDeleted FROM "TableName"
    WHERE LOWER(JsonData->>'Name') LIKE '%transaction%'
    ORDER BY ID OFFSET 0 FETCH NEXT 100 ROWS ONLY

The subquery with ROW_NUMBER() was used with older versions of SQL Server, but since SQL Server 2012, the OFFSET 0 FETCH NEXT N ROWS ONLY version has been available and is more efficient.

The syntax OFFSET 0 FETCH NEXT N ROWS ONLY syntax comes from the SQL standard so it should work with most databases. CockroachDB also supports the LIMIT keyword which is used in MySQL and PostgreSQL for the same purpose.

Upvotes: 3

Related Questions