On Tuesday, December 18, 2018 2:59 PM, I wrote that I thought I had a solution to the phantom packages with Ubuntu that I hadn't tested yet.
OK, there were a few typos and syntax errors, but this corrected stored proc appears to work well. SET search_path = rpm, pg_catalog; create or replace FUNCTION rpmstrcmp (string1 IN VARCHAR, string2 IN VARCHAR) RETURNS INTEGER as $$ declare str1 VARCHAR := string1; str2 VARCHAR := string2; digits VARCHAR(10) := '0123456789'; lc_alpha VARCHAR(27) := 'abcdefghijklmnopqrstuvwxyz'; uc_alpha VARCHAR(27) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'; alpha VARCHAR(54) := lc_alpha || uc_alpha; one VARCHAR; two VARCHAR; sep1 VARCHAR; sep2 VARCHAR; isnum BOOLEAN; BEGIN if str1 is NULL or str2 is NULL then RAISE EXCEPTION 'VALUE_ERROR.'; end if; if str1 = str2 then return 0; end if; one := str1; two := str2; <<segment_loop>> while one <> '' and two <> '' loop declare segm1 VARCHAR; segm2 VARCHAR; begin sep1 := ''; sep2 := ''; --DBMS_OUTPUT.PUT_LINE('Params: ' || one || ',' || two); -- Pull out all separating non-alphanum characters while one <> '' and not rpm.isalphanum(one) loop sep1 := sep1 || substr(one, 1, 1); one := substr(one, 2); end loop; while two <> '' and not rpm.isalphanum(two) loop sep2 := sep2 || substr(two, 1, 1); two := substr(two, 2); end loop; str1 := one; str2 := two; if str1 <> '' and rpm.isdigit(str1) then str1 := ltrim(str1, digits); str2 := ltrim(str2, digits); isnum := true; else str1 := ltrim(str1, alpha); str2 := ltrim(str2, alpha); isnum := false; end if; if str1 <> '' then segm1 := substr(one, 1, length(one) - length(str1)); else segm1 := one; end if; if str2 <> '' then segm2 := substr(two, 1, length(two) - length(str2)); else segm2 := two; end if; -- if one of the separators is for a point subversion indicator and the other isn't, then the point subversion is considered more recent. if isnum and sep1 <> '' and sep2 <> '' then if sep1 = '.' and sep2 <> '.' then return 1; elsif sep2 = '.' and sep1 <> '.' then return -1; end if; end if; if segm1 = '' then return -1; end if; /* arbitrary */ if segm2 = '' then if isnum then return 1; else return -1; end if; end if; if isnum then segm1 := ltrim(segm1, '0'); segm2 := ltrim(segm2, '0'); if segm1 = '' and segm2 <> '' then return -1; end if; if segm1 <> '' and segm2 = '' then return 1; end if; if length(segm1) > length(segm2) then return 1; end if; if length(segm2) > length(segm1) then return -1; end if; end if; if segm1 < segm2 then return -1; end if; if segm1 > segm2 then return 1; end if; one := str1; two := str2; end; end loop segment_loop; if one = '' and two = '' then return 0; end if; if one = '' then return -1; end if; return 1; END ; $$ language 'plpgsql'; ALTER FUNCTION rpm.rpmstrcmp(string1 character varying, string2 character varying) OWNER TO spaceuser; I incorporated that into a duplicate of our postgres database. Then I ran the following query on both the unpatched and modified DBs select sp.server_id, count(*) FROM (SELECT sp_sp.server_id, sp_sp.name_id, sp_sp.package_arch_id, max(sp_pe.evr) AS max_evr FROM rhnServerPackage sp_sp join rhnPackageEvr sp_pe ON sp_pe.id = sp_sp.evr_id GROUP BY sp_sp.server_id, sp_sp.name_id, sp_sp.package_arch_id) sp join rhnPackage p ON p.name_id = sp.name_id join rhnPackageEvr pe ON pe.id = p.evr_id AND sp.max_evr < pe.evr join rhnPackageUpgradeArchCompat puac ON puac.package_arch_id = sp.package_arch_id AND puac.package_upgrade_arch_id = p.package_arch_id join rhnServerChannel sc ON sc.server_id = sp.server_id join rhnChannelPackage cp ON cp.package_id = p.id AND cp.channel_id = sc.channel_id group by sp.server_id order by sp.server_id; Wherever there were differences in package counts, I looked at the individual package evrs with select sp.server_id, p.id, sp.max_evr, pe.evr FROM (SELECT sp_sp.server_id, sp_sp.name_id, sp_sp.package_arch_id, max(sp_pe.evr) AS max_evr FROM rhnServerPackage sp_sp join rhnPackageEvr sp_pe ON sp_pe.id = sp_sp.evr_id GROUP BY sp_sp.server_id, sp_sp.name_id, sp_sp.package_arch_id) sp join rhnPackage p ON p.name_id = sp.name_id join rhnPackageEvr pe ON pe.id = p.evr_id AND sp.max_evr < pe.evr join rhnPackageUpgradeArchCompat puac ON puac.package_arch_id = sp.package_arch_id AND puac.package_upgrade_arch_id = p.package_arch_id join rhnServerChannel sc ON sc.server_id = sp.server_id join rhnChannelPackage cp ON cp.package_id = p.id AND cp.channel_id = sc.channel_id where sp.server_id IN (<server id list with discrepancies>) order by sp.server_id, p.id; The updated SP corrected the erroneous false positives, and also caught some packages which needed to be updated but weren't listed. Can somebody put this in a PR? Thanks, Paul-Andre _______________________________________________ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list