Pulse notify_users scheduled task fails with "operator does not exist: bigint = character varying

[lehung0108]

Moodle 4.4.5: Pulse notify_users scheduled task fails with "operator does not exist: bigint = character varying"

Issue Description

When running Moodle’s Send notification scheduled task (\pulseaction_notification\task\notify_users) on Moodle 4.4.5, the task fails with an error similar to:

Root Cause

• Type Mismatch in PostgreSQL:
  PostgreSQL is strict about comparing different data types. Here, mdl_course_modules.id is a bigint, while ni.dynamiccontent (from mdl_pulseaction_notification_ins) is a varchar.
• Query Fails on Join Condition:
  mod/pulse/actions/notification/classes/schedule.php => protected function get_scheduled_records($userid=null)
  The query attempts cm.id = ni.dynamiccontent, which fails in Postgres because bigint = character varying is not allowed without an explicit cast.

Affected Code Snippet

LEFT JOIN mdl_course_modules cm ON cm.id = ni.dynamiccontent
ALTER TABLE mdl_pulseaction_notification_ins
    ALTER COLUMN dynamiccontent TYPE bigint
    USING dynamiccontent::bigint;
LEFT JOIN mdl_course_modules cm ON cm.id = ni.dynamiccontent

Detail error message

Send notification
Execute scheduled task: Send notification (pulseaction_notification\task\notify_users)
... started 14:46:51. Current memory use 19.6 MB.
... used 1 dbqueries
... used 0.013933897018433 seconds
Scheduled task failed: Send notification (pulseaction_notification\task\notify_users),Error reading from database (ERROR:  operator does not exist: bigint = character varying
LINE 11: ...         LEFT JOIN mdl_course_modules cm ON cm.id = ni.dynam...
                                                              ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
SELECT ns.id AS id, ns.id AS ns_id, ns.instanceid AS ns_instanceid, ns.userid AS ns_userid, ns.type AS ns_type, ns.notifycount AS ns_notifycount, ns.scheduletime AS ns_scheduletime, ns.notifiedtime AS ns_notifiedtime, ns.status AS ns_status, ns.suppressreached AS ns_suppressreached, ns.timecreated AS ns_timecreated, ai.id AS ai_id, ai.templateid AS ai_templateid, ai.courseid AS ai_courseid, ai.status AS ai_status, ai.timemodified AS ai_timemodified, pat.id AS pat_id, pat.title AS pat_title, pat.reference AS pat_reference, pat.visible AS pat_visible, pat.notes AS pat_notes, pat.status AS pat_status, pat.tags AS pat_tags, pat.tenants AS pat_tenants, pat.categories AS pat_categories, pat.triggerconditions AS pat_triggerconditions, pat.triggeroperator AS pat_triggeroperator, pat.timemodified AS pat_timemodified, pati.id AS pati_id, pati.instanceid AS pati_instanceid, pati.title AS pati_title, pati.insreference AS pati_insreference, pati.notes AS pati_notes, pati.tags AS pati_tags, pati.tenants AS pati_tenants, pati.categories AS pati_categories, pati.triggerconditions AS pati_triggerconditions, pati.triggeroperator AS pati_triggeroperator, pati.timemodified AS pati_timemodified, ni.id AS ni_id, ni.instanceid AS ni_instanceid, ni.sender AS ni_sender, ni.senderemail AS ni_senderemail, ni.notifyinterval AS ni_notifyinterval, ni.week AS ni_week, ni.month AS ni_month, ni.time AS ni_time, ni.notifydelay AS ni_notifydelay, ni.delayduration AS ni_delayduration, ni.suppress AS ni_suppress, ni.suppressoperator AS ni_suppressoperator, ni.notifylimit AS ni_notifylimit, ni.recipients AS ni_recipients, ni.cc AS ni_cc, ni.bcc AS ni_bcc, ni.subject AS ni_subject, ni.headercontent AS ni_headercontent, ni.staticcontent AS ni_staticcontent, ni.dynamiccontent AS ni_dynamiccontent, ni.contentlength AS ni_contentlength, ni.contenttype AS ni_contenttype, ni.chapterid AS ni_chapterid, ni.footercontent AS ni_footercontent, ni.timemodified AS ni_timemodified, an.id AS an_id, an.templateid AS an_templateid, an.sender AS an_sender, an.senderemail AS an_senderemail, an.notifyinterval AS an_notifyinterval, an.week AS an_week, an.month AS an_month, an.time AS an_time, an.notifydelay AS an_notifydelay, an.delayduration AS an_delayduration, an.notifylimit AS an_notifylimit, an.recipients AS an_recipients, an.cc AS an_cc, an.bcc AS an_bcc, an.subject AS an_subject, an.headercontent AS an_headercontent, an.headercontentformat AS an_headercontentformat, an.staticcontent AS an_staticcontent, an.staticcontentformat AS an_staticcontentformat, an.dynamiccontent AS an_dynamiccontent, an.contentlength AS an_contentlength, an.contenttype AS an_contenttype, an.footercontent AS an_footercontent, an.footercontentformat AS an_footercontentformat, an.timemodified AS an_timemodified, con.status AS con_status, con.additional AS con_additional, con.isoverridden AS con_isoverridden, ue.id AS ue_id, ue.auth AS ue_auth, ue.confirmed AS ue_confirmed, ue.policyagreed AS ue_policyagreed, ue.deleted AS ue_deleted, ue.suspended AS ue_suspended, ue.mnethostid AS ue_mnethostid, ue.username AS ue_username, ue.password AS ue_password, ue.idnumber AS ue_idnumber, ue.firstname AS ue_firstname, ue.lastname AS ue_lastname, ue.email AS ue_email, ue.emailstop AS ue_emailstop, ue.phone1 AS ue_phone1, ue.phone2 AS ue_phone2, ue.institution AS ue_institution, ue.department AS ue_department, ue.address AS ue_address, ue.city AS ue_city, ue.country AS ue_country, ue.lang AS ue_lang, ue.theme AS ue_theme, ue.timezone AS ue_timezone, ue.firstaccess AS ue_firstaccess, ue.lastaccess AS ue_lastaccess, ue.lastlogin AS ue_lastlogin, ue.currentlogin AS ue_currentlogin, ue.lastip AS ue_lastip, ue.secret AS ue_secret, ue.picture AS ue_picture, ue.description AS ue_description, ue.descriptionformat AS ue_descriptionformat, ue.mailformat AS ue_mailformat, ue.maildigest AS ue_maildigest, ue.maildisplay AS ue_maildisplay, ue.autosubscribe AS ue_autosubscribe, ue.trackforums AS ue_trackforums, ue.timecreated AS ue_timecreated, ue.timemodified AS ue_timemodified, ue.trustbitmask AS ue_trustbitmask, ue.imagealt AS ue_imagealt, ue.lastnamephonetic AS ue_lastnamephonetic, ue.firstnamephonetic AS ue_firstnamephonetic, ue.middlename AS ue_middlename, ue.alternatename AS ue_alternatename, ue.calendartype AS ue_calendartype, ue.moodlenetprofile AS ue_moodlenetprofile, c.id AS c_id, c.category AS c_category, c.sortorder AS c_sortorder, c.fullname AS c_fullname, c.shortname AS c_shortname, c.idnumber AS c_idnumber, c.summary AS c_summary, c.summaryformat AS c_summaryformat, c.format AS c_format, c.showgrades AS c_showgrades, c.newsitems AS c_newsitems, c.startdate AS c_startdate, c.marker AS c_marker, c.maxbytes AS c_maxbytes, c.legacyfiles AS c_legacyfiles, c.showreports AS c_showreports, c.visible AS c_visible, c.visibleold AS c_visibleold, c.groupmode AS c_groupmode, c.groupmodeforce AS c_groupmodeforce, c.defaultgroupingid AS c_defaultgroupingid, c.lang AS c_lang, c.theme AS c_theme, c.timecreated AS c_timecreated, c.timemodified AS c_timemodified, c.requested AS c_requested, c.enablecompletion AS c_enablecompletion, c.completionnotify AS c_completionnotify, c.calendartype AS c_calendartype, c.cacherev AS c_cacherev, c.enddate AS c_enddate, c.relativedatesmode AS c_relativedatesmode, c.originalcourseid AS c_originalcourseid, c.downloadcontent AS c_downloadcontent, c.showactivitydates AS c_showactivitydates, c.showcompletionconditions AS c_showcompletionconditions, c.pdfexportfont AS c_pdfexportfont, ctx.id AS ctx_id, ctx.contextlevel AS ctx_contextlevel, ctx.instanceid AS ctx_instanceid, ctx.path AS ctx_path, ctx.depth AS ctx_depth, ctx.locked AS ctx_locked, cm.id AS cm_id, cm.course AS cm_course, cm.module AS cm_module, cm.instance AS cm_instance, md.name AS md_name FROM mdl_pulseaction_notification_sch ns
            JOIN mdl_pulse_autoinstances ai ON ai.id = ns.instanceid
            JOIN mdl_pulse_autotemplates pat ON pat.id = ai.templateid
            JOIN mdl_pulse_autotemplates_ins pati ON pati.instanceid = ai.id
            JOIN mdl_pulseaction_notification_ins ni ON ni.instanceid = ns.instanceid
            JOIN mdl_pulseaction_notification an ON an.templateid = ai.templateid
            JOIN mdl_user ue ON ue.id = ns.userid
            JOIN mdl_course c ON c.id = ai.courseid
            JOIN mdl_context ctx ON ctx.instanceid = c.id AND ctx.contextlevel = 50
            LEFT JOIN mdl_pulse_condition_overrides con ON con.instanceid = pati.instanceid AND con.triggercondition = 'session'
            LEFT JOIN mdl_course_modules cm ON cm.id = ni.dynamiccontent
            LEFT JOIN mdl_modules md ON md.id = cm.module
            JOIN (
                SELECT DISTINCT eu1_u.id, ej1_e.courseid, COUNT(ej1_ue.enrolid) AS activeenrolment
                    FROM mdl_user eu1_u
                    JOIN mdl_user_enrolments ej1_ue ON ej1_ue.userid = eu1_u.id
                    JOIN mdl_enrol ej1_e ON (ej1_e.id = ej1_ue.enrolid)
                WHERE 1 = 1 AND ej1_ue.status = 0
                AND (ej1_ue.timestart = 0 OR ej1_ue.timestart <= $1)
                AND (ej1_ue.timeend = 0 OR ej1_ue.timeend > $2)
                GROUP BY eu1_u.id, ej1_e.courseid
            ) active_enrols ON active_enrols.id = ue.id AND active_enrols.courseid = c.id
            WHERE ns.status = $3 AND ai.status <> 0
            AND active_enrols.activeenrolment <> 0
            AND c.visible = 1
            AND c.startdate <= $4 AND  (c.enddate = 0 OR c.enddate >= $5)
            AND ue.deleted = 0 AND ue.suspended = 0
            AND ns.suppressreached = 0 AND ns.scheduletime <= $6  ORDER BY ns.timecreated ASC LIMIT 100
[array (
  0 => 1737038811,
  1 => 1737038811,
  2 => 2,
  3 => 1737038811,
  4 => 1737038811,
  5 => 1737038811,
)])
Debug info:
ERROR:  operator does not exist: bigint = character varying
LINE 11: ...         LEFT JOIN mdl_course_modules cm ON cm.id = ni.dynam...
                                                              ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
SELECT ns.id AS id, ns.id AS ns_id, ns.instanceid AS ns_instanceid, ns.userid AS ns_userid, ns.type AS ns_type, ns.notifycount AS ns_notifycount, ns.scheduletime AS ns_scheduletime, ns.notifiedtime AS ns_notifiedtime, ns.status AS ns_status, ns.suppressreached AS ns_suppressreached, ns.timecreated AS ns_timecreated, ai.id AS ai_id, ai.templateid AS ai_templateid, ai.courseid AS ai_courseid, ai.status AS ai_status, ai.timemodified AS ai_timemodified, pat.id AS pat_id, pat.title AS pat_title, pat.reference AS pat_reference, pat.visible AS pat_visible, pat.notes AS pat_notes, pat.status AS pat_status, pat.tags AS pat_tags, pat.tenants AS pat_tenants, pat.categories AS pat_categories, pat.triggerconditions AS pat_triggerconditions, pat.triggeroperator AS pat_triggeroperator, pat.timemodified AS pat_timemodified, pati.id AS pati_id, pati.instanceid AS pati_instanceid, pati.title AS pati_title, pati.insreference AS pati_insreference, pati.notes AS pati_notes, pati.tags AS pati_tags, pati.tenants AS pati_tenants, pati.categories AS pati_categories, pati.triggerconditions AS pati_triggerconditions, pati.triggeroperator AS pati_triggeroperator, pati.timemodified AS pati_timemodified, ni.id AS ni_id, ni.instanceid AS ni_instanceid, ni.sender AS ni_sender, ni.senderemail AS ni_senderemail, ni.notifyinterval AS ni_notifyinterval, ni.week AS ni_week, ni.month AS ni_month, ni.time AS ni_time, ni.notifydelay AS ni_notifydelay, ni.delayduration AS ni_delayduration, ni.suppress AS ni_suppress, ni.suppressoperator AS ni_suppressoperator, ni.notifylimit AS ni_notifylimit, ni.recipients AS ni_recipients, ni.cc AS ni_cc, ni.bcc AS ni_bcc, ni.subject AS ni_subject, ni.headercontent AS ni_headercontent, ni.staticcontent AS ni_staticcontent, ni.dynamiccontent AS ni_dynamiccontent, ni.contentlength AS ni_contentlength, ni.contenttype AS ni_contenttype, ni.chapterid AS ni_chapterid, ni.footercontent AS ni_footercontent, ni.timemodified AS ni_timemodified, an.id AS an_id, an.templateid AS an_templateid, an.sender AS an_sender, an.senderemail AS an_senderemail, an.notifyinterval AS an_notifyinterval, an.week AS an_week, an.month AS an_month, an.time AS an_time, an.notifydelay AS an_notifydelay, an.delayduration AS an_delayduration, an.notifylimit AS an_notifylimit, an.recipients AS an_recipients, an.cc AS an_cc, an.bcc AS an_bcc, an.subject AS an_subject, an.headercontent AS an_headercontent, an.headercontentformat AS an_headercontentformat, an.staticcontent AS an_staticcontent, an.staticcontentformat AS an_staticcontentformat, an.dynamiccontent AS an_dynamiccontent, an.contentlength AS an_contentlength, an.contenttype AS an_contenttype, an.footercontent AS an_footercontent, an.footercontentformat AS an_footercontentformat, an.timemodified AS an_timemodified, con.status AS con_status, con.additional AS con_additional, con.isoverridden AS con_isoverridden, ue.id AS ue_id, ue.auth AS ue_auth, ue.confirmed AS ue_confirmed, ue.policyagreed AS ue_policyagreed, ue.deleted AS ue_deleted, ue.suspended AS ue_suspended, ue.mnethostid AS ue_mnethostid, ue.username AS ue_username, ue.password AS ue_password, ue.idnumber AS ue_idnumber, ue.firstname AS ue_firstname, ue.lastname AS ue_lastname, ue.email AS ue_email, ue.emailstop AS ue_emailstop, ue.phone1 AS ue_phone1, ue.phone2 AS ue_phone2, ue.institution AS ue_institution, ue.department AS ue_department, ue.address AS ue_address, ue.city AS ue_city, ue.country AS ue_country, ue.lang AS ue_lang, ue.theme AS ue_theme, ue.timezone AS ue_timezone, ue.firstaccess AS ue_firstaccess, ue.lastaccess AS ue_lastaccess, ue.lastlogin AS ue_lastlogin, ue.currentlogin AS ue_currentlogin, ue.lastip AS ue_lastip, ue.secret AS ue_secret, ue.picture AS ue_picture, ue.description AS ue_description, ue.descriptionformat AS ue_descriptionformat, ue.mailformat AS ue_mailformat, ue.maildigest AS ue_maildigest, ue.maildisplay AS ue_maildisplay, ue.autosubscribe AS ue_autosubscribe, ue.trackforums AS ue_trackforums, ue.timecreated AS ue_timecreated, ue.timemodified AS ue_timemodified, ue.trustbitmask AS ue_trustbitmask, ue.imagealt AS ue_imagealt, ue.lastnamephonetic AS ue_lastnamephonetic, ue.firstnamephonetic AS ue_firstnamephonetic, ue.middlename AS ue_middlename, ue.alternatename AS ue_alternatename, ue.calendartype AS ue_calendartype, ue.moodlenetprofile AS ue_moodlenetprofile, c.id AS c_id, c.category AS c_category, c.sortorder AS c_sortorder, c.fullname AS c_fullname, c.shortname AS c_shortname, c.idnumber AS c_idnumber, c.summary AS c_summary, c.summaryformat AS c_summaryformat, c.format AS c_format, c.showgrades AS c_showgrades, c.newsitems AS c_newsitems, c.startdate AS c_startdate, c.marker AS c_marker, c.maxbytes AS c_maxbytes, c.legacyfiles AS c_legacyfiles, c.showreports AS c_showreports, c.visible AS c_visible, c.visibleold AS c_visibleold, c.groupmode AS c_groupmode, c.groupmodeforce AS c_groupmodeforce, c.defaultgroupingid AS c_defaultgroupingid, c.lang AS c_lang, c.theme AS c_theme, c.timecreated AS c_timecreated, c.timemodified AS c_timemodified, c.requested AS c_requested, c.enablecompletion AS c_enablecompletion, c.completionnotify AS c_completionnotify, c.calendartype AS c_calendartype, c.cacherev AS c_cacherev, c.enddate AS c_enddate, c.relativedatesmode AS c_relativedatesmode, c.originalcourseid AS c_originalcourseid, c.downloadcontent AS c_downloadcontent, c.showactivitydates AS c_showactivitydates, c.showcompletionconditions AS c_showcompletionconditions, c.pdfexportfont AS c_pdfexportfont, ctx.id AS ctx_id, ctx.contextlevel AS ctx_contextlevel, ctx.instanceid AS ctx_instanceid, ctx.path AS ctx_path, ctx.depth AS ctx_depth, ctx.locked AS ctx_locked, cm.id AS cm_id, cm.course AS cm_course, cm.module AS cm_module, cm.instance AS cm_instance, md.name AS md_name FROM mdl_pulseaction_notification_sch ns
            JOIN mdl_pulse_autoinstances ai ON ai.id = ns.instanceid
            JOIN mdl_pulse_autotemplates pat ON pat.id = ai.templateid
            JOIN mdl_pulse_autotemplates_ins pati ON pati.instanceid = ai.id
            JOIN mdl_pulseaction_notification_ins ni ON ni.instanceid = ns.instanceid
            JOIN mdl_pulseaction_notification an ON an.templateid = ai.templateid
            JOIN mdl_user ue ON ue.id = ns.userid
            JOIN mdl_course c ON c.id = ai.courseid
            JOIN mdl_context ctx ON ctx.instanceid = c.id AND ctx.contextlevel = 50
            LEFT JOIN mdl_pulse_condition_overrides con ON con.instanceid = pati.instanceid AND con.triggercondition = 'session'
            LEFT JOIN mdl_course_modules cm ON cm.id = ni.dynamiccontent
            LEFT JOIN mdl_modules md ON md.id = cm.module
            JOIN (
                SELECT DISTINCT eu1_u.id, ej1_e.courseid, COUNT(ej1_ue.enrolid) AS activeenrolment
                    FROM mdl_user eu1_u
                    JOIN mdl_user_enrolments ej1_ue ON ej1_ue.userid = eu1_u.id
                    JOIN mdl_enrol ej1_e ON (ej1_e.id = ej1_ue.enrolid)
                WHERE 1 = 1 AND ej1_ue.status = 0
                AND (ej1_ue.timestart = 0 OR ej1_ue.timestart <= $1)
                AND (ej1_ue.timeend = 0 OR ej1_ue.timeend > $2)
                GROUP BY eu1_u.id, ej1_e.courseid
            ) active_enrols ON active_enrols.id = ue.id AND active_enrols.courseid = c.id
            WHERE ns.status = $3 AND ai.status <> 0
            AND active_enrols.activeenrolment <> 0
            AND c.visible = 1
            AND c.startdate <= $4 AND  (c.enddate = 0 OR c.enddate >= $5)
            AND ue.deleted = 0 AND ue.suspended = 0
            AND ns.suppressreached = 0 AND ns.scheduletime <= $6  ORDER BY ns.timecreated ASC LIMIT 100
[array (
  0 => 1737038811,
  1 => 1737038811,
  2 => 2,
  3 => 1737038811,
  4 => 1737038811,
  5 => 1737038811,
)]
Backtrace:
* line 293 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
* line 358 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->read_slave_query_end()
* line 1056 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
* line 314 of /mod/pulse/actions/notification/classes/schedule.php: call to pgsql_native_moodle_database->get_records_sql()
* line 144 of /mod/pulse/actions/notification/classes/schedule.php: call to pulseaction_notification\schedule->get_scheduled_records()
* line 55 of /mod/pulse/actions/notification/classes/task/notify_users.php: call to pulseaction_notification\schedule->send_scheduled_notification()
* line 458 of /lib/classes/cron.php: call to pulseaction_notification\task\notify_users->execute()
* line 194 of /admin/cli/scheduled_task.php: call to core\cron::run_inner_scheduled_task()