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

Support large number of SQL-IN parameters in Postgres

XMLWordPrintable

    • PostgreSQL
    • MOODLE_310_STABLE, MOODLE_311_STABLE, MOODLE_400_STABLE
    • 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.

      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.

            kabalin Ruslan Kabalin
            kabalin Ruslan Kabalin
            Paul Holden Paul Holden
            Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
            CiBoT CiBoT
            Votes:
            0 Vote for this issue
            Watchers:
            9 Start watching this issue

              Created:
              Updated:
              Resolved:

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

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