Support large number of SQL-IN parameters in Postgres (2024)

Log inSkip to main contentSkip to sidebar

Closed

Export

Details

Type: Improvement
Resolution:Fixed
Priority: Minor
Fix Version/s:3.11
Affects Version/s:3.10, 3.11, 4.0
Component/s:Database SQL/XMLDB
Labels:
- ci
- release_notes

Database:
PostgreSQL
Affected Branches:
MOODLE_310_STABLE, MOODLE_311_STABLE, MOODLE_400_STABLE
Fixed Branches:
MOODLE_311_STABLE
Pull from Repository:
https://github.com/kabalin/moodle.git
Pull Main Branch:
~~MDL-70055~~
-master
Pull Main Diff URL:
https://github.com/moodle/moodle/compare/master...kabalin:MDL-70055-master
Testing Instructions:
Hide
Unittest contains good coverage of all cases, however if regression test is needed, one may use any bulk user action to ensure get_in_or_equal works correctly (they all use $DB->get_in_or_equal($SESSION->bulk_users) as part of users processing). For example:
a. Make sure you are using Postgresql as db engine.
Unit test
$ vendor/bin/phpunit lib/dml/tests/pgsql_native_moodle_database_test.php
Regression test
1. Create test site, "upload users" from csv, so that you have 65000 users in total.
2. Navigate to Bulk user actions
3. Click "Add all"
4. "With selected users..." set "Delete" and click "Go" - No errors expected.
New functionality test
1. Using existing test site, "upload users" from csv, so that you have 65600 users in total.
2. Navigate to Bulk user actions
3. Click "Add all"
4. "With selected users..." set "Delete" and click "Go" - No errors expected.
Show
Unittest contains good coverage of all cases, however if regression test is needed, one may use any bulk user action to ensure get_in_or_equal works correctly (they all use $DB->get_in_or_equal($SESSION->bulk_users) as part of users processing). For example:a. Make sure you are using Postgresql as db engine.Unit test$ vendor/bin/phpunit lib/dml/tests/pgsql_native_moodle_database_test.phpRegression test1. Create test site, "upload users" from csv, so that you have 65000 users in total.2. Navigate to Bulk user actions3. Click "Add all"4. "With selected users..." set "Delete" and click "Go" - No errors expected.New functionality test1. Using existing test site, "upload users" from csv, so that you have 65600 users in total.2. Navigate to Bulk user actions3. Click "Add all"4. "With selected users..." set "Delete" and click "Go" - No errors expected.

Description

When querying/deleting records by bulk, we can get errors when the values array becomes too large (i.e. > 65535 parameters for PostgreSQL). This patch adds a solution for Postgres that allows overriding IN statement limit of 16bit (65535 values) by wrapping items in VALUES list. This only takes place in number of items exceeds 65535, otherwise parent class get_in_or_equal() function is used.

Attachments

Issue Links

has a non-specific relationship to

MDL-27071Oracle cannot cope withan IN operator with more than 1000 conditions in it

Closed

has been marked as being related by

MDL-71433Having trouble performing certain actions with courses with more than 65,000 participants.

Open

MDL-75160Really support large number of SQL-IN parameters in Postgres

Open

will help resolve

MDL-53735Split very large SQL-IN parameters to chunks

Closed

Activity

People

Assignee:: Ruslan Kabalin

Reporter:: Ruslan Kabalin

Peer reviewer:: Paul Holden

Integrator:: Eloy Lafuente (stronk7)

Tester:: CiBoT

Participants:: Andrew Lyons, cameron1729, CiBoT, Eloy Lafuente (stronk7), Paul Holden, Ruslan Kabalin, Sara Arjona (@sarjona)

Votes:: 0Vote for this issue

Watchers:: 9Start watching this issue

Dates

Created:: 29/Oct/20 7:07 PM

Updated:: 27/Jun/23 10:46 AM

Resolved:: 22/Jan/21 11:54 AM

Time Tracking

Estimated:: 0m

Remaining:: 0m

Logged:: 4d 3h 46m

Clockify

Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.

Support large number of SQL-IN parameters in Postgres (2024)

FAQs

What is the maximum number of parameters in PostgreSQL? ›

passing the 65535 parameter limit in postgres, for those bulk data use cases : r/programming.

Get More Info Here ›

What is the limit of bind parameters in PostgreSQL? ›

Work around PostgreSQL's limit of 32767 bind variables per statement · Issue #5701 · jOOQ/jOOQ · GitHub.

Explore More ›

What is the max connection parameter in PostgreSQL? ›

The default is typically 100 connections, but might be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at server start.

Show Me More ›

How many parameters are allowed in a SQL query data source? ›

The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

Learn More ›

How many parameters can be used at a maximum? ›

The number of method parameters is limited to 255 by the definition of a method descriptor (§4.3. 3), where the limit includes one unit for this in the case of instance or interface method invocations.

Discover More Details ›

What is the maximum number of query parameters in SQL server? ›

Microsoft SQL Server has a limit on the number of parameters that a parameterized query can have (2100).

Discover More Details ›

Does PostgreSQL support limit? ›

The simplest and most straightforward way to limit the number of rows returned in PostgreSQL is to use the LIMIT clause. The LIMIT clause allows you to specify the maximum number of rows that you want returned from your query.

What is the maximum size of a query in Postgres? ›

The size restriction for SQL queries in PostgreSQL is determined by the maximum size of a query packet that can be transmitted over the network. postgresql. file. By default, the maximum packet size is 4 MB.

Learn More ›

What is the maximum database size in PostgreSQL? ›

Table K.1. PostgreSQL Limitations

Item	Upper Limit	Comment
database size	unlimited
number of databases	4,294,950,911
relations per database	1,431,650,303
relation size	32 TB	with the default BLCKSZ of 8192 bytes

10 more rows

Read On ›

How many concurrent queries can Postgres handle? ›

This parameter determines the maximum number of concurrent connections to the database server. The default setting is 100 connections. However, it may be lower if your kernel settings do not allow it (as determined during initdb).

Find Out More ›

How many connections are allowed in Postgres? ›

Connection Limits

15 connections are reserved for the superuser to maintain the state and integrity of your database, and 100 connections are available for you and your applications. If the number of connections to the database exceeds the 100-connection limit, new connections fail and return an error.

Explore More ›

What is the maximum number size in Postgres? ›

Integers

Integer type	Length	Applicable range
integer	4 bytes	-2147483648 to 2147483647
smallint	2 bytes	-32768 to 32767
bigint	8 bytes	-9223372036854775808 to 9223372036854775807

Get More Info ›

How many parameters does SQL accept? ›

A procedure can have a maximum of 2100 parameters; each assigned a name, data type, and direction. Optionally, parameters can be assigned default values.

Discover More Details ›

What is the parameter limit in SQL? ›

On SQL Server if the User selects a lot/all of the possible values, this occasionally leads to the following error: Too many parameters were provided in this RPC request. The maximum is 2100.

Find Out More ›

How to use multiple parameters in SQL? ›

To enter multiple parameters in an SQL command within a Logic program, you need to use the ? character to represent the value of each parameter, and the WITH_PARAMS keyword to define the parameters.

What is the max limit in Postgres? ›

Table K.1. PostgreSQL Limitations

Item	Upper Limit	Comment
relation size	32 TB	with the default BLCKSZ of 8192 bytes
rows per table	limited by the number of tuples that can fit onto 4,294,967,295 pages
columns per table	1,600	further limited by tuple size fitting on a single page; see note below
columns in a result set	1,664

10 more rows

What is the maximum number of parameters a function can receive? ›

Theoretically you can set these max stack size to 8192 bits. Each variable takes up 32 bits then you could pass 256 parameters. 8192/32 = 256. There is no maximum limit to pass parameters or arguments to a user defined function.

Explore More ›

What is the maximum number of parameters used in a stored procedure? ›

A procedure can have a maximum of 2100 parameters; each assigned a name, data type, and direction. Optionally, parameters can be assigned default values.

Get More Info Here ›