[Spacewalk-list] Modification of PGSQL queries

[Michael Mraka]

Pierre Casenove wrote:
% Hello,
% After getting rid of hotmail, I'll try to send well formatted mail from
% gmail...
% So, I've continued to modify queries having (+) in them to get them PGSQL
% compatible.
% I attach the diff output. i've tested the queries from psql command line as
% I didn't found where there were used in spacewalk.
% Here are the modified queries:
% contact_method_queries.xml : query "orgs_contact_method_tree"
% CustomInfo_queries.xml : query "custom_info_keys_sans_value_for_system"
% SystemGroup_queries.xml : query "visible_to_user_overview_fast" and
% query "visible_groups_summary"
% probe_queries.xml : query "system_probes"

Hi Pierre,

I reviewed your patches and have a couple of comments:

% Here are the queries I can't find how to modify:
% config_queries.xml : query "configfiles_for_system"
% The keywork NVL2 is used, but when I replace it by COALESCE keyword, I get:
% ERROR:  invalid input syntax for integer: "Y"
% LINE 5: ...CFt WHERE CFT.latest_config_revision_id = CR.id), 'Y', 'N') ...

COALESCE outputs first non-null parameter so it can't substitute NVL2 here.
The CASE ... END is right way. See
https://fedorahosted.org/spacewalk/wiki/PostgreSQLPortingGuide#TheDECODENVL2functionsproblem

As for the 'invalid input syntax for integer: "Y"' - in PostgreSQL COALESCE
expects that all parameters are of the same type which not the case
here: 1 is integer while 'Y' is varchar. So it could be rewritten as 
COALESCE((select 'Y' from ...), 'N'). Moreover there is one more
difference in Oracle's varchar2 and PostgreSQL varchar why I ended up
with NVL((select 'Y' from ...), 'N'). For the explanation see
https://fedorahosted.org/spacewalk/wiki/PostgreSQLPortingGuide#TheNVLfunctionproblem


% Package_queries.xml : query "system_upgradable_package_list":
% When I put LEFT JOIN in the query, it crash with error
% ERROR:  syntax error at or near "VERSION"
% LINE 9:         full_list.evr.version VERSION,
% If I replace "VERSION" by "FOO", it crashes with error
% ERROR:  schema "full_list" does not exist

Although the error message is different this is exactly
https://fedorahosted.org/spacewalk/wiki/PostgreSQLPortingGuide#SELECTcolumnASalias issue.

% I hope this helps.
% Please let me know if the changes are commited too the master branch.
% 
% Could you please help me on the last two queries I can't get to work? I
% can't find anything in the postgresql porting guide.
% 
% I hope my email will be readable.
% 
% Pierre

Please use 'diff -u' to create patches or even better checkout git
repository and send output of 'git format-patch'. It's hard to see
actual changes in the standard diff's output.

% diff contact_method_queries.xml /root/contact_method_queries.xml.orig
% 31,38c31,36
% <        CASE MT.method_type_name
% <          WHEN 'Email' THEN CM.email_address
% <          WHEN 'Pager' THEN CM.pager_email END method_target
% <   FROM web_contact WC
% <   RIGHT JOIN rhn_contact_methods CM
% <   ON CM.contact_id  = WC.id

This RIGHT JOIN is wrong. You've been probably misleaded by the fact that (+)
sign is on the opposite side of = than usual. Well, it's the order of tables
in 'FROM X JOIN Y' not order of columns in 'ON X.column1 = Y.column2'  
what does matter. So LEFT JOIN means return all rows from the left table,
even if there are no matches in the right table.

And one more notice: you can almost forgot about RIGHT JOIN, it's very rearly
needed. In 99.99% you can use LEFT JOIN which is more natural.

% <   LEFT JOIN rhn_method_types MT
% <   ON MT.recid = CM.method_type_id
...
% diff probe_queries.xml /root/probe_queries.xml.orig
% 23a24
% >        rhn_probe P,
% 25,27c26
% <        rhn_probe P
% <   RIGHT JOIN rhn_probe_state PS
% <     ON PS.probe_id = P.recid
% ---
% >        rhn_probe_state PS
% 31a31
% >    AND PS.probe_id(+) = P.recid

Please be carefull about syntax; there have to be a comma after the join
if it isn't the last in the list:
  FROM rhn_sat_cluster SC,
       rhn_command C,
       rhn_probe P
  LEFT JOIN rhn_probe_state PS
    ON PS.probe_id = P.recid,
       rhn_check_probe CP



I've corrected and commited your changes in master. The fixed package is
spacewalk-base-1.6.10-1.
Thank you for your contribution.

Regards,

--
Michael Mráka
Satellite Engineering, Red Hat