[ZBX-6700] Upgrade process from 1.8 to 2.0 is very slow for PostgreSQL Created: 2013 Jun 13 Updated: 2017 May 30 Resolved: 2013 Jun 25 |
|
Status: | Closed |
Project: | ZABBIX BUGS AND ISSUES |
Component/s: | Installation (I) |
Affects Version/s: | 2.0.7rc1 |
Fix Version/s: | 2.0.7rc1, 2.1.0 |
Type: | Incident report | Priority: | Major |
Reporter: | Alexey Pustovalov | Assignee: | Unassigned |
Resolution: | Fixed | Votes: | 0 |
Labels: | postgresql, slow, upgrade | ||
Remaining Estimate: | Not Specified | ||
Time Spent: | Not Specified | ||
Original Estimate: | Not Specified | ||
Environment: |
PostgreSQL |
Issue Links: |
|
Description |
Please look attached procedure for upgrade events table for PostgreSQL database. 1. Current version upgrade takes about 2-3 days. |
Comments |
Comment by Alexey Pustovalov [ 2013 Jun 13 ] |
CREATE OR REPLACE FUNCTION zbx_convert_events() RETURNS SETOF events AS $BODY$ DECLARE prev_triggerid bigint; DECLARE prev_value integer; r RECORD; BEGIN FOR r IN SELECT e.eventid, e.objectid, e.value, t.type as type FROM events e JOIN triggers t ON t.triggerid = e.objectid WHERE e.source = 0 AND e.object = 0 AND e.value IN (0,1) ORDER BY e.objectid, e.clock, e.eventid LOOP IF prev_triggerid IS NULL OR prev_triggerid <> r.objectid THEN prev_value := NULL; END IF; IF r.value = 0 THEN IF prev_value IS NULL OR prev_value = 1 THEN UPDATE events set value_changed = 1 WHERE eventid = r.eventid; END IF; ELSE IF r.type = 1 OR prev_value IS NULL OR prev_value = 0 THEN UPDATE events set value_changed = 1 WHERE eventid = r.eventid; END IF; END IF; prev_value := r.value; prev_triggerid := r.objectid; END LOOP; END; $BODY$ LANGUAGE 'plpgsql' ; |
Comment by Oleg Egorov (Inactive) [ 2013 Jun 17 ] |
(1) SQL Error ERROR: language "plpgsql" does not exist oleg.egorov CLOSED |
Comment by Alexey Pustovalov [ 2013 Jun 17 ] |
try that function. CREATE OR REPLACE FUNCTION zbx_convert_events() RETURNS BOOLEAN AS $$ DECLARE prev_triggerid bigint; DECLARE prev_value integer; r RECORD; BEGIN FOR r IN SELECT e.eventid, e.objectid, e.value, t.type as type FROM events e JOIN triggers t ON t.triggerid = e.objectid WHERE e.source = 0 AND e.object = 0 AND e.value IN (0,1) ORDER BY e.objectid, e.clock, e.eventid LOOP IF prev_triggerid IS NULL OR prev_triggerid <> r.objectid THEN prev_value := NULL; END IF; IF r.value = 0 THEN IF prev_value IS NULL OR prev_value = 1 THEN UPDATE events set value_changed = 1 WHERE eventid = r.eventid; END IF; ELSE IF r.type = 1 OR prev_value IS NULL OR prev_value = 0 THEN UPDATE events set value_changed = 1 WHERE eventid = r.eventid; END IF; END IF; prev_value := r.value; prev_triggerid := r.objectid; END LOOP; RETURN 1; END; $$ LANGUAGE plpgsql; SELECT zbx_convert_events(); DROP FUNCTION zbx_convert_events(); oleg.egorov ERROR ERROR: language "plpgsql" does not exist HINT: Use CREATE LANGUAGE to load the language into the database. ERROR: function zbx_convert_events() does not exist LINE 1: SELECT zbx_convert_events(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. ERROR: function zbx_convert_events() does not exist CREATE LANGUAGE plpgsql; |
Comment by Oleg Egorov (Inactive) [ 2013 Jun 17 ] |
FIXED IN svn://svn.zabbix.com/branches/dev/ZBX-6700 r36404 |
Comment by Eduards Samersovs (Inactive) [ 2013 Jun 25 ] |
(2) Little difference with mysql patch in triggers.sql:31 is used "r.objectid" in place of "r.triggerid". oleg.egorov r.triggerid should be declared, for example in SELECT e.eventid, e.objectid as triggerid, e.value, t.type as type CLOSED |
Comment by Eduards Samersovs (Inactive) [ 2013 Jun 26 ] |
Tested! |
Comment by Oleg Egorov (Inactive) [ 2013 Jun 26 ] |
FIXED IN 2.0.7rc1 r36531, 2.1.0(trunk) r36532 |