Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-70055

Support large number of SQL-IN parameters in Postgres

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Integration review in progress
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.10
    • Fix Version/s: None
    • Component/s: Database SQL/XMLDB
    • Labels:
    • Database:
      PostgreSQL
    • 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.
    • Affected Branches:
      MOODLE_310_STABLE
    • Pull 3.10 Branch:
      MDL-70055-MOODLE_310_STABLE
    • Pull Master Branch:
      MDL-70055-master

      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

            Activity

              People

              Assignee:
              kabalin Ruslan Kabalin
              Reporter:
              kabalin Ruslan Kabalin
              Peer reviewer:
              Paul Holden
              Integrator:
              Andrew Nicols
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:

                  Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 4 days
                  4d