Обсуждение: Small change needed to support Greenplum DB

<font face="Verdana, Helvetica, Arial"><span style="font-size:14.0px">Hi all,<br /><br /> A customer of ours is using
pgAgentwith Greenplum Database and needs a change to one of the queries used by pgAgent to allow it to work with
Greenplum.<br/><br /> Here is the conversation:<br />   </span></font><font color="#0000FF"><font face="Arial"><span
style="font-size:13.0px"><u><a
href="http://groups.google.com/group/pgsql.bugs/browse_thread/thread/27866354fa4d7a5e/60e6e5de3c5156d4#60e6e5de3c5156d4">http://groups.google.com/group/pgsql.bugs/browse_thread/thread/27866354fa4d7a5e/60e6e5de3c5156d4#60e6e5de3c5156d4</a><br
/><br/></u></span></font></font><font face="Verdana, Helvetica, Arial"><span style="font-size:14.0px">The problem is
thatGreenplum does not support some correlated subqueries, so Jon created a version of the query that is both faster
anddoes not use a correlated subquery.<br /><br /> The requested change is from this in pgaJob.cpp:<br /><br
/></span></font><fontsize="2"><font face="Courier, Courier New"><span style="font-size:12.0px">SELECT *, <br />
           (SELECTjlgstatus FROM pgagent.pga_joblog jl WHERE jl.jlgjobid = <br /> j.jobid ORDER BY jlgid DESC LIMIT 1)
ASjoblastresult <br />            FROM pgagent.pga_job j <br />          JOIN pgagent.pga_jobclass cl ON
cl.jclid=jobjclid<br />          LEFT OUTER JOIN pgagent.pga_jobagent ag ON ag.jagpid=jobagentid <br /> --       +
restriction+ <br />         ORDER BY jobname; <br /></span></font><span style="font-size:12.0px"><font face="Arial"><br
/></font></span></font><fontface="Verdana, Helvetica, Arial"><span style="font-size:14.0px">To this:<br
/></span></font><fontsize="2"><font face="Arial"><span style="font-size:12.0px"><br /></span></font><span
style="font-size:12.0px"><fontface="Courier, Courier New">select j.*, cl.*, ag.*, sub3.jlgstatus <br />    from
pgagent.pga_jobj join <br />         pgagent.pga_jobclass cl on cl.jclid=jobjclid left outer join <br />
        pgagent.pga_jobagentag on ag.jagpid=jobagentid <br />         join (select j2.jlgstatus, <br />
                     sub.jlgjobid<br />                 from pgagent.pga_joblog j2 join <br />
                     (selectjl.jlgjobid, <br />                              max(jl.jlgid) as max_jlgid <br />
                        frompgagent.pga_joblog jl <br />                        group by jl.jlgjobid) sub <br />
                     onsub.jlgjobid = j2.jlgjobid and <br />                         sub.max_jlgid = j2.jlgid) sub3 <br
/>        on sub3.jlgjobid = j.jobid <br /> --       + restriction + <br /> order by jobname;<br /></font><font
face="Arial"><br/></font></span></font><font face="Verdana, Helvetica, Arial"><span style="font-size:14.0px">-
Luke</span></font>

Luke Lonergan wrote:
> Hi all,
> 
> A customer of ours is using pgAgent with Greenplum Database and needs a
> change to one of the queries used by pgAgent to allow it to work with
> Greenplum.
> 
> Here is the conversation:
>   _http://groups.google.com/group/pgsql.bugs/browse_thread/thread/27866354fa4d7a5e/60e6e5de3c5156d4#60e6e5de3c5156d4
> 
> _The problem is that Greenplum does not support some correlated
> subqueries, so Jon created a version of the query that is both faster
> and does not use a correlated subquery.
> 
> The requested change is from this in pgaJob.cpp:


Hi Luke,

Does Greenplum support DISTINCT ON? Specifically,

SELECT j.*, cl.*, ag.*, sub.jlgstatus
FROM pgagent.pga_job j JOIN    pgagent.pga_jobclass cl ON cl.jclid=jobjclid LEFT OUTER JOIN    pgagent.pga_jobagent ag
ONag.jagpid=jobagentid JOIN    (SELECT DISTINCT ON (jlgjobid) jlgstatus, jlgjobid     FROM pgagent.pga_joblog     ORDER
BYjlgjobid, jlgid desc) sub ON sub.jlgjobid = j.jobid
 
--       + restriction +
ORDER BY jobname;

Regards, Dave.