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

Support large number of SQL-IN parameters in Postgres

    XMLWordPrintable

Details

    • PostgreSQL
    • MOODLE_310_STABLE, MOODLE_311_STABLE, MOODLE_400_STABLE
    • MOODLE_311_STABLE
    • MDL-70055-MOODLE_311_STABLE
    • MDL-70055-master
    • 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

          Activity

            People

              kabalin Ruslan Kabalin
              kabalin Ruslan Kabalin
              Paul Holden Paul Holden
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              CiBoT CiBoT
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                17/May/21

                Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 4 days, 3 hours, 46 minutes
                  4d 3h 46m