[MDL-70055] Support large number of SQL-IN parameters in Postgres - Moodle Tracker

XML

Word

Printable

Details

Type: Improvement
Status: Closed
Priority: Minor
Resolution: Fixed
Affects Version/s: 3.10, 3.11, 4.0
Fix Version/s: 3.11
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 3.11 Branch:
~~MDL-70055~~-MOODLE_311_STABLE
Pull 3.11 Diff URL:
https://github.com/moodle/moodle/compare/MOODLE_311_STABLE...kabalin:MDL-70055-MOODLE_311_STABLE
Pull Master Branch:
~~MDL-70055~~-master
Pull Master 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.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.

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-27071 Oracle cannot cope withan IN operator with more than 1000 conditions in it

Closed

has been marked as being related by

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

Open

will help resolve

MDL-53735 Split 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)

Component watchers:: David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo

Votes:: 0 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

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

Updated:: 06/Jul/22 3:27 PM

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

Fix Release Date:: 17/May/21

Time Tracking

Estimated:

Remaining:

Logged:

4d 3h 46m